Description of data recovery operations after the mysql database is deleted by mistake, mysql Data Recovery

Description of data recovery operations after the mysql database is deleted by mistake, mysql Data Recovery

In daily O & M work, mysql database backup is crucial! The importance of databases for websites makes it imperative for us to manage mysql Data!
Then, it is inevitable that people will make mistakes. Maybe the brain will be short-circuited one day and a misoperation will be triggered to delete the database. What should I do ???

The following describes the restoration solution after the mysql database is deleted by mistake.

I. Work scenarios

(1) the MySQL database is automatically completely backed up at every night.
(2) I went to work one morning. At, a colleague fainted and dropped a database!
(3) urgent recovery is required! You can use the backup data files and incremental binlog files to recover data.

Ii. Data Recovery ideas

(1) Use the change master statement recorded in the full-Backup SQL file, binlog file and its location information to find the incremental part of the binlog file.
(2) Use the mysqlbinlog command to export the preceding binlog file as an SQL file and remove the drop statement.
(3) The SQL file exported from the full backup file and incremental binlog file can be used to restore the complete data.

Iii. instance description

----------------------------------------
First, make sure that the binlog function is enabled for mysql.
Add the following in 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) perform full backup now

[Root @ vm-002 ~] #Mysqldump-uroot-p-B-F-R-x -- master-data = 2 ops | gzip>/opt/backup/ops _ $ (date must have f0000. SQL .gz
Enter password:
[Root @ vm-002 ~] # Ls/opt/backup/
Ops_2016-09-25. SQL .gz

-----------------

Parameter description:

-B: Specifies the database.
-F: refresh the 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) The test database is deleted due to misoperations.

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

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

(5)View new binlog files after full backup

[root@vm-002 ~]# cd /opt/backup/[root@vm-002 backup]# lsops_2016-09-25.sql.gz[root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz [root@vm-002 backup]# lsops_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 location of the binlog file at the full backup time.
That is, the 106 line of the mysql-bin.000002, so the data in the binlog file prior to the file has been included in this full-Backup SQL File

(6) Move the binlog file and export it as an SQL file, removing the drop statement.

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

Export the binlog file to the SQL file and edit it in vim 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_2016-09-25. SQL [root @ vm-002 backup] # vim 002bin. SQL # Delete the drop statement

Note:

The binlog file must be removed before full-backup data recovery. Otherwise, the statement will be written to the binlog during the recovery process, resulting in confusion in the incremental data recovery part.

(7) restore 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 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 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 time, the data at the full backup time is restored.

Then, use the 002bin. SQL file to restore the new data between the full backup time and the deleted database.

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

Check the database again and find that 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, we will summarize the following points:

1) this case is applicable to the troubleshooting of human SQL statement misoperation or hot standby scenarios without master-slave replication.

2) The recovery condition is that mysql needs to enable the binlog function, and all data must be fully standby and incremental.

3) We recommend that you stop updating the database.

4) restore the full data first, and then restore the incremental logs after the full backup time point to SQL files in order, then, delete the problematic SQL statement in the file (the time and location can also be used) and restore it to the database.

The above data recovery instructions on the accidental deletion of mysql databases are all the content that I have shared with you. I hope you can provide a reference and support for the customer's home.