MySql通过binlog恢复数据

12/28/2021 mysql

# binlog恢复误删数据✨

MySQL 中的日志比较重要的有 binlog(归档日志)、redo log(重做日志)以及 undo log

# 1、详细概述🎇

binlog 是 MySQL Server 层的日志,而不是存储引擎自带的日志,它记录了所有的 DDL 和 DML(不包含数据查询语句)语句,而且是以事件形式记录,还包含语句所执行的消耗的时间等

需要注意的是:

1、binlog 是一种逻辑日志,他里边所记录的是一条 SQL 语句的原始逻辑,例如给某一个字段 +1,注意这个区别于 redo log 的物理日志(在某个数据页上做了什么修改)

2、binlog 文件写满后,会自动切换到下一个日志文件继续写,而不会覆盖以前的日志,这个也区别于 redo log,redo log 是循环写入的,即后面写入的可能会覆盖前面写入的

3、一般来说,我们在配置 binlog 的时候,可以指定 binlog 文件的有效期,这样在到期后,日志文件会自动删除,这样避免占用较多存储空间

根据 MySQL 官方文档的介绍,开启 binlog 之后,大概会有 1% 的性能损耗,不过这还是可以接受的,一般来说,binlog 有两个重要的使用场景:

  • MySQL 主从复制时:在主机上开启 binlog,主机将 binlog 同步给从机,从机通过 binlog 来同步数据,进而实现主机和从机的数据同步。
  • MySQL 数据恢复,通过使用 mysqlbinlog 工具再结合 binlog 文件,可以将数据恢复到过去的某一时刻。

# 2、开启binlog🌋

-- 登录mysql查看binlog日志的状态,off为关闭状态
SHOW VARIABLES LIKE 'log_bin%';
1
2

开启 binlog : vim /etc/my.cnf🛻

# 开启,并且可以将mysql-bin改为其它的日志名
# log-bin=mysql-bin
log-bin=my-bin

# 超过200M将生产新的文件,最大和默认值是1GB
# 设置最大 100MB
max_binlog_size=104857600

# 设置了 binlog 文件的有效期(单位:天)
expire_logs_days = 7

# 此参数配置binlog的日志格式,默认为mixed。
binlog_format=mixed

# binlog 日志只记录指定库的更新(配置主从复制的时候会用到)
#binlog-do-db=my_db

# binlog 日志不记录指定库的更新(配置主从复制的时候会用到)
#binlog-ignore-db=my_no_db

# 写缓存多少次,刷一次磁盘,默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘,n 则表示 n 个事务提交才会写磁盘
sync_binlog=0

# 添加id号,如果做主从,就不能一样
server-id=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

配置完成后,重启 mysql ,重启之后,再次执行 show variables like 'log_bin%'; 即可看到 binlog 已经开启了。

mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------------------------------------+
| Variable_name                   | Value                                                       |
+---------------------------------+-------------------------------------------------------------+
| log_bin                         | ON                                                          |
| log_bin_basename                | G:\java\sql\MySQL Server 8.0\Data\LAPTOP-RDCIFBH0-bin       |
| log_bin_index                   | G:\java\sql\MySQL Server 8.0\Data\LAPTOP-RDCIFBH0-bin.index |
| log_bin_trust_function_creators | OFF                                                         |
| log_bin_use_v1_row_events       | OFF                                                         |
+---------------------------------+-------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
  • log_bin_basename:这个是将来产生的 binlog 日志文件的名称前缀,换句话说,根据大家目前所看到的配置,将来产生的 binlog 日志文件名为 my-bin.xxx,这个文件中将会用来记录所有的 DDL 和 DML 语句事件。
  • log_bin_index:这个是 binlog 的索引文件,保存了所有 binlog 的目录,因为 binlog 可能会有多个。

# 3. 常见 binlog 操作👍

-- 查看所有binlog日志列表

show master logs;

-- 查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
show master status;

-- 刷新 binlog:正常来说,一个 binlog 写满之后,会自动切换到下一个 binlog 开始写,不过我们也可以执行一个 flush logs 命令来手动刷新 binlog;
-- 手动刷新 binlog 之后,就会产生一个新的 binlog 日志文件,接下来所有的 binlog 日志都将记录到新的文件中。
flush logs

-- 重置 binlog
reset master

-- 查看 binlog
-- 为了查看 binlog,MySQL 为我们提供了两个官方工具,我们一个一个来看,首先是 mysqlbinlog 命令
mysqlbinlog /var/lib/mysql/my-bin.00001

-- 方法2:这个表示以事件的方式来查看 binlog
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

-- log_name:可以指定要查看的 binlog 日志文件名,如果不指定的话,表示查看最早的 binlog 文件。
-- pos:从哪个 pos 点开始查看,凡是 binlog 记录下来的操作都有一个 pos 点,这个其实就是相当于我们可以指定从哪个操作开始查看日志,如果不指定的话,就是从该 binlog 的开头开始查看。
-- offset:这是是偏移量,不指定默认就是 0。
-- row_count:查看多少行记录,不指定就是查看所有。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 4、使用binlog恢复数据🧐

注意点:

恢复的时候要配合全备份,先进行全备份,在用 mysqldump 全备时添加-F刷新binlog,这时候mysqldump备份的是最新的binlog日志之前的内容了。

先进行全备份恢复,再将最新的binlog文件用mysqlbinlog进行查看,grep或者其他方式过滤,找到有问题的sql语句,记录下当时的pos点或者时间。只恢复出问题之前得时间点即可。

mysqldump -uroot -p --flush-logs --lock-tables -B mydb>/root/my.bak.sql

-- --flush-logs:这个表示在导出之前先刷新 binlog,刷新 binlog 之后将会产生新的 binlog 文件,后续的操作都存在新的 binlog 中。

-- --lock-tables:这个表示开始导出前,锁定所有表。需要注意的是当导出多个数据库时,lock-tables 分别为每个数据库锁定表
-- 因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性,不同数据库表的导出状态可以完全不同。

-- -B:这个表示指定导出的数据库名称,如果使用 `--all-databases` 或者 `-A` 代替 `-B` 表示导出所有的数据库。
1
2
3
4
5
6
7
8

通过binlog还原数据库

mysqlbinlog /var/lib/mysql/my-bin.000002 --stop-position=764 --database=mydb | mysql -uroot -p
1

常用参数选项解释:

--start-position= 875 起始pos点

--stop-position= 954 结束pos点

--start-datetime="2016-9-25 22:01:08" 起始时间点

--stop-datetime="2019-9-25 22:09:46" 结束时间点

--database=mydb 指定只恢复mydb数据库(一台主机上往往有多个数据库,只限本地log日志)

十年
陈奕迅