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

The following content mainly brings you how to reply to the MySQL of the binlog binary log. Unlike books, the knowledge mentioned is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

The overall operation flow:

Step0, turn off external access to the database [prevent user operations from writing to this library]

Step1 and mysqlbinlog export binary logs of databases in related time periods

Step2, edit today.sql to find the misoperation of those pieces of data, delete and save.

Step3, perform a full backup to restore mysql-e'source / root/backup.sql;'

Step4, restore today's modified mysql-e 'source / root/today.sql;' with binary log

Step5, log in to mysql, and verify that the data is back.

The contents of a table in the original database are as follows:

MariaDB [hellodb] > select * from students

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 1 | Shi Zhongyu | 22 | M | 2 | 3 | |

| | 2 | Shi Potian | 22 | M | 1 | 7 | |

| | 3 | Xie Yanke | 53 | M | 2 | 16 |

| | 4 | Ding Dian | 32 | M | 4 | 4 |

| | 5 | Yu Yutong | 26 | M | 3 | 1 | |

| | 6 | Shi Qing | 46 | M | 5 | NULL |

| | 7 | Xi Ren | 19 | F | 3 | NULL |

| | 8 | Lin Daiyu | 17 | F | 7 | NULL |

| | 9 | Ren Yingying | 20 | F | 6 | NULL |

| | 10 | Yue Lingshan | 19 | F | 3 | NULL |

| | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| | 12 | Wen Qingqing | 19 | F | 1 | NULL |

| | 13 | Tian Boguang | 33 | M | 2 | NULL |

| | 14 | Lu Wushuang | 17 | F | 3 | NULL |

| | 15 | Duan Yu | 19 | M | 4 | NULL |

+-+ +

Backup the next data, backup as backup.sql, simulation as the previous day's full backup file.

Then do some random operations, as follows:

INSERT INTO students VALUES (30 pencils, 21 pas, 2 pas, 2 pas)

DELETE FROM students where `StuID` = 10

Update students set age=10 where `StuID` = 1

The results are as follows:

MariaDB [hellodb] > select * from students

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 1 | Shi Zhongyu | 10 | M | 2 | 3 | |

| | 2 | Shi Potian | 22 | M | 1 | 7 | |

| | 3 | Xie Yanke | 53 | M | 2 | 16 |

| | 4 | Ding Dian | 32 | M | 4 | 4 |

| | 5 | Yu Yutong | 26 | M | 3 | 1 | |

| | 6 | Shi Qing | 46 | M | 5 | NULL |

| | 7 | Xi Ren | 19 | F | 3 | NULL |

| | 8 | Lin Daiyu | 17 | F | 7 | NULL |

| | 9 | Ren Yingying | 20 | F | 6 | NULL |

| | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| | 12 | Wen Qingqing | 19 | F | 1 | NULL |

| | 13 | Tian Boguang | 33 | M | 2 | NULL |

| | 14 | Lu Wushuang | 17 | F | 3 | NULL |

| | 15 | Duan Yu | 19 | M | 4 | NULL |

| | 30 | lee | 21 | M | 2 | 1 | |

+-+ +

If suddenly DBA discovers that the previous DELETE should not be executed, you must recover this data.

Step0, at this time, first of all, we should turn off the permissions of external access to the database to prevent users from writing data and interfere with the recovery operation.

(you can modify the file of the front-end web connection database and change it to another slave node. Although the user cannot write it, it is at least better than the website cannot be accessed.)

Step1, first extract the binary log for the period of misoperation, and name it today.sql, as follows:

Mysqlbinlog-- start-datetime='2016-07-12 23 stop-datetime='2016-07-12 23 15 51V 48'--stop-datetime='2016-07-12 23 23 V 53 00'\

/ usr/local/mariadb/var/mysql-bin.000033 > / root/today.sql

Step2, edit the today.sql, find the DELETE operation just now, remove the DELETE statement, and save exit.

Step3, restore data with full backup backup.sql:

Mysql-e'source backup.sql;'

The effect of recovery is as follows:

MariaDB [hellodb] > select * from students

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 1 | Shi Zhongyu | 22 | M | 2 | 3 | |

| | 2 | Shi Potian | 22 | M | 1 | 7 | |

| | 3 | Xie Yanke | 53 | M | 2 | 16 |

| | 4 | Ding Dian | 32 | M | 4 | 4 |

| | 5 | Yu Yutong | 26 | M | 3 | 1 | |

| | 6 | Shi Qing | 46 | M | 5 | NULL |

| | 7 | Xi Ren | 19 | F | 3 | NULL |

| | 8 | Lin Daiyu | 17 | F | 7 | NULL |

| | 9 | Ren Yingying | 20 | F | 6 | NULL |

| | 10 | Yue Lingshan | 19 | F | 3 | NULL |

| | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| | 12 | Wen Qingqing | 19 | F | 1 | NULL |

| | 13 | Tian Boguang | 33 | M | 2 | NULL |

| | 14 | Lu Wushuang | 17 | F | 3 | NULL |

| | 15 | Duan Yu | 19 | M | 4 | NULL |

+-+ +

15 rows in set (0.00 sec)

You can see that the data is back to its original state on the first day, and the 10th data is back, but the INSERT data is gone, so we have to use the binary log to continue the recovery.

Step4, continue to restore with binary log:

Mysql-e'source today.sql;'

Step5, view the results after recovery:

The effect of recovery is as follows:

MariaDB [hellodb] > select * from students

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 1 | Shi Zhongyu | 10 | M | 2 | 3 | |

| | 2 | Shi Potian | 22 | M | 1 | 7 | |

| | 3 | Xie Yanke | 53 | M | 2 | 16 |

| | 4 | Ding Dian | 32 | M | 4 | 4 |

| | 5 | Yu Yutong | 26 | M | 3 | 1 | |

| | 6 | Shi Qing | 46 | M | 5 | NULL |

| | 7 | Xi Ren | 19 | F | 3 | NULL |

| | 8 | Lin Daiyu | 17 | F | 7 | NULL |

| | 9 | Ren Yingying | 20 | F | 6 | NULL |

| | 10 | Yue Lingshan | 19 | F | 3 | NULL |

| | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| | 12 | Wen Qingqing | 19 | F | 1 | NULL |

| | 13 | Tian Boguang | 33 | M | 2 | NULL |

| | 14 | Lu Wushuang | 17 | F | 3 | NULL |

| | 15 | Duan Yu | 19 | M | 4 | NULL |

| | 30 | lee | 21 | M | 2 | 1 | |

+-+ +

16 rows in set (0.00 sec)

As you can see, the Age of the first data has been changed to the required value, the last data of INSERT has been restored, and the data with a StuID of 10 deleted by mistake has also been restored.

At this point, our recovery is complete.

For the above MySQL about how to reply to binlog binary logs, if you need to know more, you can continue to follow our industry promotion. If you need professional solutions, you can contact the pre-sales and after-sales ones on the official website. I hope this article can bring you some knowledge updates.

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.