Slime: Restore data before MySQL backup by Binlog

This article by show according to Lin Feng to provide friendship sponsorship, starting in the mud row world.

In the previous article, we explained how to restore the MySQL database through MySQL's Binlog log, which was linked to slime: recovering MySQL database by Binlog. Where we mentioned the backup database to be late with the database time to be recovered, that is, the database to be restored before, while the backed up database is behind.

I mentioned that I would write a separate article to explain the situation, and this article will show you how to restore the database state earlier than the backup database by Binlog log. We are also the data of the above article and backup files as the basis for this article to explain.

When the Ailanni database is fully backed up, it has 1, 2, 3, 4, four data in its database. At this point, MySQL has a total of three binlog log files: mysql-bin.000001, mysql-bin.000002, mysql-bin.000003. In mysql-bin.000001, we insert two data 1, 2 into the ilannitable table. In mysql-bin.000002 we insert two data 3, 4 into the ilannitable table.

Now we need to restore the Ailanni database to the state of data 4 o'clock without inserting data 3. That is, the database Ailanni only data 1, 2, 3, three data.

Analysis: To achieve the above requirements, we need to use this binlog log file to mysql-bin.000002. Then, based on the Binlog log recovery database, you can specify a method for the POS location node to recover the Ailanni database.

In addition to this very important, we have to first restore the Ailanni database to the state of the full backup, if the Ailanni database is not first restored to the state of the full backup, we use the Binlog log for recovery, the system will error.

We need to take the following steps to restore the Ailanni database status required by the topic. As follows:

1. Full Recovery Ailanni Database

2. Delete all data in Ailanni database after recovery

3. Recover Ailanni Database by Binlog log

I. Complete recovery of Ailanni database

To fully recover the Ailanni database, we can do so with the MySQL command. But before we do, we need to explain the existence of the Ailanni database.

If there is a Ailanni database on the MySQL database server, we do not need to recreate the Ailanni database when we recover the data. If the database is not ailanni on the MySQL database server, then we need to recreate an empty Ailanni database. That is, the database is just an empty library with no tables or other elements.

If we do not create the Ailanni database, we will get an error when we restore the database through the MySQL command, as follows:

Now let's execute the MySQL command to import the backed up SQL file, as follows:

Create Database Ailanni;

/usr/local/mysql/bin/mysql-uroot-p123456 Ailanni</root/ailanni.sql

mysql-uroot-p123456

Use Ailanni;

Select ID from ilannitable;

Through, we can see that the Ailanni database has been fully restored.

Second, delete all the data in the Ailanni database after recovery

In the first step we have all restored the Ailanni database, and now we need to empty the Ailanni database. As follows:

Delete from ilannitable;

Select ID from ilannitable;

Through, we can see the current Ailanni database has no data, is an empty database.

Iii. recovering Ailanni database through Binlog log

The second step has emptied the Ailanni database so that we can recover the Ailanni database through MySQL's binlog log.

Perhaps you will ask why we have to fully recover the Ailanni database before the Ailanni database is emptied?

This is because if we do not first complete recovery Ailanni database, we use Binlog to restore the Ailanni database, the system will error. As follows:

If we do not empty the Ailanni database, you will be issued after the recovery of the Ailanni database, or not meet our requirements. As follows:

So we're going to do the first to second step.

Now let's review the mysql-bin.000002 file to determine the POS location node for the Ailanni database recovery, as follows:

/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql-bin.000002

Note the number 304 marked in the figure, which is the POS node location of the Binlog. We can see that MySQL does not execute the SQL statement that inserts data 4 after the POS node. Then we just need to restore the data to this node.

Use the following command to recover data, as follows:

/usr/local/mysql/bin/mysqlbinlog--stop-position=304/usr/local/mysql/data/mysql-bin.000002 |mysql-uroot-p123456

By, we can now see that the Ailanni database has been restored to the state of 4 after inserting data 3. Now the Ailanni database is really only 1, 2, 3, these three data, has reached our requirements.

In fact, we can also say that there is only MySQL all binlog logs, and there is a relatively complete database backup, then how to restore the database?

The above method can also be recovered.

Slime: Restore data before MySQL backup by Binlog