线上MYSQL同步报错故障处理⽅法总结（必看篇）
前⾔
在发⽣故障切换后，经常遇到的问题就是同步报错，数据库很⼩的时候，dump完再导⼊很简单就处理好了，但线上的数据库都150G-200G，如果⽤单纯的这种⽅法，成本太⾼，故经过⼀段时间的摸索，总结了⼏种处理⽅法。
⽣产环境架构图
⽬前现⽹的架构，保存着两份数据，通过异步复制做的⾼可⽤集，两台机器提供对外服务。在发⽣故障时，切换到slave 上，并将其变成master，坏掉的机器反向同步新的master，在处理故障时，遇到最多的就是主从报错。下⾯是我收录下来的报错信息。
常见错误
最常见的3种情况
这3种情况是在HA切换时，由于是异步复制，且sync_binlog=0，会造成⼀⼩部分binlog没接收完导致同步报错。
第⼀种：在master上删除⼀条记录，⽽slave上不到。
Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Can'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
第⼆种：主键重复。在slave已经有该记录，⼜在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
第三种：在master上更新⼀条记录，⽽slave上不到，丢失了数据。
Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Can't find record in 't1',
Error_code: 1032;mysql下载失败怎么办
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263
异步半同步区别
异步复制
简单的说就是master把binlog发送过去，不管slave是否接收完，也不管是否执⾏完，这⼀动作就结束了.
半同步复制
简单的说就是master把binlog发送过去，slave确认接收完，但不管它是否执⾏完，给master⼀个信号我这边收到了，这⼀动作就结束了。（⾕歌写的代码，5.5上正式应⽤。）
异步的劣势
当master上写操作繁忙时，当前POS点例如是10，⽽slave上IO_THREAD线程接收过来的是3，此时master宕机，会造成相差7个点未传送到slave上⽽数据丢失。
特殊的情况
slave的中继⽇志relay-bin损坏。
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
这种情况SLAVE在宕机，或者⾮法关机，例如电源故障、主板烧了等，造成中继⽇志损坏，同步停掉。
⼈为失误需谨慎：多台slave存在重复server-id
这种情况同步会⼀直延时，永远也同步不完，error错误⽇志⾥⼀直出现上⾯两⾏信息。解决⽅法就是
把server-id改成不⼀致即可。
Slave: received end packet from server, apparent master shutdown:
Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000012' at postion 106
问题处理
删除失败
在master上删除⼀条记录，⽽slave上不到。
Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Can'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
解决⽅法：
由于master要删除⼀条记录，⽽slave上不到故报错，这种情况主上都将其删除了，那么从机可以直接跳过。可⽤命令：
stop slave;
set global sql_slave_skip_counter=1;
start slave;
如果这种情况很多，可⽤我写的⼀个脚本skip_error_replcation.sh，默认跳过10个错误（只针对这种情况才跳，其他情况输出错误结果，等待处理），这个脚本是参考maakit⼯具包的mk-slave-restart原理⽤shell写的，功能上定义了⼀些⾃⼰的东西，不是⽆论什么错误都⼀律跳过。）
主键重复
在slave已经有该记录，⼜在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
解决⽅法：
在slave上⽤desc hcy.t1; 先看下表结构：
mysql> desc hcy.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id  | int(11) | NO  | PRI | 0    |    |
| name | char(4) | YES |  | NULL  |    |
+-------+---------+------+-----+---------+-------+
删除重复的主键
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;
在master上和slave上再分别确认⼀下。
更新丢失
在master上更新⼀条记录，⽽slave上不到，丢失了数据。
Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Can'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
解决⽅法：
在master上，⽤mysqlbinlog 分析下出错的binlog⽇志在⼲什么。
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000
010 | 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 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
在slave上，查下更新后的那条记录，应该是不存在的。
mysql> select * from t1 where id=2;
Empty set (0.00 sec)
然后再到master查看
mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
| 2 | BTV |
+----+------+
1 row in set (0.00 sec)
把丢失的数据在slave上填补，然后跳过报错即可。
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
…
…
中继⽇志损坏
slave的中继⽇志relay-bin损坏。
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
⼿⼯修复
解决⽅法：到同步的binlog和POS点，然后重新做同步，这样就可以有新的中继⽇值了。例⼦：
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 ：接收master的binlog信息
Master_Log_File
Read_Master_Log_Pos
Slave_SQL_Running：执⾏写操作
Relay_Master_Log_File
Exec_Master_Log_Pos
以执⾏写的binlog和POS点为准。
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:
Ibbackup
各种⼤招都⽤上了，⽆奈slave数据丢失过多，ibbackup（需要银⼦）该你登场了。
Ibbackup热备份⼯具，是付费的。xtrabackup是免费的，功能上⼀样。
Ibbackup备份期间不锁表，备份时开启⼀个事务（相当于做⼀个快照），然后会记录⼀个点，之后数据的更改保存在ibbackup_logfile⽂件⾥，恢复时把ibbackup_logfile 变化的数据再写⼊到ibdata⾥。
Ibbackup 只备份数据（ ibdata、.ibd ），表结构.frm不备份。
下⾯⼀个演⽰例⼦：
备份：ibbackup /bak/etc/my_localf /bak/etc/my_bakf
恢复：ibbackup --apply-log /bak/etc/my_bakf
[root@vm01 etc]# more my_localf
datadir =/usr/local/mysql/data
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 100M
innodb_log_file_size = 5M
innodb_log_files_in_group=2
[root@vm01 etc]# ibbackup /bak/etc/my_localf /bak/etc/my_bakf
InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
License A21488 is granted to vm01 (chunyang_he@126)
(--apply-log works in any computer regardless of the hostname)
Licensed for use in a computer whose hostname is 'vm01'
Expires 2012-5-1 (year-month-day) at 00:00
See www.innodb for further information
Type ibbackup --license for detailed license terms, --help for help
Contents of /bak/etc/my_localf:
innodb_data_home_dir got value /usr/local/mysql/data
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /usr/local/mysql/data
innodb_log_group_home_dir got value /usr/local/mysql/data
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880
Contents of /bak/etc/my_bakf:
innodb_data_home_dir got value /bak/data
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /bak/data
innodb_log_group_home_dir got value /bak/data
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。