Configuring MySQL replication has several options. The simplest setup is one master node and multiple slave nodes where the slaves are fresh and do not have any data. For this solution, we consider only this simplest setup.
A.3.1 Overview
The general replication configuration steps are as follows: For each master,
o Stop the corresponding MySQL service.
o Edit the master configuration parameter file.
o Start the MySQL service.
o Collect replication service parameters. For each slave,
o Stop the corresponding MySQL service.
o Edit the slave configuration file.
o Start the corresponding MySQL service.
o Join the master node using replication service parameters. These steps are detailed in the following paragraphs.
A.3.2 Master node replication configuration
To configure a master node for replication, first stop the MySQL service. Use the sc stop MySQL command in Windows or the service mysqld stop command in Linux to perform this task.10
Next, open the my.ini file in the Windows installation directory or the /etc/my.cnf file in Linux using a text editor. Find the [mysqld] section within this file and add the following statements at the end of this section:
log-bin=<binary log file name>
server-id=1
max_connections=500 max_connect_errors=999999
Note <binary log file name>is an arbitrary parameter. One may use any file for which the MySQL service user has read/write permission.
Continuing, start the MySQL service using either the sc start MySQL command in Windows or the service mysqld start command in Linux.
10Note that for Windows, one chooses the service name at installation. This name may be MySQL
or it may be something else, like MySQL56. Or use the Services snap-in under Administrative Tools in the Control Panel to manage this service.
If the service fails to start, verify the MySQL service user has
read/write/execute permission for the directory containing the binary log file and try again.
A.3.3 Collect replication service parameters
Replication is now running on the master node. To join a slave node, we need to determine a reference point from which to start copying data.
We can do this step by flushing all tables and then recording the master’s
replication status. To do so, first login to the MySQL prompt via the mysql –u root command. Now issue the following two commands at the mysql> prompt:
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
This last command retrieves four data items as shown here:
Record the first two data items: the File, a string; and the Position, an integer. Then proceed with joining slaves to the master for replication as detailed in the next section.
To avoid releasing the table lock, do not exit this master session until replication setup is completed on all slaves.
A.3.4 Slave node replication configuration
Joining a slave with the master node for replication purposes follows a pattern similar to the one used for configuring replication on the master: stop the service, edit the configuration file, start the service, and issue some SQL commands. These steps must be performed on each slave node as detailed here:
First, stop the MySQL service using the sc stop MySQL or service mysqld stop command as appropriate.
Next, edit the my.ini or my.cnf file and add the following lines at the end of the [mysqld] section:
server-id=<slave ordinal +1>
max_connections=500
Since the master server-id is 1, use 2 for the first slave, 3 for the second, and so on.
Save the changes to this file and then issue the sc start MySQL or service mysqld start command as appropriate. Then join the slave to the master as discussed in the next section.
A.3.5. Join a slave to a replication configuration master
After the slave MySQL service is up and running, log into the mysql> prompt using the mysql –u root command. Then issue the following SQL commands:
CHANGE MASTER TO master_host='<master-IP>', master_user='<replication-user-name>', master_password='<replication-password>', master_log_file='<File>’, master_log_pos=<Position>; START SLAVE;
Note the MySQL client displays a -> continuation prompt after each Enter in a multi-line command. Also, the <Position> parameter is an integer and should not be enclosed in quotes.
To verify this particular slave is synchronized with the master, execute the following SQL command:
SHOW SLAVE STATUS \G
The \G terminates the command while asking for single row output. This command results in a status screen something like the following one:
When Slave_IO_Running and Slave_SQL_Running are both Yes, replication is running properly. When Slave_IO_State is “Waiting for a master to send event,” the slave is in synchronization with the master. Once success is achieved and
replication is finished, continue with the testing as discussed in the next section.
A.3.6 Installing MySQL replication with existing data
If a website is running from one single MySQL database server, dump the
appropriate tables from the replication master and upload these tables to all slaves. Note that replication does not copy existing data from master to slave – only changes made after replication is working.
MySQL provides the SQL function MASTER_POS-WAIT (<log-file-name>,
<Position> to block updates until slaves catch up. See Appendix B.13 of the