mysql 主从 故障_mysql主从复制常见故障解决

一、主从复制常见故障

1、从库已存在数据库，主库建立同名库导致从库同步失败。报错如下：

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.222.145

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000005

Read_Master_Log_Pos: 475

Relay_Log_File: mysql2-relay-bin.000004

Relay_Log_Pos: 519

Relay_Master_Log_File: mysqld-bin.000005

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 'butongbu'; database exists' on query. Default database: 'butongbu'. Query: 'create database butongbu'

Skip_Counter: 0

Exec_Master_Log_Pos: 304

Relay_Log_Space: 1065

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 'butongbu'; database exists' on query. Default database: 'butongbu'. Query: 'create database butongbu'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: cd6ab947-045e-11e9-98a6-000c29d4d5b5

Master_Info_File: /data/3307/data/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: 181222 14:31:28

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

***解决办法：

方法一：

stop slave;set global sql_slave_skip_counter=1;

start slave

方法二：根据错误号跳过指定错误，在从服务的my.cnf配置跳过错误

slave-name-resolve # 忽略名字解释，MySQL慢的时候加入该参数可以提升速度

slave-skip-erros = 1032,1062,1007