Foreword
After a failover, a common problem encountered is synchronous error reporting. The following is the most recent error information collected.
Record delete failed
Delete a record on the master, but not found on the slave
Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Ca n‘t find record in ‘t1’,
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event ’s master log mysql-bin.000006, end_log_pos 254
Solution: The master wants to delete a record, but no error is reported on the slave. In this case, the master has already deleted, so the slave can skip it directly.
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
If this is the case, you need to write a script specifically for this error.
Duplicate primary keys
The record already exists on the slave, and the same record is inserted on the master.
Last_SQL_Error: Could not execute Write_rows event on table hcy.t1;
Duplicate entry ‘2’ for key ‘PRIMARY’,
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event ‘s master log mysql-bin.000006, end_log_pos 924
Solution:
Use desc hcy.t1 on the slave; first look at the following structure:
mysql> desc hcy.t1;
+ ------- + --------- + ------ + ----- + --------- + ------- +
Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ------- +
| id | int (11) | NO | PRI | 0 | |
| name | char (4) | YES | | NULL | |
+ ------- + --------- + ------ + ----- + --------- + ------- +
Remove duplicate primary keys
mysql> delete from t1 where id = 2;
Query OK, 1 row affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \ G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
mysql> select * from t1 where id = 2;
Check again on the master and slave.
Update lost
Update a record on the master, but not found on the slave, lost data.
Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Ca n‘t find record in ‘t1’,
Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event ’s master log mysql-bin.000010, end_log_pos 794
Solution:
On the master, use mysqlbinlog to analyze what the wrong binlog log is doing.
/ usr / local / mysql / bin / mysqlbinlog --no-defaults -v -v --base64-output = DECODE-ROWS mysql-bin.000010 | grep -A ‘10’ 794
# 120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F
### UPDATE hcy.t1
### WHERE
### @ 1 = 2 / * INT meta = 0 nullable = 0 is_null = 0 * /
### @ 2 = ‘bbc’ / * STRING (4) meta = 65028 nullable = 1 is_null = 0 * /
### SET
### @ 1 = 2 / * INT meta = 0 nullable = 0 is_null = 0 * /
### @ 2 = ‘BTV’ / * STRING (4) meta = 65028 nullable = 1 is_null = 0 * /
# at 794
# 120302 12:08:36 server id 22 end_log_pos 821 Xid = 60
COMMIT / *! * /;
DELIMITER;
# End of log file
ROLLBACK / * added by mysqlbinlog * /;
/ *! 50003 SET [email protected] _COMPLETION_TYPE * /;
On the slave, look for the updated record, which should not exist.
mysql> select * from t1 where id = 2;
Empty set (0.00 sec)
Then go to the master to check
mysql> select * from t1 where id = 2;
+ ---- + ------ +
| id | name |
+ ---- + ------ +
| 2 | BTV |
+ ---- + ------ +
1 row in set (0.00 sec)
Fill the missing data on the slave and skip the error reporting.
mysql> insert into t1 values (2, ‘BTV’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1 where id = 2;
+ ---- + ------ +
| id | name |
+ ---- + ------ +
| 2 | BTV |
+ ---- + ------ +
1 row in set (0.00 sec)
mysql> stop slave; set global sql_slave_skip_counter = 1; start slave;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \ G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
1236 Error, missing binary
Various reasons, such as accidentally deleting binary files, caused the main library mysql-bin.000012 to be lost, and the synchronization from the secondary library failed.
Master_Log_File: mysql-bin.000012
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
Stop syncing from the library first
slave stop;
View main library log files and locations
mysql> show master logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| mysql-bin.000013 | 154 |
+ ------------------ + ----------- +
Back to the slave library, make the log file and location correspond to the master library
CHANGE MASTER TO MASTER_LOG_FILE = ‘log-bin.000013’, MASTER_LOG_POS = 154;
Finally, start the slave:
`` `bash
slave start;
show slave status \ G;
Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Error:
`` `
Corrupted relay log
The slave relay log relay-bin is damaged.
Last_SQL_Error: Error initializing relay log position: I / O error reading the header from the binary log
Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number;
It ’s not a binary log file that can be used by this version of MySQL
1. Manual repair
Solution: Find the synchronized binlog and POS points, and then re-synchronize, so that you can have a new relay daily value.
example:
mysql> show slave status \ G;
*************************** 1. row ******************** *******
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 1191
Relay_Log_File: vm02-relay-bin.000005
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1593
Last_Error: Error initializing relay log position: I / O error reading the header from the binary log
Skip_Counter: 1
Exec_Master_Log_Pos: 821
Slave_IO_Running: receive the binlog information of the master
Master_Log_File
Read_Master_Log_Pos
Slave_SQL_Running: Perform a write operation
Relay_Master_Log_File
Exec_Master_Log_Pos
Based on the binlog and POS point of execution.
Relay_Master_Log_File: mysql-bin.000010
Exec_Master_Log_Pos: 821
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE MASTER TO MASTER_LOG_FILE = ‘mysql-bin.000010’, MASTER_LOG_POS = 821;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \ G;
*************************** 1. row ******************** *******
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.22
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 1191
Relay_Log_File: vm02-relay-bin.000002
Relay_Log_Pos: 623
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1191
Relay_Log_Space: 778
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Summary of online MYSQL synchronous error reporting and troubleshooting methods
