MySQL deferred replication implementation

we use MySQL master-Slave synchronization to achieve MySQL real-time synchronization and backup of the data, but because the data is in real-time synchronization, the backup library operates the error data in real-time when the data is manipulated by mistake in the main library.

in order to avoid the above data error situation, I hope to establish a delay synchronization MySQL Backup Library, when the main library data is mistakenly manipulated, the backup library also retains the correct data before operation, which facilitates data recovery.

The use of third-party tools Percona-toolkit enables the need for deferred replication.

Implementing the Environment:

192.168.189.143 (MySQL Main Library )

192.168.189.144 (MySQL standby, 3 minutes delay )

Steps:

1: Install on the server MySQL , and configure master-slave replication. (omitted)

2: Check The replication status of the 144 from the library, confirm that the synchronization is OK, or write the data test on the main library.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/67/wKioL1WbfMvSSE7SAAKB1LuCF-4285.jpg "title=" 1.jpg " alt= "Wkiol1wbfmvsse7saakb1lucf-4285.jpg"/>

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 problem, resulting in failure, modify / etc/yum.conf gpgcheck parameter 0

installation successful, viewing Pt-slave-delay is available:

# Pt-slave-delay--help

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/67/wKioL1WbfXjBQEiFAAOyML3B92w751.jpg "width=" 700 " height= "282" title= "2.jpg" border= "0" hspace= "0" vspace= "0" style= "width:700px;height:282px;" alt= " Wkiol1wbfxjbqeifaaoyml3b92w751.jpg "/>

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

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

Mysql>flush privileges;

5: To turn on deferred replication:

#pt-slave-delay--user= ' delay_rep '--password= ' 123456 '--delay=3m--interval=20s 192.168.189.144 650) this.width=650, "src=" http://s3.51cto.com/wyfs02/M01/6F/67/wKioL1WbfpOApJgIAAH_maJXMsg742.jpg "title=" 3.jpg " Width= "720" height= "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:720px;height:53px; "alt=" Wkiol1wbfpoapjgiaah_ Majxmsg742.jpg "/> Note:

--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 20shere.

192.168.189.144: The IP address of the deferred server .

Check now 144 of the slave synchronization status, you can see that the slave_sql_running status changes to NO . Deferred replication is now in effect.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/67/wKioL1WbfxriyFwnAAJ5-uZ4nE4447.jpg "title=" 4.jpg " alt= "Wkiol1wbfxriyfwnaaj5-uz4ne4447.jpg"/>

6: Test:

Insert a piece of data in the 143 Main Library:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/6A/wKiom1WbfjGh_1GAAAAzWz8HDFw129.jpg "title=" 5.jpg " alt= "Wkiom1wbfjgh_1gaaaazwz8hdfw129.jpg"/>

---------

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/6A/wKiom1WbfkGDiXVmAAEcRo1dLBw969.jpg "title=" 6.jpg " alt= "Wkiom1wbfkgdixvmaaecro1dlbw969.jpg"/>

View 144 from the library, the data has not been synchronized:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/68/wKioL1WbgIXTYFDjAABIZJomXUA210.jpg "title=" 7.jpg " alt= "Wkiol1wbgixtyfdjaabizjomxua210.jpg"/>

-----------

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6F/6B/wKiom1WbftyTjtE9AACrl2Wrvdo899.jpg "title=" 8.jpg " alt= "Wkiom1wbftytjte9aacrl2wrvdo899.jpg"/>

in about 3 minutes, the log shows you start synchronizing data:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/68/wKioL1Wbg7eiHoL2AAHCI1uRnxE300.jpg "title=" 9.jpg " Width= "height=" border= "0" hspace= "0" vspace= "0" style= "width:750px;height:72px;" alt= " Wkiol1wbg7eihol2aahci1urnxe300.jpg "/>

Now look at the 144 from the library and find that the data has been synchronized, indicating that deferred replication has started to take effect:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6F/6B/wKiom1Wbgg_wFqI3AABIDKuUe-U258.jpg "title=" 10.jpg "alt=" Wkiom1wbgg_wfqi3aabidkuue-u258.jpg "/>

------------

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/6B/wKiom1WbgiDiWFmqAACyevwTlxo373.jpg "title=" 11.jpg "alt=" Wkiom1wbgidiwfmqaacyevwtlxo373.jpg "/>

The attachment is the installation file of Porcona-toolkit, everyone can download ...

This article is from the Linux OPS blog, so be sure to keep this source http://icenycmh.blog.51cto.com/4077647/1671702

MySQL deferred replication implementation