mysqlbinlog-二进制日志文件的分析工具

没有评论

mysqlbinlog-二进制日志文件的分析工具

mysqlbinlog 是 MySQL 提供的一个二进制日志分析工具，可以将二进制日志和中继日志以二进制格式或文本格式显示。

调用语法

mysqlbinlog [options] log_file ...

事件分组

使用事件组时，可以将事件的文件偏移量 (开始位置) 分组在一起，并且可以将事件的注释分组在一起。请勿将这些分组的事件误认为空白文件偏移量。

系统变量

  • binlog_encryption=ON
    • 加密二进制日志

      如果二进制日志被加密，mysqlbinlog 就无法直接读取，必须使用 –read-from-remote-server 选项远程读取它们。

    • 查看二进制日志是否加密

      • SHOW BINARY LOGS
      • 通过文件头的魔术数字区分
        • 加密的文件的文件头：0xFD62696E
        • 未加密的文件的文件头：0xFE62696E
  • binlog_transaction_compression=ON
    • 压缩二进制日志

      当二进制日志事务已被压缩时，mysqlbinlog 自动进行解压缩和解码。

      如果事务负载已经被压缩，但 mysqlbinlog 自己的连接压缩的作用较小，但仍可对未压缩的事务和标头进行操作。

  • pseudo_slave_mode

    • 在执行任何 SQL 语句之前，mysqlbinlog 将 pseudo_slave_mode 的值设置为 true
    • 此变量影响 XA 事务的处理，original_commit_timestamp 复制延迟时间戳和 original_server_version 系统变量以及不支持的 SQL 模式

环境变量

  • TMPDIR
    • 临时文件的目录

      mysqlbinlog 读取大型二进制日志时，请注意文件系统有足够的空间容纳生成的文件。

选项

mysqlbinlog 会读取配置文件中 [mysqlbinlog]、[client] 标签组。

帮助选项

  • –help, -?
    • 帮助
  • –version, -V
    • 显示版本

连接选项

  • –bind-address=ip_address
    • 当有多个网络接口时，可以用这个选项指定要连接的服务端
  • –compress, -C

    已弃用。

    • 尽可能压缩客户端和服务器之间发送的所有信息
  • –compression-algorithms=value
    • 连接时的压缩算法，默认值是 uncompressed
  • –default-auth=plugin
    • 指定身份验证插件
  • –get-server-public-key
    • 从服务器请求基于 RSA 密钥对的密码交换所需的公共密钥。此选项适用于使用 caching_sha2_password 身份验证插件进行身份验证的客户端。
    • –server-public-key-path=file_name 选项的优先级高于此选项
  • –host=host_name, -h host_name
    • 主机名
  • –login-path=name
    • 指定登录路径文件 .mylogin.cnf
  • –password[=password], -p
     此处含有隐藏内容，需要正确输入密码后可见！

    • 密码
  • –plugin-dir=dir_name
    • 指定插件目录
  • –port=port_num, -P port_num
    • 连接端口
  • –protocol={TCP|SOCKET|PIPE|MEMORY}
    • 连接协议
  • –server-public-key-path=file_name
    • PEM 格式的文​​件的路径名，其中包含服务器用于基于 RSA 密钥对的密码交换所需的公用密钥的客户端副本。
    • 此选项适用于使用 sha256_password或caching_sha2_password 身份验证插件进行身份验证的客户端。
  • –socket=path, -S path
    • 连接使用的 socket 文件路径
  • –ssl*
    • 以 –ssl 开头的选项指定是否使用 SSL 连接到服务器，并指示在何处查找 SSL 密钥和证书。
  • –ssl-fips-mode={OFF|ON|STRICT}
    • 是否在客户端上启用 FIPS 模式
  • –tls-ciphersuites=ciphersuite_list
    • 使用 TLSv1.3 的加密连接的允许密码列表
  • –tls-version=protocol_list
    • 加密连接允许的 TLS 协议
  • –user=user_name, -u user_name
    • 用户
  • –zstd-compression-level=level
    • zstd 算法的压缩级别，允许的压缩级别是 1-22，默认是3
  • –connection-server-id=server_id
    • 指定 mysqlbinlog 连接的服务器 ID
    • 它可以用来避免与副本服务器或另一个 mysqlbinlog 进程的 ID 冲突。
    • 与其他选项的关系
      • 如果指定了 –read-from-remote-server 选项，则 mysqlbinlog 报告服务器 ID 为0，该 ID 告诉服务器在发送最后一个日志文件后断开连接 (非阻塞行为)
      • 如果还指定了 –stop-never 选项来维持与服务器的连接，则 mysqlbinlog 默认报告服务器 ID 为1而不是0，并且 –connection-server-id 可用于替换该服务器 ID (如果需要)
  • –local-load=dir_name, -l dir_name
    • 此选项用于显式的指定 LOAD DATA 的加载目录

      对于与 LOAD DATA 语句相对应的数据加载操作，mysqlbinlog 从二进制日志事件中提取文件，将它们作为临时文件写入本地文件系统，并写入 LOAD DATA LOCAL 语句使文件被加载。默认情况下，mysqlbinlog 将这些临时文件写入特定于操作系统的目录。

    • 因为其他进程可能将文件写入系统的默认目录，所以使用 mysqlbinlog 时建议将 –local-load 和 –load-data-local-dir 指定为相同目录

      mysqlbinlog --local-load=/my/local/data ...
    | mysql --load-data-local-dir=/my/local/data ...
    • 这些临时文件不会被 mysqlbinlog 或任何其他 MySQL 程序自动删除

  • –read-from-remote-master=type

    • 从远程读取二进制日志
    • 选项值
      • BINLOG-DUMP-NON-GTIDS
        • 通过 COM_BINLOG_DUMP 命令读取二进制日志
        • 类似 –read-from-remote-server 选项
      • BINLOG-DUMP-GTIDS
        • 通过 COM_BINLOG_DUMP_GTID 命令读取二进制日志
        • 与 –exclude-gtids 结合使用，则可以在源上过滤掉事务，从而避免不必要的网络流量。
  • –read-from-remote-server, -R
    • 从远程读取二进制日志 (不适用于中继日志)
    • 所需权限
      • REPLICATION SLAVE
  • –server-id=id
    • 指定服务器 ID
  • –server-id-bits=N

    只有 NDB Cluster 发行版附带的 mysqlbinlog 版本或 NDB Cluster 支持的内置版本的 mysqlbinlog 才支持此选项。

    • 仅使用 server_id 的前 N 位来标识服务器
  • –stop-never-slave-server-id=id

    已被 –connection-server-id 选项取代，将来会删除。

    • 为 mysqlbinlog 指定要报告的服务器 ID
  • –stop-never
    • 保持服务器的连接
    • 此选项与 –read-from-remote-server 一起使用
    • 意味着 –to-last-log，因此仅需要在命令行上命名要传输的第一个日志文件
    • 通常与 –raw 一起使用以进行实时二进制日志备份，但也可以不与 –raw 一起使用，以在服务器生成日志事件时保持日志事件的连续文本显示
    • 默认情况下，mysqlbinlog 在连接到服务器时报告服务器 ID 为1。
      • 使用 –connection-server-id 显式指定要报告的备用 ID，可以避免与副本服务器或另一个 mysqlbinlog 进程的 ID 冲突。

国际化选项

  • –character-sets-dir=dir_name
    • 字符集的安装路径
  • –set-charset=charset_name
    • 将 SET NAMES default_character_set 记录到输出中。默认已开启。使用 –skip-set-charset 禁用此功能。

资源限制选项

  • –binlog-row-event-max-size=N
    • 指定基于行的二进制日志事件的最大大小，以字节为单位
    • 如果可能，将行分组为小于此选项的事件。该值应为256的倍数。默认值为 4GB。
  • –open-files-limit=N
    • 指定要保留的打开文件描述符的数量

格式选项

  • –base64-output=value
    • 控制何时将二进制日志事件显示为 base-64 字符串编码

    • 选项值

      不区分大小写。

      • AUTO,UNSPEC (未指定，unspecified)
        • 在必要时 (对于格式描述事件和行事件) 自动显示二进制日志的语句
        • 如果未提供此选项，则效果与 –base64-output = AUTO 相同
        • 如果使用 mysqlbinlog 重做二进制日志，则 BINLOG 的自动显示是唯一安全的行为
      • NEVER
        • 不显示 BINLOG 语句
        • 如果找到行事件，它会因错误而退出
      • DECODE-ROWS
        • 配合 –verbose 使用时，控制对行事件进行解码并显示为带注释的 SQL 语句
        • 禁止显示 BINLOG 语句，但是与 NEVER 不同，如果找到行事件，它不会因错误而退出
  • –disable-log-bin, -D
    • 禁用二进制日志记录
    • 如果使用 –to-last-log 选项并将输出发送到同一 MySQL 服务器，这对于避免无限循环很有用
    • 在意外退出后恢复时，此选项也很有用，以避免重复已记录的语句
    • 此选项使 mysqlbinlog 在其输出中包含 SET sql_log_bin = 0 语句，以禁用其余输出的二进制日志记录
  • –hexdump, -H
    • 在注释中显示日志的十六进制转储
    • 十六进制输出对于复制结构的调试很有帮助
  • –raw
    • 告诉 mysqlbinlog 以其原始二进制格式编写写入事件
      • 默认是以文本格式写入
    • 需要和 –read-from-remote-server 选项配合使用，因为文件是从服务器请求的

    • 可用于备份服务器的二进制日志

      • –stop-never
        • mysqlbinlog 保持与服务器的连接 (在线备份)
      • –result-file
        • 输出目录
        • 默认情况下，输出文件将以与原始日志文件相同的名称写入当前目录
  • –result-file=name, -r name
    • 指定输出的二进制文件名或文件名的前缀
    • 如果没有 –raw 选项，则此选项指示 mysqlbinlog 的输出文件名
    • 如果使用 –raw 选项，则此选项指示 mysqlbinlog 的输出文件名前缀 (默认是输出到当前目录)
  • –require-row-format
    • 要求使用基于行的二进制日志格式。强制 mysqlbinlog 输出基于行的复制事件
    • 使用此选项产生的事件流将被使用 CHANGE REPLICATION SOURCE TO 语句的 REQUIRE_ROW_FORMAT 选项保护的复制通道接受
    • 必须在写入二进制日志的服务器上设置 binlog_format = ROW
    • 如果遇到此选项的禁止事件，则 mysqlbinlog 将停止并显示错误消息
      • LOAD DATA INFILE 指令
      • 创建或删除临时表
      • INTVAR，RAND 或 USER_VAR 事件
      • DML 事务中非基于行的事件
    • mysqlbinlog 还在其输出的开头打印 SET @@ session.require_row_format 语句，以在执行输出时应用限制，但是不打印 SET @@ session.pseudo_thread_id 语句

筛选选项

  • 按库筛选

    • –database=db_name, -d db_name
      • 指定输出信息中的数据库。
      • 类似于 mysqld 的 –binlog-do-db 选项，但是只能用于指定一个数据库。如果被多次给出，则仅使用最后一个实例。
  • 替换字符串
    • –rewrite-db=’from_name->to_name’
      • 从基于行或基于语句的日志中读取时，将所有出现的 from_name 重写为 to_name

      • 对于基于行的日志，对行进行重写；对于基于语句的日志，对 USE 子句进行重写。

      • 使用此选项时，表名称与数据库名称匹配的语句不会被重写为使用新名称

      • from_name 和 to_name 是字符串，必须引起来

      • 要采用多个重写规则，请多次指定该选项

        mysqlbinlog --rewrite-db='dbcurrent->dbold' --rewrite-db='dbtest->dbcurrent' \
    binlog.00001 > /tmp/statements.sql
      • 与 –database 选项一起使用时，–rewrite-db 选项将首先应用；然后使用重写的数据库名称应用 –database 选项。与选项出现的顺序无关。 
        --rewrite-db='mydb->yourdb' --database=yourdb  # 正确示例
--rewrite-db='mydb->yourdb' --database=mydb  # 错误示例
  • 按 GTID 筛选
    • –exclude-gtids=gtid_set
      • 不要显示 gtid_set 中列出的任何组
    • –include-gtids=gtid_set
      • 仅显示 gtid_set 中列出的组
    • –skip-gtids[=(true|false)]
      • 不要在输出中显示任何 GTID

      • 从一个或多个包含 GTID 的二进制日志写入转储文件时，这是必需的

        mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
mysql -u root -p -e "source /tmp/dump.sql"
  • 按时间筛选
    • –start-datetime=datetime
      • 读取二进制日志的开始时间 
        mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003
      • 在时间点恢复中，用于截取二进制日志

    • –stop-datetime=datetime

      • 读取二进制日志的结束时间
      • 在时间点恢复中，用于截取二进制日志
  • 按位置筛选
    • –offset=N, -o N
      • 偏移量，跳过日志前面的 N 个条目
    • –start-position=N, -j N
      • 从位置 N 开始读取二进制日志
      • 该位置是日志文件中的一个字节点，而不是事件计数器。它需要指向事件的开始位置以生成有用的输出
      • 此选项适用于在命令行上命名的第一个日志文件
      • 在时间点恢复中，用于截取二进制日志
    • –stop-position=N
      • 读取二进制日志的中止位置
      • 在时间点恢复中，用于截取二进制日志
    • –to-last-log, -t
      • 不要在从 MySQL 服务器请求的二进制日志的结尾处停止，而要继续打印直到最后一个二进制日志的结尾
      • 如果将输出发送到同一 MySQL 服务器，则可能导致无限循环
      • 此选项需要 –read-from-remote-server 选项配合使用。

调试选项

  • –debug[=debug_options], -# [debug_options]

    MySQL 的二进制发行版不是以此选项构建的。

    • 编写调试日志。典型的 debug_options 字符串是 d:t:o,file_name。默认值为 d:t:o,/tmp/mysql.trace。仅当 MySQL 是使用 WITH_DEBUG 构建的时，此选项才可用
  • –debug-check

    MySQL 的二进制发行版不是以此选项构建的。

    • 程序退出时，打印一些调试信息。仅当 MySQL 是使用 WITH_DEBUG 构建的时，此选项才可用。
  • –debug-info

    MySQL 的二进制发行版不是以此选项构建的。

    • 程序退出时，打印调试信息以及内存和 CPU 使用情况统计信息。仅当 MySQL 是使用 WITH_DEBUG 构建的时，此选项才可用。
  • –verbose, -v
    • 详细模式，输出更多信息。
    • 重建行事件并将其显示为带注释的 SQL 语句，并在适用时带有表分区信息
    • 如果 binlog_rows_query_log_events=TRUE，两次给出此选项 (-vv 或 –verbose –verbose) ，则输出将包含指示列数据类型和某些元数据的注释，以及诸如行查询日志事件之类的信息日志事件
  • –force-if-open, -F
    • 读取二进制日志文件，即使它们已打开或未正确关闭
  • –force-read, -f
    • 使用此选项，如果 mysqlbinlog 读取了它无法识别的二进制日志事件，它将打印警告，忽略该事件，然后继续
    • 如果没有此选项，则 mysqlbinlog 如果读取到此类事件，则会停止
  • –idempotent
    • 在处理更新时使用幂等模式
    • 这会导致在处理更新时，在当前会话中抑制任何重复键或未找到键的错误
    • 每当需要将一个或多个二进制日志重播到可能不包含日志所引用的所有数据的 MySQL 服务器时，此选项就可能有用
    • 该选项的作用范围仅包括当前的 mysqlbinlog
      客户端和会话
  • –print-table-metadata
    • 从二进制日志中打印与表相关的元数据
    • 系统变量
      • binlog-row-metadata
        • 记录与表相关的元数据二进制文件的数量
  • –short-form, -s

    已弃用，将来会删除。

    • 仅显示日志中包含的语句，而没有任何其他信息或基于行的事件
    • 仅用于测试
  • –verify-binlog-checksum, -c
    • 验证二进制日志文件中的校验和

配置文件选项

  • –defaults-extra-file=file_name
    • 额外的配置文件。在全局配置文件之后，用户配置文件之前会读取此文件。
  • –defaults-file=file_name
    • 仅读取给定的配置文件 (.mylogin.cnf 例外，这个配置文件总是被读取)
  • –defaults-group-suffix=str
    • 选项组名称的后缀

      如果 –defaults-group-suffix=_other`，mysql 也会读取 [client_other] 和 [mysql_other]

  • –no-defaults

    • 不读取任何配置文件 ( .mylogin.cnf 例外)
  • –print-defaults
    • 打印输出程序名称和所有读取的配置文件内容

事务选项

  • –shared-memory-base-name=name
    • 在 Windows 上，共享内存名称，用于使用共享内存与本地服务器建立的连接。

二进制日志的操作

读取二进制日志

  • 读取方式
    • 读取本地二进制日志
    • 读取远程二进制日志
      • –read-from-remote-server + 连接选项
  • 输出中的注释字段 
    # at 141
#100309  9:28:36 server id 123  end_log_pos 245
  Query thread_id=3350  exec_time=11  error_code=0

    Note: 事件之前有提供其他信息的标题注释。

    • at
      • 表示事件的偏移量或开始位置
    • server id
      • 发生此事件的服务器 server_id
    • end_log_pos
      • 下一个事件的起始位置 (当前事件的结束位置 + 1)
    • thread_id
      • 执行此事件的进程 id
    • exec_time：
      • 在复制的源服务器上，表示执行事件所花费的时间
      • 在副本上，它是副本上结束执行时间减去源上开始执行时间的差。差异可作为复制滞后于源的指标。
    • error_code
      • 指示执行事件的结果。0表示没有错误。

重做二进制日志

可以使用 mysqlbinlog 重做二进制日志进行恢复。

如果 mysqlbinlog 产生的语句可能包含 BLOB 值，则当 mysql 处理它们时可能会引起问题。在这种情况下，请使用 –binary-mode 选项调用 mysql。

mysqlbinlog binlog.000001 | mysql -u root -p
mysqlbinlog binlog.[0-9]* | mysql -u root -p
  • 处理多个二进制日志文件
    • 安全的方法是，使用与服务器的单个连接来处理它们 
      # 错误示例
mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
# 正确示例
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
    • 另一种方法是，将所有日志写入单个文件，然后处理该文件 
      mysqlbinlog binlog.000001 >  /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"
  • 使用压缩 
    gzip -cd binlog-files_1.gz | ./mysqlbinlog - | ./mysql -uroot  -p
or
gzip -cd binlog-files_1.gz binlog-files_2.gz | ./mysqlbinlog - | ./mysql -uroot  -p
  • LOAD DATA 操作

    因为 mysqlbinlog 将 LOAD DATA 语句转换为 LOAD DATA LOCAL 语句 (添加了 LOCAL ) ，所以用于处理语句的客户端和服务器都必须配置为启用 LOCAL 功能。

    • 选项
      • –local-load
        • 指定 LOAD DATA LOCAL 的目录
    • 临时文件
      • 名称
        • original_file_name-＃-＃
      • 清理
        • 不再需要语句日志后，应自行删除临时文件。

          不会自动删除为 LOAD DATA LOCAL 语句创建的临时文件，因为在实际执行这些语句之前，将需要它们。

十六进制转储格式

mysqlbinlog --hexdump source-bin.000001

输出格式

/*!40019 SET @@SESSION.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051024 17:24:13 server id 1  end_log_pos 98
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 00000004 9d fc 5c 43   0f   01 00 00 00   5e 00 00 00   62 00 00 00   00 00
# 00000017 04 00 35 2e 30 2e 31 35  2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
# 00000027 6f 67 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |og..............|
# 00000037 00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |................|
# 00000047 00 00 00 00 9d fc 5c 43  13 38 0d 00 08 00 12 00 |.......C.8......|
# 00000057 04 04 04 04 12 00 00 4b  00 04 1a                |.......K...|
#       Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
#       at startup
ROLLBACK;

输出字段

  • Position

    • 日志文件中的字节位置
  • Timestamp
    • 事件的时间戳
  • Type
    • 事件类型代码
  • Master ID
    • 发生事件的源服务器的 ID
  • Size
    • 以字节为单位的事件的大小
  • Master Pos
    • 下个事件的位置
  • Flags
    • 事件标志值

行事件显示

行事件类型代码

  • WRITE_ROWS_EVENT
  • UPDATE_ROWS_EVENT
  • DELETE_ROWS_EVENT

系统变量

  • binlog_rows_query_log_events=TRUE
    • 指定两次 –verbose 或 -v，以同时显示每列的数据类型和一些元数据，以及信息性日志事件，例如，行查询日志事件。

影响行事件输出的选项

  • –verbose，-v
    • 此输出级别还显示适用的表分区信息
    • 输出包含以 ### 开头的行
  • –base64-output = DECODE-ROWS
    • 对行事件取消 BINLOG 语句
    • 类似于 –base64-output = NEVER，但是如果发现行事件，则不会退出并显示错误
    • 可以和 –verbose 的配合使用
      • –verbose 为行事件生成的 SQL 语句比相应的 BINLOG 语句更具可读性
      • 但是，它们与生成事件的原始 SQL 语句不完全对应
        • 原始列名将丢失，并由 @N 代替，其中 N 是列号
        • 字符集信息在二进制日志中不可用，这会影响字符串列的显示
    • 如果打算重新执行 mysqlbinlog 输出，则不应禁止 BINLOG 语句

示例

默认情况下，mysqlbinlog 使用 BINLOG 语句显示编码为 base-64 字符串的行事件。

  • 原本语句 
    CREATE TABLE t
(
  id   INT NOT NULL,
  name VARCHAR(20) NOT NULL,
  date DATE NULL
) ENGINE = InnoDB;

START TRANSACTION;
INSERT INTO t VALUES(1, 'apple', NULL);
UPDATE t SET name = 'pear', date = '2009-01-01' WHERE id = 1;
DELETE FROM t WHERE id = 1;
COMMIT;
  • 二进制事件输出 
    shell> mysqlbinlog log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
  • 以伪 SQL 语句的形式将行事件视为注释 
    shell> mysqlbinlog -v log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
###   @1=1
###   @2='apple'
###   @3=NULL
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
###   @1=1
###   @2='apple'
###   @3=NULL
### SET
###   @1=1
###   @2='pear'
###   @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
###   @1=1
###   @2='pear'
###   @3='2009:01:01'
  • 在每个列更改之后包含一个附加注释 
    shell> mysqlbinlog -vv log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
  • 将行事件仅作为 SQL 语句查看 
    shell> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F
### INSERT INTO test.t
### SET
###   @1=1
###   @2='apple'
###   @3=NULL
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F
### UPDATE test.t
### WHERE
###   @1=1
###   @2='apple'
###   @3=NULL
### SET
###   @1=1
###   @2='pear'
###   @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F
### DELETE FROM test.t
### WHERE
###   @1=1
###   @2='pear'
###   @3='2009:01:01'

文件头信息

正确解释行事件需要二进制日志开头的格式描述事件中的信息。

  • 由于 mysqlbinlog 事先不知道日志的其余部分是否包含行事件，因此默认情况下，它将在输出的初始部分使用 BINLOG 语句显示格式描述事件
  • 如果已知二进制日志不包含任何需要 BINLOG 语句的事件 (即，没有行事件) ，则可以使用 –base64-output = NEVER 选项阻止写入此标头。

备份二进制日志

mysqlbinlog 备份功能

  • 支持静态备份和实时备份
  • 可以备份加密和未加密的二进制日志文件

备份选项

  • –read-from-remote-server, -R
    • 连接远程服务器并请求二进制日志
  • –raw
    • 将二进制日志以原始格式输出，而不是文本格式
    • 可以配合使用的选项
      • –stop-never
        • 保持连接
      • –connection-server-id=id
        • 连接的服务器 ID
      • –result-file
        • 输出文件的前缀

静态和实时备份

  • 查看当前二进制日志文件名 
    mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000130 |     27459 | No        |
| binlog.000131 |     13719 | No        |
| binlog.000132 |     43268 | No        |
+---------------+-----------+-----------+
  • 静态备份 
    mysqlbinlog --read-from-remote-server --host=host_name --raw
  binlog.000130 binlog.000131 binlog.000132

mysqlbinlog --read-from-remote-server --host=host_name --raw
  --to-last-log binlog.000130
  • 实时备份 
    mysqlbinlog --read-from-remote-server --host=host_name --raw
  --stop-never binlog.000130

输出文件命名

  • 不使用 –raw 选项，输出为文本格式

  • 使用 –raw 选项，输出为二进制格式

    –result-file Option Output File Names
    –result-file=x xbinlog.000999 and up
    –result-file=/tmp/ /tmp/binlog.000999 and up
    –result-file=/tmp/x /tmp/xbinlog.000999 and up

使用 mysqldump + mysqlbinlog 进行备份和还原

  • 使用 mysqlbinlog 进行实时备份 
    mysqlbinlog --read-from-remote-server --host=host_name --raw
  --stop-never binlog.000999
  • 使用 mysqldump 做一个全备快照，定期执行 
    mysqldump --host=host_name --all-databases --events --routines --master-data=2> dump_file
  • 如果遇到数据丢失故障，使用最近一次的全备恢复数据 
    mysql --host=host_name -u root -p < dump_file
  • 查看二进制日志备份的开始位置 
    -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001002', MASTER_LOG_POS=27284;
  • 截取二进制日志，重做事件 
    mysqlbinlog --start-position=27284 binlog.001002 binlog.001003 binlog.001004
  | mysql --host=host_name -u root -p

mysqlbinlog 备份的限制

  • 如果连接断开不会自动重连
  • 备份的延迟类似于副本服务器的延迟

指定服务器 ID

从服务器请求日志文件

  • 指定一组明确命名的文件
  • 指定开始文件和 –to-last-log
  • 指定开始文件和 –stop-never (这意味着 –to-last-log)

选项

  • –read-from-remote-server
    • mysqlbinlog 使用服务器 ID 0 连接，该 ID 告诉服务器在发送最后一个请求的日志文件后断开连接
  • –stop-never
    • 和 –read-from-remote-server 配合使用，mysqlbinlog 使用非零服务器 ID 连接，因此服务器在发送最后一个日志文件后不会断开连接。
  • –connection-server-id
    • 指定连接的服务器 ID。默认1。
发表于：MySQL
2021-04-21
# MySQL# mysqlbinlog# Programs# 管理和实用程序
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-04-21发表，共计15599字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
安装 MySQL
perror-显示错误信息
mysqldump-数据库备份程序
安装和卸载插件
评论（没有评论）