Shulou(Shulou.com)06/01 Report--

In the daily operation and maintenance of MySQL DBA, master-slave synchronization failures must be encountered, and the most common build error is 1032. 1032 the main reason for the error is that the updated or deleted records of the master database do not exist on the slave database. There are generally two ways to deal with this kind of error: 1, skip the error execution statement directly, find the error execution statement, and repair the slave data. The first solution will cause the main inconsistency (delete statements can be skipped), and the second is to fundamentally solve the problem.

The method of statement skip operation is as follows:-- traditional mode mysql > stop slave; # means skip one step error, followed by a variable number mysql > set global sql_slave_skip_counter = 1; mysql > start slave

Then use mysql > show slave status\ G to view:

Slave_IO_Running: Yes Slave_SQL_Running: Yes

-- GTID mode mysql > stop slave

Find Retrieved_Gtid_Set:7800a22c-95ae-11e4-983d-080027de205a:10 through show slave status\ G;

Mysql > set GTID_NEXT='7800a22c-95ae-11e4-983d-080027de205a:10'

Mysql > begin;commit

Mysql > set GTID_NEXT='AUTOMATIC'

Mysql > start slave

The method to repair slave data is as follows:

Mysql > select * from T1; Empty set (0.00 sec) mysql > set sql_log_bin=0; Query OK, 0 rows affected (0.01 sec) mysql > insert into T1 values; Query OK, 1 row affected (0.01 sec) mysql > insert into t1 values; Query OK, 1 row affected (0.00 sec) mysql > insert into T1 values Query OK, 1 row affected (0.02 sec) mysql > insert into T1 values; Query OK, 1 row affected (0.02 sec) mysql > set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql > insert into T1 values (3 meme cc'); Query OK, 1 row affected (0.02 sec) mysql > select * from T1 +-1 | aaaa | 2 | bb | 3 | cc | 4 | dd | | 5 | ee | +-+-+ 5 rows in set (0.00 sec)

Slave library: mysql > select * from T1; +-+-+ | id | name | +-+-+ | 3 | cc | +-+-- + 1 row in set (0.00 sec)

Simulated failure: master library: mysql > update T1 set name = 'aaaa' where id=1; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 slave library: mysql > show slave status\ G; Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1032 Last_Error: Could not execute Update_rows event on table reptest.t1; Can't find record in't 1, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND The event's master log mysql-bin.000009, end_log_pos 42303

Single fault handling: find the missing data from the main database according to the location of master log and end_log_pos indicated in Last_Error: shell > # mysqlbinlog-v-- base64-output=decode-rows-- stop-position=42303 / data/mysql/mysql3306/logs/mysql-bin.000009 | tail-20 SET timestamp 1496988091According to the location of the slave database; BEGIN / *! / # at 42198 # 170609 14:01:31 server id 1003306 end_log_pos 42249 CRC32 0xfff09796 Table_map: `reptest`.`t1`mapped to number # at 42249 # 170609 14:01:31 server id 1003306 end_log_pos 42303 CRC32 0x67a63dd5 Update_rows: table id 240flags: STMT_END_F # UPDATE `reptest`.`t1` # WHERE # @ 1 repair 1 # # @ 2 roomaaa 'ROLLBACK / * added by mysqlbinlog * / *! / * SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / / *! * /; DELIMITER; # End of log file / *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; / *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

After finding it, manually change it to insert into `reptest`.`t1` values; from the library: mysql > insert into `reptest`.`t1` values (1dagada'); Query OK, 1 row affected (0.01 sec) mysql > start slave; Query OK, 0 rows affected (0.03 sec) mysql > show slave status\ G; Slave_IO_Running: Yes Slave_SQL_Running: Yes

At this point, the fault has been restored and the master-slave synchronization has returned to normal.

However, if there are many inconsistencies, or even involving multiple tables, it takes a lot of effort to deal with it, you can write a script to deal with it, and the database can be redone if it is not large. After the 1032 error, if it is not solved by redoing, it is best to use pt-table-checksum check and pt-table-sync repair. Pt tools can only be used in the case of master-slave double yes.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.