MySQL Replication Commands

MySQL Replication Commands:

SQL Statements for Controlling Master Servers

• PURGE MASTER LOGS Syntax

PURGE {MASTER | BINARY} LOGS TO ‘log_name‘

PURGE {MASTER | BINARY} LOGS BEFORE ‘date‘

Deletes all the binary logs listed in the log index prior to the specified log or date. The logs also are removed from the list recorded in the log index file, so that the given log becomes the first.

Example:

PURGE MASTER LOGS TO ‘mysql-bin.010′;

PURGE MASTER LOGS BEFORE ‘2003-04-02 22:46:26′;

To safely purge logs, follow this procedure:

1. On each slave server, use SHOW SLAVE STATUS to check which log it is reading.

2. Obtain a listing of the binary logs on the master server with SHOW BINARY LOGS.

3. Determine the earliest log among all the slaves. This is the target log. If all the slaves are up to date, this is the last log on the list.

4. Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)

5. Purge all logs up to but not including the target log.

• RESET MASTER Syntax

Deletes all binary logs listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. This statement was named FLUSH MASTER before MySQL 3.23.26.

• SET SQL_LOG_BIN Syntax

SET SQL_LOG_BIN = {0|1}

Disables or enables binary logging for the current connection (SQL_LOG_BIN is a session variable) if the client that has the SUPER privilege. The statement is refused with an error if the client does not have that privilege. (Before MySQL 4.1.2, the statement was simply ignored in that case.)

• SHOW BINLOG EVENTS Syntax

SHOW BINLOG EVENTS

[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify ‘log_name‘, the first binary log is displayed. The LIMIT clause has the same syntax as for the SELECT statement. This statement is available as of MySQL 4.0.

• SHOW BINARY LOGS Syntax

SHOW BINARY LOGS

SHOW MASTER LOGS

Lists the binary log files on the server. This statement is used as part of the procedure described in Section 13.6.1.1, “PURGE MASTER LOGS Syntax”, that shows how to determine which logs can be purged.

mysql> SHOW BINARY LOGS;

+—————+———–+

| Log_name | File_size |

+—————+———–+

| binlog.000015 | 724935 |

| binlog.000016 | 733481 |

+—————+———–+

SHOW MASTER LOGS was added in MySQL 3.23.38. As of MySQL 4.1.1, you can also use SHOW BINARY LOGS, which is equivalent. The File_size column is displayed as of MySQL 5.0.7.

• SHOW MASTER STATUS Syntax

SHOW MASTER STATUS

Provides status information about the binary log files of the master.

Example:

mysql > SHOW MASTER STATUS;

+—————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+—————+———-+————–+——————+

| mysql-bin.003 | 73 | test | manual,mysql |

+—————+———-+————–+——————+

• SHOW SLAVE HOSTS Syntax

SHOW SLAVE HOSTS

Displays a list of replication slaves currently registered with the master. Only slaves started with the

–report-host=slave_name option are visible in this list.

The list is displayed on any server (not just the master server). The output looks like this:

mysql> SHOW SLAVE HOSTS;

+————+———–+——+———–+

| Server_id | Host | Port | Master_id |

+————+———–+——+———–+

| 192168010 | iconnect2 | 3306 | 192168011 |

| 1921680101 | athena | 3306 | 192168011 |

+————+———–+——+———–+

• Server_id: The unique server ID of the slave server, as configured in the server’s option file, or on the command line with –server-id=value.

• Host: The host name of the slave server, as configured in the server’s option file, or on the command line with –report-host=value. Note that this can differ from the machine name as configured in the operating system.

• Port: The port the slave server is listening on.

• Master_id: The unique server ID of the master server that the slave server is replicating from. Some MySQL versions report another variable, Rpl_recovery_rank. This variable was never used, and was eventually removed.

SQL Statements for Controlling Slave Servers

• CHANGE MASTER TO Syntax

CHANGE MASTER TO master_def [, master_def] …

master_def:

MASTER_HOST = ‘host_name‘

| MASTER_USER = ‘user_name‘

| MASTER_PASSWORD = ‘password‘

| MASTER_PORT = port_num

| MASTER_CONNECT_RETRY = count

| MASTER_LOG_FILE = ‘master_log_name‘

| MASTER_LOG_POS = master_log_pos

| RELAY_LOG_FILE = ‘relay_log_name‘

| RELAY_LOG_POS = relay_log_pos

| MASTER_SSL = {0|1}

| MASTER_SSL_CA = ‘ca_file_name‘

| MASTER_SSL_CAPATH = ‘ca_directory_name‘

| MASTER_SSL_CERT = ‘cert_file_name‘

| MASTER_SSL_KEY = ‘key_file_name‘

| MASTER_SSL_CIPHER = ‘cipher_list‘

CHANGE MASTER TO changes the parameters that the slave server uses for connecting to and communicating with the master server. It also updates the contents of the master.info and relay- log.info files.MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER provide information to the slave about how to connect to its master. The relay log options (RELAY_LOG_FILE and RELAY_LOG_POS) are available beginning with MySQL 4.0.

You want to set up the slave to replicate the master:

CHANGE MASTER TO

MASTER_HOST=’master2.mycompany.com’,

MASTER_USER=’replication’,

MASTER_PASSWORD=’bigs3cret’,

MASTER_PORT=3306,

MASTER_LOG_FILE=’master2-bin.001′,

MASTER_LOG_POS=4,

MASTER_CONNECT_RETRY=10;

• MASTER_POS_WAIT() Syntax

SELECT MASTER_POS_WAIT(‘master_log_file‘, master_log_pos)

This is actually a function, not a statement. It is used to ensure that the slave has read and executed events up to a given position in the master’s binary log. See Section 12.9.4, “Miscellaneous Functions”, for a full description.

• RESET SLAVE Syntax

RESET SLAVE

RESET SLAVE makes the slave forget its replication position in the master’s binary logs. This statement is meant to be used for a clean start: It deletes the master.info and relaylog. info files, all the relay logs, and starts a new relay log.

Note: All relay logs are deleted, even if they have not been completely executed by the slave SQL thread. (This is a condition likely to exist on a replication slave if you have issued a STOP SLAVE statement or if the slave is highly loaded.) Connection information stored in the master.info file is immediately reset using any values specified in the corresponding startup options. This information includes values such as master host, master port, master user, and master password. If the slave SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET SLAVE is issued, these replicated temporary tables are deleted on the slave. This statement was named FLUSH SLAVE before MySQL 3.23.26.

• SET GLOBAL SQL_SLAVE_SKIP_COUNTER Syntax

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N

This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement. This statement is valid only when the slave thread is not running. Otherwise, it produces an error. Before MySQL 4.0, omit the GLOBAL keyword from the statement.

• SHOW SLAVE STATUS Syntax

SHOW SLAVE STATUS

This statement provides status information on essential parameters of the slave threads. If you issue this statement using the mysql client, you can use a \G statement terminator rather than a semicolon to obtain a more readable vertical layout:

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: root

Master_Port: 3306

Connect_Retry: 3

Master_Log_File: gbichot-bin.005

Read_Master_Log_Pos: 79

Relay_Log_File: gbichot-relay-bin.005

Relay_Log_Pos: 548

Relay_Master_Log_File: gbichot-bin.005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 79

Relay_Log_Space: 552

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 8

Depending on your version of MySQL, you may not see all the fields just shown. In particular, several fields are present only as of MySQL 4.1.1. SHOW SLAVE STATUS returns the following fields:

• Slave_IO_State

A copy of the State field of the output of SHOW PROCESSLIST for the slave I/O thread.This tells you what the thread is doing: trying to connect to the master, waiting for events from the master, reconnecting to the master, and so on. Possible states are listed in Section 6.3, “Replication Implementation Details”. It is necessary to check this field for older versions of MySQL (prior to 4.1.14) because in these versions the thread could be running while unsuccessfully trying to connect to the master; only this field makes you aware of the connection problem.

The state of the SQL thread is not copied because it is simpler. If it is running, there is no problem; if it is not, you can find the error in the Last_Error field (described below). This field is present beginning with MySQL 4.1.1.

• Master_Host

The current master host.

• Master_User

The current user used to connect to the master.

• Master_Port

The current master port.

• Connect_Retry

The current value of the –master-connect-retry option.

• Master_Log_File

The name of the master binary log file from which the I/O thread is currently reading.

• Read_Master_Log_Pos

The position up to which the I/O thread has read in the current master binary log.

• Relay_Log_File

The name of the relay log file from which the SQL thread is currently reading and executing.

• Relay_Log_Pos

The position up to which the SQL thread has read and executed in the current relay log.

• Relay_Master_Log_File

The name of the master binary log file containing the most recent event executed by the SQLthread.

• Slave_IO_Running

Whether the I/O thread is started and has connected successfully to the master. For older versions of MySQL (prior to 4.1.14 and 5.0.12) Slave_IO_Running is YES if the I/O thread is started, even if the slave hasn’t connected to the master yet.

• Slave_SQL_Running

Whether the SQL thread is started.

• Replicate_Do_DB, Replicate_Ignore_DB

The lists of databases that were specified with the –replicate-do-db and - -replicate-ignore-db options, if any. These fields are present beginning with MySQL 4.1.1.

• Replicate_Do_Table, Replicate_Ignore_Table,

Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table

The lists of tables that were specified with the –replicate-do-table, –replicate-ignore-table, –replicate-wild-do-table, and –replicate-wild-ignore_table options, if any.These fields are present beginning with MySQL 4.1.1.

• Last_Errno, Last_Error

The error number and error message returned by the most recently executed query. An error number of 0 and message of the empty string mean “no error.” If the Last_Error value is not empty, it also appears as a message in the slave’s error log. For example:

Last_Errno: 1051

Last_Error: error ‘Unknown table ‘z” on query ‘drop table z’

The message indicates that the table z existed on the master and was dropped there, but it did not exist on the slave, so DROP TABLE failed on the slave. (This might occur, for example, if you forget to copy the table to the slave when setting up replication.)

• Skip_Counter

The most recently used value for SQL_SLAVE_SKIP_COUNTER.

• Exec_Master_Log_Pos

The position of the last event executed by the SQL thread from the master’s binary log

(Relay_Master_Log_File). (Relay_Master_Log_File, Exec_Master_Log_Pos) in the master’s binary log corresponds to (Relay_Log_File, Relay_Log_Pos) in the relay log.

• Relay_Log_Space

The total combined size of all existing relay logs.

• Until_Condition, Until_Log_File, Until_Log_Pos

The values specified in the UNTIL clause of the START SLAVE statement.

Until_Condition has these values:

• None if no UNTIL clause was specified

• Master if the slave is reading until a given position in the master’s binary logs

• Relay if the slave is reading until a given position in its relay logs Until_Log_File and Until_Log_Pos indicate the log filename and position values that define the point at which the SQL thread stops executing. These fields are present beginning with MySQL 4.1.1.

• Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, Master_SSL_Cipher, Master_SSL_Key

These fields show the SSL parameters used by the slave to connect to the master, if any. Master_SSL_Allowed has these values:

• Yes if an SSL connection to the master is permitted

• No if an SSL connection to the master is not permitted

• Ignored if an SSL connection is permitted but the slave server does not have SSL support enabled The values of the other SSL-related fields correspond to the values of the –master-ca, - -master-capath, –master-cert, –master-cipher, and –master-key options.These fields are present beginning with MySQL 4.1.1.

• Seconds_Behind_Master

This field is present beginning with MySQL 4.1.1. It is been experimental and has been changed in MySQL 4.1.9. The following applies to slaves running MySQL 4.1.9 or newer. This field is an indication of how “late” the slave is:

• When the slave SQL thread is actively running (processing updates), this field is the number of seconds that have elapsed since the timestamp of the most recent event on the master executed by that thread.

• START SLAVE Syntax

START SLAVE [thread_type [, thread_type] … ]

START SLAVE [SQL_THREAD] UNTIL

MASTER_LOG_FILE = ‘log_name‘, MASTER_LOG_POS = log_pos

START SLAVE [SQL_THREAD] UNTIL

RELAY_LOG_FILE = ‘log_name‘, RELAY_LOG_POS = log_pos

thread_type: IO_THREAD | SQL_THREAD

START SLAVE with no thread_type options starts both of the slave threads. The I/O thread reads queries from the master server and stores them in the relay log. The SQL thread reads the relay log and executes the queries. START SLAVE requires the SUPER privilege. If START SLAVE succeeds in starting the slave threads, it returns without any error. However, even in that case, it might be that the slave threads start and then later stop (for example, because they do not manage to connect to the master or read its binary logs, or some other problem).

START SLAVE does not warn you about this. You must check the slave’s error log for error messages generated by the slave threads, or check that they are running satisfactorily with SHOW SLAVE STATUS. As of MySQL 4.0.2, you can add IO_THREAD and SQL_THREAD options to the statement to name which of the threads to start. As of MySQL 4.1.1, an UNTIL clause may be added to specify that the slave should start and run until the SQL thread reaches a given point in the master binary logs or in the slave relay logs. When the SQL thread reaches that point, it stops. If the SQL_THREAD option is specified in the statement, it starts only the SQL thread. Otherwise, it starts both slave threads. If the SQL thread is running, the UNTIL clause is ignored and a warning is issued.For an UNTIL clause, you must specify both a log filename and position. Do not mix master and relay log options.

Any UNTIL condition is reset by a subsequent STOP SLAVE statement, a START SLAVE statement that includes no UNTIL clause, or a server restart. The UNTIL clause can be useful for debugging replication, or to cause replication to proceed until just before the point where you want to avoid having the slave replicate a statement. For example, if an unwise DROP TABLE statement was executed on the master, you can use UNTIL to tell the slave to execute up to that point but no farther. To find what the event is, use mysqlbinlog with the master logs or slave relay logs, or by using a SHOW BINLOG EVENTS statement. If you are using UNTIL to have the slave process replicated queries in sections, it is recommended that you start the slave with the –skip-slave-start option to prevent the SQL thread from running when the slave server starts. It is probably best to use this option in an option file rather than on the command line, so that an unexpected server restart does not cause it to be forgotten. The SHOW SLAVE STATUS statement includes output fields that display the current values of the UNTIL condition. This statement is called SLAVE START before MySQL 4.0.5. SLAVE START is still accepted for backward compatibility, but is now deprecated.

• STOP SLAVE Syntax

STOP SLAVE [thread_type [, thread_type] … ]

thread_type: IO_THREAD | SQL_THREAD

Stops the slave threads. STOP SLAVE requires the SUPER privilege. Like START SLAVE, as of MySQL 4.0.2, this statement may be used with the IO_THREAD and SQL_THREAD options to name the thread or threads to be stopped. This statement is called SLAVE STOP before MySQL 4.0.5. SLAVE STOP is still accepted for backward compatibility, but is deprecated.

Replication Features and Known Problems

Replication Features and Known Problems

In general, replication compatibility at the SQL level requires that any features used be supported by both the master and the slave servers. If you use a feature on a master server that is available only as of a given version of MySQL, you cannot replicate to a slave that is older than that version. Such incompatibilities are likely to occur between series, so that, for example, you cannot replicate from MySQL 5.0 to 4.1. However, these incompatibilities also can occur for within-series replication. For example, the SLEEP() function is available in MySQL 5.0.12 and up. If you use this function on the master server, you cannot replicate to a slave server that is older than MySQL 5.0.12. If you are planning to use replication between 5.0 and a previous version of MySQL you should consult the edition of the MySQL Reference Manual corresponding to the earlier release series for information regarding the replication characteristics of that series.

• Known issue:

In MySQL 5.0.17, the syntax for CREATE TRIGGER changed to include a DEFINER clause for specifying which access privileges to check at trigger invocation time. However, if you attempt to replicate from a master server older than MySQL 5.0.17 to a slave running MySQL 5.0.17 through 5.0.19, replication of CREATE TRIGGER statements fails on the slave with a Replication Definer not fully qualified error. A workaround is to create triggers on the master using a version-specific comment embedded in each CREATE TRIGGER statement:

CREATE /*!50017 DEFINER = ‘root’@'localhost’ */ TRIGGER … ;

CREATE TRIGGER statements written this way will replicate to newer slaves, which pick up the DEFINER clause from the comment and execute successfully. This slave problem is fixed as of MySQL 5.0.20.

• Replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly, subject to the following exceptions. A stored procedure that uses LAST_INSERT_ID() does not replicate properly. When a statement uses a stored function that inserts into an AUTO_INCREMENT column, the generated AUTO_INCREMENT value is not written into the binary log, so a different value can in some cases be inserted on the slave. Adding an AUTO_INCREMENT column to a table with ALTER TABLE might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT number. Assuming that you want to add an AUTO_INCREMENT column to the table t1, the following statements produce a new table t2 identical to t1 but with an AUTO_INCREMENT column:

CREATE TABLE t2 LIKE t1;

ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;

INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

This assumes that the table t1 has columns col1 and col2.

Important:

To guarantee the same ordering on both master and slave, all columns of t1 must be referenced in the ORDER BY clause.

The instructions just given are subject to the limitations of CREATE TABLE … LIKE: Foreign key definitions are ignored, as are the DATA DIRECTORY and INDEX DIRECTORY table options. If a table definition includes any of those characteristics, create t2 using a CREATE TABLE statement that is identical to the one used to create t1, but with the addition of the AUTO_INCREMENT column.

Regardless of the method used to create and populate the copy having the AUTO_INCREMENT column, the final step is to drop the original table and then rename the copy:

DROP t1;

ALTER TABLE t2 RENAME t1;

• Certain functions do not replicate well under some conditions:

• The USER(), CURRENT_USER(), UUID(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave.

• As of MySQL 5.0.13, the SYSDATE() function is no longer equivalent to NOW(). Implications are that SYSDATE() is not replication-safe because it is not affected by SET TIMESTAMP statements in the binary log and is non-deterministic. To avoid this, you can start the server with the –sysdate-is-now option to cause SYSDATE() to be an alias for NOW().

• The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK()

functions that handle user-level locks are replicated without the slave knowing the concur-Replication rency context on master. Therefore, these functions should not be used to insert into a master’s table because the content on the slave would differ. (For example, do not issue a statement

such as INSERT INTO mytable VALUES(GET_LOCK(…)).)

As a workaround for the preceding limitations, you can use the strategy of saving the problematic function result in a user variable and referring to the variable in a later statement. For example, the following single-row INSERT is problematic due to the reference to the UUID() function:

INSERT INTO t VALUES(UUID());

To work around the problem, do this instead:

SET @my_uuid = UUID();

INSERT INTO t VALUES(@my_uuid);

That sequence of statements replicates because the value of @my_uuid is stored in the binary log as a user-variable event prior to the INSERT statement and is available for use in the INSERT.

The same idea applies to multiple-row inserts, but is more cumbersome to use. For a two-row insert, you can do this:

SET @my_uuid1 = UUID(); @my_uuid2 = UUID();

INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);

However, if the number of rows is large or unknown, the workaround is difficult or impracticable. For example, you cannot convert the following statement to one in which a given individual user variable is associated with each row:

INSERT INTO t2 SELECT UUID(), * FROM t1;

• User privileges are replicated only if the mysql database is replicated. That is, the GRANT, REVOKE, SET PASSWORD, CREATE USER, and DROP USER statements take effect on the slave only if the replication setup includes the mysql database. If you’re replicating all databases, but don’t want statements that affect user privileges to be replicated, set up the slave to not replicate the mysql database, using the - -replicate-wild-ignore-table=mysql.% option. The slave will recognize that issuing privilege-related SQL statements won’t have an effect, and thus not execute those statements.

• The FOREIGN_KEY_CHECKS, SQL_MODE, UNIQUE_CHECKS, and QL_AUTO_IS_NULL variables are all replicated in MySQL 5.0. The storage_engine system variable (also known as table_type) is not yet replicated, which is a good thing for replication between different storage engines.

• Starting from MySQL 5.0.3 (master and slave), replication works even if the master and slave have different global character set variables. Starting from MySQL 5.0.4 (master and slave), replication works even if the master and slave have different global time zone variables.

• The following applies to replication between MySQL servers that use different character sets:

1. If the master uses MySQL 4.1, you must always use the same global character set and collation on the master and the slave, regardless of the MySQL version running on the slave. (These are controlled by the –character-set-server and - -collation-server options.) Otherwise, you may get duplicate-key errors on the slave, because a key that is unique in the master character set might not be unique in the slave character set. Note that this is not a cause for concern when master and slave are both MySQL 5.0 or later.

2. If the master is older than MySQL 4.1.3, the character set of any client should never be Replication. made different from its global value because this character set change is not known to the slave. In other words, clients should not use SET NAMES, SET CHARACTER SET, and so forth. If both the master and the slave are 4.1.3 or newer, clients can freely set session values for character set variables because these settings are written to the binary log and so are known to the slave. That is, clients can use SET NAMES or SET CHARACTER SET or can set variables such as collation_client or collation_server. However, clients are prevented from changing the global value of these variables; as stated previously, the master and slave must always have identical global character set values.

3. If you have databases on the master with character sets that differ from the global character_set_server value, you should design your CREATE TABLE statements so that tables in those databases do not implicitly rely on the database default character set. A good workaround is to state the character set and collation explicitly in CREATE TABLE statements.

• If the master uses MySQL 4.1, the same system time zone should be set for both master and slave. Otherwise some statements will not be replicated properly, such as statements that use the NOW() or FROM_UNIXTIME() functions. You can set the time zone in which MySQL server runs by using the –timezone=timezone_name option of the mysqld_safe script or by setting the TZ environment variable. Both master and slave should also have the same default connection time zone setting; that is, the –default-time-zone parameter should have the same value for both master and slave. Note that this is not necessary when the master is MySQL 5.0 or later.

• CONVERT_TZ(…,…,@@global.time_zone) is not properly replicated. CONVERT_ TZ(…,…,@@session.time_zone) is properly replicated only if the master and slave are from MySQL 5.0.4 or newer.

• Session variables are not replicated properly when used in statements that update tables. For example, SET MAX_JOIN_SIZE=1000 followed by INSERT INTO mytable VALUES(@@ MAX_JOIN_SIZE) will not insert the same data on the master and the slave. This does not apply to the common sequence of SET TIME_ZONE=… followed by INSERT INTO mytable VALUES(CONVERT_TZ(…,…,@@time_zone)), which replicates correctly as of MySQL 5.0.4.

• It is possible to replicate transactional tables on the master using non-transactional tables on the slave. For example, you can replicate an InnoDB master table as a MyISAM slave table. However, if you do this, there are problems if the slave is stopped in the middle of a BEGIN/COMMIT block because the slave restarts at the beginning of the BEGIN block.

• Update statements that refer to user-defined variables (that is, variables of the form @var_name) are replicated correctly in MySQL 5.0. However, this is not true for versions prior to 4.1. Note that user variable names are case insensitive starting in MySQL 5.0. You should take this into account when setting up replication between MySQL 5.0 and older versions.

• Slaves can connect to masters using SSL.

• Views are always replicated to slaves. Views are filtered by their own name, not by the tables they refer to. This means that a view can be replicated to the slave even if the view contains a table that would normally be filtered out by replication-ignore-table rules. Care should therefore be taken to ensure that views do not replicate table data that would normally be filtered for security reasons.

• In MySQL 5.0 (starting from 5.0.3), there is a global system variable slave_transaction_retries: If the replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because it exceeded the InnoDB innodb_lock_wait_timeout or the NDBCluster TransactionDeadlockDetection- Timeout or TransactionInactiveTimeout value, the transaction automatically retries slave_transaction_retries times before stopping with an error. The default value is 10. Starting from MySQL 5.0.4, the total retry count can be seen in the output of SHOW STATUS;

• If a DATA DIRECTORY or INDEX DIRECTORY table option is used in a CREATE TABLE statement on the master server, the table option is also used on the slave. This can cause problems if no corresponding directory exists in the slave host filesystem or if it exists but is not accessible to the slave server. MySQL supports an sql_mode option called NO_DIR_IN_CREATE. If the slave server is run with this SQL mode enabled, it ignores the DATA DIRECTORY and INDEX DIRECTORY table options when replicating CREATE TABLE statements. The result is that MyISAM data and index files are created in the table’s database directory.

• It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, left to the will of the query optimizer. (This is in general not a good practice, even outside of replication.)

• Using LOAD TABLE FROM MASTER where the master is running MySQL 4.1 and the slave is running MySQL 5.0 may corrupt the table data, and is not supported. (Bug#16261 [http://bugs.mysql.com/16261])

• The following applies only if either the master or the slave is running MySQL version 5.0.3 or older: If on the master a LOAD DATA INFILE is interrupted (integrity constraint violation, killed connection, and so on), the slave skips the LOAD DATA INFILE entirely. This means that if this command permanently inserted or updated table records before being interrupted, these modifications are not replicated to the slave.

• Some forms of the FLUSH statement are not logged because they could cause problems if replicated to a slave: FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK. For a syntax example, see Section 13.5.5.2, “FLUSH Syntax”. The FLUSH TABLES, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements are written to the binary log and thus replicated to slaves. This is not normally a problem because these statements do not modify table data. However, this can cause difficulties under certain circumstances. If you replicate the privilege tables in the mysql database and update those tables directly without using GRANT, you must issue a FLUSH PRIVILEGES on the slaves to put the new privileges into effect. In addition, if you use FLUSH TABLES when renaming a MyISAM table that is part of a MERGE table, you must issue FLUSH TABLES manually on the slaves.

These statements are written to the binary log unless you specify NO_WRITE_TO_BINLOG or its alias LOCAL.

• When a server shuts down and restarts, its MEMORY (HEAP) tables become empty. The master replicates this effect to slaves as follows: The first time that the master uses each MEMORY table after startup, it logs an event that notifies the slaves that the table needs to be emptied by writing a DELETE statement for that table to the binary log.

• Temporary tables are replicated except in the case where you shut down the slave server (not just the slave threads) and you have replicated temporary tables that are used in updates that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates are no longer available When the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure:

1. Issue a STOP SLAVE statement.

2. Use SHOW STATUS to check the value of the Slave_open_temp_tables variable.

3. If the value is 0, issue a mysqladmin shutdown command to stop the slave.

4. If the value is not 0, restart the slave threads with START SLAVE.

5. Repeat the procedure later until the Slave_open_temp_tables variable is 0 and you can stop the slave.

• The syntax for multiple-table DELETE statements that use table aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows should be deleted:

DELETE test FROM test AS t1, test2 WHERE …

In MySQL 4.1, you must use the alias:

DELETE t1 FROM test AS t1, test2 WHERE …

If you use such DELETE statements, the change in syntax means that a 4.0 master cannot replicate to 4.1 (or higher) slaves.

• It is safe to connect servers in a circular master/slave relationship if you use the - -log-slave-updates option. That means that you can create a setup such as this:

A -> B -> C -> A

However, many statements do not work correctly in this kind of setup unless your client code is written to take care of the potential problems that can occur from updates that occur in different sequence on different servers. Server IDs are encoded in binary log events, so server A knows when an event that it reads was originally created by itself and does not execute the event (unless server A was started with the –replicate-same-server-id option, which is meaningful only in rare cases). Thus, there are no infinite loops. This type of circular setup works only if you perform no conflicting updates between the tables. In other words, if you insert data in both A and C, you should never insert a row in A that may have a key that conflicts with a row inserted in C. You should also not update the same rows on two servers if the order in which the updates are applied is significant.

• If a statement on a slave produces an error, the slave SQL thread terminates, and the slave writes a message to its error log. You should then connect to the slave manually and determine the cause of the problem. (SHOW SLAVE STATUS is useful for this.) Then fix the problem (for example, you might need to create a non-existent table) and run START SLAVE.

• It is safe to shut down a master server and restart it later. When a slave loses its connection to the master, the slave tries to reconnect immediately and retries periodically if that fails. The default is to retry every 60 seconds. This may be changed with the - -master-connect-retry option. A slave also is able to deal with network connectivity outages. However, the slave notices the network outage only after receiving no data from the master for slave_net_timeout seconds. If your outages are short, you may want to decrease slave_net_timeout.

• Shutting down the slave (cleanly) is also safe because it keeps track of where it left off. Unclean shutdowns might produce problems, especially if the disk cache was not flushed to disk before the system went down. Your system fault tolerance is greatly increased if you have a good uninterruptible power supply. Unclean shutdowns of the master may cause inconsistencies between the content of tables and the binary log in master; this can be avoided by using InnoDB tables and the –innodb-safe-binlog option on the master.

Note: –innodb-safe-binlog is unneeded as of MySQL 5.0.3, having been made obsolete by the introduction of XA transaction support.

• A crash on the master side can result in the master’s binary log having a final position less than the most recent position read by the slave, due to the master’s binary log file not being flushed. This can cause the slave not to be able to replicate when the master comes back up. Setting sync_binlog=1 in the master my.cnf file helps to minimize this problem because it causes the master to flush its binary log more frequently.

• Due to the non-transactional nature of MyISAM tables, it is possible to have a statement that only partially updates a table and returns an error code. This can happen, for example, on a multiple- row insert that has one row violating a key constraint, or if a long update statement is killed after updating some of the rows. If that happens on the master, the slave thread exits and waits for the database administrator to decide what to do about it unless the error code is legitimate and execution of the statement results in the same error code on the slave. If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the - -slave-skip-errors option.

• If you update transactional tables from non-transactional tables inside a BEGIN/COMMIT sequence, updates to the binary log may be out of synchrony with table states if the nontransactional table is updated before the transaction commits. This occurs because the transaction is written to the binary log only when it is committed.

• In situations where transactions mix updates to transactional and non-transactional tables, the order of statements in the binary log is correct, and all needed statements are written to the binary log even in case of a ROLLBACK. However, when a second connection updates the nontransactional table before the first connection’s transaction is complete, statements can be logged out of order, because the second connection’s update is written immediately after it is performed, regardless of the state of the transaction being performed by the first connection.

• Floating-point values are approximate, so comparisons involving them are inexact. This is true for operations that use floating-point values explicitly, or values that are converted to floatingpoint implicitly. Comparisons of floating-point values might yield different results on master and slave servers due to differences in computer architecture, the compiler used to build MySQL, and so forth.