Understanding and Implementing MySQL Replication: A Guide for All Skill Levels

By Sudheer S

In this tutorial, I will provide step-by-step process to implement MySQL replication. We will create one master and one slave. We will use two CentOS 6 servers - one for master and the other for slave. This following steps have been tested on two virtual machines.

Our master server will have IP address 192.168.122.10. Our slave server will have IP address 192.168.122.12.

You might want to run SELinux in permissive mode.

Perform these steps on the master:

Install MySQL server.

yum install mysql-server -y

Configure binary log and assign server ID to master. In /etc/my.cnf append the following to mysqld section:

log-bin=mysql-bin
server-id=1

Each server will have a unique ID. Our master will have ID 1 and slave 2.

Start the MySQL server.

/etc/init.d/mysqld start

Set up the initial MySQL server settings.

mysql_secure_installation

Create a MySQL user account and provide REPLICATION SLAVE access to it.

mysql> CREATE USER 'repl'@'192.168.122.12' IDENTIFIED BY 'secretpassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.122.12';

Be sure to replace ‘secretpassword’ with the actual password. Also, note that 192.168.122.12 is the IP address of the slave server. repl MySQL user will be able to access the master server only from the IP address 192.168.122.12.

Let us create a sample database, table and a row.

mysql>CREATE DATABASE sample;
mysql>USE sample;
mysql>CREATE TABLE mytable (mycol varchar(255));
mysql>INSERT INTO mytable VALUES ('first entry');

At this point, we will prepare the master server for replication.

First, we lock all the tables.

mysql>FLUSH TABLES WITH READ LOCK;

Then, we note down the binary log file and position.

mysql>  SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1377 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The log file is mysql-bin.000003 and position is 1377. The values on your server may vary.

Don’t exit the MySQL client. In a separate session/terminal run the following commands.

Stop MySQL server.

/etc/init.d/mysqld stop

Copy the files to the slave server.

rsync --recursive /var/lib/mysql root@192.168.122.12:/var/lib --exclude="mysql/mysql" --exclude='mysql-bin.*' --exclude='auto.cnf' -v

If you use other methods to copy the data, be sure to exclude mysql.bin, auto.cnf and mysql directory placed inside /var/lib/mysql.

auto.cnf was added in MySQL 5.6.

Start the MySQL server.

/etc/init.d/mysqld start

Perform these steps on the slave server.

Install MySQL server.

yum install mysql-server -y

Make sure mysql is the owner of the files.

chown -R mysql.mysql /var/lib/mysql/

Edit /etc/my.cnf and append the following line in mysqld section.

server-id=2

Start the MySQL server.

/etc/init.d/mysqld start

Perform the initial settings.

mysql_secure_installation

Inform the slave how to access the master server.

mysql> CHANGE MASTER TO
        MASTER_HOST='192.168.122.10',
        MASTER_USER='repl',
         MASTER_PASSWORD='secretpassword',
         MASTER_LOG_FILE='mysql-bin.000003',
         MASTER_LOG_POS=1377;

Remember 1377 is the position and mysql-bin.000003 is the log file name after we locked the tables on master and copied the files.

Start the slave.

mysql> START SLAVE;

You can check the slave status using:

mysql> SHOW SLAVE STATUS\G;

Here’s a sample output:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 214
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 359
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 214
              Relay_Log_Space: 660
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)
 
ERROR: 
No query specified

The key fields are Slave_IO_State, Slave_IO_Running and Slave_SQL_Running.

Let’s see if our sample database has been copied.

mysql> USE sample;
mysql> SELECT * FROM mytable;

On the master run a few SQL statements.

mysql> USE sample;
mysq;> INSERT INTO mytable VALUES ('second entry');

On the slave, verify whether replication is working.

mysql> USE sample;
mysql> SELECT * FROM mytable;

Resources

  • MySQL reference manual