How to set up Master Slave replication in mysql 5.7

Advertisements

We need to keep our data safe so we do the database replication. In mysql the most common replication is Master-Slave replication. In this post we will see how we can setup a mysql server as master and another one as slave. We are using Mysql version 5.7 in this example on Centos Linux 6.8.

We need two systems

1. Master
IP : 172.16.0.50
2. Slave
IP : 172.16.0.60

Configuring Master:
Install mysql community server version from mysql website and edit the my.cnf file as below
vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-ignore-db =mysql

innodb_flush_log_at_trx_commit=1
sync_binlog=1
expire_logs_days=7

Restart the mysql server.
/etc/init.d/mysqld restart

Once the mysql-server is running, you can check the status of the master with the following command.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000089 |    35626 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Configuring slave:

Install mysql community server version from mysql website and edit the my.cnf file as below
vim /etc/my.cnf
bind-address = 0.0.0.0
server-id = 2
relay-log=mysqld-relay-bin

restart the mysql server and in the mysql console, set the details of the Master server from where client has to receive data.

mysql> CHANGE MASTER TO  MASTER_HOST='172.16.0.50',MASTER_USER='replication_user', MASTER_PASSWORD='abcdefgh', MASTER_LOG_FILE='mysql-bin.000089', MASTER_LOG_POS=23694;

If the slave service is already running then you will get the following error.
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.


Start the slave, using the --skip-slave-start option so that replication does not start.

Or you can use the command
mysql>STOP SLAVE;

Then once the master details are set, you can start slave service.
 mysql>START SLAVE;

Once the Slave is running and receiving the data from the master, you can see the slave status as follows.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.0.50
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000089
          Read_Master_Log_Pos: 35626
               Relay_Log_File: mysqld-relay-bin.000168
                Relay_Log_Pos: 7186
        Relay_Master_Log_File: mysql-bin.000089
             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: 35626
              Relay_Log_Space: 9874
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a7e33c46-3463-11e5-a325-2e092b9265bc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql>

In the master server, you can see how many slaves are connected to that by issuing the following command.

mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | c18cf848-2efa-11e6-8534-ee8445cdbbe1 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)


References:
0. https://dev.mysql.com/doc/refman/5.7/en/replication.html
1. https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html
2. https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterstatus.html
3. https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html
4. https://dev.mysql.com/doc/refman/5.7/en/replication-administration-status.html