Shulou(Shulou.com)06/01 Report--

The company has a production library of mysqlAB replication architecture, one master and one slave structure, which is automatically done every Monday morning.

Everything is fine, but today (assuming Saturday) accidentally deleted a table, from the library will also be deleted by mistake. Suppose there are several years of business data in this table, how to recover it?

There are several ways of thinking:

You need to merge all the binary logs in the past few years, and then use sed,grep,awk and other command processing to find out all the operations from the creation of the table to the present (of course, remove the last one you mistakenly deleted), and then import it back (it is not recommended if the amount of data is too large).

First restore Monday's full to the test library, and then restore the full to the current binary log (except for the sentence that was mistakenly deleted), and then export that table back to the production library

If there is delayed replication, export the table on the delay library, import back to A, and then use the binary log to recover the operation of erroneously deleting the table within the delay (except for the sentence that was mistakenly deleted)

From the above analysis, delayed replication can simplify the recovery process in a certain program.

Maatkit is an open source Mysql management kit written by Perl, which has a package that can be downloaded from the www.maatkit.org website. My soft bag here is

Mk-slave-delay

Maatkit-7540.tar.gz

Command can achieve deferred replication.

Building process

First, you need to set up mysqlAB replication (traditional AB replication, GTIDs-based AB replication, semi-synchronous replication, etc.), and then do the following process

1, install mattkit on the slave that needs to do deferred replication

(note: master and normal slave do not need to be installed)

2. Confirm that all machines in the entire architecture are synchronized.

Because the delay is calculated by time, the time must be consistent, and all servers execute the following command and compare the time

3, run the deferred replication process

Verify that the time is the same, and that AB replication is normal, execute the following command on slave

4, test

If you check the replication status on slave, you will find that the SQL thread is NO, which is normal, because in deferred replication, the IO thread is real-time, only the SQL thread is delayed.

Then find a test table on master to insert a few pieces of data and wait 1 minute before you see that the data has been successfully replicated on salve.

From: the number of dark horse programmers.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.