环境说明:
两主一从的架构是基于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无法同步数据的解决方法。
墨值悬赏

评论
