Setting Up Replication Servers

One form of database "replication" involves copying a database to another server. But then you have to repeat the operation later if the original changes and you want to keep the copy up to date. To achieve continual updating of a secondary database as changes are made to the contents of a master database, use MySQL's live replication capabilities. This gives you a means of keeping a copy of a database and making sure that changes to the original database propagate on a timely basis to the copy automatically.

Replication Concepts
Database replication in MySQL is based on the following principles:

In a replication relationship, one server acts as the master and another server acts as the slave. Each server must be assigned a unique replication ID.

The master and the slave must begin in a synchronized state with identical copies of the databases to be replicated. After that, updates are made on the master server and propagate to the slave. Updates are not made directly to the replicated databases on the slave.

The means of communication of updates is based on the master server's binary update logs, which is where updates that are to be sent to the slave are recorded. Consequently, binary logging must be enabled on the master server.

The slave server must have permission to connect to the master and request updates. The slave's progress is tracked based on replication coordinates, which consist of the name of the binary log file from which the slave currently is reading and its position within the file.

You can have multiple slaves per master, but not multiple masters per slave. However, a slave can serve as a master to another slave, thus creating a chain of replication servers.

Replication support is relatively new in MySQL and still under active development, so it's sometimes difficult to keep track of just which replication-related feature was added when. Replication capabilities were added beginning with MySQL 3.23.15 but, in general, it's best to run the most recent server that you can. You'll also need to consider replication compatibility constraints between different versions of the server. The general compatibility rules are as follows:

3.23.x slaves cannot communicate with 4.x masters.

4.0.0 slaves can communicate only with 4.0.0 masters.

4.0.1 and later slaves can communicate with 3.23.x masters or with 4.x masters from a version equal to or greater than the slave.

In general, I recommend that you adhere to the following guidelines:

Try to match MySQL versions for your master and slave servers. For example, try to match 3.23.x masters with 3.23.x slaves, not 4.x masters with 3.23.x slaves—or vice versa.

Within a given 3.23.x or 4.x version series, try to use the most recent versions possible. This will give you the benefit of the richest feature set and the greatest number of restrictions removed and problems eliminated.

Assuming that your servers are version-compatible, they must also be feature-compatible. For example, if the master server replicates InnoDB tables that use foreign keys and MyISAM tables that require RAID features, the slave server must include the InnoDB handler and RAID support.

Establishing a Master-Slave Replication Relationship
The following procedure describes how to set up a master-slave replication relationship between two servers:

Determine what ID value you want to assign to each server. These IDs must be different and should be positive integer values that fit into 64 bits. The ID values will be needed for the server-id startup option used with each server.

The slave server needs an account on the master server so that it can connect and request updates. On the master server, set up an account like this:

GRANT REPLICATION SLAVE ON *.*
TO 'slave_user'@'slave_host'
IDENTIFIED BY 'slave_pass';

Use the REPLICATION SLAVE privilege for MySQL 4.0.2 and up and the FILE privilege for earlier versions. (The required privilege changed as part of the grant table modifications that were introduced in MySQL 4.0.2.) You'll need the slave_user and slave_pass values later when you set up the slave server. No other privileges are needed if the account is used only for the single, limited purpose of replication. However, you may want to grant additional privileges to the account if you plan to use it connect to the master from the slave "manually" with mysql for testing. Then you won't be so limited in what you can do. (For example, if REPLICATION SLAVE is the only privilege the account has, you may not even be able to see database names on the master server with SHOW DATABASES.)

Perform the initial synchronization of the slave server to the master server by copying the master's databases to the slave. One way to do this is to make a backup on the master host and then move it to the slave host and load it into the slave server. Another method is to copy all the databases over the network from the master to the slave. See Chapter 13 for database backup and copying techniques.

Another way to set up the slave is to use LOAD DATA FROM MASTER, available as of MySQL 4.0.0. Use of this statement is subject to the following conditions:

All tables to be replicated must be MyISAM tables.

You must issue the statement while connected to the slave server using an account that has the SUPER privilege.

The account used by the slave server to connect to the master server must have the RELOAD and SUPER privileges. To grant these privileges at the same time as REPLICATION SLAVE when you set up the account, modify the GRANT statement shown earlier to look like this:

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO 'slave_user'@'slave_host'
IDENTIFIED BY 'slave_pass';

Note that this is an account on the master server and differs from the one just mentioned in the previous item, which is an account on the slave.

The operation performed by LOAD DATA FROM MASTER acquires a global read lock. This blocks all updates on the master server for the duration of the transfer to the slave.

Some of these restrictions may be lifted in the future.

Whatever method you use to copy databases from the master to the slave, you'll need to make sure that no updates occur on the master between the time when you make the backup and the time that you reconfigure the master to enable binary logging.

Shut down the master server if it is running.

Modify the master's configuration to tell the server its replication ID and to enable binary logging. To do this, add lines like the following to an option file that the master server reads when it starts up:

[mysqld]
server-id=master_server_id
log-bin=binlog_name

Restart the master server; from this point on, it will log updates by writing them to the binary log. (If you already had binary logging enabled, back up your existing binary logs before restarting the server. Then, after it comes up, connect to it and issue a RESET MASTER statement to clear the existing binary logs.)

Shut down the slave server if it is running.

Configure the slave server to know its replication ID, where to find the master server, and how to connect to it. In the simplest case, the two servers will be running on separate hosts and using the default TCP/IP port, and you'll need only four lines in the [mysqld] group of an option file that the slave server reads when it starts up:

[mysqld]
server-id=slave_server_id
master-host=master_host
master-user=slave_user
master-password=slave_pass

slave_server_id is the replication ID of the slave server. It must be different from the master's ID. master_host is the name of the host where the master server is running. On UNIX, if the master host is the same as the slave host, use 127.0.0.1 rather than localhost to make sure that the slave uses a TCP/IP connection. (A socket file is used for connections to localhost, and replication through a socket file is not supported.) The slave_user and slave_pass values should be the name and password of the account that you set up on the master server earlier for the slave server to use when it connects to the master to request updates. Be sure to put these lines in an option file that is accessible only to the MySQL administrator's login account on the slave server because you should keep the username and password confidential. Don't use /etc/my.cnf, for example, which normally is world-readable. One possibility is to use my.cnf in the server's data directory and make sure the data directory contents are locked down, as described in Chapter 12.

If it's necessary to be more specific about how to establish the connection to the master server, you can include a master-port line in the option group to indicate a port number if the master isn't listening on the default port.

If the connection between the master and slave is intermittent or unreliable, you may want to change the defaults for the connection attempt interval or the number of retries before giving up (60 seconds and 86,400 times, respectively). The master-connect-retry and master-retry-count options can be used for this.

Restart the slave server. A slave uses two sources of information to figure out where it is in the replication process. One is the master.info file in the data directory, and the other is the configuration information specified by the server's startup options. The first time you start a slave server, it finds no master.info file and uses the values of the master-xxx options in the option file to determine how to connect. Then it creates a new master.info file in which to record that information and its replication status. Thereafter, when the slave server starts, it uses the information in the master.info file in preference to the option file. (This means that if you later change the master host information in the option file, you'll need to remove the master.info file and restart the slave server or it will ignore your changes.)

The procedure just described is based on the assumption that you want to replicate all databases from one server to another, including the mysql database that contains the grant tables. If you don't want to have the same accounts on both hosts, you can exclude replication of the mysql database. (For example, you might want to set up a private replication slave that people cannot connect to even if they have an account on the master.) To exclude the mysql database, add the following line to the [mysqld] group in the master's option file when you enable binary logging:

binlog-ignore-db=mysql

To exclude multiple databases, use this option multiple times, once per database.

After you have replication set up and running, there are several statements that you may find useful for monitoring or controlling the master and the slave. Details about these statements are available in Appendix D. A brief summary follows:

SLAVE STOP and SLAVE START suspend and resume a slave server's replication-related activity. These statements can be useful for telling the slave to be quiescent while you're making a backup, for example.

SHOW SLAVE STATUS on the slave shows its replication coordinates. This information can be used to determine which binary logs are no longer needed.

PURGE MASTER on the master expires binary logs. You can use this after using SHOW SLAVE STATUS on each of the slaves.

CHANGE MASTER on the slave lets you alter several of the slave's current replication parameters, such as which binary update log it reads from the master or which relay log file it writes to.

As of MySQL 4.0.2, replication slaves use two threads internally. The I/O thread talks to the master server, requests updates from it, and writes updates to a relay log file. The SQL thread reads the relay logs and executes the updates it finds there. (The relay logs serve as the means by which the I/O thread communicates changes to the SQL thread.) For slave servers that operate using this two-thread model, you can use SLAVE STOP and SLAVE START to suspend or resume each thread individually by adding IO_THREAD or SQL_THREAD to the end of the statement. For example, SLAVE STOP SQL_THREAD stops execution by the slave of the updates in the relay logs, but allows the slave to continue to read updates from the master and record them in the relay logs.

Relay logs are generated in numbered sequence, much like the binary update logs. There is also a relay log index file analogous to the binary update log index. The default relay log and index filenames are HOSTNAME-relay-bin.nnn and HOSTNAME-relay-bin.index. The defaults can be changed with the --relay-log and --relay-log-index server startup options. Another related status file is the relay information file, which has a default name of relay-log.info and can be changed with the --relay-log-info-file option.