mysql主备复制1105错误_MySQL主从复制遇到错误的处理办法

MySQL在主从复制的时候经常遇到错误而导致Slave复制中断，这个时候就需要人工干涉，来跳过这个错误，才能使Slave端的复制，得以继续进行；

跳过错误的方法：

MariaDB [(none)]> STOP SLAVE;

MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'SQL_SLAVE_SKIP_COUNTER';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| sql_slave_skip_counter | 1 |

+------------------------+-------+

MariaDB [(none)]> START SLAVE;

例如：

Slave错误发生时的状态:

MariaDB [(none)]> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.180.100

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 658

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 601

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:

Replicate_Wild_Ignore_Table:

Last_Errno: 1008

Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test'

Skip_Counter: 0

Exec_Master_Log_Pos: 313

Relay_Log_Space: 4079

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

Last_SQL_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Replicate_Do_Domain_Ids:

Replicate_Ignore_Domain_Ids:

Parallel_Mode: conservative

使用上面的方法使MySQL的Slave端复制恢复正常：

MariaDB [(none)]> STOP SLAVE;

MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'SQL_SLAVE_SKIP_COUNTER';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| sql_slave_skip_counter | 1 |

+------------------------+-------+

MariaDB [(none)]> START SLAVE;

MariaDB [(none)]> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.180.100

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 658

Relay_Log_File: relay-bin.000006

Relay_Log_Pos: 537

Relay_Master_Log_File: mysql-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: 658

Relay_Log_Space: 1526

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

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Replicate_Do_Domain_Ids:

Replicate_Ignore_Domain_Ids:

Parallel_Mode: conservative

到此，MySQL的主从复制恢复正常。。。大功告成