How to Set Up MySQL Master-Slave Replication

This article will help you to setup master-slave replication between MySQL servers. We are using MySQL 5.5 on CentOS 6.3. I hope it will help you to setup replication.

Setup Details:

Master Server: 192.168.1.10
Slave  Server: 192.168.1.20
Database: mydb

If you are using different – different versions of MySQL on Master and Slave use this link to check compatibility.

1. Setup MySQL Master Server

• Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20' IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;

Block write statement on all the tables, so not changes made after taking backup.

mysql> use mydb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> exit;

Edit MySQL configuration file and add the following lines under [mysqld] section.

# vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin
binlog-do-db=mydb
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Restart master mysql server to changes take effect.

# service mysqld restart

Check the current binary log file name (File) and current offset (Position) value using following command.

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | mydb         |                  |
+------------------+----------+--------------+------------------+

The above output is showing that the current binary file is using mysql-bin.000002 and offset value is 107. Note down these values to use on slave server.

Take a backup of database and copy it to slave mysql server.

# mysqldump -u root -p mydb > mydb.sql
# scp mydb.sql 192.168.1.20:/opt/

After completing backup remove the READ LOCK from tables, So that changes can be made.

mysql> UNLOCK TABLES;

2. Setup MySQL Slave Server

Edit salve mysql configuration file and add following values under [mysqld] section.

# vim /etc/my.cnf

[mysqld]
server-id=2
replicate-do-db=mydb

server-id always be an non zero numeric value. These value will never be similar with other master and slave servers.

Restart mysql slave server, If you had already configured replication use –skip-slave-start in start to not to immediate connect to master server.

# /etc/init.d/mysqld restart

Restore database backup taken from master server.

# mysql -u root -p mydb < mydb.sql

Setup option values on slave server using following command.

mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.10',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='secretpassword',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=107;

Finally start the slave thread

mysql> SLAVE START;

Check the status of slave server.

mysql> show slave status G

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.15
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: mydb
          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: 107
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
mysql>

MySQL Master-slave Replication has been configured successfully on your system and in working mode. Thanks for using this tutorial, Kindly do not forgot to share it with your friends.