MySQL Database hacked---Use Backup and binlog for data recovery

Data tampering is to modify, add or delete computer network data, resulting in data destruction. The database data was attacked first to see if it was deleted or tampered with? Is there any backup data that can be restored and reinforced? This article comes from the database technical expert Zhang, mainly describes the MySQL attack tampering data, utilizes the Binlog from the library backup and the main library to carry on the incomplete recovery.

The following is the author's original text:

the problem of Discovery

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:

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.

         One months ago, my friend asked me to go to Yangshuo, Yangshuo is also the place I have always wanted to go, especially the legendary West Street. Collect information on the Internet, work out our schedule (hehe, it may be a professional habit, planning and scheduling is our strength, planning and flexibility is our characteristics, the purpose is very clear, is a holiday leisure, do not have to walk in various places of interest, in fact, I think friends are also in many places are more travel, I also know how some sights come out, all the same.

          After setting the main melody, our General schedule is as follows:

          No. 19th, Guilin, 20th set off to Yangshuo, first go to Yangshuo to settle down (some people from Guilin with luggage at the junction of Yang Causeway off, directly to the Lijiang River rafting, and then go to Yangshuo, seems to save time, however, we do not arrange so full, also do not want to take luggage to play, so choose to go to Yangshuo first settled, After a good chat with the innkeeper, decide the details.

         The main content of the holiday is: Lijiang River rafting, river Rafting (Pang), ten-Li Gallery West Street, staring, Daze, West Street Bar, impression Sanjie, other according to the situation and mood temporarily decided.

I posted the article, the first condemnation, it is likely to be a tourist agency in order to advertise for people to do.

Two, Solution

This library is backed 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.

three, 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

Found the backup task made comments

To view backup files:

<p>[root@localhost 6084]# ll</p><p>total 128</p><p>drwxr-xr-x 2 root 4096 modified 25 03:13 20140825</p><p>drwxr-xr-x 2 root 4096 modified 03:13 20140826</p><p>drwxr-xr-x 2 root Root 4096 modified 03:13 20140827</p><p>drwxr-xr-x 2 root 4096 Modified-03:13 Drwxr-xr-x 2 root 4096 modified 03:13 20140829</p><p>drwxr-xr-x 2 root root 4096 modified-03:13 20140830</p ><p>drwxr-xr-x 2 root 4096 modified 03:13 20140831</p><p>drwxr-xr-x 2 root root 4096 Sep  1 03:13 20140901</p><p>drwxr-xr-x 2 root 4096 Sep  2 03:13 20140902</p><p> Drwxr-xr-x 2 root 4096 Sep  3 03:13 20140903</p><p>drwxr-xr-x 2 root root 4096 Sep  4 03:13 20140904</p><p>drwxr-xr-x 2 root 4096 Sep  5 03:13 20140905</p><p> Drwxr-xr-x 2 root 4096 Sep  6 03:13 20140906</p><p>drwxr-xr-x 2 root 4096 Sep  7 03:13 20140907</p><p>drwxr-xr-x 2 root root 4096 Sep  8 03:13 20140908</p><p>drwxr-xr-x 2 root 4096 Sep  9 03:13 <p>drwxr-xr-x 2 root 4096 Sep 03:13 20140910</p><p>drwxr-xr-x 2 root 4096 Sep 11 03:13 20140911 </p><p>drwxr-xr-x 2 root root 4096 Sep 03:13 20140912</p><p>drwxr-xr-x 2 root root 4096 Sep 13 03:13 20140913</p><p>drwxr-xr-x 2 root 4096 Sep 03:13 20140914</p><p>drwxr-xr-x 2 root Root 4096 Sep 03:13 20140915</p><p>drwxr-xr-x 2 root 4096 Sep 03:13 Drwxr-xr-x 2 root 4096 Sep 03:13 20140917</p><p>drwxr-xr-x 2 root 4096 Sep 03:14 ><p>drwxr-xr-x 2 root 4096 Sep 03:14 20140919</p><p>drwxr-xr-x 2 root root 4096 Sep 20 03:13 20140920</p><p>drWxr-xr-x 2 root 4096 Sep 03:13 20140921</p><p>drwxr-xr-x 2 root root 4096 Sep 03:14 20140922</p> <p><strong>drwxr-xr-x 2 root 4096 Sep 18:33 20140923</strong></p><p>-rw-r--r--1 Root root 5475 Sep 18:33 mysql-bakup.log</p>

Back up 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, the tables are MyISAM. 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

Pass:

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:

<strong>mysqlbinlog--database=[db_name]--start-datetime= ' 2014-09-23 </strong>< Strong>18:19:12</strong><strong> '--stop-datetime= ' 2014-09-25 21:53:56 '  <br></ Strong><strong>[binlog_name] > Binlog_name0000x.sql</strong>

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 tester 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 tester:

View Main Library Binlog

-RW-RW----1 mysql mysql  87669492 Sep 00:00 mysql-bin.000469<br>-rw-rw----1 mysql mysql 268436559 Sep 23 04:20 mysql-bin.000470<br>-rw-rw----1 mysql mysql 268435558 Sep 17:32 mysql-bin.000471<br>-rw-rw----1 mysql MySQL  37425262 Sep 00:00 mysql-bin.000472<br>-rw-rw----1 mysql mysql 137389819 Sep 25 00:00 MYSQL-BIN.000473<BR>-RW-RW----1 mysql mysql 147386521 Sep 00:00 mysql-bin.000474<br> We need the Binlog time period: 2014-09-23 18:28:00 to 2014-09-25 21:53:56<br> so only need: <BR>-RW-RW----1 mysql mysql   37425262 Sep 00:00 mysql-bin.000472<br>-rw-rw----1 mysql mysql 137389819 Sep 00:00 mysql-bin.000473<br> -RW-RW----1 mysql mysql 147386521 Sep 00:00 mysql-bin.000474

Copy the 3 Binlog past:

SCP mysql-bin.000472 < recovery tester ip>:/data/opdir/20140926


SCP mysql-bin.000473 < recovery tester ip>:/data/opdir/20140926


SCP mysql-bin.000474 < recovery tester 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 '  <br>mysql-bin.000472 > 472.sql<br>mysqlbinlog--database=[db_name]--start-datetime= ' 2014-09-23 18:19:12 '--stop-datetime= ' 2014-09-25 21:53:56 '  <br>mysql-bin.000473 > 473.SQL<br >mysqlbinlog--database=[db_name]--start-datetime= ' 2014-09-23 18:19:12 '--stop-datetime= ' 2014-09-25 21:53:56 '  <br>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;<br>+-----------------+-----------------+ <br>| Max (Createtime) | Max (updatetime) |<br>+-----------------+-----------------+<br>|      1411648043 |      1411648043 |<br>+-----------------+-----------------+<br>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:

Online 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. When the validation is fine, restart the application.

Welcome to subscribe to the "CSDN cloud" micro-signal for more cloud information. (Zebian/Liu Yajon)

Welcome to @csdn Cloud Computing Micro Blog for the latest cloud computing and large data information.