mysql 误删除库之后的数据恢复--通过binlog和dump文件进行恢复
程序员文章站
2022-05-11 19:52:55
...
前提
对mysql库进行了全备和增量备份（全备就是误删除的库进行了dump备份，增量备份就是开启了mysql的binlog日志进行备份，全备是基础，增备是基础上的改动）
情景说明
由于误操作，将包含有多张表的数据库给误删了
要求
恢复误删的数据库
具体步骤
说明
- Binlog日志，即binary log，是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据
- 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
说明
- 每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志
- 查看binlog文件内容，使用查看工具mysqlbinlog来查看(cat/vi/more都是无法打开的)
恢复步骤
- 执行flush logs重新生成一个binlog日志用来记录新的日志，防止对之前的binlog污染
- 恢复之前检查需要的内容: 一个最新的dump文件(a.sql) 一个最新的binlog日志(mysql-bin.00000a)
- 先恢复备份的数据：
create database a; -- 创建误删除的库a
use a;
source /a.sql; -- 通过source命令, 恢复dump文件
注：如果dump的文件是所有的库，则需要找出误删除库a的相关sql去执行
- dump文件只是恢复到备份时间的数据，剩下实时缺少的数据需要通过binlog日志来恢复
- 在binlog日志中查找出需要恢复的日志记录信息
6. mysqlbinlog mysql-bin.00000a | grep 库名等信息(a); -- 可以先查找需要恢复的记录的pos信息 如下图中的 end_log_pos
7. show binlog events in 'mysql-bin.00000a' ; -- 然后查找对应记录的信息的大概位置
- 恢复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