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正常