Troubleshoot MySQL on Gtid master-slave sync 1236 error "turn"

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 Error 1236 from master when reading data from binary log: ' The slave are connecting using change M ASTER to Master_auto_position = 1, but the MASTER had purged binary logs containing gtids that the slave requires. '

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> show global variables like '%gtid% ' \g

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

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 is the gtid_purged queried on the main library , and the other is the gtid_executed value that has been executed from the library (this machine does not need, the main library Gtid)

Note: Be sure to add the gtid that have been executed from the library, if only the gtid_purged on the main library is set, the library will pull back all the binary log files on the main library, and the synchronization process will have other errors, causing the synchronization to fail .

Mysql> 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: when setting the gtid_purged value, thegtid_executed value must be empty otherwise error, the value of the empty method is the reset Master command

When you're done, review the information again

5. turn on sync again

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

mysql> StArt 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-checksum h=192.168.1.15,u=root,p=xxx, p=3306 h=192.168.1.19,u=root,p=xxxx, p=3307-d 6coursestudychoose_test | Mk-checksum-filter

Turn from

Troubleshoot MySQL on Gtid master-slave sync 1236 error-linux Road -51cto Blog

http://blog.51cto.com/hnr520/1883282

Troubleshoot MySQL on Gtid master-slave sync 1236 error "turn"