MySQL uses backup and binlog for data recovery

This article mainly describes how MySQL is attacked and tampered with data, and uses slave database backup and Binlog of the master database for Incomplete recovery.

This article mainly describes how MySQL is attacked and tampered with data, and uses slave database backup and Binlog of the master database for Incomplete recovery.

This article mainly describes how MySQL is attacked and tampered with data, and uses slave database backup and Binlog of the master database for Incomplete recovery.

I. Problems Found

Today is. the development team said that the database was attacked last night. The content and fields of an article table in the database have been tampered with, and all of them have been changed to the same article.

By checking the token, we found that the data was tampered with at 21:53:57.

All contents are changed to the following:

I post the article and condemn it first. It is very likely that people from a travel agency have hired people to advertise.

Ii. Solution

This database is backed up every morning and is retained for 30 days. The Binlog retention time of the master database is 7 days.

So it is easy to think of a way to recover the backup from the database in the early morning of, and then filter out all the changes and subsequent data from the early morning to 21:53:56 through the Binlog of the primary database, once confirmed by the business, it can be discarded. Or you can use other methods to find the data. But it is imperative to restore the database immediately.

Iii. Backup Search and time point

Check the backup on the backup slave database:

#0 3 ***/data/opdir/mysqlbak/backup_mysqldump.sh 6084>/data/opdir/mysqlbak/6084/mysql-bakup.log 2> & amp; 1

The backup task was commented out.

View the backup file:

The backup is only valid until PM.

Truncate the last part of the backup log:

Because these tables are backed up from the slave database and all tables are MyiSAM tables. Check the backup script. The backup starts only after stopping Slave. Therefore, the backup start time is found in the log output from the backup script:

18:19:12

Pass:

Drwxr-xr-x 2 root 4096 Sep 23 18:33 20140923

The End Time is 18:33:00.

Now, consider whether it is based on the backup Start Time: 18:19:12 is Start-DateTime or 18:33:00 is Start-DateTime.

As mentioned above, the backup script is backed up from the database and started at 18:19:12 a.m. on February 23,. At this time, the backup starts and the Stop Slave is executed; therefore, the entire backup status reflects the status at 18:19:12 on the slave database. In addition, monitoring shows that the slave database delay is 0 at this time point, so we can think that this backup is the backup of the master database at this time.

NOTES:

(Some people may be confused because the slave database has Binlog and the slave database accepts the Binlog mechanism of the master database. Here we will look at the specific backup and recovery methods to select the correct time point .)

As mentioned above, the time when logs are tampered with is 21:53:57, so 21:53:56 can be used as the Stop-DateTime

Therefore, the Binlog command should be like this:

Iv. Specific recovery operations

The specific operations are as follows:

1. copy backup from backup machine: 2. unzip the recovery test machine: 3. restore Test Machine import (the database db_name is not found in the test recovery database): 4. copy the Binlog of the master database to the recovery Tester:

View the Binlog of the master database

The Binlog time range we need is 18:28:00 to 21:53:56, so we only need:

Copy the three binlogs in the past:

5. Use MySQLBinlog to generate an SQL Script: 6. Import the SQL script generated by Binlog:

After 20140923. db_name is imported to the recovery test database, import the SQL script generated by MySQLBinlog to the database:

7. Check the data correctness after the import is complete:

Let's take a rough look at the data, and then we can look at the situation through the time field:

It's about 20: 27.

This judgment can only be used as a DBA to view part of the data. Whether the specific needs are OK or not depends on the business development personnel.

After Business Development confirmation, you can export the data and import it to the online master database.

8. Export and compress the database:

Compression:

Scp to the master database (take network factors into account during replication to ensure that no excessive bandwidth is occupied and other online services are affected)

9. Import the recovery test data to the online master database:

Online master database operations:

Before the operation, it is recommended that the development pause the application business section, otherwise the import may be affected. For example, this indicates MyISAM. If the application does not listen to update, the data import will be blocked.

A. Change the name of the original tampered table in the master database: (do not drop the table, rename the table first, and then consider drop after confirmation, because many problems are not reflected in an instant)

Rename table_name to old_table_name;

B. decompress the package:

C. Import New table data:

Development is required to further verify whether the data is OK. After the verification is complete, start the application.