mysql 误删除库之后的数据恢复--通过binlog和dump文件进行恢复

前提

对mysql库进行了全备和增量备份（全备就是误删除的库进行了dump备份，增量备份就是开启了mysql的binlog日志进行备份，全备是基础，增备是基础上的改动）

情景说明

由于误操作，将包含有多张表的数据库给误删了

要求

恢复误删的数据库

具体步骤

说明

1. Binlog日志，即binary log，是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据
2. Binlog日志包括两类文件；第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*),记录数据库所有的DDL和DML(除了查询语句select)语句事件

操作过程中的sql说明

show variables like '%log_bin%'; -- 查看binlog日志启动状态，on为开启，off为关闭
SHOW MASTER LOGS; -- 查看所有binlog日志信息
show master status; -- 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值
-- reset master; -- 清空所有binlog日志 谨慎使用
FLUSH LOGS; -- 生成一个新的binlog日志文件
SHOW BINLOG EVENTS IN 'mysql-bin.00000x' FROM 2799 limit 2, 4; -- 查看mysql-bin.00000x中从log_pos为2799开始,偏移(跳过)2条记录，查看4条记录
SHOW GLOBAL VARIABLES LIKE 'gtid_mode';  -- 查看GLOBAL.GTID_MODE的值   OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON
SET @@GLOBAL.GTID_MODE = ON;  -- 修改GLOBAL.GTID_MODE的值, 必须按照顺序OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON 一层一层修改才行

开启binlog日志

开启mysql binlog日志，进入mysql配置文件(vi /etc/my.cnf) 在mysqld区域内添加如下内容，
①server-id = 1(单个节点id)
②log-bin= /var/lib/mysql/mysql-bin(位置一般和mysql库文件所在位置一样)
③expire_logs_days = 10(表示此日志保存时间为10天),重启mysqld,再次查看binlog日志开启状态为ON

说明

1. 每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志
2. 查看binlog文件内容，使用查看工具mysqlbinlog来查看(cat/vi/more都是无法打开的)

恢复步骤

1. 执行flush logs重新生成一个binlog日志用来记录新的日志，防止对之前的binlog污染
2. 恢复之前检查需要的内容: 一个最新的dump文件(a.sql) 一个最新的binlog日志(mysql-bin.00000a)
3. 先恢复备份的数据：

create database a; -- 创建误删除的库a
use a; 
source /a.sql; -- 通过source命令, 恢复dump文件

注：如果dump的文件是所有的库，则需要找出误删除库a的相关sql去执行

4. dump文件只是恢复到备份时间的数据，剩下实时缺少的数据需要通过binlog日志来恢复
5. 在binlog日志中查找出需要恢复的日志记录信息

6. mysqlbinlog mysql-bin.00000a  | grep 库名等信息(a); -- 可以先查找需要恢复的记录的pos信息  如下图中的 end_log_pos
7. show binlog events in 'mysql-bin.00000a' ; -- 然后查找对应记录的信息的大概位置

8. 恢复binlog中的一个事务:

 /usr/bin/mysqlbinlog  --start-position=1 --stop-position=435 --database=a /var/lib/mysql/mysql-bin.00000a | /usr/bin/mysql -uroot -p密码 -v a -h ip (其中整个命令的含义是通过mysqlbinlog读取日志内容并通过管道传给mysql命令，-v表示执行此mysql命令)

/usr/bin/mysqlbinlog --start-datetime="2018-04-27 20:57:55" --stop-datetime="2018-04-27 20:58:18" --database=a/var/lib/mysql/mysql-bin.00000a | /usr/bin/mysql -uroot -p8856769abcd -v a 

注: 在上一步中可能会出现问题:

ERROR 1781 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.   

此时需要将GLOBAL.GTID_MODE依次设置为OFF，然后再执行恢复命令，最后再切换为ON

参考1：binlog恢复
参考2: mysqldump命令