Slime: Recovering MySQL database with 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 the basics of MySQL's binlog logs. In this article, we'll explain how to recover a database using MySQL's binlog log.

Before we use the Binlog log to recover the database, we have some prerequisite work to do. The steps are as follows:

1. Create a new database Ailanni

2. Create a new table ilannitable

3. Inserting data into the table ilannitable

4. Refresh Mysqlbin Log

5. Full Backup Ailanni Database

6. Delete some of the data inserted in table ilannitable

7. Delete Database Ailanni

8, the content of each Binlog log parsing

9. Recover all Ailanni data

10, restore the Ailanni database specified section

First, Create a new database Ailanni

Use the following command to create the database as follows:

MySQL--h 192.168.1.213-uroot-p123456

Create Database Ailanni;

show databases;

Second, Create a new table ilannitable

Use the following command to create the table, as follows:

Use Ailanni;

CREATE TABLE ilannitable (ID int (4));

Show tables;

Third, inserting data into the table ilannitable

Use the following command to insert Data 1, 2, in table ilannitable as follows:

INSERT into ilannitable values (1);

INSERT into ilannitable values (2);

Select ID from ilannitable;

Four, Refresh Mysqlbin Log

At this point, the MySQL binlog log file has only one mysql-bin.000001, as follows:

Show master logs;

Note: MySQL's binlog file is mysql-bin.000001 at this point, and there are only 1 or 22 data in the database Ailanni. As follows:

Select ID from ilannitable;

Now let's refresh the Binlog log and generate a new Binlog log file mysql-bin.000002, as follows:

Flush logs;

Show master logs;

Now we re-insert the new data into the Ailanni database 3, 4, as follows:

INSERT into ilannitable values (3);

INSERT into ilannitable values (4);

After inserting data 3 and 4, we refresh the Binlog log again and generate a new Binlog log file for mysql-bin.000003, as follows:

Now we have 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.

We can view the contents of the Binlog as follows:

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

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

Five, full backup Ailanni database

Now let's complete the backup Ailanni database, using the mysqldump command, as follows:

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

See if the backup file matches the contents of the Ailanni database as follows:

Cat Ailanni.sql

It is known that the backup database file is consistent with the content of the Ailanni database.

Six, Delete some of the data inserted in the table ilannitable

In the third to fourth step, we inserted 1, 2, 3, 44 data into the table ilannitable. In order to simulate the actual situation after the Ailanni database was destroyed, we now delete the data from the Ailanni database by 3, as follows:

Delete from ilannitable where id=3;

At this point we refresh the Binlog log again, generating a new binlog log file for mysql-bin.000004, as follows:

Flush logs;

Show master logs;

Seven, Delete Database Ailanni

In the sixth step, we deleted the data record 3, and now we're going to delete the Ailanni database. As follows:

Drop database Ailanni;

show databases;

After the deletion is complete, we refresh the Binlog log again, generating a new binlog log file for mysql-bin.000005, as follows:

Flush logs;

Show master logs;

Eight, content parsing for each binlog log

There are now 5 MySQL binlog log files, from mysql-bin.000001 to mysql-bin.000005, in which we have inserted 1, 22 data into the Ilannitable table.

In mysql-bin.000002 we inserted 3 or 42 data into the Ilannitable table, and in mysql-bin.000003 we deleted 3 of the data from the table ilannitable. In the mysql-bin.000004 we have deleted the Ailanni database.

The SQL operation statements for each Binlog log are as follows:

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

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

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

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

Nine, recover Ailanni All data

Now we are going to restore the Ailanni database, which first explains 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:

In the fifth step, the database we backed up was the entire data of the Ailanni database. If we want to recover all the data from the Ailanni database, we just need to execute the MySQL command to import the backup SQL file. As follows:

Create Database Ailanni;

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

See if the restored Ailanni database is the same as the data before it was deleted, as follows:

mysql-uroot-p123456

Use Ailanni;

Select ID from ilannitable;

Through, we can clearly see that the current Ailanni database has been fully restored, and the data is not lost.

10. recovering the specified portion of the Ailanni database

When recovering a database through MySQL's binlog log, we can specify the location of the database recovery, the time of recovery, and the location and time at which the database recovery is not specified.

For instructions on how to use the Mysqlbinlog command, we can view it through the Mysqlbinlog help command, as follows:

/usr/local/mysql/bin/mysqlbinlog--help

Note the numbers that are marked in the figure, where 1 and 2 are the start and end times of the recovered data by Binlog, 3 and 4 represent the starting and ending positions of the binlog recovery data.

The time we all know, look at the Binlog log file content will know. And the location is the number that we marked when we looked at the various Binlog log contents in the front. As follows:

The yellow part of the Figure 5 indicates the time, 6 represents the position of the node, the position node we also called Binlog Pos point.

10.1 do not specify a location and time to recover the database

Let's test the database without specifying the time and location to restore it.

In the Nineth step we recovered all the Ailanni databases and now we need to restore the database to the state of delete data record 3 o'clock. That is, there are only 1, 2, 4, three data in the Ailanni database. This time we will be based on the MySQL binlog log to recover, or not meet this requirement.

In the eighth step we have analyzed the SQL statements executed in each Binlog log. According to the analysis, we need to use mysql-bin.000003 this binlog log to recover the database. Now we look at the Binlog log again, as follows:

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

After deleting 3 of this data, the data record in the Ailanni database is the state we want after we recover the database. As follows:

Now let's use Mysqlbin to recover the database when deleting 3 of this data, using the following command:

/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql-bin.000003 |mysql-uroot-p123456

We can see that at this time the database Ailanni has been restored to the state of the deleted data 3 o'clock, the Ailanni database is really only 1, 2, 4, three data. And that's what we're asking for.

Important NOTE:

Through the above article, we know now that the Ailanni database is the case, all the data has been restored.

In order to do the following experiment, we need to restore the Ailanni database to a state that has not yet been fully backed up, that is, the Ailanni database has no data yet. What we need to do is empty the current Ailanni database and keep Ailanni as an empty database. However, the table structure, primary key, foreign key and so on of the Ailanni database are all complete.

At the same time, the following: If at the same point in the Binlog log to execute too many SQL statements, then we restore the database, we must be based on the location of the Pos point to recover the data, remember to remember. Because the time node is the same in the Binlog log at this point, the POS location node is unique.

Therefore, when recovering a database through Binlog, it is strongly recommended to use the POS location node method to recover the data.

Empty the Ailanni database using the following command, as follows:

Delete from ilannitable;

Select ID from ilannitable;

In this case, I will write another article.

Now we have a Ailanni database with no data, so we can proceed with the next experiment.

10.2 Specify the time to recover the database

Request to revert to the database that deleted data record 3 o'clock. That is, there are only 1, 2, 4, three data in the database.

It's the same as the 10.1 requirement, but this time we're going to restore the data based on the point of time. We also need an empty Ailanni database, which we can get through the previous instructions.

Now we look at the mysql-bin.000003 file again, as follows:

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

Notice that the yellow part of the graph marks the point in time, and we can see that after this point in time, Data 3 is deleted. We recover the data and recover to this point in time to meet the requirements. Use the following command to recover, as follows:

/usr/local/mysql/bin/mysqlbinlog--stop-datetime= ' 2014-11-17 11:30:43 '/usr/local/mysql/data/mysql-bin.000003 | mysql-uroot-p123456

mysql-uroot-p123456

Use Ailanni;

Select ID from ilannitable;

Through, we can see the time to restore the database to meet our requirements, and the Ailanni database is really only 1, 2, 4, three data.

10.3 to restore a database by specifying a location

The state of data 4 o'clock is not inserted when the database Ailanni is requested to be restored to data 3 after it is inserted. That is, the database Ailanni only data 1, 2, 3, three data.

To meet this requirement, we can only recover data by Binlog logs. According to the eighth step of the Binlog log can be learned that to restore to this time the database, then we will use mysql-bin.000002 this log file.

To achieve the above purpose, we need to recover data 1, 2, using the mysql-bin.000001 file, as follows:

/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql-bin.000001 |mysql-uroot-p123456

After data 1 and 2 have been restored, we are now officially starting to recover the database after inserting Data 3, which is not inserted in data 4 o'clock.

Review the mysql-bin.000002 log file again, 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.

To this we are about to restore the MySQL database by Binlog log.

Slime: Recovering MySQL database with Binlog