• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
sharadchhetri

sharadchhetri

Tutorials On Linux, Unix & Open Source

  • Home
  • Linux Commands
  • Resources
    • Learn Linux
  • My WordPress plugins

Set delay replication to MySQL slave server

December 27, 2013 by Sharad Chhetri Leave a Comment

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

Share this:

  • Twitter
  • Facebook
  • More
  • Print
  • Email
  • LinkedIn
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
  • WhatsApp
  • Mastodon

Related posts:

  1. Setup MySQL master slave replication in CentOS 6
  2. Find mysql commands history without mysql server login
  3. Setup Master Slave Chroot BIND DNS in CentOS 6 or Red Hat 6
  4. How to install mysql server in RedHat and CentOS
  5. How to check default storage engine in MySQL server
  6. How to install MySQL Server 5.6 on CentOS 7 / RHEL 7
  7. Install MySQL Server 5.6 in CentOS 6.x and Red Hat 6.x Linux
  8. How to install mysql server 5.6 on Ubuntu 14.04 LTS ( Trusty Tahr )
  9. how to change path of tmpdir in MySQL Server On Ubuntu
  10. How to install MySQL Server 5.7 on Ubuntu 14.04 LTS

Filed Under: Linux, mysql Tagged With: mysql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Our Social Media Presence

  • Facebook
  • GitHub
  • Twitter

Linux Command

What is Linux Internal And External Command

Linux Basic Commands With Examples For Every Beginner

tr command to convert lines to space , tab and vertical tab

smbpasswd command not found on CentOS 7 and RHEL 7

Solution : semanage command not found

Unix / Linux : How to print duplicate lines from file

More Posts from this Category

You Might Like These Articles!

simplecodesyntax wordpress plugin

SimpleCodeSyntax : My Another WordPress Plugin

Install Nginx

How To Install Nginx On Ubuntu 22.04 LTS

Install Latest Git package in Ubuntu Operating System

How To Always Install Latest Git Package In Ubuntu Operating System

Bash script for installing VirtualBox on Ubuntu 22.04 LTS Desktop

Install VirtualBox On Ubuntu 22.04 LTS Desktop (Bash Script)

libfuse

dlopen(): error loading libfuse.so.2 – Got Error On Ubuntu

Failed to open/create the internal network

VirtualBox Error: Failed to open/create the internal network

Always Useful Tips And Tricks

How to see line numbers in file through cat command

How to see system load average in terminal with graphical representation

How to install pam_mysql in CentOS or Red Hat

Allow only members of Wheel group to use su command on RHEL/CentOS

df command not showing correct free space in linux

Agent admitted failure to sign using the key ssh

How to zip directory in linux explained with examples

Explore 90+ Article On "Linux Tips And Tricks"

Copyright © 2023 ยท
The material in this site cannot be republished either online or offline, without our permission.
Proudly Blogging From Bharat.

  • Contact
  • About Me
  • My WordPress plugins
  • Privacy Policy