MySQL5.7 below, the drop table db1.tb1 caused by misoperation; Method of recovery:
0, stop business data write. "Iptables ban"
1. Pull the latest full file from the backup server, restore to a temporary server, unzip and start mysqld.
2. Execute the following command on this new slave:
2.1 Configure the replication relationship first to the server that is currently operating in error, but do not start the replication process. "Similar to the following command"
123456 |
>CHANGE MASTER TO MASTER_HOST= ‘172.16.20.73‘ , MASTER_USER= ‘rpl‘ , MASTER_PASSWORD= ‘rpl‘ , master_log_file= ‘master-bin.000005‘ , master_log_pos=245; |
2.2 Perform a copy filter on the new slave:
1 |
> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ( ‘db1.tb1‘ ); |
2.3 Turn on slave copy and stop before you get to the wrong place.
12 |
> start slave io_thread ; > start slave sql_thread until master_LOG_FILE= ‘mysql-bin.000010‘ ,master_LOG_POS=10020; -- 执行到最后一次没问题的位移点 |
2.4 Skipping this mis-operation on slave
123456 |
>  set gtid_next= ;   -- This is the transaction that should be skipped (this gtid number can be found in the binlog of the main library) > begin >  commit >  set gtid_next= " AUTOMATIC " ; > start slave ; > show slave status \g view replication |
2.5 The DB1.TB1 from the library is exported by mysqldump and then poured into an instance of the error operation on the line. "Big table words, you can use Xtrabackup Backup single table, and then import into the table space to complete the data pour"
MySQL5.7 below, the drop table db1.tb1 caused by misoperation; Method of recovery: