Mysql 主从同步问题总结

死siua11

　　问题 1
　　master：
　　mysql> delete from test2;
　　Query OK, 419431 rows affected (0.00 sec)
　　mysql> insert into test2 select * from test_no_myisam;
　　Ctrl-C -- sending "KILL QUERY 10" to server ...
　　Ctrl-C -- query aborted.
　　ERROR 1317 (70100): Query execution was interrupted
　　mysql>
　　mysql> select count(1) from test2;
　　+----------+
　　| count(1) |
　　+----------+
　　| 919888 |
　　+----------+
　　1 row in set (0.00 sec)
　　
　　slave：
　　
　　mysql> select count(1) from test2;
　　+----------+
　　| count(1) |
　　+----------+
　　| 0 |
　　
　　
　　mysql> show slave status\G;
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.8.199
　　Master_User: rep1
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: binlog.000018
　　Read_Master_Log_Pos: 631625681
　　Relay_Log_File: relaylog.000044
　　Relay_Log_Pos: 167918085
　　Relay_Master_Log_File: binlog.000018
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: No
　　Replicate_Do_DB:
　　Replicate_Ignore_DB: mysql
　　Replicate_Do_Table:
　　Replicate_Ignore_Table: discuz.cdb_sessions
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 1317
　　Last_Error: Query partially completed on the master (error on master: 1317) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'update ucgh_userlogin set firstlogin=(select min(logdate) from ucgh_loginlogs1 where ucgh_loginlogs1.uid=ucgh_userlogin.uid and ucgh_loginlogs1.serverid=ucgh_userlogin.serverid) where ucgh_userlogin.firstlogin='0000-00-00''
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 622930512
　　Relay_Log_Space: 176613857
　　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: 1317
　　Last_SQL_Error: Query partially completed on the master (error on master: 1317) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'update ucgh_userlogin set firstlogin=(select min(logdate) from ucgh_loginlogs1 where ucgh_loginlogs1.uid=ucgh_userlogin.uid and ucgh_loginlogs1.serverid=ucgh_userlogin.serverid) where ucgh_userlogin.firstlogin='0000-00-00''
　　1 row in set (0.00 sec)
　　
　　ERROR:
　　
　　No query specified
　　
　　解决方法：
　　mysql> slave stop;
　　mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
　　ERROR 2006 (HY000): MySQL server has gone away
　　No connection. Trying to reconnect...
　　Connection id:    120
　　Current database: *** NONE ***
　　
　　Query OK, 0 rows affected (0.00 sec)
　　
　　
　　Query OK, 0 rows affected (0.00 sec)
　　
　　
　　mysql> show slave status\G;
　　
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.8.199
　　Master_User: rep1
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: binlog.000018
　　Read_Master_Log_Pos: 632099171
　　Relay_Log_File: relaylog.000044
　　Relay_Log_Pos: 167930769
　　Relay_Master_Log_File: binlog.000018
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB:
　　Replicate_Ignore_DB: mysql
　　Replicate_Do_Table:
　　Replicate_Ignore_Table: discuz.cdb_sessions
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 622943196
　　Relay_Log_Space: 177087347
　　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: 7954
　　Master_SSL_Verify_Server_Cert: No
　　Last_IO_Errno: 0
　　Last_IO_Error:
　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　1 row in set (0.00 sec)
　　
　　ERROR:
　　
　　No query specified
　　
　　
　　问题 2
　　mysql slave不能同步Last_SQL_Error: Error ‘Duplicate entry ‘
　　昨天一台mysql slave 重启后,出现了 Last_SQL_Error: Error 'Duplicate entry ' 错误,不能同步更新
　　mysql> show slave status\G;
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.10.100
　　Master_User: slave_user
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000773
　　Read_Master_Log_Pos: 63325
　　Relay_Log_File: server122-relay-bin.000002
　　Relay_Log_Pos: 165661
　　Relay_Master_Log_File: mysql-bin.000771
　　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: 1062
　　Last_Error: Error 'Duplicate entry '1438019' for key 'PRIMARY'' on query. Default database: 'otrs'. Query: 'INSERT INTO ticket (tn, title, create_time_unix, queue_id, ticket_lock_id, user_id, group_id, ticket_priority_id, ticket_state_id, ticket_answered, escalation_start_time, timeout, valid_id, create_time, create_by, change_time, change_by) VALUES ('2012061310001851', 'Your order ORD201205A000016 was bounced back', 1339585744, 44, 1, 43, 1, 3, 4, 0, 1339585744, 0, 1, current_timestamp, 43, current_timestamp, 43)'
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 41969067
　　Relay_Log_Space: 625695
　　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: 1062
　　Last_SQL_Error: Error 'Duplicate entry '1438019' for key 'PRIMARY'' on query. Default database: 'otrs'. Query: 'INSERT INTO ticket (tn, title, create_time_unix, queue_id, ticket_lock_id, user_id, group_id, ticket_priority_id, ticket_state_id, ticket_answered, escalation_start_time, timeout, valid_id, create_time, create_by, change_time, change_by) VALUES ('2012061310001851', 'Your order ORD201205A000016 was bounced back', 1339585744, 44, 1, 43, 1, 3, 4, 0, 1339585744, 0, 1, current_timestamp, 43, current_timestamp, 43)'
　　1 row in set (0.00 sec)
　　ERROR:
　　No query specified
　　
　　解决的办法是:
　　mysql> slave stop;
　　mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
　　mysql> slave start;
　　我一般用上面那种方法（推荐）,不过下面这种方法也可以:
　　修改mysql配置文件 /etc/my.cnf 在 [mysqld]下加一行 slave_skip_errors = 1062 ,保存.重启mysql. mysql slave可以正常同步了.
　　
　　问题 3
　　今天我们一个同事在操作数据库时，在主数据库插入了一个字段，以为没有插入成功，又在另外一台数据库上插入相同的字段，结果数据库主从出问题了。
　　mysql> show slave status\G;
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.10.100
　　Master_User: slave_user
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000773
　　Read_Master_Log_Pos: 63325
　　Relay_Log_File: server122-relay-bin.000002
　　Relay_Log_Pos: 165661
　　Relay_Master_Log_File: mysql-bin.000771
　　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_SQL_Errno: 1060
　　Last_SQL_Error: Error 'Duplicate column name 'agentpath'' on query. Default database: 'webgame'. Query: 'ALTER TABLE `ucgh_gameuser`  ADD `agentpath` VARCHAR(255) NULL AFTER `agentextcode`'
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 41969067
　　Relay_Log_Space: 625695
　　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: 1060
　　Last_SQL_Error: Error 'Duplicate column name 'agentpath'' on query. Default database: 'webgame'. Query: 'ALTER TABLE `ucgh_gameuser`  ADD `agentpath` VARCHAR(255) NULL AFTER `agentextcode`'
　　1 row in set (0.00 sec)
　　ERROR:
　　No query specified
　　
　　解决的办法和上面的一样:
　　mysql> slave stop;
　　mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
　　mysql> slave start;