[MySQL] Sql_error 1032 Solutions

First, Reason:

in a test environment in which the primary master synchronizes, because the business side does not follow the principle of writing only one point at a time, it results in the deletion of a piece of data on a library . This data is updated at the same time on library B.

because of the asynchronous and network delay, the update event of B arrives at the A-side first, causing the end of a to not find this record, therefore sql_thread error 1032, master-slave synchronization stop.

Second, error description:

MySQL master-slave synchronization 1032 error, generally refers to the data to be changed does not exist, Sql_thread extracted log can not be applied error, resulting in synchronization failure

(Update, delete, insert a data that has been delete).

1032 of the error itself has no effect on data consistency, the biggest impact is caused by synchronization failure, synchronization stop.

if the primary master (master-Slave) has failed synchronization, the first time to review and proceed to resolve. Because of the different steps, it will result in inconsistent reading data. The synchronization should be resumed at the first time,

Minimize the impact on your business. Then the specific analysis of the reasons for the different steps, manual or automatic repair data, and do pt-table-checksum data consistency check.

The current business is generally the master synchronization, the main master synchronization due to asynchronous update, there is an issue of update conflict, and it is easy to cause SQL ERROR 1032 error. This should be resolved on the business side,

ensure that only one point of the database is updated at the same time, similar to single-point writing. Our solution is to write an underlying database call library, which may involve updating conflicting operations and calling this library.

in the configuration file, database A and B with 2 points, ensure that the a library is always updated, if a library is not available, go to update the B library.

In addition, if it is a scenario with high data consistency requirements, such as involving money, it is recommended to use PXC (strong consistency, true synchronous replication).

Third, the solution:

MySQL5.6.30 version, Binlog mode is row.

Show slave Status\g, you can see the following error:

Slave_sql_running:no
last_sql_errno:1032' at Master log mysql-bin.000003, End_log_pos 440267874;    not the Execute Delete_rows event on table Db_test.tbuservcbgolog; 　　　　　　　　 Can'tbuservcbgolog ', error_code:1032; Handler error ha_err_key_not_found; The event'S master log mysql-bin.000003, End_log_pos 440267874

As can be seen from the above, is the Sql_thread thread error, error number 1032. is when you apply an event with a row of data in the Delete Db_test.tbuservcbgolog table, because this data

does not exist and error. The location of this event in the master server Master Binlog is mysql-bin.000003, End_log_pos 440267874. (Of course, you can slave the relay from the server

Log to find the method, see last)

Method 1: Skip the Error Event

Skip this error first to get the master-slave synchronization back to normal. (or N-event, one-by-one skip)

Stop slave;

Set global sql_slave_skip_counter=1;

Start slave;

Method 2: Skip all 1032 errors

Change the my.cnf file and add it under Replication settings:

Slave-skip-errors = 1032

Restart the database, and then start salve.

Note: Because the database is being restarted, it is not recommended unless there are too many error events.

Method 3: Restore the deleted data

Based on the error message, use Mysqlbinlog to find the data event SQL and perform the reverse manually. If delete is changed to insert.

In this case, the location of this event in the master server Master Binlog is mysql-bin.000003, End_log_pos 440267874.

1) Use the Mysqlbinlog tool to find 440267874 of events

/usr/local/mysql-5.6.30/bin/mysqlbinlog--base64-output=decode-rows-vv mysql-bin.000003 |grep-a 20 ' 440267874 '

or/usr/local/mysql-5.6.30/bin/mysqlbinlog--base64-output=decode-rows-vv mysql-bin.000003--stop-position= 440267874 | Tail-20

or Usr/local/mysql-5.6.30/bin/mysqlbinlog--base64-output=decode-rows-vv mysql-bin.000003 > Decode.log

(or add parameter-D,--database=name to further filter)

#160923 20:01:27 Server ID 1223307 end_log_pos 440267874 CRC32 0x134b2cbc delete_rows:table ID 319 flags:stmt_e Nd_f## # DELETE from ' db_99ducj '. ' Tbuservcbgolog '## # WHERE## @1=10561502/* INT meta=0 nullable=0 is_null=0 * /## @2=1683955/* INT meta=0 nullable=0 is_null=0 * /## @3=90003/* INT meta=0 nullable=0 is_null=0 * /## @4=0/* INT meta=0 nullable=0 is_null=0 * /## # @5= ' 2016-09-23 17:02:24 '/* DATETIME (0) meta=0 nullable=1 is_null=0 * *## # @6=null * DATETIME (0) meta=0 nullable=1 is_null=1 */#At 440267874

The above is the retrieved result, the transaction statement is: Delete from Db_99ducj.tbuservcbgolog where @1=10561502 and @2=1683955 ...

Which @1 @2 @3 ... respectively, the table tbuservcbgolog the column name, fill up can be.

We can reverse this SQL deleter into insert, manually execute this insert SQL from the library, then restart slave.

Note: Find event SQL Http://www.tuicool.com/articles/6RvUnqV by relay log

[MySQL] Sql_error 1032 Solutions