Mysql database is compromised (using Backup and Binlog for data recovery) _mysql

This article mainly describes the MySQL attack tampering data, using the backup from the library and the Binlog of the main library for incomplete recovery.

Welcome to reprint, please indicate the author, source.
Author: Zhang
qq:176036317
If you have questions, please contact.

First, the discovery of problems
Today is 2014-09-26, development early in the morning that the database was attacked. The Content field of an article table in the database has been tampered with and changed to the same article.

The data found by viewing the day was tampered with in 2014-09-25 21:53:57.
All the content has been changed to the following:

Copy Code code as follows:

Subject: Yangshuo-Self Tour
Content
has always been a self-help tour, never like the regiment. Before I went to do homework on the internet, really is very grateful to those who write travel notes to write strategy friends. So, now also want to own experience and experience to write out, and you share, hope to later friends helpful.
Omit N word here ...

Ii. Solutions

This library we are backing up every morning for 30 days. The Binlog retention time for the main library is 7 days.
So it's easy to think of a way to recover from the early 2014-09-25 backup of the library, and then through the main library Binlog through the time period to filter out the early morning to 2014-09-25 21:53:56 all changes, after the data, confirmed by the business can be discarded. Or to find out this part of the data in some other way later. But the immediate task is to restore the database immediately.

Third, find backup and time point

Check backup from library on backup:
Crontab-l
#0 3 * * */data/opdir/mysqlbak/backup_mysqldump.sh 6084 >>/data/opdir/mysqlbak/6084/mysql-bakup.log 2>&1
The backup task was found to be annotated

To view backup files:
[Root@localhost 6084]# LL
Total 128
Drwxr-xr-x 2 root 4096 Aug 25 03:13 20140825
Drwxr-xr-x 2 root 4096 Aug 26 03:13 20140826
Drwxr-xr-x 2 root 4096 Aug 27 03:13 20140827
Drwxr-xr-x 2 root 4096 Aug 28 03:13 20140828
Drwxr-xr-x 2 root 4096 Aug 29 03:13 20140829
Drwxr-xr-x 2 root 4096 Aug 30 03:13 20140830
Drwxr-xr-x 2 root 4096 Aug 31 03:13 20140831
Drwxr-xr-x 2 root 4096 Sep 1 03:13 20140901
Drwxr-xr-x 2 root 4096 Sep 2 03:13 20140902
Drwxr-xr-x 2 root 4096 Sep 3 03:13 20140903
Drwxr-xr-x 2 root 4096 Sep 4 03:13 20140904
Drwxr-xr-x 2 root 4096 Sep 5 03:13 20140905
Drwxr-xr-x 2 root 4096 Sep 6 03:13 20140906
Drwxr-xr-x 2 root 4096 Sep 7 03:13 20140907
Drwxr-xr-x 2 root 4096 Sep 8 03:13 20140908
Drwxr-xr-x 2 root 4096 Sep 9 03:13 20140909
Drwxr-xr-x 2 root 4096 Sep 10 03:13 20140910
Drwxr-xr-x 2 root 4096 Sep 11 03:13 20140911
Drwxr-xr-x 2 root 4096 Sep 12 03:13 20140912
Drwxr-xr-x 2 root 4096 Sep 13 03:13 20140913
Drwxr-xr-x 2 root 4096 Sep 14 03:13 20140914
Drwxr-xr-x 2 root 4096 Sep 15 03:13 20140915
Drwxr-xr-x 2 root 4096 Sep 16 03:13 20140916
Drwxr-xr-x 2 root 4096 Sep 17 03:13 20140917
Drwxr-xr-x 2 root 4096 Sep 18 03:14 20140918
Drwxr-xr-x 2 root 4096 Sep 19 03:14 20140919
Drwxr-xr-x 2 root 4096 Sep 20 03:13 20140920
Drwxr-xr-x 2 root 4096 Sep 21 03:13 20140921
Drwxr-xr-x 2 root 4096 Sep 22 03:14 20140922
drwxr-xr-x 2 root 4096 Sep 23 18:33 20140923
-rw-r--r--1 root root 5475 Sep 18:33 mysql-bakup.log

Backup only to 20140923 days, 18:33 minutes.

Backup LOG last intercept: tail-n 5 Mysql-bakup.log

Deleting backup of days ago-20140824
2014-09-23 18:19:12 begin Backup ...
20140824 deleted OK
2014-09-23 18:33:43 End Backup ...

Because these tables are backed up from the library, and the tables are MyISAM tables. To view the backup script, you start the backup after stop slave, so the time to start the backup from the log output from the backup script is:
2014-09-23 18:19:12
Through: drwxr-xr-x 2 root 4096 Sep 23 18:33 20140923
You can see the end time: 2014-09-23 18:33:00

Now consider the time to start the backup: 2014-09-23 18:19:12 for Start-datetime or 2014-09-23 18:33:00 as start-datetime.
As mentioned earlier, the backup script was backed up from the library, starting at 18:19:12 in 2014-09-23, at which time the backup started and the stop slave was executed, so the status of the entire backup reflected the state from the library 2014-09-23 18:19:12. and monitoring can be seen at this point in time, the delay from the library is 0, so you can think of this backup is the main library at this time of the backup.
NOTES:
(One might be confused by a mechanism such as the binlog of the main library from the library because of the binlog from the library.) This is a combination of our specific backup and recovery methods to select the right point in time. ）

The previous reference to the time of tampering through the log is: 2014-09-25 21:53:57, so you can use the 2014-09-25 21:53:56 as a stop-datetime

So the Binlog command should be like this:
Mysqlbinlog--database=[db_name]--start-datetime= ' 2014-09-23 18:19:12 '--stop-datetime= ' 2014-09-25 21:53:56 '
[Binlog_name] > Binlog_name0000x.sql

Iv. Specific recovery operations

Clear these, the specific operation is simple:

1. Copy backup from backup machine:
SCP < backup machine ip>:/data/mysqlbak/20140923/20140923.db_name.gz < recovery test machine ip>:/data/opdir/20140926

2. Recovery test Machine Decompression:
Gunzip 20140923.db_name.gz

3. Restore test machine import (this library is not db_name before the test Recovery Library):
Mysql-uroot-pxxxxxx-s/tmp/mysql.sock < 20140923.db_name

4. Copy the Binlog of the main library to the recovery test machine:
View Main Library Binlog
-RW-RW----1 mysql mysql 87669492 Sep 00:00 mysql-bin.000469
-RW-RW----1 mysql mysql 268436559 Sep 04:20 mysql-bin.000470
-RW-RW----1 mysql mysql 268435558 Sep 17:32 mysql-bin.000471
-RW-RW----1 mysql mysql 37425262 Sep 00:00 mysql-bin.000472
-RW-RW----1 mysql mysql 137389819 Sep 00:00 mysql-bin.000473
-RW-RW----1 mysql mysql 147386521 Sep 00:00 mysql-bin.000474

We need the Binlog time period: 2014-09-23-18:28:00 to 2014-09-25-21:53:56
So you only need to:
-RW-RW----1 mysql mysql 37425262 Sep 00:00 mysql-bin.000472
-RW-RW----1 mysql mysql 137389819 Sep 00:00 mysql-bin.000473
-RW-RW----1 mysql mysql 147386521 Sep 00:00 mysql-bin.000474
Copy the 3 Binlog to 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. Generate SQL Scripts using Mysqlbinlog:
Mysqlbinlog--database=[db_name]--start-datetime= ' 2014-09-23 18:19:12 '--stop-datetime= ' 2014-09-25 21:53:56 '
mysql-bin.000472 > 472.sql
Mysqlbinlog--database=[db_name]--start-datetime= ' 2014-09-23 18:19:12 '--stop-datetime= ' 2014-09-25 21:53:56 '
mysql-bin.000473 > 473.sql
Mysqlbinlog--database=[db_name]--start-datetime= ' 2014-09-23 18:19:12 '--stop-datetime= ' 2014-09-25 21:53:56 '
mysql-bin.000474 > 474sql

6.binlog generated SQL script import:
After the 20140923.db_name is imported into the Recovery test library, import the mysqlbinlog generated SQL script into 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 correctness of the data after the import is complete:
Take a rough look at the data, and then 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 almost 20:27 the night.
This judgment, as a DBA, to view some of the data, can only play a supporting role, the specific needs of the end is OK, needs the business development of the people to judge.
After business development is confirmed, the data can be exported and then imported into the online master library.

8. Export the library and compress:
Mysqldump-uroot-pxxxxxx-s/TMP/MYSQL.SOCK-Q db_name table_name > Table_name.sql
Compression:
Gzip Table_name.sql
SCP to main library (when replicating, take into account network considerations, and make sure that it does not consume too much bandwidth and affect other online businesses)

9. Data from the recovery test is imported into the online Master library:
On-line Main library operations:
Before the operation, it is best to allow development to suspend the Application Business section, otherwise it may affect the import. For example, this represents MyISAM, the application side if you do not listen to update come in, will block the data import.
A, the main library will be the original altered table renamed: (Do not come up on the drop, first rename, follow-up confirmation no problem to consider the drop, because many problems are not instantaneous can be all reflected up)
Rename table_name to Old_table_name;
B, Decompression:
Gunzip table_name.sql.gz
C, import new table data:
Mysql-uroot-pxxxxxx-s/tmp/mysql.sock Db_name < Table_name.sql

Later you need to develop to further verify that the data is OK. After verifying that there is no problem, start the application again.