mysql8主从复制配置 mysql主从复制问题

一、修复已中断数据复制的方法

在MySQL复制中，如果Slave节点上遇到错误，比如数据不存在或者主键冲突等错误时，想要忽略这些错误(你要明确清楚你忽略的数据造成主从不一致性，且你能够接受这个不一致性所带来的问题)。有一种情况需要注意，如果明确是主键冲突，应先采取主从数据对比，如果数据一致则忽略跳过这些错误，如果数据不一致则要先删除Slave上的数据，然后重写关闭开启SQL线程，然后在比较主从数据的一致性，一定保证数据一致。

可以采用以下几种方法：

1、未启用GTID模式时

只需通过设定SQL_SLAVE_SKIP_COUNTER的值，即可忽略一些复制事件(注意这里是event，不是transaction，这两个是很有区别的)。使用方式如下：

# 需要先关闭SLAVE服务;

mysql> STOP SLAVE;

# 忽略N个事件(event)，通常一个SQL是一个事件;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=N;

# 再次启动SLAVE服务;

mysql> START SLAVE;

# 需要先关闭SLAVE服务;

mysql>STOPSLAVE;

# 忽略N个事件(event)，通常一个SQL是一个事件;

mysql>SETGLOBALSQL_SLAVE_SKIP_COUNTER=N;

# 再次启动SLAVE服务;

mysql>STARTSLAVE;

忽略特定的错误码，使用静态参数slave_skip_errors，如忽略：

Last_Errno: 1054

Last_Error: Error 'Unknown column 'notify_url' in 'field list'' on query. Default database: 'pay_loki'. Query:

'UPDATE `pay_coupon_activity` SET `notify_url` = 'http://api.bilibili.com/lottery/notify?gift_id=8' WHERE `id` = '131' AND `ver` = '1''

Last_Errno:1054

Last_Error:Error'Unknown column 'notify_url' in 'fieldlist''onquery.Defaultdatabase:'pay_loki'.Query:

'UPDATE `pay_coupon_activity` SET `notify_url` = 'http://api.bilibili.com/lottery/notify?gift_id=8' WHERE `id` = '131' AND `ver` = '1''

在配置文件中添加参数即可，需要重启数据库：

slave_skip_errors = 1054

slave_skip_errors=1054

如果有多个错误码，使用逗号分隔即可。

如果在多源复制模式下，可能需要注意一点不同，需要设置default_master_connection变量为需要忽略的复制通道。

mysql> set @@default_master_connection='name';

mysql>set@@default_master_connection='name';

然后再关闭某个复制通道，执行SQL_SLAVE_SKIP_COUNTER。

2、启用GTID模式时

启用GTID，想要忽略某些错误事件就稍微麻烦一点点了。

首先，我们需要先查看当前SLAVE复制的进度：

mysql> SHOW SLAVE STATUS/G

mysql>SHOWSLAVESTATUS/G

从中看到，当前SLAVE复制的GTID进展是：

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno: 1062

Last_Error: …Duplicate…key ‘PRIMARY’, Error_code: 1062;…

Master_UUID: f2b6c829-9c87-11e4-84e8-deadeb54b599

Retrieved_Gtid_Set: 3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-33

Executed_Gtid_Set: 3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-31

Auto_Position: 1

Slave_IO_Running:Yes

Slave_SQL_Running:No

Last_Errno:1062

Last_Error:…Duplicate…key‘PRIMARY’,Error_code:1062;…

Master_UUID:f2b6c829-9c87-11e4-84e8-deadeb54b599

Retrieved_Gtid_Set:3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-33

Executed_Gtid_Set:3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-31

Auto_Position:1

从上面的信息可以看到，当前从MASTER取到了 1-33 的事务列表，并且已执行(看 Executed_Gtid_Set )到了 31 这个事务GTID位置，在这下一个位置( 32 )上发生错误。

这时候，我们需要手工调整SLAVE已清除的GTID列表 GTID_PURGED ，人为通知SLAVE哪些事务已经被清除了，后续可以忽略：

mysql> STOP SLAVE;

mysql> RESET MASTER;

mysql> SET @@GLOBAL.GTID_PURGED = "3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-32";

mysql> START SLAVE;

mysql>STOPSLAVE;

mysql>RESETMASTER;

mysql>SET@@GLOBAL.GTID_PURGED="3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-32";

mysql>STARTSLAVE;

上面这些命令的用意是，忽略 f2b6c829-9c87-11e4-84e8-deadeb54b599:32 这个GTID事务，下一次事务接着从 33 这个GTID开始，即可跳过上述错误。

3、无论是否启用GTID，都使用pt-slave-restart工具

首先不得不说，percona toolkit工具集对DBA而言实在太方便了。 pt-slave-restart工具的作用是监视某些特定的复制错误，然后忽略，并且再次启动SLAVE进程(Watch and restart MySQL replication after errors)。

简单用法示例：

#忽略所有1062错误，并再次启动SLAVE进程

$ pt-slave-resetart -S ./mysql.sock --error-numbers=1062

$pt-slave-resetart-S./mysql.sock--error-numbers=1062

#检查到错误信息只要包含 test.yejr，就一概忽略，并再次启动 SLAVE 进程

$ pt-slave-resetart -S ./mysql.sock --error-text=”test.yejr”

$pt-slave-resetart-S./mysql.sock--error-text=”test.yejr”

综上，我们虽然可以利用工具来快速忽略复制错误，但还是要掌握如何人为忽略复制错误的方法，在没有工具的时候也能了然于胸。但是不管那种错误忽略的临时方案，都不是合适的解决方案。而刚刚发生的是我们引发了主数据库和从数据库中的数据不一致。MySQL数据复制是个非同步过程，并且不会执行表中基础数据的一致性校验和。只要SQL语句是在没有错误的情况下执行完毕，数据复制就会报告成功，而不管有多少行数据受到了影响。

了解了各种复制模式下的忽略错误的方式之后，下面进行对于忽略错误时应该注意的问题进行探讨。

二、诊断数据复制错误的基本信息

首先给出这么一个复制错误的场景，我们首先要做的就是观察服务器的当前数据复制状态。我们会获得如下SQL命令：

slave> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.48
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001220
Read_Master_Log_Pos: 3453586
Relay_Log_File: relay-log.003586
Relay_Log_Pos: 3452185
Relay_Master_Log_File: mysql-bin.001220
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: 1062
Last_Error: Error 'Duplicate entry
'42-2011-04-16 00:00:00'for key 'user_id'' on query. Default database:
'book'. Query: 'INSERT INTOproduct_comment(product_id,user_id,comment_dt,
comment) VALUES (20,42,'2011-04-16 00:00:00','I found this very useful
with product Y')'

slave>SHOWSLAVESTATUS\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:10.0.0.48
Master_User:repl
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.001220
Read_Master_Log_Pos:3453586
Relay_Log_File:relay-log.003586
Relay_Log_Pos:3452185
Relay_Master_Log_File:mysql-bin.001220
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:1062
Last_Error:Error'Duplicateentry
'42-2011-04-16 00:00:00'forkey'user_id''onquery.Defaultdatabase:
'book'.Query:'INSERTINTOproduct_comment(product_id,user_id,comment_dt,
comment)VALUES(20,42,'2011-04-16 00:00:00','Ifoundthisveryuseful
withproductY')'

下面的几个指标指出了上述输出中的数据复制问题：

● SQL线程没有运行，这个结论可以从下列输出看出：

Slave_SQL_Running=No

● 数据复制滞后量未知，这个结论可以从下列输出看出：

Seconds_Behind_Master=NULL

● 在Last_Errno和Last_Error中被标识出来的错误信息。

在突发状况下发生错误报告时，将问题修正以确保数据可以被从服务器使用，这比找出导致这个问题的原因更重要。所以可以根据场景使用上面的三种操作方式临时修复复制错误问题。在执行SQL_SLAVE_SKIP_COUNTER命令之前，了解SQL语句为什么失败是非常重要的。因为目前虽然只忽略了一个错误，但是后面的SQL语句很可能再次导致MySQL数据复制停止运行。 你认为在跳过多少条SQL语句后才应该引起我们的注意呢？

相比自动修正问题而言，了解为什么MySQL中的数据复制没有正常运行，显得更加重要；是因为错误？还是MySQL数据复制被其他原因中断，例如备份程序正在运行，又或者某个软件正在更新？Slave_SQL_Running=No标识符的出现不仅仅是个非预期错误结果，STOP SLAVE SQL_THREAD语句也能产生同样的情况。这是为什么在这种情况下警报不一定准确的原因之一。所以应该在Slave_SQL_Running=No标识符出现时产生警报。然而，我们明显不想每天早上的两点钟因为备份程序主动终止MySQL数据复制而被吵醒。只有当备份程序失效而且长时间没有重启时才应该启动这个警报。MySQL数据复制包括两个线程，在上面的SHOW SLAVE STATUS输出中我们已经提到过。另外，I/O这个线程仍然和SLAVE_IO_Running=Yes一起运行。同时监控两个线程的状态是十分重要的。

SHOW SLAVE STATUS命令输出结果中的Last_Error列显示发生了重复键错误。这可以通过检查基础数据库表的结构来确定，然后查看以下当前数据：

slave> SHOW CREATE TABLE product_comment\G
*************************** 1. row ***************************
Table: product_comment
Create Table: CREATE TABLE 'product_comment' (
'comment_id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'product_id' int(10) unsigned NOT NULL,
'user_id' int(10) unsigned NOT NULL,
'comment_dt' datetime NOT NULL,
'comment' varchar(1000) NOT NULL,
PRIMARY KEY ('comment_id'),
UNIQUE KEY 'user_id' ('user_id','comment_dt')
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

slave>SHOWCREATETABLEproduct_comment\G
***************************1.row***************************
Table:product_comment
CreateTable:CREATETABLE'product_comment'(
'comment_id'int(10)unsignedNOTNULLAUTO_INCREMENT,
'product_id'int(10)unsignedNOTNULL,
'user_id'int(10)unsignedNOTNULL,
'comment_dt'datetimeNOTNULL,
'comment'varchar(1000)NOTNULL,
PRIMARYKEY('comment_id'),
UNIQUEKEY'user_id'('user_id','comment_dt')
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=latin1

如你所见，在user_id和comment_dt列中有个叫做user_id的唯一键(UNIQUE KEY)。这个唯一键对应着错误信息中的编号。MySQL只能为唯一键或主键产出一条重复条目的错误信息。来自Last_Error列的值可以用来决定被插入数据的真实值。下面这条SQL语句可以用来验证那些导致错误的数据：

slave> SELECT * FROM product_comment WHERE user_id = 42 AND comment_dt = '2011-04-16 00:00:00'\G
*************************** 1. row ***************************
comment_id: 1
product_id: 10
user_id: 42
comment_dt: 2011-04-16 00:00:00
comment: The packaging does not state this requires X
(1 row in set (0.01 sec)

slave>SELECT*FROMproduct_commentWHEREuser_id=42ANDcomment_dt='2011-04-16 00:00:00'\G
***************************1.row***************************
comment_id:1
product_id:10
user_id:42
comment_dt:2011-04-1600:00:00
comment:ThepackagingdoesnotstatethisrequiresX
(1rowinset(0.01sec)

实际上，导致重复键异常的MySQL数据复制错误的SQL语句本来是正确的。那说明什么呢？说明这条语句是无效的？我们应该忽视它吗？存在表格中的数据是错误的吗？应该把它删除吗？ product_id和comment的值实际上是不同的，这意味着这条语句和上一条语句并不相同。只有被唯一键限制的user_id和comment_dt 列是重复的。此时，我们还没有足够的信息去做决定。其中一个选择就是审核MySQL的主数据库，并找出需要验证的数据：

master> SELECT * FROM product_comment WHERE user_id = 42 AND comment_dt = '2011-04-16 00:00:00'\G
*************************** 1. row ***************************
comment_id: 1
product_id: 10
user_id: 42
comment_dt: 2011-04-16 00:00:00
comment: The packaging does not state this requires X
*************************** 2. row ***************************
comment_id: 2
product_id: 20
user_id: 42
comment_dt: 2011-04-16 00:00:00
comment: I found this very useful when used with product Y
(2 rows in set (0.01 sec)

master>SELECT*FROMproduct_commentWHEREuser_id=42ANDcomment_dt='2011-04-16 00:00:00'\G
***************************1.row***************************
comment_id:1
product_id:10
user_id:42
comment_dt:2011-04-1600:00:00
comment:ThepackagingdoesnotstatethisrequiresX
***************************2.row***************************
comment_id:2
product_id:20
user_id:42
comment_dt:2011-04-1600:00:00
comment:IfoundthisveryusefulwhenusedwithproductY
(2rowsinset(0.01sec)

我们发现在主节点数据和从节点数据中存在着差异。而这个差异并不在我们的预期内， 因为这违反了已经定义的唯一键约束。通过验证主数据库中的表结构，我们可以得到：

master> SHOW CREATE TABLE product_comment\G
*************************** 1. row ***************************
Table: product_comment
Create Table: CREATE TABLE 'product_comment' (
'comment_id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'product_id' int(10) unsigned NOT NULL,
'user_id' int(10) unsigned NOT NULL,
'comment_dt' datetime NOT NULL,
'comment' varchar(1000) NOT NULL,
PRIMARY KEY ('comment_id'),
KEY 'user_id' ('user_id','comment_dt')
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

master>SHOWCREATETABLEproduct_comment\G
***************************1.row***************************
Table:product_comment
CreateTable:CREATETABLE'product_comment'(
'comment_id'int(10)unsignedNOTNULLAUTO_INCREMENT,
'product_id'int(10)unsignedNOTNULL,
'user_id'int(10)unsignedNOTNULL,
'comment_dt'datetimeNOTNULL,
'comment'varchar(1000)NOTNULL,
PRIMARYKEY('comment_id'),
KEY'user_id'('user_id','comment_dt')
)ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=latin1

有一个键位于user_id和comment_dt这两个列中。然而，进一步审查就能发现这个键不再是唯一约束键。鉴于MySQL中是从主节点向从节点进行数据复制，我们可能会问这种情况是如何发生的。在MySQL中，从节点有不同的表结构，但是仍能正常操作，这样的可能性是存在的。在上面这个例子中，这个不同的结构后来导致了错误。现在有多种技术和工具可以用来对比不同MySQL实例的数据库对象，以区别它们之间的不同。

三、再说sql_slave_skip_counter

前面说了sql_slave_skip_counter=N参数，在官方文档中明确指出N表示的是N个event，不是事务。很多同学把这个参数的单位理解成事务的原因在于他把操作都理解成了由单条语句所组成的事务。这时N的含义两者等价。

然而，更常见的场景却是一个事务可能由多个EVENT组成，例如一个事务可以有多个SQL组成，例如：BEGIN; INSERT …；DELETE …; INSERT; COMMIT； ，若这时将N理解成事务，那么结果就会非常不同。

但是如果细看文档的话，文档最后一段还说道：

When you use SET GLOBAL sql_slave_skip_counter to skip events and the results in the middle of a group, the slave continues to skip events until it reaches the end of the group. Execution then starts with the next event group.

这句非常关键，也就是说可能上面的答案是：以上全错。当你设置sql_slave_skip_counter跳过组中间事件时，那么slave将继续跳过下一个事件，直到到达组的末尾；然后从下一个事件组开始执行。假设开始时主、从的数据分别为：

Master
Slave
+—-+
| a  |
+—-+
|  1 |
|  2 |
|  3 |
+—-+
+—-+
| a  |
+—-+
|  1 |
|  3 |
+—-+

由于主从数据已然不一致，这时若在主上执行下面的SQL：

BEGIN;
INSERT INTO Z SELECT 4;
DELETE FROM Z WHERE a = 2;
INSERT INTO Z SELECT 5;
COMMIT;

BEGIN;
INSERTINTOZSELECT4;
DELETEFROMZWHEREa=2;
INSERTINTOZSELECT5;
COMMIT;

从机显然会报错，提示1032错误，因为记录2这条记录并没有找到。这时的处理方法很多DBA会选择执行SET GLOBAL sql_slave_skip_counter=1。然而，这样的处理会导致INSERT 5这条记录不被执行。因为跳过DELETE 2这个操作后，事务没有结束，会继续跳过接下去的event，这就是文档中说的：the slave continues to skip events until it reaches the end of the group。感兴趣的同学可以自行测试看看最终的结果。

若只想跳过一个EVENT该怎么做呢？其实，只需将参slave_exec_mode设置为IDEMPOTENT即可。

出现主从复制出错，最安全的方法还是重建从机。另外，MySQL的复制早就是crash safe的复制机制了。若你的复制环境经常出错，应该好好检查下数据库的配置参数。

对于参数sql_skip_slave_counter来说，这只是一个不得已为之的方法。同时，当开启GTID模式时，也无法通过参数进行跳过出错的EVENT。

四、MySQL主从错误自动修复工具

mysql_repl_repair.py是一款用于修复mysql主从复制错误的python小工具，该工具可以修复由于主从数据不一致导致的1062(duplicate key), 1032(key not found)错误。当遇到复制出错，mysql_repl_repair.py会流式读取relay log中的数据，并构造成修复sql，在从库上执行，解决sql线程apply时遇到的问题。mysql_repl_repair.py非常轻巧，即使在遇到大事务时也不会对服务器造成性能影响，mysql_repl_repair.py支持以daemon方式后台运行，支持单机多实例下同时修复多个实例

目前网易内部的使用方法：监控服务定期监控MySQL主从复制状态，如遇1062，1032则执行mysql_repl_repair.py进行修复。