Set delay replication to MySQL slave server

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 -h -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