mysql_upgrade引起的master/slave replication中断解决_MySQL

bitsCN.com

mysql_upgrade引起的master/slave replication中断解决

 

在生产环境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 [错误]表 mysql.proc 的定义不正确：预期位置 15 处的列“comment”具有文本类型，发现类型为 char(64)。

 

13053015:00:53 [错误] 从属 SQL：查询在主从服务器上导致不同的错误。     主服务器上的错误：消息（格式）='无法从 mysql 加载。%s。该表可能已损坏' errorcode=1548 ；从站错误：实际消息=“无错误”，错误代码=0。默认数据库：“performance_schema”。查询：'DROP DATABASE IF EXISTSperformance_schema'，Error_code：0

 

13053015:00:53 [警告]从站：无法从 mysql.proc 加载。该表可能已损坏 Error_code: 1548

 

13053015:00:53 [错误] 运行查询时出错，从属 SQL 线程中止。修复问题，并使用“SLAVE START”重新启动从属 SQL 线程。我们停在log 'mysql-bin.002947'位置721651903

 

5.5的日志错误还是很人性化的，slave时停止读取的binlog日志文件，位置很清楚。为我们重启slave提供了方便。既然是DROP DATABASE IFEXISTS Performance_schema导致的错误，那就跳过这个事件。

 

从服务器：

 

mysql> 显示类似 '%skip%' 的变量;

 

mysql>setglobal sql_slave_skip_counter =1;

 

mysql>slave start ;

 

复制正常

 

总结：复制虽然正常了。为什么 mysql_upgrade 会做 DROP DATABASE IF EXISTSperformance_schema 这个操作？希望遇到类似问题的朋友，一起交流。

bitsCN.com