Mysql创建相同名字库_Mysql 5.7 多源复制 创建相同的数据库时提示 Slave: Can't create database 'test'; database exists Error_c...

Mysql 5.7 多源复制有个限制，源数据库的名称不能相同。下面测试在两个源主库创建相同数据库test,第二个主库创建的时候查看从库状态报错如下所示：

查看从库进程状态

https://www.cndba.cn/leo1990/article/2720

https://www.cndba.cn/leo1990/article/2720

mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_2'/G;

...

Last_SQL_Errno: 1007

Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'

...

错误日志：https://www.cndba.cn/leo1990/article/2720

https://www.cndba.cn/leo1990/article/2720https://www.cndba.cn/leo1990/article/2720https://www.cndba.cn/leo1990/article/2720https://www.cndba.cn/leo1990/article/2720https://www.cndba.cn/leo1990/article/2720

2018-04-11T06:31:41.608259Z 14 [Note] Slave SQL thread for channel 'master_2' initialized, starting replication in log 'master2-bin.000004' at position 411, relay log './relay-bin-master_2.000007' position: 628

2018-04-11T06:31:41.608381Z 14 [ERROR] Slave SQL for channel 'master_2': Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test', Error_code: 1007

2018-04-11T06:31:41.608402Z 14 [Warning] Slave: Can't create database 'test'; database exists Error_code: 1007

2018-04-11T06:31:41.608410Z 14 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master2-bin.000004' position 411

解决方法：https://www.cndba.cn/leo1990/article/2720https://www.cndba.cn/leo1990/article/2720

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

将binglog指针下移一个位置。

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave for CHANNEL 'Master_2';

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS FOR CHANNEL 'Master_2'/G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.33

Master_User: rep2

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master2-bin.000004

Read_Master_Log_Pos: 720

Relay_Log_File: relay-bin-master_2.000010

Relay_Log_Pos: 322

Relay_Master_Log_File: master2-bin.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: 720

Relay_Log_Space: 700

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: 33

Master_UUID: 73acf59b-3d3c-11e8-b976-080027686323

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:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: master_2

Master_TLS_Version:

1 row in set (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。