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"