Sample code sharing about data recovery operation after accidental deletion of mysql database

The following editor will bring you an instruction on data recovery operations after the mysql database is accidentally deleted. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

In daily operation and maintenance work, the backup of mysql database is crucial! The importance of the database to the website makes it impossible for us to fail in the management of mysql data!
Then, people will inevitably make mistakes. Maybe one day the brain will short-circuit and the database will be deleted by mistake. What should I do? ? ?

The following is an explanation of the recovery plan after the MySQL database is accidentally deleted.

1. Work scenario

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

2. Data recovery ideas

(1) Use the data recorded in the complete sql file CHANGE MASTER statement, binlog file and its location point information, find the incremental part in the binlog file.
(2) Use the mysqlbinlog command to export the above binlog file as a sql file, and remove the drop statement .
(3) Complete data can be restored by exporting sql files of full files and incremental binlog files.

3. Example description

------------- --------------------------
First of all, make sure that mysql has the binlog function enabled Add the [mysqld] block in the /etc/my.cnf file:
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;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
+----+-----------+-----+
3 rows in set (0.00 sec)

(2) Now perform full backup

[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 database

-F: Refresh log
-R: Backup
stored procedure , etc. -x: Lock table
--master-data: Add the CHANGE MASTER statement, binlog file and location information to the backup statement
-----------------

(3) AgainInsert data

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) At this time, the test database was deleted by mistake.

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

At this time, after all preparations The data written by the user is in the binlog between the moment of misoperation and needs to be restored!

(5) View all new ones The binlog file

[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 full preparation time

That is, line 106 of mysql-bin.000002, so the data in the binlog file before this file is already included in This complete sql file is included

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

View mysql The data storage directory, as shown below, is under /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 the 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 #删除里面的drop语句

Note:

The binlog file must be moved out before restoring the full backup data, otherwise the recovery process , will continue to write statements to the binlog, eventually causing the incremental recovery data part to become confusing

(7) Recovery 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 is there

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)

The data at the full backup time is restored at this time

Then, use the 002bin.sql file to restore the full backup time until the database is deleted. During the period, the newly added data

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

checked the database again and found that the part of 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 applies to Repair of misoperations caused by artificial SQL statements or hot standby situations without master-slave replication and downtime

2) The recovery condition is that mysql must enable the binlog log function, and all data must be fully prepared and incremental

3) During recovery, it is recommended to stop external updates, that is, prohibit updates to the database

4) First restore the full volume, then restore the incremental logs after the full backup time into SQL files in order, and then delete the problematic SQL statements in the file (you can also use time and location points) , and then restore to the database.

The above is the detailed content of Sample code sharing about data recovery operation after accidental deletion of mysql database. For more information, please follow other related articles on the PHP Chinese website!