线上MySQL同步报错故障处理总结
公司使用云数据库，今天在MySQL从库上面查询相关数据时候，显示没有任何记录，登录后show slave status\G 查看到状态中报1032错误，这里把相关主从同步故障总结一下。
先上Master库：
mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。
mysql> show master status; 也是正常的。
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
解决方法：
方法一：忽略错误后，继续同步
适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况
mysql> Stop slave ;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了
方式二：指定跳过错误代码，继续同步
主键冲突、表已存在等错误代码如1062,1032,1060等，可以在mysql主配置文件指定略过此类异常并继续下条sql同步，这样也可以避免很多主从同步的异常中断
[mysqld]
slave-skip-errors = 1062,1032,1060
重新启动mysql
service mysqld restart
之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
方式三：重新做主从，完全同步
适用于主从库数据相差较大，或者要求数据完全统一的情况 。
1.先进入主库，进行锁表，防止数据写入
mysql> flush tables with read lock; 注意：该处是锁定为只读状态，语句不区分大小写
2.在主库上面进行数据备份
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
注意：数据库备份一定要定期进行，可以用shell脚本或者Python脚本，确保数据万无一失 。
3.查看master 状态
mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
4.把mysql备份文件传到从库机器，进行数据恢复
[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/
5.停止从库的状态
mysql> stop slave;
6.然后到从库执行mysql命令，导入数据备份
mysql> source /tmp/mysql.bak.sql
7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项
change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;
8.重新开启从库同步
mysql> start slave;
9.查看同步状态
mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
好了，同步完成啦。
MySQL出现同步延迟解决优化方法
1.主从复制的从库太多导致复制延迟
优化：建议从库数量3-5个为宜（具体看自己硬件配置）
2.从库硬件比主库硬件差
优化：提升硬件性能
3.慢SQL语句过多
优化：SQL语句执行时间太长，需要优化SQL语句（需要联系DBA开发共同商讨优化语句）
4.主从复制的设计问题
优化：主从复制单线程，可以通过多线程IO方案解决；另外MySQL5.6.3支持多线程IO复制。
5.主从库之间的网络延迟
优化：尽量链路短，提升端口带宽
6.主库读写压力大
优化：前端加buffer和缓存。主从延迟不同步（延迟的多少，只要不影响业务就没事）
7、业务设计缺陷导致延迟影响业务
优化：从库没有数据改读主库
前言
在发生故障切换后，经常遇到的问题就是同步报错，数据库很小的时候，在主库Mysqldump导出数据，再从库导入就处理好了，但如果线上的数据库都150G-200G，如果用单纯的这种方法，现总结了几种处理方法。
生产环境架构图
目前现网的架构，保存着两份数据，通过异步复制做的高可用集群，两台机器都提供对外服务。在发生故障时，切换到slave上，并将其变成master，坏掉的机器反向同步新的master，在处理故障时，遇到最多的就是主从报错。
异步半同步区别
异步复制
简单说就是master把binlog发送过去，不管slave是否接收完，也不管是否执行完，这一动作就结束了.
半同步复制
就是master把binlog发送过去，slave确认接收完，但不管它是否执行完，给master一个信号我这边收到了，这一动作就结束了。（5.5上正式应用。）
异步的劣势
当master上写操作繁忙时，当前POS点例如是10，而slave上IO_THREAD线程接收过来的是3，此时master宕机，会造成相差7个点未传送到slave上而数据丢失。
错误汇总
最常见的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
解决方法：由于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_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '192442' for key 'PRIMARY'' on query. Default database: 'xxxxxxxxxxxxxx'. Query: 'INSERT INTO xxxxxxxxxxx(playerId, `type`, `count`)VALUES( NAME_CONST('pPlayerId',629014986), NAME_CONST('pType',8), 0)'
解决方法：
在master上查看一下记录：
mysql> select * from hcy.t1 where id=192442;
+--------+-----------+------+-------+-------+---------------+
| id | playerId | type | count | total | lastResetTime |
+--------+-----------+------+-------+-------+---------------+
| 192442 | 629014986 | 8 | 0 | 0 | 0 |
+--------+-----------+------+-------+-------+---------------+
row in set (0.00 sec)
在slave上查看记录
mysql> select * from hcy.t1 where id=192442;
+--------+-----------+------+-------+-------+---------------+
| id | playerId | type | count | total | lastResetTime |
+--------+-----------+------+-------+-------+---------------+
| 192442 | 629015414 | 8 | 0 | 0 | 0 |
+--------+-----------+------+-------+-------+---------------+
row in set (0.00 sec)
在slaver上用desc hcy.t1; 先看下表结构：
mysql> desc hcy.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
可以看到slave的记录与主库不一样，当然以主库的记录为准，所以要在slave库上把主键冲突的记录删除掉，
slave上面删除重复的主键
mysql> delete from t1 where id=192442;
mysql> start slave;
mysql> show slave status\G;
....
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
mysql> select * from t1 where id=192442;
在master上和slave上再分别确认一下。
如果一两条，手动删除没什么问题，但比较多的时候，手动删除的效率是灰常慢的，下面写了个脚本，这个脚本只适合主键冲突的情况。
#!/bin/bash
#Delete duplicate records primary key conflict
mysql=/usr/local/mysql-5.1.66/bin/mysql
sock=/data/mysql-slave-3311/mysql.sock
passwd=123456
while true
do
SQL_THREAD=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | egrep 'Slave_SQL_Running' | awk '{print $2}'`
LAST_ERROR=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | egrep Last_Errno | awk '{print $2}'`
duplicate=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep Last_Error | awk '/Duplicate entry/{print $5}' | awk -F "'" '{print $2}'`
DATABASE=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep Last_Error | awk '{print $13}' | awk -F "'" '{print $2}'`
TABLE=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep Last_Error | awk -F ":" '{print $4}' | awk -F "(" '{print $1}' | awk '{print $NF}'`
$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep HA_ERR_FOUND_DUPP_KEY
if [ $? -eq 1 ]
then
if [ "$SQL_THREAD" == No ] && [ "$LAST_ERROR" == 1062 ]
then
FILED=`$mysql -uroot -p$passwd -S $sock -Nse "desc $DATABASE.$TABLE" | grep PRI | awk '{print $1}'`
$mysql -uroot -p$passwd -S $sock -e "delete from $DATABASE.$TABLE where $FILED=$duplicate"
$mysql -uroot -p$passwd -S $sock -e "start slave sql_thread"
else
echo "====================== ok ========================"
$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | egrep 'Slave_.*_Running'
echo "====================== ok ========================"
break
fi
fi
done
第三种：在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 = 60
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
在slave上，查找下更新后的那条记录，应该是不存在的。
1
2 mysql> select * from t1 where id=2;
Empty set (0.00 sec)
然后再到master查看
mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
| 2 | BTV |
+----+------+
把丢失的数据在slave上填补，然后跳过报错即可。
mysql> insert into t1 values (2,'BTV');
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;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
正常同步了。如果有N多数据缺失，得用pt-table-checksum校验数据一致性，为什么slave库上会少数据呢？我总结了以下几种情况：
1、当人为设置set session sql_log_bin=0时，当前session操作是不记录到Binlog的。
2、就是slave没设置为read only，在slave库上有删除操作
3、slave读取master的binlog日志后，需要落地3个文件：relay log、relay log info、master info，这三个文件如果不及时落地，则主机crash后会导致数据的不一致