Shulou(Shulou.com)06/01 Report--

Recovery case of Club database based on time point and single table

Problem found:

DBA found sql injection in club database on 2016-05-20, as shown in the figure below.

At this time, all the data in the database has been replaced, the specific data are as follows

You can see that the nickname,realname has been modified, which means that the business has been affected and the data needs to be restored.

Solution:

1. Find the backup for the same day and start the backup file directly.

Pay attention to adjust the server-id in the configuration file when starting to avoid conflicts with the online slave library

2. Parse all binlog logs from backup time to current time through mysqlbinlog tool and append them to a temporary file

Mysqlbinlog-d club-- start-datetime '2016-05-20 00 mysql-bin.000161 > / tmp/liuyaxin.sql

Mysqlbinlog-d club-- start-datetime '2016-05-20 00 mysql-bin.000162 > > / tmp/liuyaxin.sql

3. Filter the operations on the table in the temporary file, find the sql statement that caused the problem, and record the log_ post point of the sql before the problem sql at that time. The binlog_file information is not recorded and needs to be confirmed according to the time point of sql execution.

Grep-B 2 user_doctor_new liuyaxin.sql | grep-A 5-B 6 qwqrnore | more

Cause the problem sql and time point:

The sql has no conditional restrictions that cause the whole table data to be maliciously modified.

The previous sql and time point before causing the problem:

4. Start synchronization by specifying the end location of the binlog_pos point on the instance started with backup

Start slave until MASTER_LOG_FILE='mysql-bin.000162',MASTER_LOG_POS=101655506

Wait for data synchronization, and the sql thread will automatically break when it reaches the specified pos point.

Localhost.club > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: *

Master_User: *

Master_Port: *

Connect_Retry: 60

Master_Log_File: mysql-bin.000164

Read_Master_Log_Pos: 107671841

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 101704628

Relay_Master_Log_File: mysql-bin.000162

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 101655506

Relay_Log_Space: 2255157326

Until_Condition: Master

Until_Log_File: mysql-bin.000162

Until_Log_Pos: 101655506

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

According to the red field, we can see that the data has been synchronized to our specified location.

5. Dump the table to be restored after the synchronization is completed, and restore it to the online library

6. Check data

The name has changed back.

9. Recovery complete

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.