Setting Up and Configuring for Replication

Most replication systems use multiple machines on one internal network. If you are using machines connected via the public Internet, think carefully about the security implications. As a practical starting point, make sure that the port you are using for MySQL is accessible for any machines you need to connect through a firewall (3306 is the default port).

Assuming that you have a recent version of MySQL installed on your machines and one or more databases you want replicated, the following steps are required to set up a single master with one or more slaves.

Create a Replication User

It is good practice to create users with only the permissions that they need for their tasks , rather than using the root user for everything, as we discussed in Chapter 1, "Installing MySQL." You therefore need a user for replication activity on the master server.

If you are going to populate your slaves initially by using LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER , your replication user needs a special set of permissions. The following GRANT statement (run on the master) will create a user with the permissions required for the startup tools to connect:

 grant replication slave, reload, super, select       on logs.*       to replication@" %"  identified by '  password  '; 

(Note that this example uses the logs database. You should change this to the name of your database, and obviously you should change the password to something more secure.)

After the initial copy from master to slave is complete, the replication user will not need so many permissions. If you are populating your slaves from a backup or reducing the user's permissions after the initial copying is complete, the user needs only the replication permission, so the following query will create a user named replication that can connect from any of the slave servers (and any other machines):

 grant replication slave on logs.* to replication@"%"  identified by 'password'; 

The syntax for GRANT queries was covered in Chapter 11, "Managing User Privileges." The permission replication slave was added to MySQL 4.0.2 and is specifically for this purpose. For older versions of MySQL, use the permission file .

The query as shown will allow access only to the database named logs. If you want all databases on this machine to be able to be replicated, use *.* instead of logs.* .

Check Master Configuration

Your master server needs to have binary logging enabled. If you have read the installation instructions in this book, you have hopefully already enabled it because it has other uses beyond replication. To check that it is on, you can run a SHOW VARIABLES query. You can simply type

 show variables; 

to get a complete list, but for concise output, type the following:

 show variables like "log_bin" ; 

If binary logging is off, add log-bin to your options file as shown in the example in Listing 1.1. The options file will be named my.ini or my.cnf , depending on which operating-system conventions you have followed.

Edit your my.ini / my.cnf file to give your master server a unique id. At a minimum, your options file should now look like this:

 [mysqld] log-bin server-id=1 

The server-id is a unique identifier for each of your MySQL servers. It must be a positive integer, but the choice of 1 here was completely arbitrary.

If you have edited your options file, you will need to restart the server for changes to take effect.

Create a Master Snapshot

To start replication, you need three things:

• A complete, consistent snapshot of the current database

• The name of the master server's binary log file

• The offset into the binary log where the server is currently

Exactly how you grab these will vary a little depending on whether you are using MyISAM or InnoDB tables and how averse you are to stopping access to the database for a period.

For MyISAM tables, you can grab a snapshot after you start each slave. See the section "Start Slaves," later in this chapter. This method is not very efficient, though, particularly if you have a large amount of data and many slaves. For each slave, the LOAD DATA FROM MASTER query will obtain a lock on the master's data and hold it until a complete copy has been transmitted. You can lock the databases for a much shorter time by making the snapshot manually via the file system. This will also allow you to use one snapshot to start as many slaves as required, reducing time when the server is locked.

For InnoDB tables, you do not have the option of running a LOAD DATA FROM MASTER query. You can make a file-system snapshot or buy the hot backup tool.

If you are making a file-system snapshot, you first need to make sure that the stored data is consistent and up-to-date by running the following query:

 flush tables with read lock; 

This will also lock the table, barring any writes until you unlock it.

You can get the current binary log file and offset from the following query:

 show master status; 

The output should look something like this:

 +-------------------+----------+--------------+------------------+  File               Position  Binlog_do_db  Binlog_ignore_db  +-------------------+----------+--------------+------------------+  server-bin.000007       211                                  +-------------------+----------+--------------+------------------+ 

You need to record the contents of the first column (the binary log file name) and the second column (the offset into the binary log). If these are empty, you will be using an empty string for the filename and 4 for the offset.

To create a snapshot of a MyISAM table, simply copy the directory that contains the data using an archiving program. On Unix, type something like this to get a snapshot of our logs database:

 tar -cvf /tmp/snapshot.tar /path/mysql/data/logs 

On Windows, use WinZip or a similar archiving tool to grab a copy of the directory C:\mysql\data\logs.

After the copy is complete, you can re-enable write access to the database by typing this:

 unlock tables; 

The employee table we have been using throughout this book (like most tables the authors use on projects) is an InnoDB table. If you have purchased the (commercial) InnoDB hot backup tool, it is perfect for this task. It is available from Innobase Oy at

www.innodb.com/hotbackup.html

Without this tool, the safest approach is to flush and lock the database with the following queries. Use

 flush tables with read lock; 

and then display and record the binary log file and offset (as for MyISAM):

 show master status; 

Without unlocking the database, shut it down and make a copy of the directory that relates to that database inside your MySQL data directory. For InnoDB tables, you will also need to copy across the data files and logs. After the snapshot is complete, you can restart and unlock the database.

Configure Slaves

Each slave needs a unique server id. Edit your options file ( my.cnf / my.ini ) to add a line like this one to each:

 server-id=2 

The id must be a positive integer, but as long as no two are the same, it does not matter what you choose. If you are going to have more than a handful of servers running, an escalating sequence is probably your best hope of keeping them unique.

If you are working from a file-system snapshot, you need to copy the files into the appropriate places on the slave server. If you are working with more than one operating system, remember to consider filename capitalization.

Editing your options file or copying across InnoDB files will require you to restart your slave server.

Start Slaves

To start a slave that was set up as described here, you will need to run the following queries:

 change master to master_host='server',                  master_user='replication',                  master_password='password',                  master_log_file='server-bin.000007',                  master_log_pos=211; start slave; 

In this example, the word server is the hostname of the master server. The word replication is the username of the user we created to do the replication tasks. That user's password should go in place of password . The binary log file name and offset fill out the parameters required.

The START SLAVE query launches the slave's replication threads, causing it to try to connect to the master and collect updates.

If you have copied a snapshot via the file system, you should be able to run some matching queries against the master and slave to check that the replication is working correctly. Make a small update to the master and check that it is mirrored on the slave.

If the tables you are replicating are relatively small MyISAM tables, you can create and populate them via a query like

 load table logs.logJan2003 from master; 

to copy a single table or

 load data from master; 

to copy all tables onto this slave.

After you have replication working, entering queries manually to configure replication is not practical, even if you restart only occasionally. The same information can go in your options file with a slightly different syntax.

Your slave's my.ini or my.cnf file could look something like this:

 [mysqld] server-id = 2 master-host = server master-user = replication master-password = password replicate-do-db = logs