MySQL mistakenly deleted the database to restore the actual combat

Create test libraries, tables

Create database test;use test;create table leo (id int,name varchar (10))

Insert data

Insert into leo values (1, "liufeng"); insert into leo values (2, "zhangsan"); insert into leo values (3, "liufeng"); insert into leo values (4, "zhangsan"); mysql > select * from leo +-+-+ | id | name | +-+-+ | 1 | liufeng | | 2 | zhangsan | 3 | liufeng | | 4 | zhangsan | +-+-+.

Complete database

#! / bin/shbak_path=/home/mysql/backupfile_name=bak_$ (date +% F) if [!-e $bak_path] Then mkdir-p $bak_pathfi mysqldump-uroot-proot-S / home/mysql/3306/data/mysql.sock-A-- single-transaction-- master-data=2 > $bak_path/$ {file_name} .sqlmd5sum $bak_path/$ {file_name} .sql > $bak_path/$ {file_name}. Find $BakPath-name "* .sql.gz"-mtime + 7 | xargs rm-f

Confirm backup status

[root@leo home] # cd mysql/backup/ [root@leo backup] # lltotal 792 RWMurray-1 root root 72 Jun 28 22:18 bak_2019-06-28.There are some examples of bak_2019-06-28.sql [root@leo backup] # more bak_2019-06-28.flag db297e95d491ae3b85ed2b5d2496e527 / home/mysql/backup/bak_2019-06-28.sql [root@leo backup] Cd. [root@leo mysql] # [root@leo mysql] # md5sum-c / home/mysql/backup/bak_2019-06-28.flag/home/mysql/backup/bak_2019-06-28.sql: OK

Insert data

Mysql > insert into leo values (5, "liufeng"); Query OK, 1 row affected (0.01 sec) mysql > insert into leo values (6, "zhangsan"); Query OK, 1 row affected (0.01 sec) mysql > select * from leo +-+-+ | id | name | +-+-+ | 1 | liufeng | | 2 | zhangsan | | 3 | liufeng | 4 | zhangsan | | 5 | liufeng | | 6 | zhangsan | +-+

Delete database

Mysql > show databases;+-+ | Database | +-+ | information_schema | | leo | | mysql | | performance_schema | | sys | | test | +-+ 6 rows in set (0.00 sec) mysql > drop database test Query OK, 0 rows affected (0.01 sec) mysql > drop database test;Query OK, 1 row affected (0.01 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec)

Backup bin-log

[root@leo backup] # cd / home/mysql/3306/data/ [root@leo data] # lltotal 123740 Murray. 1 mysql mysql 56 Jun 18 00:59 auto.cnf-rw-r- 1 mysql mysql 456 Jun 28 17:41 ib_buffer_pool-rw-r-. 1 mysql mysql 12582912 Jun 29 02:45 ibdata1-rw-r-. 1 mysql mysql 50331648 Jun 29 02:45 ib_logfile0-rw-r-. 1 mysql mysql 50331648 Jun 18 00:59 ib_logfile1-rw-r- 1 mysql mysql 12582912 Jun 29 02:42 ibtmp1drwxr-x---. 2 mysql mysql 4096 Jun 25 23:09 mysql-rw-r- 1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003-rw-r- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004-rw-r- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005-rw-r- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006-rw-r- -1 mysql mysql 76 Jun 28 19:36 mysql-bin.indexsrwxrwxrwx 1 mysql mysql 0 Jun 28 19:36 mysql.sock-rw- 1 mysql mysql 5 Jun 28 19:36 mysql.sock.lockdrwxr-x---. 2 mysql mysql 4096 Jun 18 00:59 performance_schema-rw-r- 1 mysql mysql 5 Jun 28 19:36 resourcepool-0559.piddrwxr-x---. 2 mysql mysql 12288 Jun 18 00:59 sys [root@leo data] # cp-a mysql-bin.* / home/mysql/backup/ [root@leo data] # ll / home/mysql/backup/total 1612Muhashi RWQUR Mustang-1 root root 72 Jun 29 02:42 bak_2019-06-29.Muhashi RWKui-1 root root 804515 Jun 29 02:42 bak_2019-06-29.sql Mustang RWKul r-1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003-rw-r- 1 mysql mysql 815245 Jun 25 23:17 mysql-bin.000004-rw-r- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005-rw-r- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006-rw-r- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index

Recovery steps

1. Stop the external access of the database to prevent data destruction caused by update.

2. Because-- master-data=2 is added to the backup statement, you can record the recovery point corresponding to the complete binlog.

[root@leo data] # cd / home/mysql/backup/ [root@leo backup] # lltotal 1612 Rwkashi-1 root root 72 Jun 29 02:42 bak_2019-06-29.The Mustang RWKul-1 root root 804515 Jun 29 02:42 bak_2019-06-29.sqlKul r mysql mysql r-1 mysql mysql 177 Jun 25 22:54 mysql-bin.000003-rw-r- 1 mysql mysql 815245 Jun 25 23 17 mysql-bin.000004-rw-r- 1 mysql mysql 642 Jun 28 17:41 mysql-bin.000005-rw-r- 1 mysql mysql 5909 Jun 29 02:45 mysql-bin.000006-rw-r- 1 mysql mysql 76 Jun 28 19:36 mysql-bin.index [root@leo backup] # sed-n '22p' bak_2019-06-29.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006' MASTER_LOG_POS=5181

3. Convert binlog to SQL

[root@leo backup] # mysqlbinlog-d test mysql-bin.000006-- start-position=5181-r test.sql [root@leo backup] # cat test.sql

4. In the actual environment, there is also mysql-bin.000007. Need to be converted to SQL one after another

5. Delete the drop statement in test.sql

[root@leo backup] # grep-w drop test.sql drop database test [root@leo backup] # sed-I'/ drop database test/d' test.sql [root@leo backup] # grep-w drop test.sql

6. Fully recover the database

[root@leo backup] # mysql-u root-p-S / home/mysql/3306/data/mysql.sock < bak_2019-06-29.sql Enter password: [root@leo backup] # mysql-u root-p-S / home/mysql/3306/data/mysql.sock-e "select * from test.leo" Enter password: +-+-+ | id | name | +-+-+ | 1 | liufeng | | 2 | zhangsan | | 3 | liufeng | | 4 | zhangsan | +-+-+

7. Restore binlog

[root@leo backup] # mysql-u root-p-S / home/mysql/3306/data/mysql.sock < test.sqlEnter password: [root@leo backup] # mysql-u root-p-S / home/mysql/3306/data/mysql.sock-e "select * from test.leo" Enter password: +-+-+ | id | name | +-+-+ | 1 | liufeng | | 2 | zhangsan | 3 | liufeng | 4 | zhangsan | | 5 | liufeng | | 6 | zhangsan | +-+-+

8. Verify the data and restore the external access of the database