Mysql 主从同步问题总结
来源：互联网 发布：mac 使用oracle 编辑：程序博客网 时间：2024/06/08 20:01
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;
本文出自 “菜鸟中的战斗机” 博客，请务必保留此出处http://linushai.blog.51cto.com/4976486/1080784
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;
本文出自 “菜鸟中的战斗机” 博客，请务必保留此出处http://linushai.blog.51cto.com/4976486/1080784
0 0
- Mysql 主从同步问题总结
- MySQL主从同步总结
- mysql主从同步总结
- MySQL数据库主从同步总结
- MySQL数据库主从同步总结
- mysql主从同步问题思考
- MySQL主从同步 问题定位
- mysql主从同步-问题梳理
- mysql主从同步-问题梳理
- MYSQL同步--主从同步问题集锦
- MySQL服务器主从同步版本问题
- mysql主从数据库同步和字符集问题
- mysql主从数据库同步和字符集问题
- mysql 主从同步遇到的问题
- mysql主从同步异常问题整理
- mysql主从数据库同步延迟问题
- Mysql主从同步中的问题及解决方案
- Cent os mysql主从同步问题
- LinkIssue: Error 'LINK : fatal error LNK1123: failure during conversion to COFF: file invalid or cor
- 的是个负担是格式大哥
- Executors.newFixedThreadPool和ArrayBlockingQueue一点使用心得
- [总结]视音频技术零基础学习方法
- ZooKeeper 安装、配置
- Mysql 主从同步问题总结
- 黑马程序员_类的一些边缘知识
- 正责表达式匹配多行注释
- JQuery对html控件操作总结
- Android项目用到的一些开源项目
- Android2.1下调试3G模块（三）
- Mac下PHP连接MySQL报错＂No such file or directory＂的解决办法
- 在linux下安装并操作tomcat
- OC代码块block
