Chapter 14. Replication

Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup.

Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers. SELECT queries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of synchrony. This load-balancing strategy is effective if nonupdating queries dominate, but that is the normal case.

Another benefit of using replication is that you can perform database backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made. See Section 6.1, “Database Backups”.

Slave I/O thread.  When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.

The slave I/O thread reads the updates that the master' Binlog Dump thread sends (see next item) and copies them to local files — known as relay logs ‐ in the slave's data directory.

The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

Binlog dump thread.  The master creates a thread to send the binary log contents to the slave. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.

The binlog dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

Slave SQL thread.  The slave creates this thread to read the relay logs that were written by the slave I/O thread. The slave SQL thread is also used to execute the updates contained in the relay logs.

Use one or more ALTER TABLE statements on the master to change the names of any database objects where these names would be considered reserved words on the slave, and change any SQL statements that use the old names to use the new names instead.

In any SQL statements using these database object names, set the names off using backtick characters (`).

Replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly, subject to the following exceptions.

INSERT DELAYED ... VALUES(LAST_INSERT_ID()) inserts a different value on the master and the slave. (Bug#20819)

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.

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;

See also Section A.1.7.1, “Problems with ALTER TABLE”.

The USER(), CURRENT_USER(), UUID(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave. This is also true for CONNECTION_ID() in slave versions older than 4.1.1. The new PASSWORD() function in MySQL 4.1 is well replicated in masters from 4.1.1 and up; your slaves also must be 4.1.1 or above to replicate it. If you have older slaves and need to replicate PASSWORD() from your 4.1.x master, you must start your master with the --old-passwords option, so that it uses the old implementation of PASSWORD(). (Note that the PASSWORD() implementation in MySQL 4.1.0 differs from every other version of MySQL. It is best to avoid 4.1.0 in a replication scenario.)

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 concurrency 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(...)).)

The FOUND_ROWS() function is also not replicated reliably. A workaround is to store the result of the function call in a user variable, and then use that in the INSERT statement. For example, if you wish to store the result in a table named mytable, you might normally do so like this:

SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
INSERT INTO mytable VALUES( FOUND_ROWS() );

However, if you are replicating mytable, then you should use SELECT INTO, and then store the variable in the table, like this:

SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
INSERT INTO mytable VALUES(@found_rows);

In this way, the user variable is replicated as part of the context, and applied on the slave correctly.

User privileges are replicated only if the mysql database is replicated. That is, the GRANT, REVOKE, SET PASSWORD, and DROP USER (available as of MySQL 4.1.1) 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. That option is available as of MySQL 4.0.13. 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 variable is replicated as of MySQL 4.0.14. The sql_mode, unique_checks, sql_auto_is_null, and storage_engine (also known as table_type) variables are not replicated in MySQL 4.1 or earlier versions.

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

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. Also starting from version 4.1.3 both master and slave should have the same default connection time zone set, that is the --default-time-zone parameter should have the same value for both master and slave.

CONVERT_TZ(...,...,@@global.time_zone) is not properly replicated.

Session variables are not replicated properly when used in statements which update tables; for example: SET MAX_JOIN_SIZE=1000; INSERT INTO mytable VALUES(@@MAX_JOIN_SIZE); will not insert the same data on the master and on the slave.

It is possible to replicate transactional tables on the master using nontransactional tables on the slave. For example, you can replicate an InnoDB master table as a MyISAM slave table. However, there are issues that you should consider before you do this:

Update statements that refer to user-defined variables (that is, variables of the form @var_name) are badly replicated in 3.23 and 4.0. This is fixed in 4.1.

The slave can connect to the master using SSL if both are 4.1.1 or newer.

Starting from MySQL 4.1.11, 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 TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout value, the transaction automatically retries slave_transaction_retries times before stopping with an error. The default value is 0 in MySQL 4.1. Starting from MySQL 4.1.11, the total retry count can be seen in SHOW STATUS; see Section 5.1.6, “Server Status Variables”.

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 file system or if it exists but is not accessible to the slave server. As of MySQL 4.0.15, there is 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 nondeterministic; that is, left to the will of the query optimizer. (This is in general not a good practice, even outside of replication.) For a detailed explanation of this issue, see Section A.1.8.4, “Open Issues in MySQL”.

If on the master a LOAD DATA INFILE is interrupted (for example, by a integrity constraint violation or a killed connection), the slave skips this 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.

In addition, LOAD DATA INFILE does not replicate correctly when --binlog-do-db is used. (Bug#19662)

LOAD DATA INFILE also does not replicate well from 4.0 and earlier masters to 5.1 or later slaves. In such cases, it is best to upgrade the master to 5.0 or later. (Bug#31240)

The LOAD DATA INFILE statement's CONCURRENT option is not replicated; that is, LOAD DATA CONCURRENT INFILE is replicated as LOAD DATA INFILE, and LOAD DATA CONCURRENT LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE. (Bug#34628)

Before MySQL 4.1.1, the FLUSH, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements are not written to the binary log and thus are not replicated to the slaves. This is not normally a problem because these statements do not modify table data. However, it can cause difficulties under certain circumstances. If you replicate the privilege tables in the mysql database and update those tables directly without using the GRANT statement, you must issue a FLUSH PRIVILEGES statement on your slaves to put the new privileges into effect. Also if you use FLUSH TABLES when renaming a MyISAM table that is part of a MERGE table, you have to issue FLUSH TABLES manually on the slaves. As of MySQL 4.1.1, these statements are written to the binary log (unless you specify NO_WRITE_TO_BINLOG, or its alias LOCAL). Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. (Any of these may cause problems if replicated to a slave.) For a syntax example, see Section 12.5.6.2, “FLUSH Syntax”.

MySQL 4.1 and earlier support only replication scenarios involving one master and many slaves.

When a server shuts down and restarts, its MEMORY (HEAP) tables become empty. As of MySQL 4.0.18, 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. See Section 13.4, “The MEMORY (HEAP) Storage Engine”, for more information about MEMORY tables.

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:

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 nonexistent 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 CHANGE MASTER TO statement or --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. See Section 5.1.3, “Server System Variables”.

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. See Section 5.3.4, “The Binary Log”.

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 nontransactional 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. This option is available starting with MySQL 3.23.47.

If you update transactional tables from nontransactional 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.

Before version 4.0.15, any update to a nontransactional table is written to the binary log at once when the update is made, whereas transactional updates are written on COMMIT or not written at all if you use ROLLBACK. You must take this into account when updating both transactional tables and nontransactional tables within the same transaction. (This is true not only for replication, but also if you are using binary logging for backups.)

As of version 4.0.15, we changed the logging behavior for transactions that mix updates to transactional and nontransactional tables, which solves the problems (order of statements is good in the binary log, and all needed statements are written to the binary log even in case of ROLLBACK). The problem that remains is that when a second connection updates the nontransactional table while the first connection's transaction is not finished yet, incorrect ordering can still occur because the second connection's update is written immediately after it is done.

When a 4.x slave replicates a LOAD DATA INFILE from a 3.23 master, the values of the Exec_Master_Log_Pos and Relay_Log_Space columns of SHOW SLAVE STATUS become incorrect. The inaccuracy in Exec_Master_Log_Pos causes problems when you stop and restart replication; so it is a good idea to correct the value before this, by doing FLUSH LOGS on the master.

Replication slaves do not write replicated queries to the slow query log, even if the same queries were written to the slow query log on the master. This is a known issue which we intend to fix in a future version of MySQL. (Bug#23300)

DELETE, UPDATE, and INSERT ... SELECT statements containing a LIMIT clause are not guaranteed to produce the same result on the slave as on the master, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause.

LOAD DATA INFILE is handled properly, as long as the data file still resides on the master server at the time of update propagation.

LOAD DATA LOCAL INFILE is no longer skipped on the slave as it was in 3.23.

In 3.23, RAND() in updates does not replicate properly. Use RAND(some_non_rand_expr) if you are replicating updates with RAND(). You can, for example, use UNIX_TIMESTAMP() as the argument to RAND().

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 floating-point 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. See Section 11.2.2, “Type Conversion in Expression Evaluation”, and Section A.1.5.8, “Problems with Floating-Point Comparisons”.

No: Permit the statement and proceed to the table-checking stage.

Yes: Test the options using the same rules as for the --binlog-do-db and --binlog-ignore-db options to determine whether to permit or ignore the statement. What is the result of the test?

No --replicate-* options at all

The slave executes all statements that it receives from the master.

--replicate-*-db options, but no table options

The slave permits or ignores statements using the database options. Then it executes all statements permitted by those options because there are no table restrictions.

--replicate-*-table options, but no database options

All statements are permitted at the database-checking stage because there are no database conditions. The slave executes or ignores statements based on the table options.

A mix of database and table options

The slave permits or ignores statements using the database options. Then it evaluates all statements permitted by those options according to the table options. In some cases, this process can yield what might seem a counterintuitive result. Consider the following set of options:

[mysqld]
replicate-do-db    = db1
replicate-do-table = db2.mytbl2

Suppose that db1 is the default database and the slave receives this statement:

INSERT INTO mytbl1 VALUES(1,2,3);

The database is db1, which matches the --replicate-do-db option at the database-checking stage. The algorithm then proceeds to the table-checking stage. If there were no table options, the statement would be executed. However, because the options include a “do” table option, the statement must match if it is to be executed. The statement does not match, so it is ignored. (The same would happen for any table in db1.)

safe_writer_connect()

safe_reader_connect()

safe_reader_statement()

safe_writer_statement()

If N = 0 (which means we have no replication), our system can handle about 1200/11 = 109 writes per second.

If N = 1, we get up to 184 writes per second.

If N = 8, we get up to 400 writes per second.

If N = 17, we get up to 480 writes per second.

Eventually, as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only eight servers, we increase it nearly four times.

What is the read/write ratio on your system?

How much more write load can one server handle if you reduce the reads?

For how many slaves do you have bandwidth available on your network?

To tell a slave to change its master, use the CHANGE MASTER TO statement.

A good way to keep your applications informed as to the location of the master is by having a dynamic DNS entry for the master. With bind you can use nsupdate to dynamically update your DNS.

Run your slaves with the --log-bin option and without --log-slave-updates. In this way, the slave is ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO statement on the other slaves. For example, assume that you have the following setup:

       WC
        \
         v
 WC----> M
       / | \
      /  |  \
     v   v   v
    S1   S2  S3

In this diagram, M means the master, S the slaves, WC the clients issuing database writes and reads; clients that issue only database reads are not represented, because they need not switch. S1, S2, and S3 are slaves running with --log-bin and without --log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless --log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason M becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick S1, all WC should be redirected to S1, which will log updates to its binary log. S2 and S3 should then replicate from S1.

The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. Suppose that S1 has --log-slave-updates enabled. Then it will write updates that it receives from M to its own binary log. When S2 changes from M to S1 as its master, it may receive updates from S1 that it has already received from M

Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave S1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.

On the other slaves S2 and S3, use STOP SLAVE and CHANGE MASTER TO MASTER_HOST='S1' (where 'S1' represents the real host name of S1). To use CHANGE MASTER TO, add all information about how to connect to S1 from S2 or S3 (user, password, port). In CHANGE MASTER TO, there is no need to specify the name of S1's binary log or binary log position to read from: We know it is the first binary log and position 4, which are the defaults for CHANGE MASTER TO. Finally, use START SLAVE on S2 and S3.

Then instruct all WC to direct their statements to S1. From that point on, all updates statements sent by WC to S1 are written to the binary log of S1, which then contains every update statement sent to S1 since M died.

The result is this configuration:

       WC
      /
      |
 WC   |  M(unavailable)
  \   |
   \  |
    v v
     S1<--S2  S3
      ^       |
      +-------+

When M is up again, you must issue on it the same CHANGE MASTER TO as that issued on S2 and S3, so that M becomes a slave of S1 and picks up all the WC writes that it missed while it was down. To make M a master again (because it is the most powerful machine, for example), use the preceding procedure as if S1 was unavailable and M was to be the new master. During this procedure, do not forget to run RESET MASTER on M before making S1, S2, and S3 slaves of M. Otherwise, they may pick up old WC writes from before the point at which M became unavailable.

Note that there is no synchronization between the different slaves to a master. Some slaves might be ahead of others. This means that the concept outlined in the previous example might not work. In practice, however, the relay logs of different slaves will most likely not be far behind the master, so it would work, anyway (but there is no guarantee).

Verify that the master has binary logging enabled by issuing a SHOW MASTER STATUS statement. If logging is enabled, Position is nonzero. If binary logging is not enabled, verify that you are running the master with the --log-bin and --server-id options.

Verify that the slave is running. Use SHOW SLAVE STATUS to check whether the Slave_IO_Running and Slave_SQL_Running values are both Yes. If not, verify the options that were used when starting the slave server. For example, --skip-slave-start prevents the slave threads from starting until you issue a START SLAVE statement.

If the slave is running, check whether it established a connection to the master. Use SHOW PROCESSLIST, find the I/O and SQL threads and check their State column to see what they display. See Section 14.3, “Replication Implementation Details”. If the I/O thread state says Connecting to master, verify the privileges for the replication user on the master, the master host name, your DNS setup, whether the master is actually running, and whether it is reachable from the slave.

If the slave was running previously but has stopped, the reason usually is that some statement that succeeded on the master failed on the slave. This should never happen if you have taken a proper snapshot of the master, and never modified the data on the slave outside of the slave thread. If the slave stops unexpectedly, it is a bug or you have encountered one of the known replication limitations described in Section 14.7, “Replication Features and Known Problems”. If it is a bug, see Section 14.12, “How to Report Replication Bugs or Problems”, for instructions on how to report it.

MySQL Enterprise For immediate notification whenever a slave stops, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

If a statement that succeeded on the master refuses to run on the slave, try the following procedure if it is not feasible to do a full database resynchronization by deleting the slave's databases and copying a new snapshot from the master: