3 common errors and pits scenarios in MySQL master-slave replication

First, the problem description

Master-Slave replication error has been the MySQL DBA has been filling the pit, throat, also some people say that MySQL master-slave replication is not stable and so, in fact, MySQL replication than we thought to be much stronger, and most of the DBA think as long as the error to continue to copy just skip, and then no error will be good, In fact, skipping errors will have the risk of inconsistent data, data inconsistency may be more and more serious, and I copy the error in the recurrence of the 1045, 1032 and 1062 errors caused by the database owner is not consistent with the phenomenon of in-depth analysis and give a complete set of solutions.

(1) "ERROR" 1452: Unable to insert data in foreign key table without reference primary key

(2) "ERROR" 1032: Delete or update data, no records found from library

(3) "ERROR" 1062: Insert data from library, unique conflict occurs

Second, reason analysis

"ERROR" 1452: Unable to insert or update data for the reference primary key in the foreign key table. Because the Item_discovery.itemid field (foreign key) references the Items.itemid field (primary key), when you want to insert data in the Item_discovery table, if the items table's primary key does not have corresponding data, it cannot be inserted, reported 1452 error. At this point, you can check if the primary key of the referenced table has the data for the main library, insert the corresponding data for the referenced table, and then open the Copy recovery SQL thread.

"ERROR" 1032: deleted or updated data from the library, no records were found from the library. At this point, the data for the main library is newer than the one from the library, which can be taken from the library to add the same data in the Open Replication recovery SQL thread.

"ERROR" 1062: A unique conflict occurred when inserting data from the library. At this point the data from the library already has the same primary key, and if the same primary key value is inserted again, the error will be made. You can view the row data of the main library consistent with the data to be inserted from the library, such as consistent skipping errors, recovering the SQL thread, or, if not consistent, the main library, then deleting the row records from the library and then turning on replication.

If the current high-availability schema is master-master, the following operations must be set sql_log_bin=0 from the library, avoiding the synchronization of statements executed from the library to the main library (the data for the master library will prevail when recovering).

Three, standardized treatment programme

(aimed at the completion of the standardized treatment scheme)

1. Temporary solution (not suitable for use in data comparison and repair tools during business operation)

"ERROR" 1452:

Normal master-slave replication environment

From library:

Main Library:

View the main library execution statement at the corresponding location where the error occurred, and the corresponding primary key value for the INSERT or update can be obtained through SQL.

Query Item_discovery foreign KEY constraints c_item_discovery_1 reference tables items correspond to the data rows of the primary key values.

From library:

In the Items table, insert the data from the main library query.

Based on the Gtid replication environment

Same as the normal master-slave replication environment.

"ERROR" 1032:

Occurs 1032 may be delete or update when there is no corresponding data row from the library, can be processed in two cases:

(1) If could not execute delete_rows, you can skip the error directly

Normal master-slave replication environment

From library:

Based on the Gtid replication environment

From library:

To find out the replication error when the Executed_gtid_set, if there are multiple, then choose the same as Master_uuid.

(2) If could not execute update_rows, you need to find the SQL in the binary log where the error occurred, find the corresponding data row for the table in the main library, and then insert this data directly from the library to turn on SQL thread recovery.

Normal master-slave replication environment

From library:

Main Library:

View the main library execution statement at the corresponding location where the error occurred, and the corresponding primary key value for the update at that time can be obtained through SQL.

Query the data row for the Item_discovery's corresponding primary key value.

From library:

In the Items table, insert the data from the main library query.

Based on the Gtid replication environment

Same as the normal master-slave replication environment.

"ERROR" 1062:

Normal master-slave replication environment

From library:

Main Library:

View the main library execution statement at the corresponding location where the error occurred, and the corresponding primary key value for the insert at that time can be obtained through SQL.

Query the Trends_uint table for the data row that corresponds to the primary key value.

From library:

In the Trends_uint table, delete the data from the main library query.

Based on the Gtid replication environment

Same as the normal master-slave replication environment.

2. Complete Solution

Use Pt-table-checksum and Pt-table-sync to completely repair data inconsistencies.

Note: Use the PT toolkit to first install the PT toolkit and install the Perl module.

(1) Stop copying from library

(2) Create a validation information table in the main library

(3) Verifying master-slave data consistency with pt-table-checksum in the main library

In the following statement from the library, view the Last_error and find the table with inconsistent data:

Then return to the operating system to execute the following command:

The command can see if the table has data inconsistencies, and if so, use Pt-table-sync repair.

(4) in the main library with Pt-table-sync print out the repair of inconsistent data SQL (if there is a foreign key constraint, repair data should be first from the Foreign key reference field of the table to begin repair), after the repair statement is executed from the library.

Iv. Optimization Recommendations

After replication has been interrupted for 1045, 1032, 1062, you should use the temporary solution for three. 1 To restore replication and use Pt-check-sum to check data consistency at the bottom of the business.

After checking, you can execute this statement from the library to see if there is a data inconsistency table:

For the core table, you can customize the automatic data validation script, weekly data verification, but must be in the business trough calibration Oh!

3 common errors and pits scenarios in MySQL master-slave replication