MySQL5.7 below, the drop table db1.tb1 caused by misoperation; Method of recovery:

Source: Internet
Author: User

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 TOMASTER_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:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.