MySQL database under attack tampering (using backup and binlog for data recovery), mysqlbinlog

MySQL database under attack tampering (using backup and binlog for data recovery), mysqlbinlog

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.

You are welcome to reprint it. Please indicate the author and source.
Author: Zhang Zheng
QQ: 176036317
If you have any questions, contact us.

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:

Copy codeThe Code is as follows:
Subject: Guilin Yangshuo self-help tour
Content:
I have always been a self-help game and never liked it? Group. I did my homework online before I went there. I am really grateful to my friends who wrote Travel Notes and wrote guides. Therefore, I want to write my own experiences and experience and share them with you, hoping to help my friends later.
N characters are omitted here .....

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:
Crontab-l
#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:
[Root @ localhost6084] # ll
Total 128
Drwxr-xr-x 2 root 4096 Aug 25 0:13 20140825
Drwxr-xr-x 2 root 4096 Aug 26 0:13 20140826
Drwxr-xr-x 2 root 4096 Aug 27 0:13 20140827
Drwxr-xr-x 2 root 4096 Aug 28 0:13 20140828
Drwxr-xr-x 2 root 4096 Aug 29 0:13 20140829
Drwxr-xr-x 2 root 4096 Aug 30 0:13 20140830
Drwxr-xr-x 2 root 4096 Aug 31 0:13 20140831
Drwxr-xr-x 2 root 4096 Sep 1 0:13 20140901
Drwxr-xr-x 2 root 4096 Sep 2 0:13 20140902
Drwxr-xr-x 2 root 4096 Sep 3 0:13 20140903
Drwxr-xr-x 2 root 4096 Sep 4 0:13 20140904
Drwxr-xr-x 2 root 4096 Sep 5 0:13 20140905
Drwxr-xr-x 2 root 4096 Sep 6 0:13 20140906
Drwxr-xr-x 2 root 4096 Sep 7 0:13 20140907
Drwxr-xr-x 2 root 4096 Sep 8 20140908
Drwxr-xr-x 2 root 4096 Sep 9 20140909
Drwxr-xr-x 2 root 4096 Sep 10 0:13 20140910
Drwxr-xr-x 2 root 4096 Sep 11 20140911
Drwxr-xr-x 2 root 4096 Sep 12 20140912
Drwxr-xr-x 2 root 4096 Sep 13 0:13 20140913
Drwxr-xr-x 2 root 4096 Sep 14 0:13 20140914
Drwxr-xr-x 2 root 4096 Sep 15 0:13 20140915
Drwxr-xr-x 2 root 4096 Sep 16 0:13 20140916
Drwxr-xr-x 2 root 4096 Sep 17 0:13 20140917
Drwxr-xr-x 2 root 4096 Sep 18 0:14 20140918
Drwxr-xr-x 2 root 4096 Sep 19 0:14 20140919
Drwxr-xr-x 2 root 4096 Sep 20 0:13 20140920
Drwxr-xr-x 2 root 4096 Sep 21 0:13 20140921
Drwxr-xr-x 2 root 4096 Sep 22 20140922
Drwxr-xr-x 2 root 4096 Sep 23 18:33 20140923
-Rw-r -- 1 root 5475 Sep 23 mysql-bakup.log

The backup is only valid until PM.

Last segment of backup log truncation: tail-n 5 mysql-bakup.log

Deleting backup of 30 days ago-20140824
18:19:12 begin backup...
20140824 deleted OK
18:33:43 end backup...

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
Drwxr-xr-x 2 root 4096 Sep 23 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:
Mysqlbinlog -- database = [db_name] -- start-datetime = '2017-09-23 18:19:12 '-- stop-datetime = '2017-09-25 21:53:56'
[Binlog_name]> binlog_name1_x. SQL

Iv. Specific recovery operations

The specific operations are as follows:

1. Copy the backup from the backup machine:
Scp <backup machine IP address>:/data/mysqlbak/20140923/20140923 .db_name.gz <restore Test Machine IP address>:/data/opdir/20140926

2. Unzip the recovery test machine:
Gunzip 20140923.db_name.gz

3. Import the recovery test machine (the database db_name is not in the test recovery database ):
Mysql-uroot-pxxxxxx-S/tmp/mysql. sock <20140923. db_name

4. Copy the binlog of the master database to the recovery test machine:
View the binlog of the master database
-Rw ---- 1 mysql 87669492 Sep 23 mysql-bin.000469
-Rw ---- 1 mysql 268436559 Sep 23 mysql-bin.000470
-Rw ---- 1 mysql 268435558 Sep 23 mysql-bin.000471
-Rw ---- 1 mysql 37425262 Sep 24 mysql-bin.000472
-Rw ---- 1 mysql 137389819 Sep 25 mysql-bin.000473
-Rw ---- 1 mysql 147386521 Sep 26 mysql-bin.000474

The binlog time range we need is 18:28:00 to 21:53:56.
Therefore, you only need:
-Rw ---- 1 mysql 37425262 Sep 24 mysql-bin.000472
-Rw ---- 1 mysql 137389819 Sep 25 mysql-bin.000473
-Rw ---- 1 mysql 147386521 Sep 26 mysql-bin.000474
Copy the three binlogs in the past:
Scp mysql-bin.000472 <recovery test machine IP>:/data/opdir/20140926
Scp mysql-bin.000473 <recovery test machine IP>:/data/opdir/20140926
Scp mysql-bin.000474 <recovery test machine IP>:/data/opdir/20140926

5. Use mysqlbinlog to generate an SQL script:
Mysqlbinlog -- database = [db_name] -- start-datetime = '2017-09-23 18:19:12 '-- stop-datetime = '2017-09-25 21:53:56'
Mysql-bin.000472> 472. SQL
Mysqlbinlog -- database = [db_name] -- start-datetime = '2017-09-23 18:19:12 '-- stop-datetime = '2017-09-25 21:53:56'
Mysql-bin.000473> 473. SQL
Mysqlbinlog -- database = [db_name] -- start-datetime = '2017-09-23 18:19:12 '-- stop-datetime = '2017-09-25 21:53:56'
Mysql-bin.000474> 474sql

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:
Mysql-uroot-pxxxxxx-S/tmp/mysql. sock db_name <472. SQL
Mysql-uroot-pxxxxxx-S/tmp/mysql. sock db_name <473. SQL
Mysql-uroot-pxxxxxx-S/tmp/mysql. sock db_name <474. SQL

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:
Mysql> select max (createtime), max (updatetime) from table_name;
+ ----------------- +
| Max (createtime) | max (updatetime) |
+ ----------------- +
| 1411648043/1411648043 |
+ ----------------- +
1 row in set (0.00 sec)

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:
Mysqldump-uroot-pxxxxxx-S/tmp/mysql. sock-q db_name table_name> table_name. SQL
Compression:
Gzip table_name. SQL
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:
Gunzip table_name. SQL .gz
C. Import New table data:
Mysql-uroot-pxxxxxx-S/tmp/mysql. sock db_name <table_name. SQL

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