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

This article will explain in detail how to restore the MySQL database after erroneous deletion. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

First, make sure that mysql turns on binlog logging. In the [mysqld] block in the / etc/my.cnf file, add:

Log-bin=mysql-bin

Then restart the mysql service

(1) create a table customers under the ops library

Mysql > use ops; mysql > create table customers (- > id int not null auto_increment,-> name char (20) not null,-> age int not null,-> primary key (id)->) engine=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql > show tables +-+ | Tables_in_ops | +-+ | customers | +-+ 1 row in set (0.00 sec) mysql > desc customers +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | char (20) | NO | | NULL | | age | int (11) | NO | | NULL | | +-+-+ 3 rows in set (0.02 sec) mysql > insert into customers values (1 "wangbo", "24") Query OK, 1 row affected (0.06 sec) mysql > insert into customers values (2, "guohui", "22"); Query OK, 1 row affected (0.06 sec) mysql > insert into customers values (3, "zhangheng", "27"); Query OK, 1 row affected (0.09 sec) mysql > select * from customers +-1 | wangbo | 24 | 2 | guohui | 22 | 3 | zhangheng | 27 | +-+ 3 rows in set (0.00 sec)

(2) full backup now

[root@vm-002] # mysqldump-uroot-p-B-F-R-x-- master-data=2 ops | gzip > / opt/backup/ops_$ (date +% F). Sql.gz Enter password: [root@vm-002 ~] # ls / opt/backup/ops_ 2016-09-25.sql.gz

Parameter description:

-B: specify the database

-F: refresh the log

-R: backup stored procedures, etc.

-x: lock the table

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

(3) insert the data again

Mysql > insert into customers values (4, "liupeng", "21"); Query OK, 1 row affected (0.06 sec) mysql > insert into customers values (5, "xiaoda", "31"); Query OK, 1 row affected (0.07 sec) mysql > insert into customers values (6, "fuaiai", "26"); Query OK, 1 row affected (0.06 sec) mysql > select * from customers +-+ | id | name | age | +-- + | 1 | wangbo | 24 | 2 | guohui | 22 | 3 | zhangheng | 27 | 4 | liupeng | 21 | 5 | xiaoda | 31 | 6 | fuaiai | 26 | +-+ 6 rows in set (0.00 sec)

(4) erroneous operation at this time and deletion of test database

Mysql > drop database ops; Query OK, 1 row affected (0.04 sec)

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

(5) check the newly added binlog file after it is complete.

[root@vm-002 ~] # cd / opt/backup/ [root@vm-002 backup] # ls ops_2016-09-25.sql.gz [root@vm-002 backup] # gzip-d ops_2016-09-25.sql.gz [root@vm-002 backup] # ls ops_2016-09-25.sql [root@vm-002 backup] # grep CHANGE ops_2016-09-25.sql-CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106

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

(6) move the binlog file and export it to a sql file, remove the drop statement in it, and view the data storage directory of mysql. As shown below, it is under / var/lib/mysql.

[root@vm-002 backup] # ps-ef | grep mysql root 9272 10 01:43 pts/1 00:00:00 / bin/sh / usr/bin/mysqld_safe-- datadir=/var/lib/mysql-- socket=/var/lib/mysql/mysql.sock-- pid-file=/var/run/mysqld/mysqld.pid-- basedir=/usr-- user=mysql mysql 9377 9272 0 01:43 pts/1 00:00:00 / usr/libexec/mysqld-- basedir=/usr-- datadir=/var/lib/mysql -- user=mysql-- log-error=/var/log/mysqld.log-- pid-file=/var/run/mysqld/mysqld.pid-- socket=/var/lib/mysql/mysql.sock [root@vm-002 backup] # cd / var/lib/mysql/ [root@vm-002 mysql] # ls ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test [root@vm-002 mysql] # cp mysql-bin.000002 / opt/backup/

Export the binlog file to the sql file and edit it by vim to delete the drop statement in it

[root@vm-002 backup] # mysqlbinlog-d ops mysql-bin.000002 > 002bin.sql [root@vm-002 backup] # ls 002bin.sql mysql-bin.000002 ops_2016-09-25.sql [root@vm-002 backup] # vim 002bin.sql # Delete the drop statement inside

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.

(7) restore data

[root@vm-002 backup] # mysql-uroot-p

Show databases; +-+ | Database | +-+ | information_schema | | mysql | | ops | | test | +-+ 4 rows in set (0.00 sec) mysql > use ops Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A Database changed mysql > select * from customers +-1 | wangbo | 0 | 2 | guohui | 0 | 3 | zhangheng | 0 | +-+ 3 rows in set (0.00 sec)

At this time, the full-time data is restored. Next, use the 002bin.sql file to restore the new data between the full time and the deletion of the database.

[root@vm-002 backup] # mysql-uroot-p ops select * from customers +-+ | id | name | age | +-- + | 1 | wangbo | 24 | 2 | guohui | 22 | 3 | zhangheng | 27 | 4 | liupeng | 21 | 5 | xiaoda | 31 | 6 | fuaiai | 26 | +-+ -+ 6 rows in set (0.00 sec) about how to recover MySQL database after erroneous deletion is shared here. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.