Data recovery operation instructions after mis-deletion of MySQL database

In the daily operation and maintenance work, for the MySQL database permissions to evade, SQL Audit optimization, data backup recovery has become, the work necessary skills;
The importance of the database to the website makes our management of MySQL data not be lost!
Then, it is inevitable that people will make mistakes, maybe one day the brain short-circuited to a wrong operation to delete the database, how to do???

The following is a description of the recovery scenario after the MySQL database was mistakenly deleted.

First, the MySQL data recovery method Summary:

1. Article recovering data using the MySQL Data Flashback tool: http://blog.51cto.com/qiuyt/2095758
2, today's protagonist "Mysqldump", the original comes with

Second, the work scene

(1) The MySQL database is automatically fully backed up every 12:00.
(2) one morning to work, 9 o ' clock, a colleague made a faint drop a database!
(3) Need emergency Recovery! Data recovery can take advantage of backed up data files and incremental binlog files.

Third, the idea of data recovery

(1) Using the Change master statement recorded in the fully-prepared SQL file, binlog the file and its location point information to find the incremental portion of the Binlog file.
(2) Use the Mysqlbinlog command to export the above Binlog file as a SQL file and remove the drop statement.
(3) The full file and the incremental Binlog file of the exported SQL file can be restored to the complete data.

Iv. Description of the case

First, make sure that MySQL turns on the Binlog log feature
Add the [MYSQLD] block in the/etc/my.cnf file:
Log-bin=mysql-bin
Then restart the MySQL service

(1) Create a table under the OPS library customers

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;+----+-----------+-----+| ID | name | Age |+----+-----------+-----+| 1 | Wangbo | 24 | | 2 | Guohui | 22 | | 3 | Zhangheng | |+----+-----------+-----+3 rows in Set (0.00 sec) 

(2) Full backup now

[[email protected] ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gzEnter password: [[email protected] ~]# ls /opt/backup/ops_2018-07-03.sql.gz

### 参数说明：-B：指定数据库（备份中带有创库语句）-F：刷新日志-R：备份存储过程等-x：锁表 （锁表过程会造成业务无法读写，可能会出现502）--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

(3) Inserting 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) The OPS database was deleted at this time by mis-operation

mysql> drop database ops;Query OK, 1 row affected (0.04 sec)###此时，全备之后到误操作时刻之间，用户写入的数据在binlog中，需要恢复出来!

(5) See the new Binlog file after full provisioning

[[email protected] ~]# cd /opt/backup/[[email protected] backup]# lsops_2018-07-03.sql.gz[[email protected] backup]# gzip -d ops_2018-07-03.sql.gz [[email protected] backup]# lsops_2018-07-03.sql[[email protected] backup]# grep CHANGE ops_2018-07-03.sql -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=106;#这是全备时刻的binlog文件位置#即mysql-bin.000002的106行，因此在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中了

(6) Move the Binlog file and export it as a SQL file, rejecting the drop statement

View the data storage directory for MySQL, as shown below under/var/lib/mysql [[email protected] backup]# ps-ef|grep mysqlroot 9272 1 0 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[[email protected] backup]# cd/var/lib/mysql/[[email protected] mysql]# Lsibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test[[email  Protected] mysql]# CP mysql-bin.000002/opt/backup/Export the Binlog file to the SQL file and vim edits it to delete the drop statement [[email protected] backup]# mysqlbinlog-d ops mysql-bin.000002 >002bin.sql[[email protected] backup]# ls002bin.sql mysql-bin.000002 ops_2018-07-03.sql[[email protected] backup]# vim 002bin.sql #删除里面的droP Statement # # Note: The Binlog file must be moved out before the full data is restored, or it will continue to write statements to Binlog during the recovery process, resulting in a confusing portion of the incremental recovery data 

(7) Recovering data

[[email protected] backup]# mysql -uroot -p < ops_2018-07-03.sql Enter password: [[email protected] backup]#

(8) Check whether "full standby" is resumed successfully

查看数据库，看看ops库在不在mysql> 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;+----+-----------+-----+| id | name | age |+----+-----------+-----+| 1 | wangbo | 0 || 2 | guohui | 0 || 3 | zhangheng | 0 |+----+-----------+-----+3 rows in set (0.00 sec)

At this point, the full-time data is restored.

Next, use the 002bin.sql file to recover the full standby time to delete the database between the new data

[[email protected] backup]# mysql -uroot -p ops <002bin.sqlEnter password: [[email protected] backup]#再次查看数据库，发现全备份到删除数据库之间的那部分数据也恢复了！！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)

The above is the MySQL database incremental data recovery instance process!

Finally, summarize several points:

1) This case is applicable to the repair of the error caused by the SQL statement, or the hot standby without master-slave replication.
2) Restore the conditions for MySQL to turn on the Binlog log feature, and to have all the data fully prepared and incremental
3) It is recommended to stop the update on recovery, that is, prevent database update
4) Restore the full amount first, then the full standby point after the incremental log, in order to restore the SQL file, and then the file has the problem of the SQL statement deleted (also can be through time and location points), and then restore to the database.

Data recovery operation instructions after mis-deletion of MySQL database