mysql5.6, master-slave synchronization and delayed replication based on Gtid

Gtid Related:

Prior to mysql5.6, MySQL master-slave replication was slave by configuring the Binlog location of master, and each slave itself had a different binlog. In the mysql5.6 version after the introduction of the Gtid (global transaction identifier), this value in the master generation, slave as long as the master-slave replication when the user is specified, the password, you can obtain the master of the Gtid implementation synchronization, and no need to see the master of the Binlog location, greatly The configuration of the master-slave synchronization. The uniqueness of the Gtid enhances the data's primary and standby consistency, failure recovery, and fault tolerance.

Deferred replication Related:

In the previous blog, we introduced the delayed replication of MySQL, which was implemented based on the third-party tool Percona-toolkit. After the mysql5.6 version, MySQL comes with a feature of deferred replication and is simple to configure.

Lab Environment:

Main Library: 192.168.52.128:3306

From library: 192.168.52.135:3306

To configure Gtid replication:

1: Install MySQL (do not repeat)

2: Open Gtid mode, master and slave libraries are to:

Add Binlog and Gtid related configurations to the MY.CNF configuration file and restart MySQL.

Main:

[Mysqld]binlog-format=rowlog-bin=master-bin-loglog-slave-updates=truegtid-mode=on----Open Gtidenforce-gtid-co Nsistency=true---force gtid consistency master-info-repository=tablerelay-log-info-repository=tablesync-master-info= 1slave-parallel-workers=2---Set the number of SQL threads from the server binlog-checksum=crc32master-verify-checksum= 1slave-sql-verify-checksum=1binlog-rows-query-log_events=1server-id=1

From:

[Mysqld]server-id = 2log-bin=mysql-binbinlog_format=rowlog-slave-updates=true Gtid-mode=on enforce-gtid-consiste ncy=truemaster-info-repository=tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers= 4binlog-checksum=crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1

To restart MySQL:

Service mysqld Restart

Check whether the Gtid of the main library and Concou are turned on:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s1.51cto.com/wyfs02/m02/7e/f4/wkiom1cn5qnba2xwaaagtbc8uz0380.png "title=" 1.PNG "alt=" Wkiom1cn5qnba2xwaaagtbc8uz0380.png "/>

A gtid number is assigned to the main library:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s1.51cto.com/wyfs02/m02/7e/f4/wkiom1cn5tkaq_vcaaaexsho19e117.png "title=" 2.PNG "alt=" wKiom1cN5tKAQ_ Vcaaaexsho19e117.png "/>

3: Create the account required for replication on the main library:

Mysql>grant replication Slave on * * to [e-mail protected]% identified by ' 123456 '; mysql>flush privileges;

4: Configure synchronization from the library.

Mysql>change Master to master_host= ' 192.168.52.128 ', master_user= ' repl ', master_password= ' 123456 ', master_auto_ Position=1; Mysql>slave start;

You can see that there is no need to go to the main library to find Binlog and related location information, directly using Master_auto_position=1,mysql will automatically find the location of synchronization

5: See the effect:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7E/F2/wKioL1cN6MbBP_9VAADkmHeVkP0969.png "title=" 3.PNG " alt= "Wkiol1cn6mbbp_9vaadkmhevkp0969.png"/>

You can see that synchronization is in progress and the sync location is 7.

Perform some actions to verify the sync effect:

Master: Create an instance, make a new table, insert some data

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s1.51cto.com/wyfs02/m02/7e/f5/wkiom1cn6endgc5haabwdlwdoqq615.png "title=" 4.PNG "alt=" Wkiom1cn6endgc5haabwdlwdoqq615.png "/>

From:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/7E/F2/wKioL1cN6RbCPDz8AABE98w9nXQ186.png "title=" 5.PNG " alt= "Wkiol1cn6rbcpdz8aabe98w9nxq186.png"/>

Synchronization is normal, using show slave stautus\g to view the synchronization status, you can see the synchronization location to 10;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s1.51cto.com/wyfs02/m02/7e/f2/wkiol1cn6tgzupcsaaab2rz-tpm510.png "title=" 6.PNG "alt=" Wkiol1cn6tgzupcsaaab2rz-tpm510.png "/>

Replication Delay Configuration:

Mysql>stop Slave;mysql>change MASTER to Master_delay =-------Unit seconds Mysql>start Slave;mysql>show SL Ave Status \g;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/7E/F2/wKioL1cN6X6CAgx2AAAJPvTrJ00223.png "title=" 8.PNG " alt= "Wkiol1cn6x6cagx2aaajpvtrj00223.png"/>

Sql_delay: The field becomes 100, indicating that there is a fixed delay of 100 seconds from the time the library is synchronized with the main library.

Sql_remaining_delay: When the main library has a new operation, the field will enter the countdown, starting with the configured delay time (here is 100). Normal condition is null.

This article from the "Play God Clown" blog, reproduced please contact the author!

mysql5.6, master-slave synchronization and delayed replication based on Gtid