前言
相信每一个学IT的人或多或少都听说过从删库到跑路这个梗~下图也是在各种交流群屡禁不止，新人听着也是瑟瑟发抖。
人们茶余饭后，街头巷角难免要问。。。
下面技术流ken就教给各位新手们一招删库再也不用跑路的绝技~
实现原理
想要学会这个技能务必先要看我的这篇有关mysql日志的博客《MySQL系列详解三：MySQL中各类日志详解-技术流ken》。
一定要先了解二进制日志文件的作用
恢复(recovery) ：
复制(replication) :
审计(audit) ：
数据库备份
有人会有疑惑数据库都被我删了，哪还有什么备份？
殊不知，在你删库之前你们公司的运维工程师或者DBA已经悄悄的对数据库做了备份~
而且这个备份不是在你的电脑或者mysql服务器上面，可能已经被传送到了你们主管，DBA或者某台专用的备份服务器哪里去了，这个实现起来非常简单，运维工程师通过脚本就可以自动完成这个工作，总之他们肯定会有一份数据库的备份的~
但是他们的这个备份并不是完整备份，什么意思那
可能你们公司的数据库备份策略是周末做全量备份，周一至周六是做的增量备份，而且是每天只做一次增量备份
比如你们公司做增量备份是每天的凌晨时间，而你删库的时间是在早晨10点钟，那么这10个小时的数据，就没有备份了~
如果你们公司做的比较风生水起，在这十个小时的时间内有10万条的写入记录，你是不是会心口发塞，准备跑路了~
不要慌
第一时间去找你们公司的DBA问他有没有最新的数据库备份文件
如果他说没有。。
告诉他你把库删了，让他跟着你一起慌。。
但是你是看过我这篇博客的人，淡定
第二时间去找你们公司的运维工程师
他们肯定有！
总之无论你删了什么重要文件，都去找运维工程师，他们肯定有！他们就是一群没事爱做自动化备份的人
他们会告诉你：XX文件都被同步在了XX服务器上面了
恢复数据库
在DBA手里可以拿到全量备份以及增量备份的数据库文件，在运维工程师手里可以任何实时的二进制文件。
这个时候第一时间先去二进制服务器上面再次对二进制日志文件做一个备份！
这次能不能不跑路，就看这个二进制日志文件了。
拿到这些文件之后，现在就可以对数据库进行恢复了。
删库之后一定要先把数据库服务停掉，这是重点!
有了全量备份的文件，增量备份的文件以及二进制文件之后，就去拜托你们公司的DBA或者运维工程师来进行数据的恢复即可。
模拟删除数据表
接下来就完整演示从删表到数据恢复的过程
第一步：查看是是否开启了二进制日志
显示log_bin是开启的，没有问题
mysql> show global variables like "%log%bin%"; +----------------------------------+--------------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------------+ | log_bin | ON | | log_bin_basename | /data/mysql/mysql3306/logs/mysql-bin | | log_bin_index | /data/mysql/mysql3306/logs/mysql-bin.index | | log_bin_trust_function_creators | ON | | log_bin_use_v1_row_events | OFF | | log_statements_unsafe_for_binlog | ON | +----------------------------------+--------------------------------------------+ 6 rows in set (0.00 sec)
第二步：添加一些数据
我们首先往数据库中添加一些数据
下面我创建了一个ken数据库
ken数据库中创建了一个kenken数据表
在这个表中插入了一些数据
mysql> create database ken; Query OK, 1 row affected (0.00 sec) mysql> use ken; Database changed mysql> create table kenken(id int auto_increment primary key not null,name char(30)not null,tel int) -> ; Query OK, 0 rows affected (0.03 sec) mysql> desc kenken; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(30) | NO | | NULL | | | tel | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set (0.06 sec) mysql> insert into kenken (id,name,tel) values (10,"鲁班",123); Query OK, 1 row affected (0.01 sec) mysql> insert into kenken (id,name,tel) values (12,"后裔",1233); Query OK, 1 row affected (0.00 sec) mysql> insert into kenken (id,name,tel) values (13,"韩信",1234); Query OK, 1 row affected (0.00 sec) mysql> select * from kenken; +----+--------+------+ | id | name | tel | +----+--------+------+ | 10 | 鲁班 | 123 | | 12 | 后裔 | 1233 | | 13 | 韩信 | 1234 | +----+--------+------+ 3 rows in set (0.00 sec)
第三步：模拟DBA做全量备份
这样我们就获得了一个数据库全量备份的文件
[root@ken ~]# mysqldump -uroot -p --all-databases --single-transaction --flush-logs --set-gtid-purged=OFF --master-data=2 >/tmp/ken.sql Enter password: [root@ken ~]# tail /tmp/ken.sql /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-11-25 15:57:20
第四步：模拟数据写入
备份完成之后我们模拟数据写入，相当于在凌晨到早上十点钟这段时间
下面我又增加了四条记录，在备份文件中是没有的
mysql> insert into kenken (name,tel) values ("不知火舞",11); Query OK, 1 row affected (0.00 sec) mysql> insert into kenken (name,tel) values ("d貂蝉",11); Query OK, 1 row affected (0.00 sec) mysql> insert into kenken (name,tel) values ("明世隐",11); Query OK, 1 row affected (0.00 sec) mysql> insert into kenken (name,tel) values ("李白",11); Query OK, 1 row affected (0.00 sec) mysql> select * from kenken; +----+--------------+------+ | id | name | tel | +----+--------------+------+ | 10 | 鲁班 | 123 | | 12 | 后裔 | 1233 | | 13 | 韩信 | 1234 | | 14 | 不知火舞 | 11 | | 15 | d貂蝉 | 11 | | 16 | 明世隐 | 11 | | 17 | 李白 | 11 | +----+--------------+------+ 7 rows in set (0.00 sec)
第五步：模拟数据删除
现在是早晨10点钟，你把这个表删掉了
现在在备份文件中是没有这个新添加的表中的数据的
mysql> drop table kenken; Query OK, 0 rows affected (0.01 sec) mysql> select * from kenken; ERROR 1146 (42S02): Table 'ken.kenken' doesn't exist
模拟恢复数据表
第一步：查看二进制文件
进入到你们公司的二进制日志保存位置
下面这些就是二进制日志文件
[root@ken ~]# cd /data/mysql/mysql3306/logs/ [root@ken logs]# ls mysql-bin.000009 mysql-bin.000010 mysql-bin.000011 mysql-bin.000012 mysql-bin.index
第二步：查看全量备份文件
在大约22行处记录了：
自备份起，新开始的二进制日志文件保存的位置
非常重要
[root@ken logs]# vim /tmp/ken.sql
...
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=194;
--
-- Current Database: `ken`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `ken` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `ken`;
...
第三步：关闭二进制日志
第一步：先停掉数据库
第二步：修改数据库配置文件，注释掉改行
第三步：重启数据库即可
#log-bin = /data/mysql/mysql3306/logs/mysql-bin
第四步：恢复删除的表
先恢复这个全量备份的文件
感觉胜利在望，起码表和之前的数据都已经恢复了
但是还是没有我们模拟新添加的记录
mysql> source /tmp/ken.sql mysql> select * from ken.kenken; +----+--------+------+ | id | name | tel | +----+--------+------+ | 10 | 鲁班 | 123 | | 12 | 后裔 | 1233 | | 13 | 韩信 | 1234 | +----+--------+------+ 3 rows in set (0.00 sec)
第五步：使用备份文件
打开备份文件可以看到我的新产生的二进制是保存在了mysql-bin.000012,194处开始的
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=194;
第六步：找到删除表的那条命令位置可以看到在1349处就是删除表的命令
[root@ken logs]# mysqlbinlog mysql-bin.000014 ... wF/6Wx4qTw8AMwAAAOUEAAAAAA8BAAAAAAEAAgAD//gRAAAABuadjueZvQsAAACuazag '/*!*/; # at 1253 #181125 16:39:28 server id 1003306 end_log_pos 1284 CRC32 0x969a0e09 Xid = 2524 COMMIT/*!*/; # at 1284 #181125 16:39:43 server id 1003306 end_log_pos 1349 CRC32 0x6e619553 GTID last_committed=4 sequence_number=rbr_only=no SET @@SESSION.GTID_NEXT= '08e1a6ce-da57-11e8-a0af-000c292d5bb8:149'/*!*/; # at 1349 #181125 16:39:43 server id 1003306 end_log_pos 1466 CRC32 0x48ac1825 Query thread_id=18 exec_time=0 error_code=0 use `ken`/*!*/; SET TIMESTAMP=1543135183/*!*/; DROP TABLE `kenken` /* generated by server */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
第六步：截取二进制日志
开始位置是你全量备份显示的位置
停止位置是你误操作的位置
[root@ken logs]# mysqlbinlog --start-position=194 --stop-position=1349 mysql-bin.000014 >/tmp/kenken.sql
第七步：恢复全部数据
可以发现数据一个没有丢又都回来了！
恢复完数据之后千万记得开启配置文件中的二进制！！mysql> source /tmp/kenken.sql mysql> select * from ken.kenken; +----+--------------+------+ | id | name | tel | +----+--------------+------+ | 10 | 鲁班 | 123 | | 12 | 后裔 | 1233 | | 13 | 韩信 | 1234 | | 14 | 不知火舞 | 11 | | 15 | d貂蝉 | 11 | | 16 | 明世隐 | 11 | | 17 | 李白 | 11 | +----+--------------+------+ 7 rows in set (0.00 sec)
模拟删除数据库
现在我们来模拟删除一个数据库
其实和上面的步骤是一样的
千万记得恢复完数据之后重新启动二进制日志
这里我还是以上面的数据库为列
第一步：模拟备份
[root@ken logs]# mysqldump -uroot -p --all-databases --single-transaction --flush-logs --set-gtid-purged=OFF --master-data=2 >/tmp/ken.sql
第二步：添加数据
mysql> insert into kenken (name,tel) values ("庄周",121); Query OK, 1 row affected (0.01 sec) mysql> insert into kenken (name,tel) values ("马可波罗",121); Query OK, 1 row affected (0.00 sec) mysql> insert into kenken (name,tel) values ("黄忠",121); Query OK, 1 row affected (0.00 sec) mysql> select * from kenken; +----+--------------+------+ | id | name | tel | +----+--------------+------+ | 10 | 鲁班 | 123 | | 12 | 后裔 | 1233 | | 13 | 韩信 | 1234 | | 14 | 不知火舞 | 11 | | 15 | d貂蝉 | 11 | | 16 | 明世隐 | 11 | | 17 | 李白 | 11 | | 18 | 东皇太一 | 121 | | 19 | 项羽 | 121 | | 20 | 荆轲 | 121 | | 21 | 孙尚香 | 121 | | 22 | 庄周 | 121 | | 23 | 马可波罗 | 121 | | 24 | 黄忠 | 121 | +----+--------------+------+ 14 rows in set (0.00 sec)
第三步：删除ken数据库
mysql> drop database ken; 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)
模拟恢复数据库
第一步：关闭数据库，关闭二进制日志，并重新启动
[root@ken logs]# ps aux | grep mysql mysql 2515 0.2 37.3 1088560 186484 pts/3 Sl 17:01 0:01 mysqld root 2552 0.0 0.1 112704 968 pts/3 R+ 17:11 0:00 grep --color=auto mysql [root@ken logs]# kill -9 2515 [root@ken logs]# vim /etc/my.cnf [1]+ Killed mysqld [root@ken logs]# mysqld &
第二步：恢复全量备份数据
可以发现现在已经恢复了数据库以及一部分的数据
但是我们最后添加的数据还是没有
mysql> source /tmp/ken.sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ken | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use ken; Database changed mysql> select * from kenken; +----+--------------+------+ | id | name | tel | +----+--------------+------+ | 10 | 鲁班 | 123 | | 12 | 后裔 | 1233 | | 13 | 韩信 | 1234 | | 14 | 不知火舞 | 11 | | 15 | d貂蝉 | 11 | | 16 | 明世隐 | 11 | | 17 | 李白 | 11 | | 18 | 东皇太一 | 121 | | 19 | 项羽 | 121 | | 20 | 荆轲 | 121 | | 21 | 孙尚香 | 121 | +----+--------------+------+ 11 rows in set (0.00 sec)
第三步：截取二进制日志
首先查看备份文件中开始的位置
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=194;
再查看误操作的位置
... # at 1010 #181125 17:09:31 server id 1003306 end_log_pos 1075 CRC32 0x66b9786f GTID last_committed=3 sequence_number=rbr_only=no SET @@SESSION.GTID_NEXT= '08e1a6ce-da57-11e8-a0af-000c292d5bb8:157'/*!*/; # at 1075 #181125 17:09:31 server id 1003306 end_log_pos 1164 CRC32 0x6f04e5b3 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1543136971/*!*/; drop database ken /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
开始截图日志
[root@ken logs]# mysqlbinlog --start-position=194 --stop-position=1075 mysql-bin.000016 >/tmp/kenken.sql
第四步：恢复所有的数据
发现现在所有的数据都回来了
mysql> source /tmp/kenken.sql mysql> use ken; Database changed mysql> select * from kenken; +----+--------------+------+ | id | name | tel | +----+--------------+------+ | 10 | 鲁班 | 123 | | 12 | 后裔 | 1233 | | 13 | 韩信 | 1234 | | 14 | 不知火舞 | 11 | | 15 | d貂蝉 | 11 | | 16 | 明世隐 | 11 | | 17 | 李白 | 11 | | 18 | 东皇太一 | 121 | | 19 | 项羽 | 121 | | 20 | 荆轲 | 121 | | 21 | 孙尚香 | 121 | | 22 | 庄周 | 121 | | 23 | 马可波罗 | 121 | | 24 | 黄忠 | 121 | +----+--------------+------+ 14 rows in set (0.00 sec)
看完这篇博客，删掉数据库你还会怕吗？