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:

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:

TO 'replicant'@'' 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 ...

Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.