In this tutorial we will learn how to set delay replication in MySQL Master Slave replication. After setting this configuration,slave server deliberately lag behind the Master server as per the delay time you have set. The MySQL server version 5.6 has this feature and it has some benefits which will discuss in this post.
To set delay replication or lagging time in MySQL slave server,follow the given below steps
Step 1: Login with MySQL root user in Slave server
mysql -u root -p
Note: If you have set custom mysql server port no.,connecting to remote MySQL Server or running multiple instances in same server follow the given below syntax
Syntax: mysql -u root -P
Step 2: Now stop the slave
STOP SLAVE;
Step 3: Now set delay time for replication in MySQL slave server. I am setting for 30 minutes or 1800 seconds.Then I will use the below command.
CHANGE MASTER TO MASTER_DELAY = 1800;
Here N = no. of seconds
Syntax: CHANGE MASTER TO MASTER_DELAY = N;
Step 4: Now start the mysql slave
START SLAVE;
Step 5: Now check the SLAVE status
SHOW SLAVE STATUS G;
To reset back to delay seconds Zero or previous delay setting
Login as mysql root user and run the command
RESET SLAVE;
Alternatively you can also run the command
STOP SLAVE; START SLAVE;
Main Advantage of using lagging in MySQL slave replication
In MySQL Master-Slave replication, the data get sync instantly to Slave server when there is any event triggered in Master server. For eg. if accidentally any database,table or data is deleted or wrongly updated, in between of lagging time we can stop the slave to get replication.(run command, STOP SLAVE;
in slave server)
Given below is the reference from my server
Check SQL_Delay: 1800 in below given content
mysql> show slave status G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 198.168.56.100 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 24560 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 266 Relay_Master_Log_File: mysql-bin.000002 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: 24560 Relay_Log_Space: 435 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: 2 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 1800 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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 1 row in set (0.00 sec) ERROR: No query specified mysql>
Reference: http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html