After the MySQL database is attacked and tampered with, it uses backup and binlog for data recovery.

After the MySQL database is attacked and tampered with, it 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.

I. The problem was found today at. The developer 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.
3. Check the backup and time point on the slave database of the backup:

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

The backup is only valid until PM.
Truncate the last part of the backup log:

tail -n 5 mysql-bakup.logdeleting backup of 30 days ago -- 201408242014-09-23 18:19:12 begin backup ...20140824 deleted OK2014-09-23 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

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:

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

4. The specific recovery operations are clear, and the specific operations are simple: 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-rw---- 1 mysql mysql 87669492 Sep 23 00:00 mysql-bin.000469-rw-rw---- 1 mysql mysql 268436559 Sep 23 04:20 mysql-bin.000470-rw-rw---- 1 mysql mysql 268435558 Sep 23 17:32 mysql-bin.000471-rw-rw---- 1 mysql mysql 37425262 Sep 24 00:00 mysql-bin.000472-rw-rw---- 1 mysql mysql 137389819 Sep 25 00:00 mysql-bin.000473-rw-rw---- 1 mysql mysql 147386521 Sep 26 00:00 mysql-bin.000474

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

-rw-rw---- 1 mysql mysql 37425262 Sep 24 00:00 mysql-bin.000472-rw-rw---- 1 mysql mysql 137389819 Sep 25 00:00 mysql-bin.000473-rw-rw---- 1 mysql mysql 147386521 Sep 26 00:00 mysql-bin.000474

Copy the three binlogs in the past:

Scp mysql-bin.000472 <recovery test machine IP>:/data/opdir/20140926scp mysql-bin.000473 <recovery test machine IP>:/data/opdir/20140926scp mysql-bin.000474 <recovery test machine IP>:/data/opdir/20140926

5. Use MySQLBinlog to generate an SQL script:

mysqlbinlog --database=[db_name] --start-datetime='2014-09-23 18:19:12' --stop-datetime='2014-09-25 21:53:56' mysql-bin.000472 > 472.SQLmysqlbinlog --database=[db_name] --start-datetime='2014-09-23 18:19:12' --stop-datetime='2014-09-25 21:53:56' mysql-bin.000473 > 473.SQLmysqlbinlog --database=[db_name] --start-datetime='2014-09-23 18:19:12' --stop-datetime='2014-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.sqlmysql -uroot -pxxxxxx -S /tmp/mysql.sock db_name < 473.sqlmysql -uroot -pxxxxxx -S /tmp/mysql.sock db_name < 474.sql

7. Check the data correctness after the import is complete: Check the data roughly, and then you can view 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 data for the recovery test to the online master database: perform the following operations on the online master database:
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-d 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.