mysql_upgrade 引起的master/slave replication 中断
来源:互联网 发布:mac声音被锁 编辑:程序博客网 时间:2024/06/06 00:24
在生产环境master服务器上处理完《1548-Cannot loadfrom mysql.proc. The table is probably corrupted》后,接到报警信息,slave服务器复制中断查看slave 状态
mysql>show slave status
发现如下语句执行错误
DROP DATABASEIF EXISTS performance_schema
performance_schema是mysql自带的性能信息相关的库,mysql怎么会执行这个操作,看看错误日志吧
[root@db25522]# tail -n 500/data/my2/mysql/db25522.err
13051310:29:54 [Note] Error reading relay log event: slave SQL thread was killed
13051310:29:54 [ERROR] Error reading packet from server: Lost connection to MySQLserver during query ( server_errno=2013)
13051310:29:54 [Note] Slave I/O thread killed while reading event
13051310:29:54 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002734',position 1017737307
13051310:29:57 [Note] Slave I/O thread: connected to master 'repl@xxx.xxx.xxx.xxx:3306',replicationstarted in log 'mysql-bin.002734' at position 1017737307
13051310:29:57 [Note] Slave SQL thread initialized, starting replication in log'mysql-bin.002734' at position 1017729051, relay log'/my/rlog/relay-bin.000764' position: 1017729197
13053015:00:53 [ERROR] Incorrect definition of table mysql.proc: expected column'comment' at position 15 to have type text, found type char(64).
13053015:00:53 [ERROR] Slave SQL: Query caused differenterrors on master and slave. Error on master: message (format)='Cannot load from mysql.%s. The tableis probably corrupted' error code=1548 ; Error on slave: actual message='noerror', error code=0. Default database: 'performance_schema'. Query: 'DROP DATABASE IF EXISTS performance_schema',Error_code: 0
13053015:00:53 [Warning] Slave: Cannot load from mysql.proc. The table is probablycorrupted Error_code: 1548
13053015:00:53 [ERROR] Error running query, slave SQL thread aborted. Fix theproblem, and restart the slave SQL thread with "SLAVE START". We stoppedat log 'mysql-bin.002947' position 721651903
莫非是数据不一致导致的?发现master服务器有performance_schema这个库,但是slave服务器没有。在执行 mysql_upgrade -uroot 之前,主从复制在运行,判断操作发生在mysql_upgrade-uroot之后,分析master上日志,在这个时间段内mysql进行了那些操作
[root@db25522]# mysqlbinlog --no-defaults --start-date='2013-05-3015:00:00' --end-date='2013-05-3015:03:00' mysql-bin.002947 >/root/tmp.log
查询日志发现
[root@db25522]#vi /root/tmp.log
/DROP
/*!*/;
# at721651876
#13053015:00:25 server id 13084 end_log_pos721651903 Xid = 435509540
COMMIT/*!*/;
# at 721651903
#13053015:00:53 server id 13084 end_log_pos721652022 Query thread_id=418930 exec_time=0 error_code=1548
SETTIMESTAMP=1369897253/*!*/;
/*!\Cutf8 *//*!*/;
SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=28/*!*/;
DROP DATABASE IF EXISTS performance_schema
/*!*/;
# at721652022
#13053015:00:53 server id 13084 end_log_pos721652152 Query thread_id=418930 exec_time=0 error_code=0
SETTIMESTAMP=1369897253/*!*/;
CREATEDATABASE performance_schema character set utf8
/*!*/;
# at721652152
#13053015:03:19 server id 13084 end_log_pos721652223 Query thread_id=418956 exec_time=0 error_code=0
SETTIMESTAMP=1369897399/*!*/;
/*!\Cgbk *//*!*/;
SET@@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28/*!*/;
BEGIN
/*!*/;
# at721652223
#13053015:03:19 server id 13084 end_log_pos721652336 Query thread_id=418956 exec_time=0 error_code=0
usePriceDB/*!*/;
SETTIMESTAMP=1369897399/*!*/;
好吧,上面红色部分,执行了这个操作,再看看slave错误日志
13053015:00:53 [ERROR] Incorrect definition of table mysql.proc: expected column'comment' at position 15 to have type text, found type char(64).
13053015:00:53 [ERROR] Slave SQL: Query caused different errors on master andslave. Error on master: message(format)='Cannot load from mysql.%s. The table is probably corrupted' errorcode=1548 ; Error on slave: actual message='no error', error code=0. Defaultdatabase: 'performance_schema'. Query: 'DROP DATABASE IF EXISTSperformance_schema', Error_code: 0
13053015:00:53 [Warning] Slave: Cannot load from mysql.proc. The table is probablycorrupted Error_code: 1548
13053015:00:53 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,and restart the slave SQL thread with "SLAVE START". We stopped atlog 'mysql-bin.002947' position 721651903
5.5的日志错误还是很人性化的,slave停止时读取的binlog 日志文件,位置都很清楚。这为我们restart slave提供了方便。既然是DROP DATABASE IFEXISTS performance_schema 导致的错误,那么跳过这条event。
Slave服务器:
mysql>show variables like '%skip%';
mysql>set global sql_slave_skip_counter =1;
mysql>slave start ;
复制正常
总结:复制虽然正常了。为什么 mysql_upgrade 会做 DROP DATABASE IF EXISTSperformance_schema 这个操作?希望遇到类似问题的朋友,一起交流。
- mysql_upgrade 引起的master/slave replication 中断
- mysql的master和slave同步(replication)
- mysql实现master-slave的replication方案
- mysql master 和slave的replication
- MySQL Master-Slave Replication
- MySQL Master-Slave Replication
- master-slave的用户需要具有replication slave权限
- 搭建mysql master-slave replication
- MySQL Master Slave Replication配置
- Replication-Redis(redis master-slave)
- Using Master/Slave Replication with ReplicationConnection
- mysql-proxy实现replication(master&slave)
- mysql5.5 master-slave(Replication)配置
- mysql replication copy master to slave
- mysql 从master slave模式升级到group replication模式的方法
- MySQL Replication(Master与Slave基本原理)(转)
- Mysql 复制 配置详解, Replication with Master/Slave
- linux mysql replication master to slave configuration 小记
- VSFTPD错误的解决 500 OOPS: vsftpd: cannot locate user specified in ftp_userna
- Delaunay三角剖分(Delaunay Triangulation)
- 自控力与能量消耗
- gen_tcp连接半关闭问题
- Erlang版TCP服务器对抗攻击解决方案
- mysql_upgrade 引起的master/slave replication 中断
- ProtoBuf中的编码
- Flex通信-Java服务端通信实例
- crtmpserver流媒体服务器的介绍与搭建
- sysbench安装
- 《一炮走红的国家》:探寻新兴国家的经济奇迹
- 博客收集贴
- 《调试九法:软硬件错误的排查之道》学习笔记
- WebSocket Learn to sharing