MySQL in a Nutshell, 2nd Edition

The Replication User Account

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 ...