MySQL - Replication

To set up replication, each slaves requires the following:

  1. A backup copy of the master's databases. This is the replication "baseline" that sets the slave to a known initial state of the master.
  2. The filename and position within the master's binary log that corresponds to the time of the backup. The values are called the "replication coordinates." They are needed so that the slave can tell the master that it wants all updates made from that point on.
  3. An account on the master server that the slave can use for connecting to the master and requesting updates. The account must have the global REPLICATION SLAVE privilege
mysql> CREATE USER 'toyota'@'x.x.x.x' IDENTIFIED BY 'crawling';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'toyota'@'x.x.x.x';

You must also assign a unique ID value to each server that will participate in your replication setup. ID values are positive integers in the range from 1 to 2^32 - 1. The easiest way to assign these ID values is by placing a server-id option in each server's option file:


It is common, though not required, to use an ID of 1 for the master server and values greater than 1 for the slaves.

  1. Ensure that binary logging is enabled on the master server. If it is not, stop the server, enable logging, and restart the server.
  2. On the master server, make a backup of all databases to be replicated. One way to do this is by using mysqldump: mysqldump —all-databases -master-data=2 > dump_file. Assuming that binary logging is enabled, the —master-data=2 option cause the dump file to include a comment containing a CHANGE MASTER statement that indicates the replication coordinates as of the time of the backup. These coordinates can be used later when you tell the slave where to begin replicating in the master's binary log.
  3. Copy the dump file to the replication slave host and load it into the MySQL server on that machine: "mysql < dump_file"
  4. Tell the slave what master to connect to and the position in the master's binary log at which to begin replicating. To do this, connect to the slave server and issue a CHANGE MASTER statement:
mysql> CHANGE MASTER TO MASTER_HOST='', MASTER_USER='replication', MASTER_PASSWORD='lightEn&ng', MASTER_LOG_FILE='logbin.000001', MASTER_LOG_POS=109351;

The username and password are those for the slave account that you set up on the master. The log file and position are the replication coordinates in the master's binary log (You can get these from the CHANGE MASTER statement near the beginning of the dump file.)

After we perform the above procedure, issue a START SLAVE statement. The slave should connect to the master and begin replicating updates that the master send to it.

The slave also create a file in its data directory and records the values from the CHANGE MASTER statement in the file. As the slave reads updates from the master, it changes the replication coordinates in the file accordingly. Also, when the slave restarts in the future, it looks in this file to determine which master to use.

By default, the master server logs updates for all databases, and the slave server replicates all updates that it receives from the master. For more fine-grained control, it is possible to tell a master which databases to log updates for, and to tell a slave which of those updates that it receives from the master to apply. You can either name databases to be replicated (in which case those not named are ignored), or you can name databases to ignore (in which case those not named are replicated). The master host options are —binlog-do-db and —binlog-ignore-db. The slave host options are —replicate-do-db and —replicate-ignore-db.

The following example illustrates how this works, using the options that enable replication for specific databases. Suppose that a master server has three databases named a, b, and c. You can elect to replicate only databases a and b when you start the master server by placing these options in an option file read by that server:


With those options, the master server will log updates only for the named databases to the binary log. Thus, any slave server that connects to the master will receive information only for databases a and b.

Enabling binary logging only for certain databases has an unfortunate side effect: Data recovery operations require both your backup files and your binary logs, so for any database not logged in the binary log, full recovery cannot be performed. For this reason, you might prefer to have the master log changes for all databases to the binary log, and instead filter updates on the slave side.

A slave that takes no filtering action will replicate all events that it receives. If a slave should replicate events only for certain databases, such as databases a and c, you can start it with these lines in its option file:

  replicate-do-db = a
  replicate-do-db = c

The Relay Log

When a slave receives an update from the master, it does not change its own databases immediately. Instead, it records the event in its relay log. The relay log is stored on disk and processed later. The delay is normally minimal for an active slave, but you can defer relay log processing if you want to perform slave maintenance while allowing the slave to continue to receive updates from the master.

Replication-related threads

Replication processing involves three threads per master/slave relationship. One thread runs on the master, and two on the slave. The slave threads are know as the "I/O thread" and the "SQL thread" because one communicates with the master to receive events and the other processes SQL statements contained in the received events.

Replication threads interact as follows:

  1. To begin receiving replication events, an I/O thread starts on the slave server and connect to the master.
  2. The master starts a thread as a connection handler for the slave I/O thread.
  3. The master sends events from its binary log files to the slave I/O thread, which records them in the slave's relay log file. The relay log stores events to be executed later. It has the same storage format as the binary log.
  4. The slave SQL thread processes the contents of the relay log file. When it starts, it reads events from the relay log and execute them. As it finishes processing each relay log file, it delete it if the I/O thread is writing to a newer relay log. If the SQL thread is reading the same relay log that the I/O thread is writing, the SQL thread pauses until more events are available in the file.

The two slave threads operate asynchronously and it is not necessary for both of them to be running at the same time. You can start or stop them independently with the START SLAVE thread_type or STOP SLAVE thread_type statements, where thread_type is IO_THREAD or SQL_THREAD. This decoupled relationship between slave threads offers certain benefits:

  • If a problem occurs while executing events, the SQL thread stops until the problem is resolved. However, the I/O thread can continue to run and receive events from the master server. Events for the slave do not become backlogged on the master.
  • A replication slave is, in effect, a copy of its master. This means that you can use the slave for making backups by stopping the SQL thread to prevent changes to databases on the slave (and flushing the logs to force pending changes to disk). Then make the backup on the slave side. The I/O thread can continue to run and record events in the relay log. After making the backup, restart the SQL thread and it processes the pending events received by the I/O thread during the backup.

You can see whether replication thread is running by using the SHOW PROCESSLIST statement. On the master, a thread that is serving a slave has a Command value of 'Binlog Dump':


There will be one 'Binlog Dump' thread on the master for each connected slave. Use SHOW SLAVE HOSTS to see a list of connected slaves.

For replication slaves, SHOW PROCESSLIST display zero to two replication threads, depending on whether the I/O and SQL threads are running. Both threads have a Command value of Connect, but you can distinguish which is which base on the State value. For the I/O thread, State value usually refer in some way to the master server, the binary log or the SQL thread. For the SQL thread, State values usually refer to reading the relay logs, or, if the thread is currently executing an SQL statement, the State value contains the text of the statement.

Replication Troubleshooting

If replication fails, there are some general strategies we can use to diagnose and resolve the problem. Begin by verifying that replication has gotten started in the first place. If it has, check the current replication status for information about why it stopped.

To see whether replication has gotten started, check the slave's error log for messages that indicate a failure to connect to the master. You can also use the


statement, which indicates whether the slave threads are running. If replication failed to start, likely causes:

  • The master and slave were not configured with the server-id option, or their ID values are not unique. Check each server's option file and make sure that they contain server-id options that have different values.
  • The master server does not have the binary log enabled. Without binary logging, the master has no means for recording events to communicate to slave servers. Enable binary logging if it is not turned on.
  • The master server does not allow connections from the slave. Verify that you can connect to the server using the designated replication account.

If the slave can connect to the master but replication is not proceeding, issue a SHOW SLAVE STATUS statement to check whether the slave threads are running. If not, use START SLAVE to start them. If the SQL thread still does not start, check the error information in the output from SHOW SLAVE STATUS. This often indicates a statement that the slave has trouble processing, which gives a clue to the underlying problem.


When we upgrade servers used for replication, take care not to upgrade a master or slave to a version of MySQL that cannot understand the log format used by the other. Compatibility between replication servers is always best if both severs run the same version of MySQL. As a general rule, for a master and server of dissimilar versions, compatibility is often possible for a newer slave replicating from an older master, but not for an older slave replication from a newer master.

We should not upgrade a master server before upgrading its slaves. Also, if we're upgrading servers using early (pre-production) versions within a release series for which binary log format change (or incompatibility at the SQL level) has been introduced, we should ensure that all the servers are exactly the same version.

Replication as an Aid to Backup

If your MySQL server acts as a master in a replication setup, you can use a slave server to make your backup instead of backing up the master:

  1. Cause the server to stop processing updates received from the master. You can do this by stopping the server, or by issuing a STOP SLAVE SQL_THREAD statement. In the latter case, you should also flush the tables to force pending changes to disk.
  2. Make a backup of the slave's databases. The allowable methods depend on whether you stop the server or leave it running. For example, if you stop the server, you cannot use any program that must connect to it, such as mysqldump or mysqlhotcopy
  3. Restart the server if you stop it. If you left it running, restart the SQL thread by issuing a START SLAVE SQL_THREAD statement.

The advantage of making a backup this way is that it doesn't take place on the master server. Thus, the master need not be interrupted at all, and the backup procedure does not impose any extra disk or processing load on it.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License