42.2. Replication | MySQL 5.0 Certification Study Guide

MySQL supports replication capabilities that allow the databases on one server to be made available on another server. Replication is used for many purposes. For example, by replicating your databases, you have multiple copies available in case a server crashes or goes offline. Clients can use a different server if the one that they normally use becomes unavailable. Replication also can be used to distribute client load. Rather than having a single server to which all clients connect, you can set up multiple servers that each handle a fraction of the client load.

MySQL replication uses a master/slave architecture:

The server that manages the original databases is the master.
Any server that manages a copy of the original databases is a slave.
A given master server can have many slaves, but a slave can have only a single master. (If done with care, it is possible to set up two-way or circular replication, but this study guide does not describe how.)

A replication slave is set up initially by transferring an exact copy of the to-be-replicated databases from the master server to the slave server. Thereafter, each replicated database is kept synchronized to the original database. When the master server makes modifications to its databases, it sends those changes to each slave server, which makes the changes to its copy of the replicated databases.

42.2.1. Setting Up Replication

To set up replication, each slave requires the following:

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.
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.
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. For example, you can set up an account for a slave by issuing these statements on the master server, where slave_user and slave_pass are the username and password for the account, and slave_host is the host from which the slave server will connect:
```
 mysql> CREATE USER 'slave_user'@'slave_host' IDENTIFIED BY 'slave_pass'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_host'; 
```

Also, you must 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³² 1. The easiest way to assign these ID values is by placing a server-id option in each server's option file:

 [mysqld] server-id=id_value

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

The following procedure describes the general process for setting up replication. It is an overview only; for complete details, see the replication chapter of the MySQL Reference Manual.

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`: shell> mysqldump --all-databases --master-data=2 > dump_file Assuming that binary logging is enabled, the `--master-data=2` option causes 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. For other backup techniques, see Chapter 32, "Data Backup and Recovery Methods." Whichever technique you use, you must know the exact replication coordinates that correspond to the backup time.
3.	Copy the dump file to the replication slave host and load it into the MySQL server on that machine: shell> 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_host_name', -> MASTER_USER = 'slave_user', -> MASTER_PASSWORD = 'slave_pass', -> MASTER_LOG_FILE = 'master_log_file', -> MASTER_LOG_POS = master_log_pos; The hostname is the host where the master server is running. 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 you perform the preceding procedure, issue a START SLAVE statement. The slave should connect to the master and begin replicating updates that the master sends to it. The slave also creates a master.info 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 master.info 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's 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:

 [mysqld] binlog-do-db = a binlog-do-db = b

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 an option file:

 [mysqld] replicate-do-db = a replicate-do-db = c

42.2.2. The Binary and Relay Logs

The master server's binary log is the basis for communication between the master and its slaves:

When the master server makes modifications to its databases, it records the changes in its binary log files. Statements are stored in the log as "events."
Events recorded in the binary log are sent to each connected slave server, which makes the changes to its copy of the replicated databases. A slave server that isn't connected to the master when an event is recorded will receive the event when it connects later.

When a slave receives an update from the master, it doesn't change its own databases immediately. Instead, it records the event in its relay log. The relay log is stored on disk and processed later, as described in Section 42.2.3, "Replication-Related Threads." The delay normally is 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.

42.2.3. 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 known 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:

To begin receiving replication events, an I/O thread starts on the slave server and connects to the master server.
The master starts a thread as a connection handler for the slave I/O thread.
The master server sends events from its binary log files to the slave I/O thread, which records them in the slave's relay log files. The relay log stores events to be executed later. It has the same storage format as the binary log.
The slave SQL thread processes the contents of the relay log files. When it starts, it reads events from the relay logs and executes them. As it finishes processing each relay log file, it deletes 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. See Section 32.6, "Replication as an Aid to Backup."

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

 mysql> SHOW PROCESSLIST\G *************************** 1. row ***************************      Id: 36693    User: slaveuser    Host: localhost:4934      db: NULL Command: Binlog Dump    Time: 2272   State: Has sent all binlog to slave; waiting for binlog to          be updated    Info: NULL

The State value in this case shows that the master has no events waiting to be sent to the slave. The value is Sending binlog event to slave while the master is transmitting events.

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 a replication slave, SHOW PROCESSLIST displays 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 based on the State value. For example, the following output indicates that thread 9 is the I/O thread because its State value refers to communication with the master. Thread 10 is the SQL thread because it is reading the relay log and because it refers to the other thread as the I/O thread:

 mysql> SHOW PROCESSLIST\G *************************** 1. row ***************************      Id: 9    User: system user    Host:      db: NULL Command: Connect    Time: 13   State: Waiting for master to send event    Info: NULL *************************** 2. row ***************************      Id: 10    User: system user    Host:      db: NULL Command: Connect    Time: 50   State: Has read all relay log; waiting for the I/O slave          thread to update it    Info: NULL

Other I/O thread State values usually refer in some way to the master server, the binary log, or the SQL thread. Other SQL thread State values usually refer to reading the relay logs, or, if the thread currently is executing a SQL statement, the State value contains the text of the statement.

42.2.4. Replication Troubleshooting

If replication fails, there are some general strategies you 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 SHOW SLAVE STATUS statement, which indicates whether the slave threads are running. If replication has failed to start, likely causes include the following:

The master and slave were not configured with the slave-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 of 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. (From the slave host, try connecting to the master server manually using the mysql client.)

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.

Another source of monitoring information is the MySQL Administrator program, which has a Replication Status section that displays replication status information. See Chapter 26, "MySQL Administrator."

42.2.5. Replication Compatibility and Upgrading

As replication capabilities continue to develop, the format of the binary log changes on occasion to accommodate the need to record new kinds of events. For example, log format changes occurred in MySQL 4.0 and again several times in early versions of MySQL 5.0. The format likely will change again in MySQL 5.1. Because master/slave communication is based on the binary log, these format changes have implications for compatibility and for upgrading:

The master and its slaves must be able to communicate, so compatibility with a master is possible only for a slave that understands the log format used by the master.
When you 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 servers run the same version of MySQL. As a general rule, for a master and server of dissimilar versions, compatibility often is possible for a newer slave replicating from an older master, but not for an older slave replication from a newer master. In the latter case, the master may sent events in a format that the slave simply cannot interpret.

In addition to the compatibility constraint imposed by the binary log format, incompatibilities may occur at the SQL level. For example, statements executed on the master may use new features that are not available to the older slave server. For example, statements that refer to views cannot be replicated from a MySQL 5 master to a MySQL 4.1 slave because views are not available before MySQL 5. The MySQL Reference Manual provides a detailed list of SQL-level constraints on replication compatibility that apply to dissimilar-version master/slave server pairs.

An implication of the preceding remarks is that you should not upgrade a master server before upgrading its slaves. Also, if you're upgrading servers using early (pre-production) versions within a release series for which a binary log format change has been introduced, you should ensure that all the servers are exactly the same version. During early development of a format change, modifications might occur in each release that produces incompatibilities with the previous release. (For example, a MySQL 5.0.3 master cannot replicate to a 5.0.2 slave.)