mysql 同步1051,mysql 5.6下因操作两次drop table导致主从断开 1051 error

slave端error日志信息如下:

2016-05-21 08:29:59 11918 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

2016-05-21 08:29:59 11918 [Note] Slave I/O thread killed while reading event

2016-05-21 08:29:59 11918 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.001881’, position 501944997

2016-05-21 08:30:11 11918 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.

2016-05-21 08:30:11 11918 [Note] Slave I/O thread: connected to master ‘replicator@10.208.11.11:3306’,replication started in log ‘mysql-bin.001881’ at position 501944997

2016-05-21 08:30:11 11918 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

2016-05-21 08:30:11 11918 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.001878’ at position 422821657, relay log ‘./bi-pd3-relay-bin.005629’ position: 422821820

2016-05-21 08:30:11 11918 [ERROR] Slave SQL: Error ‘Unknown table ‘middle.fm_transactions_detail_20160101” on query. Default database: ‘middle’. Query: ‘DROP TABLE `fm_transactions_detail_20160101` /* generated by server */’, Error_code: 1051

2016-05-21 08:30:11 11918 [Warning] Slave: Unknown table ‘middle.fm_transactions_detail_20160101’ Error_code: 1051

2016-05-21 08:30:11 11918 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.001878’ position 422821657

2016-05-21 08:52:31 11918 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

2016-05-21 08:52:31 11918 [Note] Slave I/O thread killed while reading event

2016-05-21 08:52:31 11918 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.001881’, position 800561492

2016-05-21 08:53:26 11918 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.

2016-05-21 08:53:26 11918 [Note] Slave I/O thread: connected to master ‘replicator@10.208.11.11:3306’,replication started in log ‘mysql-bin.001881’ at position 800561492

2016-05-21 08:53:26 11918 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

2016-05-21 08:53:26 11918 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.001878’ at position 422821657, relay log ‘./bi-pd3-relay-bin.005629’ position: 422821820

2016-05-21 08:53:26 11918 [Note] ‘SQL_SLAVE_SKIP_COUNTER=1′ executed at relay_log_file=’./bi-pd3-relay-bin.005629′, relay_log_pos=’422821820′, master_log_name=’mysql-bin.001878′, master_log_pos=’422821657′ and new position at relay_log_file=’./bi-pd3-relay-bin.005629′, relay_log_pos=’422821968′, master_log_name=’mysql-bin.001878′, master_log_pos=’422821805′

从master_log_name=’mysql-bin.001878′, master_log_pos=’422821657’里信息来看，是执行了DROP TABLE `fm_transactions_detail_20160101` 语句导致 slave端读event时出现问题导致slave i/o thread 被kill

[root@bi-pd1 mysql]# mysqlbinlog –start-position=422821657 -d middle mysql-bin.001878| more

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160520 16:14:59 server id 11 end_log_pos 120 CRC32 0xdf0e6fc0 Start: binlog v 4, server v 5.6.20-log created 160520 16:14:59

BINLOG ‘

g8c+Vw8LAAAAdAAAAHgAAAAAAAQANS42LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcBv

Dt8=

‘/*!*/;

# at 422821657

#160520 18:09:19 server id 11 end_log_pos 422821805 CRC32 0x3f0b6e54 Query thread_id=361169 exec_time=65 error_code=0

use `middle`/*!*/;

SET TIMESTAMP=1463738959/*!*/;

SET @@session.pseudo_thread_id=361169/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

DROP TABLE `fm_transactions_detail_20160101` /* generated by server */

/*!*/;

解决方法及其步骤

1.在slave端stop slave

2.修改参数set global sql_slave_skip_counter = 1 让其跳过报错event,

3.start slave

4.show slave status \G;来查看是否正常，通过观察Seconds_Behind_Master的日志落后是否慢慢缩小，判断master-slave是否正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

参数说明:

而set global sql_slave_skip_counter=N的意思，即为在start slave时，从当前位置起，跳过N个event。每跳过一个event.但是注意1个event可能会包含多个执行语句，要看数据库的负载及其性能情况而定，一般操作前，可能会去binlog或者

relaylog去查看跳过点的语句具体是什么，从而来决定是否进行操作操作来保证master-slave正常进行复制

原因:

mysql的主从复制不能像oracle那么健壮或者是mysql自身特性所致，mysql利用binlog进行主从复制，而binlog会记录所有的操作，例如:做两次drop，以event