暂无图片
分享
liang
2020-03-11
多源复制部署,启动从库复制后报错,有一个channel无法同步,该如何解决?

环境说明:
两主一从的架构是基于MySQL 5.7.20版本的GTID多源复制。

192.168.0.12 db1
192.168.0.15 db2
192.168.0.16 db3

主A(MasterA):
192.168.0.12 db1

主B(MasterB):
192.168.0.15 db2

从库(slave):
192.168.0.16 db3

在从库上分别配置MasterA->slave和MasterB->slave的同步过程。
CHANGE MASTER TO
MASTER_HOST=‘192.168.0.12’,
MASTER_USER=‘bak’,
MASTER_PASSWORD=‘oracle’,
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1 for channel ‘m1’;

CHANGE MASTER TO
MASTER_HOST=‘192.168.0.15’,
MASTER_USER=‘rep1’,
MASTER_PASSWORD=‘oracle’,
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1 for channel ‘m2’;

开启主从复制:在从库
start slave for channel ‘m1’;
start slave for channel ‘m2’;

查看slave状态:
root@db 18:02: [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.12
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-A.000004
Read_Master_Log_Pos: 3058
Relay_Log_File: relay-bin-m1.000004
Relay_Log_Pos: 1703
Relay_Master_Log_File: mysql-bin-A.000002
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: 1007
Last_Error: Error ‘Can’t create database ‘testa’; database exists’ on query. Default database: ‘testa’. Query: ‘create database testa’
Skip_Counter: 0
Exec_Master_Log_Pos: 1446
Relay_Log_Space: 7754
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: 1007
Last_SQL_Error: Error ‘Can’t create database ‘testa’; database exists’ on query. Default database: ‘testa’. Query: ‘create database testa’
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330612
Master_UUID: 3fedec22-607d-11ea-8b22-000c29c10c0c
Master_Info_File: mysql.slave_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: 200310 18:02:15
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3fedec22-607d-11ea-8b22-000c29c10c0c:1-24
Executed_Gtid_Set: 3fedec22-607d-11ea-8b22-000c29c10c0c:1-6,
42ffd2c1-607d-11ea-8938-00505625afe8:1-23
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: m1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.15
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-B.000004
Read_Master_Log_Pos: 2811
Relay_Log_File: relay-bin-m2.000002
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin-B.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 2811
Relay_Log_Space: 624
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: 330615
Master_UUID: 42ffd2c1-607d-11ea-8938-00505625afe8
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 3fedec22-607d-11ea-8b22-000c29c10c0c:1-6,
42ffd2c1-607d-11ea-8938-00505625afe8:1-23
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: m2
Master_TLS_Version:
2 rows in set (0.00 sec)

ERROR:
No query specified

root@db 18:02: [(none)]>

发现channel m1有错误
Last_SQL_Errno: 1007
Last_SQL_Error: Error ‘Can’t create database ‘testa’; database exists’ on query. Default database: ‘testa’. Query: ‘create database testa’
现象是channel m1的复制无法同步数据,channel m2的复制可以同步数据,求助channel m1无法同步数据的解决方法。

收藏
分享
5条回答
默认
最新
田弼元

先把slave停了,set GLOBAL SQL_SLAVE_SKIP_COUNTER=1,将binglog指针下移之后再重新启动试试呢?

暂无图片 评论
暂无图片 有用 0
田弼元

GTID开启的话,先看看testa是真的存在吗?如果真的存在,根据Executed_Gtid_Set,set gtid_next注册一个空的事务,设置gtid_next=‘AUTOMATIC’再启动试试呢?

暂无图片 评论
暂无图片 有用 0
外包DBA

这是已经存在同名的数据库吧~

暂无图片 评论
暂无图片 有用 0
liang

首先,感谢各位的回复。
testa是主库MasterA上的库,要同步到slave库。testa是唯一的,在主库MasterB上没有testa。
由于开启gtid模式搭建多源复制。每个主库都会有一个gtid值同步到从库。所以在从库要设置多个GTID_PURGED。
GTID_PURGED值的来源:MasterA和MasterB在使用mysqldump导出时产生的gitd。查看导出的文件,里面记录了gtid值。

从MasterA导出的内容,含有如下信息:


– GTID state at the beginning of the backup

SET @@GLOBAL.GTID_PURGED=‘3fedec22-607d-11ea-8b22-000c29c10c0c:1-26’;

从MasterB导出的内容,含有如下信息:

– GTID state at the beginning of the backup

SET @@GLOBAL.GTID_PURGED=‘42ffd2c1-607d-11ea-8938-00505625afe8:1-24’;

解决方法:
在从库设置
SET @@GLOBAL.GTID_PURGED=‘3fedec22-607d-11ea-8b22-000c29c10c0c:1-26,42ffd2c1-607d-11ea-8938-00505625afe8:1-24’;

如下是操作记录:
root@db 18:43: [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.0.12’,
-> MASTER_USER=‘bak’,
-> MASTER_PASSWORD=‘oracle’,
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1 for channel ‘m1’;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

root@db 18:43: [(none)]>
root@db 18:43: [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.0.15’,
-> MASTER_USER=‘rep1’,
-> MASTER_PASSWORD=‘oracle’,
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1 for channel ‘m2’;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

root@db 18:43: [(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)
root@db 18:43: [(none)]>
root@db 18:45: [(none)]> SET @@GLOBAL.GTID_PURGED=‘3fedec22-607d-11ea-8b22-000c29c10c0c:1-26,42ffd2c1-607d-11ea-8938-00505625afe8:1-24’;
Query OK, 0 rows affected (0.01 sec)

root@db 18:45: [(none)]>
root@db 18:46: [(none)]> show master status;
±--------------------±---------±-------------±-----------------±-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------------±---------±-------------±-----------------±-------------------------------------------------------------------------------------+
| slave_binlog.000001 | 154 | | | 3fedec22-607d-11ea-8b22-000c29c10c0c:1-26,
42ffd2c1-607d-11ea-8938-00505625afe8:1-24 |
±--------------------±---------±-------------±-----------------±-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@db 18:46: [(none)]>

在从库启动复制:

root@db 18:46: [(none)]> start slave for channel ‘m1’;
Query OK, 0 rows affected (0.01 sec)

root@db 18:46: [(none)]>
root@db 18:46: [(none)]> start slave for channel ‘m2’;
Query OK, 0 rows affected (0.02 sec)

root@db 18:46: [(none)]>

查看复制状态
root@db 18:46: [(none)]> show slave status for channel ‘m1’\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.12
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-A.000004
Read_Master_Log_Pos: 3574
Relay_Log_File: relay-bin-m1.000002
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin-A.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 3574
Relay_Log_Space: 624
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: 330612
Master_UUID: 3fedec22-607d-11ea-8b22-000c29c10c0c
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 3fedec22-607d-11ea-8b22-000c29c10c0c:1-26,
42ffd2c1-607d-11ea-8938-00505625afe8:1-24
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: m1
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

root@db 18:46: [(none)]> show slave status for channel ‘m2’\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.15
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-B.000004
Read_Master_Log_Pos: 3069
Relay_Log_File: relay-bin-m2.000002
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin-B.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 3069
Relay_Log_Space: 624
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: 330615
Master_UUID: 42ffd2c1-607d-11ea-8938-00505625afe8
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 3fedec22-607d-11ea-8b22-000c29c10c0c:1-26,
42ffd2c1-607d-11ea-8938-00505625afe8:1-24
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: m2
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

root@db 18:46: [(none)]>
root@db 18:46: [(none)]> select * from performance_schema.replication_connection_status\G;
*************************** 1. row ***************************
CHANNEL_NAME: m1
GROUP_NAME:
SOURCE_UUID: 3fedec22-607d-11ea-8b22-000c29c10c0c
THREAD_ID: 35
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 2
LAST_HEARTBEAT_TIMESTAMP: 2020-03-10 18:47:01
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: m2
GROUP_NAME:
SOURCE_UUID: 42ffd2c1-607d-11ea-8938-00505625afe8
THREAD_ID: 37
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 1
LAST_HEARTBEAT_TIMESTAMP: 2020-03-10 18:46:40
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
2 rows in set (0.00 sec)

ERROR:
No query specified

root@db 18:47: [(none)]>

从上面输出的信息看,channel m1和channel m2都正常启动,状态正常。

暂无图片 评论
暂无图片 有用 0
liang
问题已关闭: 问题已经得到解决
暂无图片 评论
暂无图片 有用 0
回答交流
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签,敬请期待~~
暂无图片墨值悬赏