Troubleshoot MySQL on Gtid master-slave sync 1236 error

Recently encountered MySQL open gtid replication, 1236 errors from the library, resulting in synchronization can not be carried out, this article on this issue record the processing steps, about Gtid knowledge is not introduced here, the MySQL version is 5.7.16.

First, the cause of error analysis

The error message is as follows:

last_io_errno:1236

Last_io_error:got fatal error1236 from master if reading data from binary log: ' The slave was connectingusing change MAS TER to Master_auto_position = 1, but the MASTER had purgedbinary logs containing gtids that the slave requires. '

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/8B/A4/wKiom1hTZoLgihvpAAAilkspyr0737.png "title=" 01.png "alt=" Wkiom1htzolgihvpaaailkspyr0737.png "/>

In general, the above phenomenon can occur in two cases

1. manually purge binary log files on the main library

2. The main library restarts, when resynchronization

Second, the solution:

1. Execute the following command on the main library, query the gtid_purged, record the change value

Mysql> ShowGlobal variables like '%gtid% ' \g

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/8B/A4/wKiom1hTZpKCU8WoAABPgzDyrTQ054.png "title=" 02.png "alt=" Wkiom1htzpkcu8woaabpgzdyrtq054.png "/>

2. Execute the following command from the library, query the Gtid that have been executed , namely gtid_executed, record the value of the main library, the local does not need

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/8B/A0/wKioL1hTZp-DQZMvAAAspE0SKJ8150.png "title=" 03.png "alt=" Wkiol1htzp-dqzmvaaaspe0skj8150.png "/>

3. Stop the synchronization thread and reset the synchronization information by executing the following command from the library

mysql> stop slave;

mysql> reset Slave;

mysql> reset Master;

4. Set the gtid_purged from the library

This value has two sources, one that is queried on the main library. gtid_purged , and the second is a query from the library has been executed gtid_executed value (this is not required for this machine, on the main library Gtid )

Note: Be sure to add the gtid that have been executed from the library if you set only the gtid_purged , all binary log files on the main library are pulled back from the library, and other errors occur during the synchronization process, causing the synchronization to fail

m ysql> set@ @global. gtid_purged= ' 4fa9ab33-3077-11e6-8ee6-fcaa14d0751b : 1-18240458,6e41a42e-8529-11e6-b72e-fcaa14d07546 : 1-56604052:56604054-56605629:56605631-56871196,9850e381-b601-11e6-8e46-fcaa14d07546:1-3126210, c5cdcae2-9cb0-11e6-909c-fcaa14d0751b:1-1189,10a59961-c02d-11e6-a2de-fcaa14d07546:1-13381418 ';

Note: Set gtid_purged value, gtid_executed The value must be empty otherwise error, the value of the empty method is Reset Master Command

When you're done, review the information again

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/A4/wKiom1hTZq6Byg8cAABpMAp-ufo118.png "title=" 04.png "alt=" Wkiom1htzq6byg8caabpmap-ufo118.png "/>

5. turn on sync again

mysql> Change master tomaster_host= ' 192.168.1.15 ', master_port=3306,master_user= ' repl ', master_password= ' xxx ', master_auto_position=1;

mysql> St Art slave;

When catching up to the main library from the library, testing the master and slave data is consistent, the test results are all normal

mk-table-checksumh=192.168.1.15,u=root,p= XXX , p=3306h=192.168.1.19,u=root,p= xxxx , p=3307-d 6coursestudychoose_test | mk-checksum-filter

This article is from the "Linux Road" blog, make sure to keep this source http://hnr520.blog.51cto.com/4484939/1883282

Troubleshoot MySQL on Gtid master-slave sync 1236 error