MySQL主从同步故障-Slave_SQL_Running: Error
故障现象:
进入slave服务器,运行:
mysql> show slave status\G
.......
Relay_Master_Log_File: mysql-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: 1008
Last_Error: Error 'Can't drop database 'arun'; database doesn't exist' on query. Default database: 'arun'. Query: 'drop database arun'
解决办法一、
进入slave服务器,运行:
mysql> show slave status\G
.......
Relay_Master_Log_File: mysql-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: 1008
Last_Error: Error 'Can't drop database 'arun'; database doesn't exist' on query. Default database: 'arun'. Query: 'drop database arun'
解决办法一、
是我从这台机器的上进行了drop库操作,当从主库向从库同步时由于没有这个数据库,所有报以上的错误.
知道了原因就很好解决了。同时参考了“http://www.linuxidc.com/Linux/2014-02/96945.htm”这篇文章,解决方法如下:
mysql> stop slave SQL_THREAD;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave SQL_THREAD;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave SQL_THREAD;
再次进行查看slave状态,ok了。
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running: Yes
同时参考官网文档:
http://dev.mysql.com/doc/refman/5.6/en/set-global-sql-slave-skip-counter.html
http://dev.mysql.com/doc/refman/5.6/en/show-binlog-events.html
13.7.5.33 SHOW RELAYLOG EVENTS Syntax
总结笔记如下:
查看binlog的event=>一定要加上limit,否则会打印出所有的输出。
show binlog events
in
'mysql-bin.0000097' [FROM pos] [LIMIT [offset,] row_count]
从slave查看已经接收完成的relaylog的event=>一定要加上limit,否则会打印出所有的输出。
SHOW RELAYLOG EVENTS
[IN 'log_name
'] [FROMpos
] [LIMIT [offset
,]row_count
]
官方还是建议使用mysqlbinlog.因为showbinlog 和 relaylog 不会打印出一些user和system变量。