Mysql master-slave synchronization problem analysis

View cluster library status show slave status\G

Original prompt from the library: Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '864e6992-0a34-11e7-a98a-7cd30ac6c9ec:148408' at master log mysql-bin.000010, end_log_pos 920578920. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

1. Follow the prompts of Congku to find the reason, enter the command

select * from performance_schema.replication_applier_status_by_worker\G

to get

Know this The transaction occurred on the table r_com_patent and the table was located, but I don't know which record.

2. What happened when we went to the main library to find the binary file. Enter the command

Mysqlbinlog --no-defaults –v –v --base64-output=decode-rows /usr/local/mysql/data/master-bin.000010 | grep –A ‘10’ 920578920

and finally locate the record.

The main database has updated the table r_com_patent, but the cluster database cannot find the updated record.

Specifically, the main database changes the record with patent_id 45 in the table r_com_patent and the field cid from NULL to 3253026. The record with patent_id 45 in the cluster database table r_com_patent has the field cid originally 3253026. The replication mechanism must find the record with patent_id 45 and id NULL in the database table r_com_patent, so it was not found. . .

3. Solution

1) Check the record in the master.

Select * from r_com_patent where patent_id = 45;

2) On the slave, search for the updated record. It should not exist.

　　Select * from r_com_patent where patent_id = 45;

3) Fill in or modify the lost data on Slave.​

　　Insert into r_com_patent values(3253026,45);

4) Skip the error-reported transaction on the slave.

Stop slave;
Set @@SESSION.GTID_NEXT=’ 864e6992-0a34-11e7-a98a-7cd30ac6c9ec:148408’
Begin;
Commit;
Set @@SESSION.GTID_NEXT = AUTOMATIC;
Start slave;

After that, check again

Show slave status\G

The above is the detailed content of Mysql master-slave synchronization problem analysis. For more information, please follow other related articles on the PHP Chinese website!