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

This article introduces how the operation process of data recovery after erroneous deletion of mysql database is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

In the daily operation and maintenance work, the evasion of the authority of mysql database, SQL audit optimization, data backup and recovery have become the necessary skills for the job.

The importance of the database to the website makes our management of mysql data not to be lost!

Then, people will inevitably make mistakes, maybe one day the brain short-circuited to a misoperation to delete the database, how to do??

1. Summary of MySQL data recovery methods:

1. Using the Mysql data flashback tool to restore data article: https://blog.51cto.com/qiuyt/2095758

2. Today's protagonist [Mysqldump] is native.

Second, work scene

(1) the MySQL database is automatically backed up at 12:00 every night.

(2) when I went to work one morning, at 9: 00, a colleague fainted drop a database!

(3) urgent recovery is needed! Backup data files and incremental binlog files can be used for data recovery.

Third, the idea of data recovery

(1) use the CHANGE MASTER statements recorded in the complete sql file, the binlog file and its location information to find out the incremental part of the binlog file.

(2) use the mysqlbinlog command to export the above binlog file to sql file, and eliminate the drop statement in it.

(3) the complete data can be recovered by exporting the sql file of the full file and the incremental binlog file.

IV. Illustration with examples

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.gzEnter password: [root@vm-002 ~] # ls / opt/backup/ops_2018-07-03.sql.gz### Parameter description:-B: specify database (with database creation statement in backup)-F: refresh log-R: backup stored procedure Wait-x: lock the table (the process of locking the table will make it impossible for the business to read and write Master-data: add CHANGE MASTER statement and 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 OPS 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 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] # lsops_2018-07-03.sql.gz [root @ vm-002 backup] # gzip-d ops_2018-07-03.sql.gz [root@vm-002 backup] # lsops_2018-07-03.sql [root @ vm-002 backup] # grep CHANGE ops_2018-07-03.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, and remove the drop statement from it

View the data storage directory of mysql We can see that it is under / var/lib/mysql [root@vm-002 backup] # ps-ef | grep mysqlroot 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=mysqlmysql 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] # lsibdata1 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/ exports binlog files to sql files And vim edit it to delete the drop statement [root@vm-002 backup] # mysqlbinlog-d ops mysql-bin.000002 > 002bin.sql [root @ vm-002 backup] # ls002bin.sql mysql-bin.000002 ops_2018-07-03.sql [root @ vm-002 backup] # vim 002bin.sql # delete the drop statement # # Note: the binlog file must be removed before restoring the complete data, otherwise the statement will continue to be written to binlog during the recovery process Finally, the incremental recovery part of the data becomes more chaotic.

(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 namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from customers +-1 | wangbo | 0 | 2 | guohui | 0 | 3 | zhangheng | 0 | +-+ 3 rows in set (0.00 sec) the full data is restored at this time.

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)

Finally, the following points are summarized:

1) it is suitable for repair when misoperation caused by man-made SQL statement or hot standby situation without master-slave replication is down.

2) the recovery condition is that mysql should enable binlog log function, and all data should be fully prepared and incremented.

3) when restoring, it is recommended to stop updating the database, that is, to prohibit updating the database.

4) restore the full volume first, then restore the incremental logs after the full time point into SQL files sequentially, and then delete the problematic SQL statements in the file (or through time and location points), and then restore them to the database.

On the mysql database erroneous deletion of the data recovery operation process is shared here, I hope the above content can be of some help to you, can 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.