Configuring MySQL Server Replication

Edit the [mysqld] section of the /etc/my.cnf file on the master server to include a log-bin option. Also include a server-id=master_id option in this section, where master_id must be a positive integer value from 1 to 2³² -1. For example:

[mysqld]

log-bin=mysql-bin

server-id=1

	NOTE: The server-id value configured in this step must be unique to each server in the replication configuration. No two slave servers or the master server may have the same server-id value.

Restart the MySQL server on the master server, and make sure no errors occurred by entering the following command:

# /etc/init.d/mysql restart

Run the following command in the MySQL client to verify that the master server is running correctly, and to gather the necessary information for configuring the slave servers:

mysql> show master status;

The sample output looks as follows:

+-----------------------+-----------+-------------------+------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+-----------+-------------------+------------------------+
| mysql-bin.000001 |   1678   |              |                  |
+-----------------------+-----------+-------------------+------------------------+
1 row in set (0.00 sec)

This output shows that the MySQL server is using mysql-bin.000001 as its binary log file. The current log position in the log file is 1678. The values of Binlog_Do_DB and Binlog_Ignore_DB are not configured. Record the name of the binary log file and the current position because these values are needed when you configure the slave servers.

Start the MySQL command-line client by entering the following command:

# mysql –u root –p

At the prompt, enter the database password.

Create a replication user on the master server that has the privileges needed to connect and change mastership to the master server. The slave server uses this account to connect and configure the master/slave relationship.

If the osmusr user account does not already exist, to create it, issue the following command:

mysql> create user 'osmusr'@'slave.test' identified by 'osmpass';

Next, grant the necessary privileges to allow the MySQL slave servers to connect to the master server and establish replication:

mysql> grant replication slave on *.* to ‘osmusr’@’slave.test’ \

identified by ‘osmpass’;

The master server is now ready for the slave servers’ connection.

Change the server-id parameter in the /etc/my.cnf file as follows:

[mysqld]

server-id=2

If you are setting up multiple slave servers, each one must have a unique server-id value that differs from that of the master server and from each of the other slave servers.

Verify that the MySQL server on the slave server can be restarted correctly by entering the following command:

# /etc/init.d/mysql restart

The following SQL commands are used to set the master server information on the slave server:

mysql> CHANGE MASTER TO     
			-> MASTER_HOST='master_host_name',     
			-> MASTER_USER='replication_user_name',     
			-> MASTER_PASSWORD='replication_password',     
			-> MASTER_LOG_FILE='recorded_log_file_name',     
			-> MASTER_LOG_POS=recorded_log_position;

For this example, enter the following commands:

	NOTE: In this example, specify the replication user account that you created, the master log file name, and the log position that you recorded previously from the output of the show master status command on the master server.

Start the slave threads, which initiate the master/slave server connection, by running the following command on the slave server:

mysql> start slave;

Run the show slave status command to verify that the slave server is connected to the master server correctly:

mysql> show slave status\G

The following output is displayed:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: osmusr
Master_Port: 3306
…

	NOTE: Use the backslash G ( \G ) option with any MySQL command to display the command output vertically rather than horizontally. This can be particularly useful with commands that result in a large amount of information.

If the value of the Slave_IO_State field is Waiting for master server to send event, the slave server has connected to the master server and will catch up on any updates that have occurred. If the slave server was not configured correctly, the value of Slave_IO_State is Connecting to master. If this occurs, review the error log on the slave server for additional information.

Create the test database, osmsdb, and the test table, customer, on the master server by referring to the SQL scripts in “Creating the Sample Database for MySQL”.

Verify that the osmsdb database and customer table are created automatically on the slave server. To do this, enter the following commands from the MySQL command-line running on the slave servers:

mysql> show databases;

Verify that osmsdb is listed in the output.

mysql> use osmsdb;

mysql> show tables;

Verify that customer is listed as a table in the output.

Run some SQL commands that modify the customer table on the master server, and verify that these changes are reflected in the table on the slave server. For example, insert a record by entering the following command on the master server:

mysql> insert into customer values (1, ‘customer1’, \

‘2000-01-01 10:10:10’, ‘table’, 1234)

Verify that the SQL commands are executed automatically on the slave server.

Run the following command on the master server to display the status:

mysql> show master status\G

The file name and position of the bin log file that the master server is using are displayed:

*************************** 1. row ***************************
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Run the following command on the master server to get information about slave servers that are connecting to the master server:

mysql> show processlist\G

The following data is displayed:

*************************** 1. row ***************************
Id: 32
User: osmusr
Host: slave-1.test:32798
db: NULL
Command: Binlog Dump
Time: 5740
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
   Id: 33
 User: osmusr
 Host: slave-2.test:32944
   db: NULL
Command: Binlog Dump
Time: 2150
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
  Id: 34
User: root
Host: localhost
  db: NULL
Command: Query
Time: State: NULL
Info: show processlist
3 rows in set (0.00 sec)

The output shows that two slave servers, slave-1.test and slave-2.test, have connected to the master server, and the master server has sent all the binary log data to both slave servers and they are both up to date.

To show the status of the slave server, run the following command on the master server

mysql> show slave status\G

The master server information for this slave server and its status are displayed. If the slave server has processed all updates from the binary log and is up to date with the master server, the values of Master_Log_File and Read_Master_Log_Pos in the output are consistent with the output of show master status on the master server from step 1.

To show the current status of the slave server I/O thread, run the following command from the slave server:

mysql> show processlist\G

*************************** 1. row ***************************
     Id: 4
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 173581
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 5
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 81990
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 225
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
3 rows in set (0.00 sec)

The show processlist command on the slave server displays the current status of the slave I/O thread, which connects to the master server and writes events from the master server to relay logs. This command also shows the status of the slave server SQL thread, which reads events from these relay logs and enters them into the slave server database. The values of the State field in the output indicate that the slave server I/O thread and the slave server SQL thread have finished their tasks and are waiting for additional updates from the master server.