Replication - High Performance MySQL - page 452

The replication user actually needs only the REPLICATION SLAVE privilege
on the master and doesn't really need the REPLICATION CLIENT privilege
on either server. So why did we grant these privileges on both servers?
We're keeping things simple, actually. There are two reasons:
• The account you use to monitor and manage replication will need
the REPLICATION CLIENT privilege, and it's easier to use the same
account for both purposes (rather than creating a separate user
account for this purpose).
• If you set up the account on the master and then clone the replica
from it, the replica will be set up correctly to act as a master, in case
you want the replica and master to switch roles.
Configuring the Master and Replica
The next step is to enable a few settings on the master, which we assume is named
server1 . You need to enable binary logging and specify a server ID. Enter (or verify the
presence of) the following lines in the master's my.cnf file:
log_bin = mysql-bin
server_id = 10
The exact values are up to you. We're taking the simplest route here, but you can do
something more elaborate.
You must explicitly specify a unique server ID. We chose to use 10 instead of 1 , because
1 is the default value a server will typically choose when no value is specified. (This is
version-dependent; some MySQL versions just won't work at all.) Therefore, using 1
can easily cause confusion and conflicts with servers that have no explicit server IDs.
A common practice is to use the final octet of the server's IP address, assuming it doesn't
change and is unique (i.e., the servers belong to only one subnet). You should choose
some convention that makes sense to you and follow it.
If binary logging wasn't already specified in the master's configuration file, you'll need
to restart MySQL. To verify that the binary log file is created on the master, run SHOW
MASTER STATUS and check that you get output similar to the following. MySQL will
append some digits to the filename, so you won't see a file with the exact name you
specified:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 98 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
The replica requires a configuration in its my.cnf file similar to the master, and you'll
also need to restart MySQL on the replica: