MySQL主从同步报错故障处理记录
从库上记录删除失败,Error_code: 1032
问题描述：在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;
如果这种情况很多，需要针对这种错误专门写相关脚本。
或者 在从库配置文件中配置，直接跳过不影响业务的错误号
# grep "slave-skip" /etc/my.cnf
slave-skip-errors = 1032,1062,1007
主键重复,Error_code: 1062
问题描述：在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上再分别确认一下。
更新丢失,Error_code: 1032
问题描述：在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.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 = 60COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* 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……
1236错误, 二进制文件缺失,
问题描述：误删二进制文件等各种原因，导致主库mysql-bin.000012文件丢失，从库同步失败。
Master_Log_File: mysql-bin.000012Slave_IO_Running: NoSlave_SQL_Running: YesLast_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'
解决办法如下：
#首先停止从库同步
slave stop;
#查看主库日志文件和位置
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000013 | 154 |
+------------------+-----------+
#回从库，使日志文件和位置对应主库
CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000013',MASTER_LOG_POS=154;
# 最后，启动从库：
slave start;
show slave status\G;
Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Error:
