Table of Contents for

MySQL in a Nutshell, 2nd Edition

There are only a few steps to setting up replication. The first step is to set up user accounts dedicated to replication on both the master and the slave. It’s best not to use an existing account for security reasons. To set up the accounts, enter an SQL statement like the following on the master server, logged in as root or a user that has the GRANT OPTION privilege:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO 'replicant'@'slave_host' IDENTIFIED BY 'my_pwd';

These two privileges are all that are necessary for a user to replicate a server. The REPLICATE SLAVE privilege permits the user to connect to the master and to receive updates to the master’s binary log. The REPLICATE CLIENT privilege allows the user to execute the SHOW MASTER STATUS and the SHOW SLAVE STATUS statements. In this SQL statement, the user account replicant is granted only what is needed for replication. The username can be almost anything. Both the username and the hostname are given within quotes. The hostname can be one that is resolved through /etc/hosts (or the equivalent on your system), or it can be a domain name that is resolved through DNS. Instead of a hostname, you can give an IP address:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO 'replicant'@'12.127.17.72' IDENTIFIED BY 'my_pwd';

If you upgraded MySQL on your server to version 4.x recently, but you didn’t upgrade your mysql database, the GRANT statement shown won’t work because these privileges didn’t exist in the earlier versions. For information on fixing this problem, see the section on mysql_fix_privilege_tables in Chapter 16.

Now enter the same GRANT statement on the slave server with the same username and password, but with the master’s hostname or IP address:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* 
TO 'replicant'@'master_host' IDENTIFIED BY 'my_pwd';

There is a potential advantage of having the same user on both the master and the slave: if the master fails and will be down for a while, you can redirect users to the slave with DNS or by some other method. When the master is back up, you can then use replication to get the master up-to-date by temporarily making it a slave to the former slave server. This is cumbersome, though, and is outside the scope of this book. For details, see High Performance MySQL (O’Reilly). You should experiment with and practice such a method with a couple of test servers before relying on it with production servers.

To see the results of the first GRANT statement for the master, enter the following:

SHOW GRANTS FOR 'replicant'@'slave_host' \G

*************************** 1. row ***************************
Grants for replicant@slave_host:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO 'replicant'@'slave_host'
IDENTIFIED BY PASSWORD '*60115BF697978733E110BA18B3BC31D181FFCG082'

Note, incidentally, that the password has been encrypted in the output. If you don’t get results similar to those shown here, the GRANT statement entry failed. Check what you typed when you granted the privileges and when you executed this statement. If everything was typed correctly and included in both statements, verify that you have version 4.0 of MySQL or higher, a version that supports these two new privileges. Enter SELECT VERSION(); on each server to determine the versions they are using.