二进制日志备份与恢复

二进制备份是 MySQL 数据库备份的一种方式，它通过记录数据库的所有更改操作，以二进制格式保存，实现对数据库的增量备份和恢复。binlog_format 是 MySQL 中用来指定二进制日志格式的参数，有三种常见的选项：STATEMENT、ROW 和 MIXED。不同的选项会影响到二进制日志的记录方式和内容。下面详细解释二进制备份以及不同 binlog_format 选项的区别：

1. 二进制备份

定义：

• 二进制备份是指备份 MySQL 数据库的二进制日志文件，这些日志记录了数据库发生的所有更改操作。
• 二进制备份通常用于增量备份和恢复，只备份从上次备份或上次差异备份以来发生的数据变更，节省了备份时间和存储空间。

操作步骤：

1. 启用二进制日志功能。
2. 使用 mysqlbinlog 命令将二进制日志文件导出到文本文件。
3. 在需要恢复数据时，使用 mysqlbinlog 命令将备份的二进制日志文件应用到数据库中。

2. binlog_format 不同选项的区别

在 MySQL 中，binlog_format 参数用来指定二进制日志的格式，有三种常见的选项：STATEMENT、ROW 和 MIXED。不同的选项会影响到二进制日志记录的方式和内容。

1. STATEMENT

• 说明： binlog_format=STATEMENT 表示二进制日志以 SQL 语句的形式记录。
• 特点：
  • 记录了执行的 SQL 语句，比如 INSERT、UPDATE、DELETE 等。
  • 适用于非常复杂的 SQL 语句，或者需要在不同数据库间进行复制时。
• 优点：
  • 日志文件相对较小。
  • 可读性较好，便于查看和分析。
• 缺点：
  • 在某些情况下可能会导致数据不一致的问题，例如使用了不确定的函数或变量。
  • 在使用不支持的语法时可能会出现错误。

2. ROW

• 说明： binlog_format=ROW 表示二进制日志以行的形式记录。
• 特点：
  • 记录了数据行的变化，即记录了被修改的行的内容。
  • 适用于大部分的数据更改场景，对于一些复杂的语句也能够保证正确性。
• 优点：
  • 数据更改的具体内容被记录，恢复时更加准确。
  • 不会受到 SQL 语句语法的影响。
• 缺点：
  • 日志文件相对较大，占用存储空间较多。
  • 在某些情况下可能会导致主从复制的延迟。

3. MIXED

• 说明： binlog_format=MIXED 表示二进制日志同时采用了 STATEMENT 和 ROW 两种方式。
• 特点：
  • 在不同情况下自动选择使用 STATEMENT 或 ROW 方式记录。
  • 对于某些语句使用 STATEMENT，对于某些语句使用 ROW。
• 优点：
  • 综合了 STATEMENT 和 ROW 的优点，适用范围更广。
  • 可以在不同场景下灵活选择合适的记录方式。
• 缺点：
  • 日志文件大小和性能可能存在波动。

3. 如何选择 binlog_format 选项

• STATEMENT： 适用于不涉及函数、变量等复杂语法的场景，日志文件相对较小，便于查看和分析。
• ROW： 适用于大部分的数据更改场景，对于需要准确恢复数据的场景较为合适，但日志文件会相对较大。
• MIXED： 综合了 STATEMENT 和 ROW 的优点，适用范围更广，可以在不同场景下灵活选择合适的记录方式，是推荐使用的方式。

示例：

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001 

这是使用 mysqlbinlog 命令查看 /usr/local/mysql/data/mysql-bin.000001 文件的部分输出结果，并且使用了一些参数：

• --no-defaults:  默认字符集，防止出现UTF-8报错。
• --base64-output=decode-rows: 表示将行事件的内容使用 base64 解码输出。
• -v: 表示显示详细的日志内容。

这个命令的目的是查看二进制日志文件的详细内容，并将行事件的内容进行 base64 解码后显示，以便更容易阅读和理解。

对数据库进行操作以增加日志记录

删除class2后回滚

 

验证：

mysqlbinlog --no-defaults --start-position='388' --stop-position='1052' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123456

--start-position='388'  表示从位置388开始

--stop-position='1052'  表示到位置1052结束