你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL同步报错故障处理及同步不一致进行邮件报警

[日期:2017-02-25] 来源:Linux社区  作者:Linux [字体: ]

线上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后会导致数据的不一致

更多详情见请继续阅读下一页的精彩内容http://www.linuxidc.com/Linux/2017-02/141061p2.htm

linux
相关资讯       MySQL同步 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款