MySQL deferred replication--percona-toolkit and MASTER to Master_delay

For data security, sometimes the database needs to delay backup, here are two ways to delay backup.

First, the use of tools.

Implementing the Environment:

192.168.189.143 (MySQL Main library)

192.168.189.144 (MySQL standby, 3 minutes delay)

Steps:

1: Install MySQL on the server and configure master-slave replication. Omitted

2: View 144 copy status from the library, confirm that synchronization is OK, or write data tests on the main library.

3: Download the Percona-toolkit software package and upload it to the 143 main Library and install:

: http://www.percona.com/downloads/percona-toolkit/

#wget http://www.percona.com/downloads/perconatoolkit/2.2.14/RPM/percona-toolkit-2.2.14-1.noarch.rpm

#yum-y Install percona-toolkit-2.2.14-1.noarch.rpm

Note: If the installation prompts Pulic key issue, resulting in a failure, modify the Gpgcheck parameter in/etc/yum.conf to 0.

Install successfully to see if Pt-slave-delay is available:

#pt-slave-delay--help

4: Configure a user on 144 from the library for use with the deferred Replication tool connection.

Mysql> Grant All on * * to ' delay_rep ' @ ' 192.168.189.% ' identified by ' 123456 ';

Mysql>flush privileges;

5: Turn on deferred replication:

#pt-slave-delay--user= ' delay_rep '--password= ' 123456 '--delay=3m--interval=20s 192.168.189.144

--user=: The user name of the authorized user in the deferred server.

--password=: The password of the authorized user in the deferred server.

--DELAY=3M: Time of delay synchronization, set here to 3 minutes.

--interval=20s: Check the time of synchronization, set to 20s here.

192.168.189.144: The IP address of the deferred server.

When viewing the slave sync status of 144, you can see that the slave_sql_running status changes to No. Deferred replication is now in effect.

Second, MySQL's own configuration.

Mysql (requires more than 5.6) delays the replication configuration by setting the MASTER to master_delay parameter on the slave:

Change MASTER to Master_delay = N;

n is the number of seconds, the statement set from the database delay of n seconds, and then synchronize data with the primary database replication

Specific operation:

Log in to the slave database server

mysql>stop slave;mysql>CHANGE MASTER TO MASTER_DELAY = 600；mysql>start slave;mysql>show slave status \G;

Viewing the value of Sql_delay is 600, indicating that the setting was successful.

Comments:

Sql_delay: A non-negative integer that represents the number of seconds, slave lags in the master.

Sql_remaining_delay: When Slave_sql_running_state waits until master_delay seconds, the master executes the event,

This field contains an integer that represents the number of seconds or so of a delay. At other times, this field is null

MySQL deferred replication--percona-toolkit and MASTER to Master_delay