MySQL误删库恢复实战

创建测试库、表

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

插入数据

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 |+------+----------+.......

全备数据库

#!/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}.flagfind $BakPath  -name "*.sql.gz"  -mtime +7|xargs rm -f

确认备份状态

[root@leo home]# cd mysql/backup/[root@leo backup]# lltotal 792-rw-r--r-- 1 root root     72 Jun 28 22:18 bak_2019-06-28.flag-rw-r--r-- 1 root root 804496 Jun 28 22:18 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

插入数据

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 |+------+----------+

删除数据库

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)

备份bin-log

[root@leo backup]# cd /home/mysql/3306/data/[root@leo data]# lltotal 123740-rw-r-----. 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 1612-rw-r--r-- 1 root  root      72 Jun 29 02:42 bak_2019-06-29.flag-rw-r--r-- 1 root  root  804515 Jun 29 02:42 bak_2019-06-29.sql-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.index

恢复步骤
1、停止数据库对外访问，防止数据库因update导致数据破坏
2、由于备份语句中加入--master-data=2，可以记录全备后的binlog对应恢复点

[root@leo data]# cd /home/mysql/backup/[root@leo backup]# lltotal 1612-rw-r--r-- 1 root  root      72 Jun 29 02:42 bak_2019-06-29.flag-rw-r--r-- 1 root  root  804515 Jun 29 02:42 bak_2019-06-29.sql-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.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、将binlog转换SQL

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

4、实际环境中后续还有 mysql-bin.000007 .... 需要陆续转换为SQL
5、删除test.sql中drop语句

[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、全备恢复数据库

[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、恢复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、校验数据，恢复数据库对外访问