Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the problem of online database deletion". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the problem of online database deletion.

Event background

Automatic and complete daily zero point of MySQL database

At 9: 00 a. M. one day, Ergouzi accidentally drop a database

We need to recover data through complete data files and incremental binlog files.

Main ideas and principles

Use the CHANGE MASTER statements recorded in the complete sql file, the binlog file and its location information to find out the increment of the binlog file.

Use the mysqlbinlog command to export the above binlog file to a sql file, and remove the drop statements from it

Through the export of sql files of full files and incremental binlog files, you can recover the complete data

Process diagram

Operation process

Simulated data

CREATE TABLE `student` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` char (20) NOT NULL, `age` tinyint (2) NOT NULL DEFAULT '0mm, PRIMARY KEY (`id`), KEY `student name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 mysql > insert student values (1) mysql > insert student values (21); mysql > insert student values (3)

Full command

# mysqldump-uroot-p-B-F-R-x-- master-data=2 test | gzip > / server/backup/test_$ (date +% F) .sql.gz

Parameter description:

-B specify the database

-F refresh log

-R backup stored procedures, etc.

-x lock table

-- master-data adds CHANGE MASTER statement, binlog file and location information to the backup statement

Continue inserting data and deleting libraries

Mysql > insert student values (4); mysql > insert student values (5)

When inserting data, we simulate misoperation and delete the test database.

Mysql > drop database test

At this point, between complete and misoperation, the data written by the user is in the binlog and needs to be recovered.

Check the newly added binlog file after complete.

# cd / server/backup/ # ls test_2020-08-19.sql.gz # gzip-d test_2020-08-19.sql.gz # grep CHANGE test_2020-08-19.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107

This is the location of the binlog file at the full time, that is, line 107of mysql-bin.000003, so the data in the binlog file before the file is already contained in this complete sql file.

Move the binlog file, read the sql, and remove the drop statements from it

# cp / data/3306/mysql-bin.000003 / server/backup/ # mysqlbinlog-d test mysql-bin.000003 > mysql-bin.000003.sql

Next, use vim to edit the mysql-bin.000003.sql file and remove the drop statement

Note: the binlog file must be removed before restoring complete data, otherwise statements will continue to be written to binlog during the recovery process, resulting in confusion in the incremental recovery part of the data.

Recover data

# mysql-uroot-p < test_2020-08-19.sql # mysql-uroot-p-e "select * from test.student "+-id | id | name | age | +-- + | 1 | zhangsan | 20 | 2 | lisi | 21 | 3 | wangwu | 22 | +-+

At this point, the full time data is restored, and then the mysql-bin.000003.sql file is used to restore the new data between the full time and the deletion of the database.

# mysql- uroot-p test < mysql-bin.000003.sql # mysql- uroot-p-e "select * from test.student "+-+ | id | name | age | +-+ | 1 | zhangsan | 20 | 2 | lisi | 20 | 3 | wangwu | 20 | 4 | xiaoming | 20 | 5 | xiaohong | 20 | +-+

At this point, the whole recovery process is over, isn't it easy? Yes, it's that simple!

At this point, I believe you have a deeper understanding of "how to solve the problem of online database deletion". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.