Rotten mud: Restore the mysql database through binlog, binlogmysql

Rotten mud: Restore the mysql database through binlog, binlogmysql

This article was sponsored by Xiuyi linfeng and first launched in the dark world.

In the previous article, we explained the basic knowledge about mysql binlog. In this article, we will explain how to restore the database through mysql binlog.

Before using binlog to restore the database, we need to make some preparations. The procedure is as follows:

1. Create a new database, ailanni

2. Create a new table ilannitable

3. insert data to the ilannitable table

4. Refresh the mysqlbin log

5. Complete backup of the ailanni Database

6. delete part of the data inserted by the ilannitable table.

7. Delete the database ailanni

8. binlog content Parsing

9. Restore all ailanni data

10. Restore the specified part of the ailanni Database

I,Create a new database, ailanni

Run the following command to create a database:

Mysql -- h 192.168.1.213-uroot-p123456

Create database ailanni;

Show databases;

II,Create a new table ilannitable

Run the following command to create a table:

Use ailanni;

Create table ilannitable (id int (4 ));

Show tables;

III,Insert data to the table ilannitable

Use the following command to insert data 1 and 2 to the ilannitable table:

Insert into ilannitable values (1 );

Insert into ilannitable values (2 );

Select id from ilannitable;

IV,Refresh the mysqlbin log

At this point, mysql's binlog file has only one mysql-bin.000001, as shown below:

Show master logs;

Note: The binlog file of mysql is the mysql-bin.000001 at this time, and the database ailanni only has 1, 2 data. As follows:

Select id from ilannitable;

Now let's refresh the binlog to generate a new binlog Log File mysql-bin.000002, as shown below:

Flush logs;

Show master logs;

Now we insert new data 3 and 4 into the ailanni database again, as shown below:

Insert into ilannitable values (3 );

Insert into ilannitable values (4 );

After data 3, 4 is inserted, we refresh the binlog again to generate a new binlog log file for the mysql-bin.000003, as shown below:

Now we have a total of three binlog files: mysql-bin.000001, mysql-bin.000002, mysql-bin.000003. In the mysql-bin.000001, we insert two data 1 and 2 to the ilannitable table. In the mysql-bin.000002, we insert two data 3 and 4 into the ilannitable table.

We can view the content in 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

V,Complete backup of the ailanni Database

Now we have a complete backup of the ailanni database, using the mysqldump Command, as shown below:

/Usr/local/mysql/bin/mysqldump-uroot-p123456 ailanni>/root/ailanni. SQL

Check whether the backup file is consistent with that of the ailanni database, as shown below:

Cat ailanni. SQL

We can see that the backup database file is consistent with that of the ailanni database.

VI,Delete part of the data inserted in the table ilannitable

In step 3 and Step 4, we insert four data types: 1, 2, 3, and 4 to the ilannitable table. To simulate the actual situation after the ailanni database is damaged, we now Delete the 3 data in the ailanni database, as shown below:

Delete from ilannitable where id = 3;

At this point we refresh the binlog again, generate a new binlog log file for the mysql-bin.000004, as shown below:

Flush logs;

Show master logs;

VII,Delete database ailanni

In step 6, we deleted data record 3. Now we will delete the ailanni database. As follows:

Drop database ailanni;

Show databases;

After the deletion, we refresh the binlog again and generate a new binlog log file as the mysql-bin.000005, as shown below:

Flush logs;

Show master logs;

8,Analysis of binlog content

There are now five binlog files in mysql, from the mysql-bin.000001 to the mysql-bin.000005, where we inserted 1 and 2 data into the ilannitable table in the mysql-bin.000001.

In the mysql-bin.000002 we inserted 3 and 4 data into the ilannitable table, and in the mysql-bin.000003 we deleted 3 from the table ilannitable. In the mysql-bin.000004 we deleted the ailanni database.

The SQL statement of each binlog is 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

IX,Recover all ailanni data

Now we need to restore the ailanni database. First, we need to explain whether the ailanni database exists.

If the current mysql database server has the Database "ailanni", we do not need to re-create the Database "ailanni" when restoring data. If the current mysql database server does not have the "ailanni" database, we need to recreate an empty "ailanni" database. That is to say, the database is just an empty database with no tables or other elements in it.

If you do not create an ailanni database, the system reports an error when restoring the database using the mysql Command, as shown below:

In step 5, we backed up all the data in the ailanni database. To recover all the data in the ailanni database, run 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

Check whether the recovered ailanni database is the same as the data before deletion, as shown in the following code:

Mysql-uroot-p123456

Use ailanni;

Select id from ilannitable;

We can see that the current ailanni database has been fully recovered, and no data is lost.

10,Recover specified part of the ailanni Database

When you restore a database through the binlog of mysql, you can specify the database recovery location and recovery time, and do not specify the database recovery location and time.

For how to use the mysqlbinlog command, we can view it using the HELP command of mysqlbinlog, as follows:

/Usr/local/mysql/bin/mysqlbinlog -- help

Note the numbers marked in the figure. values 1 and 2 indicate the start and end time of data restoration through binlog, and 3 and 4 indicate the start and end positions of binlog data restoration.

We all know the time, and we can see the content of the binlog log file. The position is the numerical part marked when we view the binlog content. As follows:

The yellow digit 5 in the figure indicates the time, and the digit 6 indicates the node at the position. The position node is also called the pos point of binlog.

10.1Restore the database without specifying the location and time

We will first test to restore the database without specifying the time and location.

In step 9, we restored all the databases of ailanni. Now we want to restore the database to the state when we delete the data record 3. That is, the ailanni database only contains three data types: 1, 2, and 4. At this time, we need to recover the Database Based on mysql binlog, or else we will not be able to meet this requirement.

In step 8, we have analyzed the SQL statements executed in each binlog. According to the analysis, we need to use the mysql-bin.000003 this binlog log to recover the database. Now we can view the binlog again, as shown below:

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

3. After this data entry is deleted, the data record in the ailanni database is the state we need to restore the database. As follows:

Now we can use mysqlbin to restore the database when the data in "3" is deleted, and use 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 when data is deleted 3, and the database ailanni does have only 1, 2, and 4. This has also met our requirements.

Important:

Through the above article, we know that the current situation of the ailanni database is that all data has been restored.

In order to do the following experiment, we need to restore the ailanni database to the state before full backup is complete, that is, the ailanni database has no data. What we need to do is to clear the current ailanni database and keep ailanni an empty database. However, the table structure, primary key, foreign key, and other information of the ailanni database are complete.

At the same time, it should be noted that if too many SQL statements are executed in the binlog at the same time point, we must recover the data according to the position of the pos point when restoring the database, remember. In this case, the time nodes in the binlog are the same, but the pos node is unique.

Therefore, we strongly recommend that you use the pos position Node Method to restore data when you restore the database through binlog.

Run the following command to clear the ailanni database:

Delete from ilannitable;

Select id from ilannitable;

I will write another article about this situation.

Now we get a ailanni database without data, so that we can continue the experiment below.

10.2Restore database at specified time

The database must be restored to the database at the time of data deletion 3. That is, the database only contains three data types: 1, 2, and 4.

It is still the same as 10.1, but this time we will restore the data based on the time point. We also need an empty ailanni database, which can be obtained through the preceding instructions.

Now let's review the mysql-bin.000003 file, as shown below:

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

Note the time points marked with yellow in the figure. We can see that data 3 is deleted after this time point. When we recover the data, we can restore it to this point in time to meet the requirements. Run the following command to restore data:

/Usr/local/mysql/bin/mysqlbinlog -- stop-datetime = '2017-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 this, we can see that the restoration of the database through time points also meets our requirements, and the ailanni database does only have 1, 2, 4, these three data.

10.3Specify a location to restore the database

It is required that the database ailanni be restored to the status when data is not inserted 4 after data is inserted 3. That is, only data 1, 2, and 3 are available in the database ailanni.

To meet this requirement, we can only restore data through binlog. According to the binlog in Step 8, we can know that to restore to the database at this time, we need to use the mysql-bin.000002 log file.

To achieve this, we need to first recover data 1, 2, using the mysql-bin.000001 file, as shown below:

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

After data 1 and 2 are restored, we now officially start to restore the database when data 3 is not inserted.

View the mysql-bin.000002 log file again, as shown below:

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

Note that the number 304 marked in the figure indicates the position of the pos node of the binlog. We can see that mysql executes the SQL statement insert data 4 after the pos node. Then we only need to recover the data to this node.

Run the following command to restore data:

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

Now we can see that the ailanni database has been restored to the status of 4 after data is inserted 3. Currently, the ailanni database only has three types of data: 1, 2, and 3. These three types of data have met our requirements.

At this point, we have come to an end to use binlog to restore the mysql database.