Data recovery operation instructions after mis-deletion of MySQL database

In daily operations, backup of MySQL database is crucial! 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 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.

Second, 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.

Third, the example of the first, to ensure that MySQL turned on the binlog log function
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 (a) is not NULL,
-age int is 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 | 27 |
+----+-----------+-----+
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.gz
Enter Password:
[Email protected] ~]# ls/opt/backup/
Ops_2016-09-25.sql.gz

Parameter description:
-B: Specify the database
-F: Refresh Log
-r: Backup stored procedures, etc.
-X: Lock table
--master-data: Add change Master Statement and Binlog file and location point information in BACKUP statement

(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) Error operation at this time, deleted the test database
mysql> drop database Ops;
Query OK, 1 row affected (0.04 sec)

At this time, the full standby to the wrong operation between the time, the user writes the data in the Binlog, need to recover!

(5) See the new Binlog file after full provisioning
[Email protected] ~]# cd/opt/backup/
[[email protected] backup]# ls
Ops_2016-09-25.sql.gz
[Email protected] backup]# gzip-d ops_2016-09-25.sql.gz
[[email protected] backup]# ls
Ops_2016-09-25.sql
[[email protected] backup]# grep change Ops_2016-09-25.sql
--Change MASTER to master_log_file= ' mysql-bin.000002 ', master_log_pos=106;

This is the Binlog file location for the full standby time
That is, the mysql-bin.000002 106 rows, so the data in the Binlog file before the file is already contained in this fully-prepared SQL file.

(6) Move the Binlog file and export it as a SQL file, rejecting the drop statement in it
to view the data storage directory for MySQL, as shown below under/var/lib/mysql
[[email protected] backup]# ps-ef|grep MySQL
root 9272 1 0 01:43 pts/1 00:00:00/bin/sh/usr/bin/mysqld_safe--datadir=/var/lib/mysql--s Ocket=/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
[[email  Protected] backup]# cd/var/lib/mysql/
[[email protected] mysql]# ls
ibdata1 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 edit it to delete the drop statement
[Email protected] backup]# mysqlbinlog-d OPS mysql-bin.000002 >002bin.sql
[[email protected] backup]# ls
002bin.sql mysql-bin.000002 Ops_2016-09-25.sql
[Email protected] backup]# vim 002bin.sql #删除里面的drop语句

Attention:
The Binlog file must be moved out before the full data is restored, or it will continue to be written to binlog during the recovery process, resulting in a somewhat confusing portion of the incremental recovery data

(7) Recovering data
[Email protected] backup]# Mysql-uroot-p < Ops_2016-09-25.sql
Enter Password:
[Email protected] backup]#

Check the database to see if the OPS library is not
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 names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> 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.sql
Enter Password:
[Email protected] backup]#

Review the database again and find that part of the data between the full backup and the deleted database is also restored!!
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