Instructions for recovering data after accidental deletion of MySQL database

In daily operation and maintenance work, backup of MySQL database is crucial! The importance of the database to the website requires us to manage MySQL data without any mistakes!
Then, it is inevitable that people make mistakes. What if one day the brain short-circuits and the database is deleted by mistake? What should we do? ? ?

Next, we will explain the recovery plan after the MySQL database is accidentally deleted.

1. Work Scenario

(1) The MySQL database is automatically fully backed up at 12:00 every night.
(2) One morning at work, at 9 o'clock, a colleague fainted and dropped a database!
(3) Urgent recovery required! The backed-up data files and incremental binlog files can be used for data recovery.

2. Data recovery ideas

(1) Use the CHANGE MASTER statement recorded in the complete SQL file, the binlog file and its position information to find the incremental part in the binlog file.
(2) Use the mysqlbinlog command to export the above binlog file into a sql file and remove the drop statement .
(3) By exporting the SQL file of the full backup file and incremental binlog file, the complete data can be restored.

3. Example

----------------------------------------
First, make sure that MySQL has binlog enabled.
In the [mysqld] section of the /etc/my.cnf file, add:
log-bin=mysql-bin
Then restart the mysql service
----------------------------------------

(1) Create a table customers under the ops database

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 | 27 |
+----+-----------+-----+
3 rows in set (0.00 sec)

(2) Perform a 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 log
-R: backup storage process, etc.
-x: lock table
--master-data: Add the CHANGE MASTER statement and binlog file and location information to the backup statement
-----------------

(3) Insert 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) By mistake, the test database was deleted.

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

At this time, between the time of full backup and the time of error operation, the data written by the user is in the binlog and needs to be restored!

(5) View the newly added binlog files after full backup

[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 binlog file location at the time of full preparation, that is, line 106 of mysql-bin.000002. Therefore, the data in the binlog files before this file are already included in this full sql file.

(6) Move the binlog file and export it as a sql file, removing the drop statement

Check the mysql data storage directory, and you can see that it is in /var/lib/mysql

[root@vm-002 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 --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 a sql file and edit it with vim to delete the drop statement

[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

Notice:

The binlog file must be removed before restoring the full backup data. Otherwise, statements will continue to be written to the binlog during the recovery process, which will eventually cause the incremental recovery data to become confusing.

(7) Recover data

[root@vm-002 backup]# mysql -uroot -p < ops_2016-09-25.sql
Enter password:
[root@vm-002 backup]#

Check the database to see if the ops library exists

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
You 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 time, the data at the time of full recovery was restored

Next, use the 002bin.sql file to restore the newly added data between the time of full preparation and the time of deleting the database.

[root@vm-002 backup]# mysql -uroot -p ops <002bin.sql
Enter password:
[root@vm-002 backup]#

Checking the database again, I found that the data between the full backup and the deletion of the database was 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 example process of incremental data recovery of MySQL database!

**********************************************

Finally, let’s summarize a few points:

1) This case is applicable to repairing downtime caused by erroneous operations caused by human SQL statements or when there is no hot standby such as master-slave replication.

2) The recovery condition is that MySQL needs to enable the binlog function and all the data in full and incremental form must be backed up.

3) When restoring, it is recommended to stop external updates, that is, prohibit updating the database

4) Restore the full amount first, then restore the incremental logs after the full backup time point into SQL files in sequence, then delete the problematic SQL statements in the files (you can also restore them by time and location point), and then restore them to the database.

The above instructions on data recovery after accidental deletion of MySQL database are all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.