欢迎访问优讯网!
您当前的位置:首页 > 爱编程

MySQL5.6 GTID模式下同步复制报错不能跳过的解决方法

时间:2020-04-13 16:31:06  来源:优讯网  作者:小卡司  浏览次数:
搭建虚拟机centos6.0, mysql5.6.10主从复制,死活不同步,搞了一整天找到这篇文章终于OK了,特分享一下,需要的朋友可以参考下

数据库版本:

mysql> select version();

+------------+ 
| version() | 
+------------+ 
| 5.6.10-log | 
+------------+ 
1 row in set (0.02 sec)

同步复制信息:

mysql> show slave status\G; 
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event 
Master_Host: 192.168.8.25 
Master_User: repl 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mysql-bin.000007 
Read_Master_Log_Pos: 5036 
Relay_Log_File: M2-relay-bin.000008 
Relay_Log_Pos: 408 
Relay_Master_Log_File: mysql-bin.000007 
Slave_IO_Running: Yes 
Slave_SQL_Running: No 
Replicate_Do_DB: 
Replicate_Ignore_DB: mysql 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 1062 
Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '12' 
for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, 
end_log_pos 2267 
Skip_Counter: 0 
Exec_Master_Log_Pos: 2045 
Relay_Log_Space: 3810 
Until_Condition: None 
Until_Log_File: 
Until_Log_Pos: 0 
Master_SSL_Allowed: No 
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: NULL 
Master_SSL_Verify_Server_Cert: No 
Last_IO_Errno: 0 
Last_IO_Error: 
Last_SQL_Errno: 1062 
Last_SQL_Error: Could not execute Write_rows event on table test.t; Duplicate entry '12' 
for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, 
end_log_pos 2267 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 25 
Master_UUID: cf716fda-74e2-11e2-b7b7-000c290a6b8f 
Master_Info_File: /usr/local/mysql/data2/master.info 
SQL_Delay: 0 
SQL_Remaining_Delay: NULL 
Slave_SQL_Running_State: 
Master_Retry_Count: 86400 
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 130313 07:24:43 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:141-151 
Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-140 
Auto_Position: 1 
1 row in set (0.02 sec) 

ERROR: 
No query specified

提示主键冲突,由于是测试机,于是我直接跳过,

mysql> set global sql_slave_skip_counter=1;

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with GTID_MODE = ON. 
Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction 
提示:由于运行在GTID模式,所以不支持sql_slave_skip_counter语法,如果你想跳过,就必须把事务ID设置为空值。
看来只能用这个方法了。

mysql> show global variables like '%GTID%';

+--------------------------+--------------------------------------------+ 
| Variable_name | Value | 
+--------------------------+--------------------------------------------+ 
| enforce_gtid_consistency | ON | 
| gtid_executed | cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-140 | 
| gtid_mode | ON | 
| gtid_owned | | 
| gtid_purged | cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-140 | 
+--------------------------+--------------------------------------------+ 
5 rows in set (0.04 sec)

mysql> set global gtid_executed=''; 
ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable 
mysql> 
mysql> set global gtid_purged='';

ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty. 
郁闷,直接设置还不行。

查看了手册,需要执行reset master才可以(注:在从上执行啊,千万别在主上)。



mysql> reset master; 
Query OK, 0 rows affected (0.16 sec) 

mysql> reset slave; 
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first 
mysql> stop slave; 
Query OK, 0 rows affected (0.08 sec) 

mysql> reset slave; 
Query OK, 0 rows affected (0.16 sec)

执行reset slave的目的是清空master.info和relay-log.info,以便后面重新change master to主从复制。
还记得刚才的gtid_purged那个点吗,只需重新设置下一个点即可。

下面是步骤:

mysql> show global variables like '%GTID%'; 
+--------------------------+-------+ 
| Variable_name | Value | 
+--------------------------+-------+ 
| enforce_gtid_consistency | ON | 
| gtid_executed | | 
| gtid_mode | ON | 
| gtid_owned | | 
| gtid_purged | | 
+--------------------------+-------+ 
5 rows in set (0.06 sec) 

mysql> set global gtid_purged='cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-141'; 
Query OK, 0 rows affected (0.16 sec) 

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.25',MASTER_USER='repl',MASTER_PASSWORD='repl'
,MASTER_AUTO_POSITION = 1; 
Query OK, 0 rows affected, 2 warnings (0.32 sec) 

mysql> start slave; 
Query OK, 0 rows affected (0.13 sec) 

mysql> show slave status\G; 
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event 
Master_Host: 192.168.8.25 
Master_User: repl 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mysql-bin.000007 
Read_Master_Log_Pos: 5036 
Relay_Log_File: M2-relay-bin.000008 
Relay_Log_Pos: 408 
Relay_Master_Log_File: mysql-bin.000007 
Slave_IO_Running: Yes 
Slave_SQL_Running: No 
Replicate_Do_DB: 
Replicate_Ignore_DB: mysql 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 1050 
Last_Error: Error 'Table 't0' already exists' on query. 
Default database: 'test'. Query: 'create table t0 like t' 
Skip_Counter: 0 
Exec_Master_Log_Pos: 2298 
Relay_Log_Space: 3557 
Until_Condition: None 
Until_Log_File: 
Until_Log_Pos: 0 
Master_SSL_Allowed: No 
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: NULL 
Master_SSL_Verify_Server_Cert: No 
Last_IO_Errno: 0 
Last_IO_Error: 
Last_SQL_Errno: 1050 
Last_SQL_Error: Error 'Table 't0' already exists' on query. 
Default database: 'test'. Query: 'create table t0 like t' 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 25 
Master_UUID: cf716fda-74e2-11e2-b7b7-000c290a6b8f 
Master_Info_File: /usr/local/mysql/data2/master.info 
SQL_Delay: 0 
SQL_Remaining_Delay: NULL 
Slave_SQL_Running_State: 
Master_Retry_Count: 86400 
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 130313 07:50:42 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:142-151 
Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-141 
Auto_Position: 1 
1 row in set (0.02 sec) 

ERROR: 
No query specified 
### 看,这里的报错信息已经不一样了,按照这种方法,重复执行,直到同步复制正常。

mysql> stop slave; 
Query OK, 0 rows affected (0.07 sec) 

mysql> reset master; 
Query OK, 0 rows affected (0.17 sec) 

mysql> reset slave; 
Query OK, 0 rows affected (0.16 sec) 

mysql> set global gtid_purged='cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-151'; 
Query OK, 0 rows affected (0.13 sec) 

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.25',MASTER_USER='repl',MASTER_PASSWORD='repl'
,MASTER_AUTO_POSITION = 1; 
Query OK, 0 rows affected, 2 warnings (0.33 sec) 

mysql> start slave; 
Query OK, 0 rows affected (0.11 sec) 

mysql> show slave status\G; 
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event 
Master_Host: 192.168.8.25 
Master_User: repl 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mysql-bin.000007 
Read_Master_Log_Pos: 5036 
Relay_Log_File: M2-relay-bin.000008 
Relay_Log_Pos: 408 
Relay_Master_Log_File: mysql-bin.000007 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
Replicate_Do_DB: 
Replicate_Ignore_DB: mysql 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0 
Last_Error: 
Skip_Counter: 0 
Exec_Master_Log_Pos: 5036 
Relay_Log_Space: 819 
Until_Condition: None 
Until_Log_File: 
Until_Log_Pos: 0 
Master_SSL_Allowed: No 
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0 
Master_SSL_Verify_Server_Cert: No 
Last_IO_Errno: 0 
Last_IO_Error: 
Last_SQL_Errno: 0 
Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 25 
Master_UUID: cf716fda-74e2-11e2-b7b7-000c290a6b8f 
Master_Info_File: /usr/local/mysql/data2/master.info 
SQL_Delay: 0 
SQL_Remaining_Delay: NULL 
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 
Master_Retry_Count: 86400 
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: 
Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-151 
Auto_Position: 1 
1 row in set (0.01 sec) 

ERROR: 
No query specified

呵呵,总算是解决好了,真麻烦啊。如果大家有更好、更方便的解决方法,也给我回帖哦,谢谢。
来顶一下
返回首页
返回首页

原文链接:https://www.jb51.net/article/184540.htm


推荐资讯
如何下载旧版centos iso镜像 如何下载迷你mini版的centos镜像
如何下载旧版centos i
计算机的正确使用姿势 电脑痴如何正确的使用电脑
计算机的正确使用姿势
好用的后台管理的前端框架模版H-ui H-ui框架模版分享
好用的后台管理的前端
微信电脑多开方法 无需辅助电脑版微信双开方法分享
微信电脑多开方法 无
相关文章
栏目更新
栏目热门