PS:原创文章,如需转载,请注明出处,谢谢!
前几天,由于同事误操作delete掉线上的一个数据库,由于操作库为MySQL8,默认二进制日志是打开的。于是开始了利用mysqlbinlog恢复表数据的折腾过程。在此仅记录一下。
什么是mysql binglog
MySQL binlog是一种用于记录MySQL服务器上所有增删改操作的日志文件。当数据发生更改时,MySQL会自动将更改信息记录到binlog文件中。因此,当MySQL数据库发生故障或数据丢失时,可以使用binlog文件进行数据恢复。另外还可以结合canal进行结合使用,解析binlog文件并将其中的数据同步到其他系统或数据库中。;
以下为操作步骤
-- 查看bin-log是否开启
show variables like '%log_bin%';
-- 查看所有binlog日志文件列表
show master logs;
-- 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值:
show master status;
-- 查看需要恢复的日志
show binlog events in 'mysql-bin.000002';
--查看binlog模式
show variables like 'binlog_format';
-- 指定位点查询,比如从pos为746开始查询
SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746;
--根据开始节点和结束节点导出sql 在LInux目录下执行
mysqlbinlog --start-position='4' --stop-position='1757980' /data/mysql/binlog.000015 > /data/Backup_3.sql
//恢复sql 登录数据库,在数据库下进行允许 (注意指定数据库)
source /data/Backup_3.sql;
binlog默认情况下是不开启的,不过一般情况下,初始化的时候建议在配置文件中增加log-bin参数来开启binlog。
# 配置文件中增加log-bin配置
[mysqld]
log-bin = mysql-binlog# 不指定路径默认在data目录下,也可以指定路径
[mysqld]
log-bin = /data/mysql/logs/binlog
关于binlog操作与管理相关的SQL也有很多,下面介绍下部分常用的语句:
SQL语句 | 语句含义 |
show master status | 查看当前最新的一个binlog日志的编号名称,及最后一个事件结束的位置 |
show binary logs | 查看目前保留的所有binlog日志列表及大小 |
flush logs | 刷新binlog,此刻开始产生一个新编号的binlog日志文件 |
purge binary logs before '2020-07-01 12:00:00' | 手动清理指定时间之前的binlog日志 |
purge binary logs to 'binlog.000012' | 将指定日志文件之前的日志清除 |
reset master | 清空所有的binlog日志,慎用 |
show binlog events in 'binlog.000030' | 查看指定的binlog日志event |
show binlog events in 'binlog.000030' from 931 | 从指定的位置开始,查看指定的binlog日志 |
show binlog events in 'binlog.000030' from 931 limit 2 | 从指定的位置开始,查看指定的binlog日志,限制查询的enent数 |