7.2 Configuring Replication

With the theory out of the way, let's get our hands on some servers and configure a master and slave. We'll cover two scenarios. In the first, we'll assume that you have a fresh installation of MySQL on the master and slave, with no data on either server aside from the default test and mysql databases. Later, we'll examine the ways you can configure replication on a running master with minimal hassle and interruptions for your users.

7.2.1 On a New Server

Configuring replication on a new server is a straightforward process. The tasks you need to perform are:

That's it. Four steps.

For the sake of clarity, we'll use the hostnames master and slave for our master and slave, respectively.

7.2.1.1 Account creation

When the slave connects to the master, it must authenticate itself just like any other MySQL client, so it needs a username and password. We'll create an account named repl with a password of c0pyIT! on both the master and slave.

Why create the account on the slave? Should the master ever fail, you'll want the slave to become the new master. When the old master is repaired, it can be put back online as a slave of the new master (which is the old slave). If the account didn't exist on the new master, the new slave wouldn't be able to replicate.

So, on each server let's create the account and give it only the minimum privileges necessary for replication: REPLICATION SLAVE and REPLICATION CLIENT. (In MySQL 3.23 you'd use USAGE and FILE.)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 

repl@"192.168.1.0/255.255.255.0" IDENTIFIED BY 'c0pyIT!';

Query OK, 0 rows affected (0.00 sec)

After creating the account, verify that the settings are correct:

mysql> SHOW GRANTS FOR repl;

+----------------------------------------------------------------------------------+

|Grants for repl@"192.168.1.0/255.255.255.0"                                         |

+----------------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'...' IDENTIFIED BY ...|

+-------------------------------------------------------------------------+

1 row in set (0.00 sec)

If that command returns no rows or doesn't list the privileges, double-check that you entered the GRANT command properly.

7.2.1.2 Configuration file entries

The next step is to update the my.cnf file on each server. You need to tell the master to enable binary logging and to tell the slave about its master, login credentials, and so on.

Finally, each server needs to be assigned an ID number, known as a server ID. As you'll see later, the server ID is recorded in each server's binary log entries so that any other server can know which server first executed and logged a query. The server ID can be any number in the range 1-4294967295.^[4]

^[4] A server ID of 1 is assumed if not explicitly assigned.

So, on the master, make sure the following lines are present in the [mysqld] section of the my.cnf file:

log-bin

server-id = 1

The log-bin option tells MySQL to enable binary logging. By default, MySQL places the log file in its data directory. To place the logs elsewhere, you can supply a path and filename:

log-bin = /var/db/repl/log-bin

The slave requires a bit more information than the master. Add the following lines to its my.cnf file:

server-id = 2

master-host = master.example.com

master-user = repl

master-password = c0pyIT!

master-port = 3306

The slave's settings are self-explanatory. It just needs to know how to contact the master and authenticate itself. Later we'll look at some optional replication settings that can also appear in my.cnf.

7.2.1.3 Restart master

With the settings on the master, it's time to stop and start MySQL and verify that the binary log appears. No replication-specific method is necessary; simply stop and restart MySQL using your normal scripts that handle MySQL when the machine boots and shuts down.

If you didn't specify a path and filename after log-bin in your my.cnf file, MySQL writes the log files in your data directory. Otherwise, the logs are written in the location you specified.

You should find a binary log file with a .001 extension. By default, the filename will be hostname-bin. On the host master.example.com, the first log file will be master-bin.001. If you haven't run any write queries yet, the file will be less than 100 bytes in size. Each log file contains a short header and some meta information.

If you then execute a few write queries, you should notice the size of the binary log file increasing. If not, check the error log for hints about what might have gone wrong.

You can use the mysqlbinlog utility to examine the data stored in a binary log file. It reads the entries and prints out the SQL for each one. It also prints some comments that contain other helpful information. For example, running it on a fresh log produces output like this:

$ mysqlbinlog master-bin.001

# at 4

#020922 14:59:11 server id 1  log_pos 4        \

 Start: binlog v 3, server v 4.0.4-beta-log created 020922 14:59:11

The first comment indicates that this entry is at offset 4 in the log. The second comment indicates when the log was created, the server ID, the log version, and the server version.

7.2.1.4 Restart slave

With the master logging properly, a simple restart of the slave should be sufficient to get replication running. When a MySQL server is started, it checks to see whether it should connect to a master and begin (or continue) replicating queries. Upon connecting to the master, MySQL logs a message in its error log to indicate whether the connection succeeded or failed:

021103 13:58:10  Slave I/O thread: connected to master 'repl@master:3306',

replication started in log 'log-bin.001' at position 4

This entry indicates that the slave has connected to the master and begun reading the binary log file master-bin.001 at position (or offset) 4, that of the first query.

Run some write queries on the master and verify that the data on the slave reflects those changes. Once the slave is happily replicating from the master, it can continue to do so indefinitely.

7.2.2 On an Existing Server

Setting up replication on a new server is easy. A few config file entries and a couple of restarts are all you need. On an existing server, however, there's a bit more work to do because you can't simply point a new slave at a master and ask it to clone all the existing data.^[5]

^[5] There are plans to fix that in a future version of MySQL.

There are a couple of ways to do get the job done. We'll look at two specific solutions in a moment. First, let's outline the work that needs to be done; we'll deal then with common solutions.

7.2.2.1 What needs to happen

Normally, to enable replication, you have to add binary logging to your server, which means subsequently restarting the server. If you happen to have binary logging already enabled, you don't have to restart the server. As described earlier, you'll need to add at least two lines to the server's my.cnf file:

log-bin

server-id = 1

Optionally, specify a full path and base filename for the binary logs:

log-bin = /var/db/repl/binary-log

The other task involves getting a copy of all the data from the master and putting it on the new slave. But there's a twist. The data given to the slave must correspond to the exact moment in time the binary log begins. Said another way, the binary log should contain all the queries that are executed on the master after the snapshot was taken and none of the queries from before the snapshot.

If the binary log contains queries that are already reflected in the data given to the slave, the slave has no way to know that. Consequently, it reexecutes the queries, possibly producing strange errors or otherwise making the data inconsistent with what is on the master.

If the binary log misses a few queries that weren't reflected in the slave's copy of the data, it won't see those queries. This can cause strange and hard-to-diagnose problems. Maybe records that were supposed to have expired are still there, or perhaps there's data on the master that doesn't appear on the slave.

Getting the initial data from the master to the slave may be complicated. If you're using only MyISAM tables and can afford to shut down the master for enough time to copy all the data, it will be easy. Otherwise, you'll need to perform an online copy or dump of the data.

7.2.2.2 Snapshot or backup, then copy

The easiest way to get the necessary data is to perform a snapshot (online backup) or a more traditional offline backup and then copy the data to the slave. Using archive tools such as tar or zip, or your traditional backup software, shut down MySQL and copy the contents of the data directory to your slave; then extract the data on the slave.

This method works well if you intend to replicate all the data and can shut down MySQL for the time required to make a copy of the data. If, however, you can't afford to have MySQL offline for more than a few seconds, there's an alternative approach: restart the server once after making the config file changes and then perform an online snapshot of the data.

A snapshot works well only for MyISAM tables. InnoDB and BDB tables are best backed up when MySQL isn't running at all. A snapshot also requires a read lock on the data for the duration of the snapshot. So you'll be able to service read requests during the snapshot process, but all writers will be blocked.

To perform the actual snapshot, you can write your own script to do the job, or you can use mysqlhotcopy or mysqlsnapshot. If you roll your own script, you need to ensure that the binary log is reset before the locks are released. The easiest way to do that is by executing FLUSH TABLES WITH READ LOCK and then RESET MASTER (or FLUSH MASTER in versions older than 3.23.26).

Chapter 9 covers backups as well as the mysqlhotcopy and mysqlsnapshot utilities.

7.2.2.3 Online table copies

Another approach is to use MySQL's command:

 LOAD TABLE mytable FROM MASTER

Doing so instructs a slave to load an entire table from the master. By writing a relatively simple script, you can instruct the slave to clone all the tables it needs using a series of those commands.

The usefulness of this technique is relatively limited, however. Like the previous option, it requires a master that isn't being updated. In an environment in which there are frequent updates to the master, this technique is simply not viable. Furthermore, the slave copies only the data from the master. It then reconstructs the indexes locally, for which large amounts of data can take hours or even days.

7.2.2.4 Online copy and synchronize (MySQL 4.x only)

MySQL 4.0 introduced the LOAD DATA FROM MASTER command. It combines the previous two approaches by first obtaining a read lock on all the master's tables, then loading each table one by one using the LOAD TABLE mechanism.^[6] It respects any slave-side database or table filtering. Once it completes the loading process, it releases the locks on the master and begins replicating.

^[6] This doesn't include the tables in the mysql database. Put another way, LOAD DATA FROM MASTER doesn't clone users and permissions from the master.

While this option is very appealing, it suffers from the same limitations as scripting the LOAD TABLE command yourself. It is much slower than using a master snapshot. It also requires that you grant the repl user SUPER and RELOAD privileges on the master. Finally, it works only with MyISAM tables.