欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-11 19:52:55
...

前提

对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去执行

  1. dump文件只是恢复到备份时间的数据,剩下实时缺少的数据需要通过binlog日志来恢复
  2. 在binlog日志中查找出需要恢复的日志记录信息
6. mysqlbinlog mysql-bin.00000a  | grep 库名等信息(a); -- 可以先查找需要恢复的记录的pos信息  如下图中的 end_log_pos
7. show binlog events in 'mysql-bin.00000a' ; -- 然后查找对应记录的信息的大概位置

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

  1. 恢复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命令

相关标签: 数据库集合