MySQL slave的故障问题---索引引起

背景:索引导致的slave服务器不正常

1.)show slave status\G;    //查看slave的状态。

   查看对应的binlog的position的位置,定位故障点。

2. 分析binlog日志(密码隐去)

/usr/local/mysql/bin/mysqlbinlog -uxxxx -p'zzzzz' -S /data/mysqldata/3306/mysql.sock -vv --start-position='234390367' ./mysql-bin.000569 >/root/mysql123.sql


 分析binlog出问题的语句:

# Warning: this binlog is either in use or was not closed properly.(可以分析到binlog没有正常的使用,其他的slave都正常使用了,这台没有)
BINLOG '
zoB+UA/q7gQAZwAAAGsAAAABAAQANS41LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 234390367
#121019 15:50:30 server id 323306  end_log_pos 234390510        Query   thread_id=56829810      exec_time=2     error_code=0
use apps/*!*/;
SET TIMESTAMP=1350633030/*!*/;
SET @@session.pseudo_thread_id=56829810/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER TABLE article_topics ADD INDEX ind_doc_topics_excellent (`excellent`)
/*!*/;

3.查看error日志

120927 15:35:01 [ERROR] Table apps/ask_questions contains 5 indexes inside InnoDB, which is different from the number of indexes 4 defined in the MySQL 
121019 15:50:33 [ERROR] Table apps/article_topics contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL
121019 16:30:07 [ERROR] /usr/local/mysql/bin/mysqld: Got an error writing communication packets

发现索引出现了问题,后面由于appagent应用一直在连接,导致communication packets出现错误,连接数最终过多

4.解决思路:

1.重启slave服务器(通过kill掉sid)
2.在通过脚本开启slave服务器后,slave正常

上一篇:CRS-1606&CRS-1604:CSSD voting file is offline的问题解决思路


下一篇:轻量的、可自定义 CSS 的 Lightbox 相册插件