MySQL主从复制问题总结及排查过程

一、概述

mysql主从是常用的高可用架构之一，也是使用最广泛的的系统架构。在生产环境中mysql主从复制有时会出现复制错误问题。mysql主从复制中的问题(coordinator stopped beacause there were errors in the workers……)

二、mysql主从复制原理

mysql主从复制是一个异步复制过程(总体感觉是实时同步的),mysql主从复制整个过程是由三个线程完成。slave端有两个线程(sql线程和io线程)，master端有另一个(io线程)。

1.mysql主从复制过程

• 在slave服务器上执行start slave，开启主从复制开关。
• 此时，slave 服务器上的 io 线程通过 master 服务器上授权复制用户的请求连接到 master 服务器。它还请求从 binlog 日志文件的指定位置发送 binlog 日志内容。 (配置主从复制任务时执行change master命令时指定日志文件名和位置)
• master服务器收到slave服务器io线程的请求后，master服务器上的io线程是基于slave的。 服务器的io线程请求的信息在指定binlog日志文件的指定位置后读取binlog日志信息，然后返回给slave端io线程。除了binlog日志内容，在日志内容返回后master服务器端还有一个新的binlog。 binlog 中的文件名和下一个指定的更新位置。
• 当 slave 服务器的 io 线程从 master 服务器获取 io 线程发送的日志内容、日志文件和位置点时，添加 binlog。日志内容依次写入slave端自身的relay log文件(mysql-relay-bin.xxxxxx)的末尾。并将新的binlog文件名和位置记录到master-info文件中，以便下次读取master端新的binlog日志时，可以告诉master服务器从新的binlog日志中从哪个文件以及从哪里开始请求新的binlog日志内容.
• slave server端的sql线程实时检测本地relay log中新增的日志内容，及时relay log。 该文件的内容被解析成在master端执行的sql语句的内容，在slave服务器本身按照语句的顺序执行sql的应用。
• 经过上述过程，可以保证在master和slave端执行相同的sql语句。当复制状态正常时，master 端和lave端的数据是完全一致的。

三、问题及解决方法

1.show slave status \g 显示如下报错信息

coordinator stopped because there were error(s) in the worker(s). the most recent failure being: worker 1 failed executing transaction …

2.根据提示信息定位报错位置

情况一：“**delete_rows”**

select * from performance_schema.replication_applier_status_by_worker \g

原因：在master上删除一条记录，而slave上找不到。

解决方法： 由于master要删除一条记录，而slave上找不到故报错，这种情况主上都将其删除了，那么从机可以直接跳过。

stop slave;
set global sql_slave_skip_counter=1;
start slave;

如上命令若报错：error 1858 (hy000): sql_slave_skip_counter can not be set when the server is running with @@global.gtid_mode = on. instead, for each transaction that you want to skip, generate an empty transaction with the same gtid as the transaction或者可以换用如下命令：

stop slave;
set @@session.gtid_next= 'f396f867-d755-11xxx85-005xxxxxb5a:264261655'    --在session里设置gtid_next，即跳过这个gtid
begin; commit;       --设置空事物
set session gtid_next = automatic;     -- 恢复gtid
start slave;xxxx

情况二：“duplicate “

last_sql_error: could not execute write_rows event on table xxx;
duplicate entry 'xxx' for key 'primary',

原因：在slave已经有该记录，又在master上插入了同一条记录

解决方法：在从库上删除该记录，或者跳过该记录。然后在master上和slave上再分别确认一下。

情况三：“update_rows” (还未碰到 待验证)

last_sql_error: could not execute update_rows event on table xxx;
can't find record in 'xxx',

参考原因：在master上更新一条记录，而slave上找不到，丢失了数据。

参考方法：在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上，查找下更新后的那条记录，应该是不存在的。

mysql> select * from t1 where id=2; empty set (0.00 sec)

然后再到master查看

ysql> 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

四、通用解决方法

mysql主从复制，经常会遇到错误而导致slave端复制中断，这个时候一般就需要人工干预，跳过错误才能继续 跳过错误有两种方式

1. 跳过指定数量的事务

mysql>slave stop;
mysql>set global sql_slave_skip_counter = 1        #跳过一个事务
mysql>slave start

2. 跳所有错误或指定类型的错误

修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误

vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误

到此这篇关于mysql主从复制问题总结及排查过程的文章就介绍到这了,更多相关mysql主从复制内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！