Shulou(Shulou.com)06/01 Report--

The following content mainly brings you a detailed explanation of how to restore the erroneous deletion of mysql database. The knowledge mentioned here, which is slightly different from books, is summarized by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

In the daily operation and maintenance work, the backup of mysql database is very important! The importance of the database to the website makes our management of mysql data not to be lost!

Then, people will inevitably make mistakes, maybe one day the brain short-circuited to a misoperation to delete the database, how to do??

Next, we will explain the recovery plan after the mysql database is deleted by mistake.

I. work scene

(1) the MySQL database is automatically backed up at 12:00 every night.

(2) when I went to work one morning, at 9: 00, a colleague fainted drop a database!

(3) urgent recovery is needed! Backup data files and incremental binlog files can be used for data recovery.

Second, the idea of data recovery

(1) use the CHANGE MASTER statements recorded in the complete sql file, the binlog file and its location information to find out the incremental part of the binlog file.

(2) use the mysqlbinlog command to export the above binlog file to sql file, and eliminate the drop statement in it.

(3) the complete data can be recovered by exporting the sql file of the full file and the incremental binlog file.

III. Illustration with examples

First, make sure that mysql turns on binlog logging

In the [mysqld] block in the / etc/my.cnf file, add:

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) 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 the log

-R: backup stored procedures, etc.

-x: lock the table

-- master-data: add CHANGE MASTER statement, binlog file and location information to the backup statement

(3) insert the 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) erroneous operation at this time and deletion of test database

Mysql > drop database ops

Query OK, 1 row affected (0.04 sec)

At this point, between complete and misoperation, the data written by the user is in the binlog and needs to be recovered!

(5) check the newly added binlog file after it is complete.

[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 location of the binlog file for the full time

That is, line 106 of mysql-bin.000002, so the data in the binlog file prior to this file is already contained in this complete sql file.

(6) move the binlog file and export it to a sql file, and remove the drop statement from it

Check the data storage directory of mysql. The following shows that it is under / var/lib/mysql.

[root@vm-002 backup] # ps-ef | grep mysql

Root 9272 10 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 by vim to delete the drop statement in it

[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

Note:

The binlog file must be removed before restoring full data, otherwise statements will continue to be written to binlog during the recovery process, resulting in confusion in the incremental recovery part of the data.

(7) restore data

[root@vm-002 backup] # mysql-uroot-p

< ops_2016-09-25.sql Enter password: [root@vm-002 backup]# 查看数据库，看看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 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 of the full moment is restored.

Next, use the 002bin.sql file to restore the new data between the full time and the deletion of the database.

[root@vm-002 backup] # mysql-uroot-p ops 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 in mysql database!

Finally, the following points are summarized:

1) this case is applicable to the repair of misoperation caused by man-made SQL statements or when there is no master-slave replication and other hot standby downtime.

2) the recovery condition is that mysql should enable binlog log function, and all data should be fully prepared and incremented.

3) when restoring, it is recommended to stop updating the database, that is, to prohibit updating the database.

4) restore the full volume first, then restore the incremental logs after the full time point into SQL files sequentially, and then delete the problematic SQL statements in the file (or through time and location points), and then restore them to the database.

For the above detailed explanation on how to restore the erroneous deletion of mysql database, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.