数据恢复

通过 MySQL Cli 查询 BINLOG 信息

在 cli 中，常见的命令如下：

# 查询 BINLOG 格式
show VARIABLES like 'binlog_format';

# 查询 BINLOG 位置
show VARIABLES like 'datadir';

# 查询当前数据库中 BINLOG 名称及大小
show binary logs;

# 查看 master 正在写入的 BINLOG 信息
show master status\G;

# 通过 offset 查看 BINLOG 信息
show BINLOG events in 'mysql-bin.000034' limit 9000,  10;

# 通过 position 查看 binlog 信息
show BINLOG events in 'mysql-bin.000034' from 1742635 limit 10;

使用 show BINLOG events 的问题：

• 使用该命令时，如果当前 binlog 文件很大，而且没有指定 limit，会引发对资源的过度消耗。因为 MySQL 客户端需要将 binlog 的全部内容处理，返回并显示出来。为了防止这种情况，mysqlbinlog 工具是一个很好的选择。

通过 mysqlbinlog 查询 BINLOG 信息

在介绍 mysqlbinlog 工具使用前，先来看下 BINLOG 文件的内容：

# 查询 BINLOG 的信息
mysqlbinlog  --no-defaults mysql-bin.000034 | less
# at 141
#100309  9:28:36 server id 123  end_log_pos 245
  Query thread_id=3350  exec_time=11  error_code=0

• at 表示 offset 或者说事件开始的起始位置
• 100309 9:28:36 server id 123 表示 server 123 开始执行事件的日期
• end_log_pos 245 表示事件的结束位置 + 1，或者说是下一个事件的起始位置。
• exec_time 表示在 master 上花费的时间，在 salve 上，记录的时间是从 Master 记录开始，一直到 Slave 结束完成所花费的时间。
• rror_code=0 表示没有错误发生。

在大致了解 binlog 的内容后，mysqlbinlog 的用途有哪些？：

• mysqlbinlog 可以作为代替 cli 读取 binlog 的工具。
• mysqlbinlog 可以将执行过的 SQL 语句输出，用于数据的恢复或备份。

查询 BINLOG 日志：

# 查询规定时候后发生的 BINLOG 日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2019-11-22 14:00:00" \
 --database sync_test  mysql-bin.000034 | less

导出 BINLOG 日志，用于分析和排查 sql 语句：

mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2019-11-22 14:00:00" \
 --database sync_test \
 mysql-bin.000034 > /home/mysql_backup/binlog_raw.sql

导入 BINLOG 日志

# 通过 BINLOG 进行恢复。
mysqlbinlog --start-position=1038 --stop-position=1164 \
 --database=db_name  mysql-bin.000034 | \
 mysql  -u cisco -p db_name

# 通过 BINLOG 导出的 sql 进行恢复。
mysql -u cisco -p db_name < binlog_raw.sql

mysqlbinlog 的常用参数：

• --database 仅仅列出配置的数据库信息

• --no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令

• --offset 跳过 log 中 N 个条目

• --verbose 将日志信息重建为原始的 SQL 陈述。

  • -v 仅仅解释行信息

  • -vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来

• --start-datetime 显示从指定的时间或之后的时间的事件。

  • 接收 DATETIME 或者 TIMESTRAMP 格式。

• --base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽。

  • AUTO 默认参数，自动显示 BINLOG 中的必要的语句

  • NEVER 不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。

  • DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据。

#

如果想知道当前 MySQL 中正在写入的 BINLOG 的名称，大小等基本信息时，可以通过 Cli 相关的命令来查询。

但想查询，定位，恢复 BINLOG 中具体的数据时，要通过 mysqlbinlog 工具，因为相较于 Cli 来说，mysqlbinlog 提供了 --start-datetime，--stop-position 等这样更为丰富的参数供我们选择。这时 Cli 中 SHOW BINLOG EVENTS 的简要语法就变得相形见绌了。

使用 BINLOG 恢复数据

恢复的大致流程如下：

1. 会创建数据库和表，并插入数据。
2. 误删一条数据。
3. 继续插入数据。
4. 误删表。
5. 最后将原来以及之后插入的数据进行恢复。

执行mysqlbinlog出现Found invalid event in binary log错

故障描述

mysqlbinlog --start-datetime='2015-04-14 09:00:00' --stop-datetime='2015-04-14 16:00:00' mysql-bin.000170 > userop.log

ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 127, event_type: 19

故障分析与解决

1. 查看当前os使用的mysqlbinlog

shell> which mysqlbinlog

/usr/bin/mysqlbinlog

2. 查看mysql当前使用的mysqlbinlog

mysql> show variables like 'basedir';

+---------------+------------------+

| Variable_name | Value |

+---------------+------------------+

| basedir | /usr/local/mysql |

+---------------+------------------+

1 row in set (0.00 sec)

3. 对比两个版本

shell> /usr/bin/mysqlbinlog --version

/usr/bin/mysqlbinlog Ver 3.2 for redhat-linux-gnu at x86_64

shell> /usr/local/mysql/bin/mysqlbinlog --version

/usr/local/mysql/bin/mysqlbinlog Ver 3.3 for linux2.6 at x86_64

4. 指定mysqlbinlog路径

shell> /usr/local/mysql/bin/mysqlbinlog --start-datetime='2015-04-14 09:00:00' --stop-datetime='2015-04-14 16:00:00' mysql-bin.000170 > userop.log

参考资料

[1] https://www.cnblogs.com/michael9