mysql报错修复_MySQL 5.7.9多源复制报错修复

版本：5.7.9

用5.7.9的版本搭建MySQL多源复制测试环境

开发说复制出现问题，上去看了一下：

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 182.2.2.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 95459627

Relay_Log_File: relay-bin-finance.000002

Relay_Log_Pos: 1479683

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%

Replicate_Wild_Ignore_Table:

Last_Errno: 1061

Last_Error: Error 'Duplicate key name 'index_ShippingId_WareId'' on query. Default database: 'B'. Query: 'alter table `swc_relation` add index `index_ShippingId_WareId` (`ShippingId`, `WareId`)'

Skip_Counter: 0

Exec_Master_Log_Pos: 95092986

Relay_Log_Space: 1848783

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

Last_SQL_Error: Error 'Duplicate key name 'index_ShippingId_WareId'' on query. Default database: 'B'. Query: 'alter table `swc_relation` add index `index_ShippingId_WareId` (`ShippingId`, `WareId`)'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2213306

Master_UUID: 67b146d5-7958-11e5-a908-f8bc123d566c

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: 151124 13:33:41

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB: (A,B)

Channel_Name: A

*************************** 2. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.178.9.69

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 992559812

Relay_Log_File: relay-bin-jxorder.000012

Relay_Log_Pos: 152738

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 992559812

Relay_Log_Space: 460320

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

Master_UUID: d0b6e95c-65ae-11e5-a494-52540064d608

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: (A,B)

Channel_Name: C

*************************** 3. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.178.9.79

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000024

Read_Master_Log_Pos: 731827753

Relay_Log_File: relay-bin-jxtms.000055

Relay_Log_Pos: 3017204

Relay_Master_Log_File: mysql-bin.000024

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 731827753

Relay_Log_Space: 11134306

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

Master_UUID: bc039305-66c9-11e5-abc9-5254007ce9e7

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: (A,B)

Channel_Name: D

*************************** 4. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.178.9.71

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 520645176

Relay_Log_File: relay-bin-jxuser.000012

Relay_Log_Pos: 4905

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 520645176

Relay_Log_Space: 13313

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

Master_UUID: 7e03aa04-6263-11e5-8f1a-52540017c59d

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: (F,B)

Channel_Name: E

*************************** 5. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.178.9.75

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 138975499

Relay_Log_File: relay-bin-zntg.000012

Relay_Log_Pos: 317

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 138975499

Relay_Log_Space: 686

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

Master_UUID: 8fb63b55-65bb-11e5-a4e8-5254007637de

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: (A,B)

Channel_Name: ZN

5 rows in set (0.00 sec)

由于存在重复的索引名，导致报错主从失效，通过sql_slave_skip_counter直接进行过滤

按照常规方法进行发现报错

mysql>set  globalsql_slave_skip_counter = 1;

mysql>stop  slave ;

mysql>start slave;

ERROR 3086 (HY000): When sql_slave_skip_counter > 0, it is not allowed to start more than one SQL thread by using 'START SLAVE [SQL_THREAD]'. Value of sql_slave_skip_counter can only be used by one SQL thread at a time. Please use 'START SLAVE [SQL_THREAD] FOR CHANNEL' to start the SQL thread which will use the value of sql_slave_skip_counter.

mysql> set globalsql_slave_skip_counter = 0;

mysql> start slave;mysql>show slave status for channel 'A'\G##channel = 'A'不能用“=”

Slave_IO_State: Waiting for master to send event

Master_Host: 182.2.2.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 95459627

Relay_Log_File: relay-bin-finance.000002

Relay_Log_Pos: 1479683

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%

Replicate_Wild_Ignore_Table:

Last_Errno: 1061

Last_Error: Error 'Duplicate key name 'index_ShippingId_WareId'' on query. Default database: 'B'. Query: 'alter table `swc_relation` add index `index_ShippingId_WareId` (`ShippingId`, `WareId`)'

Skip_Counter: 0

Exec_Master_Log_Pos: 95092986

Relay_Log_Space: 1848783

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

Last_SQL_Error: Error 'Duplicate key name 'index_ShippingId_WareId'' on query. Default database: 'B'. Query: 'alter table `swc_relation` add index `index_ShippingId_WareId` (`ShippingId`, `WareId`)'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2213306

Master_UUID: 67b146d5-7958-11e5-a908-f8bc123d566c

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: 151124 13:33:41

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB: (A,B)

Channel_Name: A

mysql> set  globalsql_slave_skip_counter = 1;

mysql> start  slave for  channel 'A';

mysql> show slave status for channel 'A'\G

Slave_IO_State: Waiting for master to send event

Master_Host: 182.2.2.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 95533635

Relay_Log_File: relay-bin-finance.000009

Relay_Log_Pos: 65660

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 95533635

Relay_Log_Space: 109705

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

Master_UUID: 67b146d5-7958-11e5-a908-f8bc123d566c

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: (A,B)

Channel_Name: A

1 row in set (0.00 sec)

主从修复