The mysql master is not synchronized, and the system prompts that the record cannot be found for the update. The mysql Master/Slave

The mysql master is not synchronized, and the system prompts that the record cannot be found for the update. The mysql Master/Slave

View the status of a cluster: show slave status \ G

The original library prompt is: 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 the cluster database to find the cause and enter the command

Select * from performance_schema.replication_applier_status_by_worker \ G

Get

We know that this transaction occurs on the r_com_patent table and locates the table, but we don't know which record it is.

2. Find the binary file in the master database. Enter the command

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

 

 

Finally, the record is located.

The master database updates the r_com_patent table, but the slave database cannot find the update record.

Specifically, the master database changes the patent_id of the table r_com_patent to 45, and the field cid from NULL to 3253026. the patent_id of the r_com_patent table is 45, and the cid of the field is 3253026. Because of the replication mechanism, you must find the records with the patent_id of 45 and the id of the r_com_patent table being NULL, so no...

3. Solution

1) view the record on the master.

Select * from r_com_patent where patent_id = 45;

2) on slave, find the updated record, which should not exist.

Select * from r_com_patent where patent_id = 45;

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

Insert into r_com_patent values (3253026,45 );

4) skip the transaction with an error in slave.

Stop slave;

Set @ SESSION. GTID_NEXT = '864e6992-0a34-11e7-a98a-7cd30ac6c9ec: 8080'

Begin;

Commit;

Set @ SESSION. GTID_NEXT = AUTOMATIC;

Start slave;

Then, check again

Show slave status \ G