likes
comments
collection
share

Mysql之查看和使用Binary Log

作者站长头像
站长
· 阅读数 13

前言

查看可以mysql命令和mysqlbinlog命令下面都会介绍到。 恢复使用mysqlbinlog命令。

SHOW BINLOG EVENTS语句

官方文档。

SHOW BINLOG EVENTS 
    [IN '*log_name*'] 
    [FROM *pos*] 
    [LIMIT [*offset*,] *row_count*]

显示二进制日志中的事件。如果不指定'log_name'，则显示第一个二进制日志。SHOW BINLOG EVENTS需要REPLICATION SLAVE权限。

该LIMIT子句与SELECT语句具有相同的语法。请参阅 第 13.2.10 节，“SELECT 语句”。

发出没有LIMIT的SHOW BINLOG EVENTS子句可能会启动一个非常耗时和耗费资源的过程，因为服务器会向客户端返回二进制日志的完整内容（包括服务器执行的所有修改数据的语句）。作为SHOW BINLOG EVENTS的替代方法，使用 mysqlbinlog实用程序将二进制日志保存到文本文件以供以后检查和分析。请参阅 第 4.6.9 节，“mysqlbinlog - 处理二进制日志文件的实用程序”。

小结

所以不建议直接使用show binlog events，一般会直接使用mysqlbinlog。

mysqlbinlog

官方文档

服务器的二进制日志由包含 “events”的文件组成，这些“events”描述了对数据库内容的修改。要以文本格式显示它们的内容，请使用 mysqlbinlog实用程序。您还可以使用 mysqlbinlog在复制设置中显示由副本服务器写入的中继日志文件的内容，因为中继日志与二进制日志具有相同的格式。二进制日志和中继日志在 第 5.4.4 节，“二进制日志”和 第 17.2.4 节，“中继日志和复制元数据存储库”中进一步讨论。

像这样调用mysqlbinlog：

mysqlbinlog [*options*] *log_file* ...

例如，要显示名为binlog.000003的二进制日志文件的内容，请使用以下命令：

mysqlbinlog binlog.0000003

输出 binlog.000003中的所有事件。对于基于语句的日志记录，事件信息包括 SQL 语句、执行它的服务器的 ID、执行语句时的时间戳、花费的时间等等。对于基于行的日志记录，事件指示行更改而不是 SQL 语句。有关日志记录模式的信息 ，请参阅第 17.2.1 节，“复制格式”。

事件前面有提供附加信息的标题注释。例如：

# at 141 
#100309 9:28:36 server id 123 end_log_pos 245 
    Query thread_id=3350 exec_time=11 error_code=0

在第一行中，后面的数字at 表示事件在二进制日志文件中的文件偏移量或起始位置。

第二行以日期和时间开头，指示语句何时在事件起源的服务器上开始。对于复制，此时间戳会传播到副本服务器。server id是 server_id事件发起的服务器的值。在副本上，它是副本上的结束执行时间减去源上的开始执行时间的差。差异可作为复制落后于源多少的指标。 error_code表示执行事件的结果。零表示没有发生错误。

注意 使用事件组时，可以将事件的文件偏移量归为一组，将事件的注释归为一组。不要将这些分组事件误认为是空白文件偏移。有关其他使用示例，请参阅本节后面和第 7.5 节“时间点（增量）恢复”中的讨论。

可以重新执行mysqlbinlog 的输出以重做日志中的语句。这对于服务器意外退出后的恢复操作很有用。使用mysqlbinlog执行BINLOG 内部使用语句，用户需要BINLOG_ADMIN特权（或者弃用SUPER特权），或REPLICATION_APPLIER特权加上适当的特权才能执行每个日志事件。

读者小结

简单的介绍了下mysqlbinlog以及它的简单查看使用和展示的格式，其中server id可以避免主从来回切换时重复执行自己的语句。还需要注意的是mysqlbinlog用于恢复的时候需要有相应权限。

时间点（增量）恢复

官方文档

时间点恢复是指恢复到给定时间点的数据更改。通常，这种类型的恢复是在还原完整备份后执行的，该备份使服务器恢复到创建备份时的状态。（可以通过多种方式进行完整备份，例如 第 7.2 节“数据库备份方法”中列出的那些。）时间点恢复然后使服务器从完整备份时间增量更新到更最近的时间。

使用二进制时间点恢复

本节介绍使用二进制日志执行时间点恢复的一般思路。下一节， 第 7.5.2 节，“使用事件位置的时间点恢复”，将通过示例详细说明该操作。

注意 本节和下一节中的许多示例都使用 mysql客户端来处理由mysqlbinlog生成的二进制日志输出。如果您的二进制日志包含\0 (null)字符，则mysql无法解析该输出，除非您使用--binary-mode选项调用它。

时间点恢复的信息来源是在完全备份操作之后生成的二进制日志文件集。因此，要允许服务器恢复到某个时间点，必须在其上启用二进制日志记录，这是 MySQL 8.0 的默认设置（请参阅第 5.4.4 节，“二进制日志”）。

要从二进制日志中恢复数据，您必须知道当前二进制日志文件的名称和位置。默认情况下，服务器会在数据目录中创建二进制日志文件，但可以使用 --log-bin选项指定路径名以将文件放置在不同的位置。要查看所有二进制日志文件的列表，请使用以下语句：

mysql> SHOW BINARY LOGS;

要确定当前二进制日志文件的名称，请发出以下语句：

mysql> SHOW MASTER STATUS;

mysqlbinlog实用程序将二进制日志文件中的事件从二进制格式转换为文本，以便可以查看或应用它们。mysqlbinlog具有用于根据事件时间或事件在日志中的位置选择二进制日志部分的选项。请参阅 第 4.6.9 节，“mysqlbinlog - 处理二进制日志文件的实用程序”。

应用二进制日志中的事件会导致它们所代表的数据修改被重新执行。这可以在给定的时间跨度内恢复数据更改。要应用二进制日志中的事件，使用mysql客户端处理mysqlbinlog输出：

$> mysqlbinlog *binlog_files* | mysql -u root -p

如果二进制日志文件已经加密，这可以从 MySQL 8.0.14 开始，mysqlbinlog不能像上面的例子那样直接读取它们，但可以使用 --read-from-remote-server ( -R) 选项从服务器读取它们。例如：

$> mysqlbinlog --read-from-remote-server --host=*host_name* --port=3306 --user=root --password --ssl-mode=required *binlog_files* | mysql -u root -p

在这里，该选项--ssl-mode=required用于确保二进制日志文件中的数据在传输过程中受到保护，因为它以未加密的格式发送到 mysqlbinlog。

当您需要确定事件时间或位置以在执行事件之前选择部分日志内容时，查看日志内容会很有用。要查看日志中的事件，请将 mysqlbinlog输出发送到分页程序：

$> mysqlbinlog *binlog_files* | more

或者，将输出保存在文件中并在文本编辑器中查看文件：

$> mysqlbinlog *binlog_files* > tmpfile 
$> ... *edit tmpfile* ...

编辑文件后，应用如下内容：

$> mysql -u root -p < tmpfile

如果您在 MySQL 服务器上应用了多个二进制日志，请使用单个连接来应用您要处理的所有二进制日志文件的内容。这是一种方法：

$> 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"

读者小结

本节主要介绍，如何通过命令查看二进制日志和当前状态，以及简单的进行恢复命令。

使用事件位置的时间点恢复

例如，假设 2020 年 3 月 11 日 20:06:00 左右，执行了删除表的 SQL 语句。您可以执行时间点恢复以将服务器恢复到表删除之前的状态。这些是实现这一目标的一些示例步骤：

  1. 还原在感兴趣的时间点之前创建的最后一个完整备份（称为 tp，在我们的示例中为 2020 年 3 月 11 日 20:06:00）。完成后，记下您已将服务器恢复到的二进制日志位置以供以后使用，然后重新启动服务器。

注意 虽然在还原和服务器重新启动后 InnoDB 也会显示最后恢复的二进制日志位置，但这不是获取还原的结束日志位置的可靠方法，因为在显示位置反映的时间之后，可能会发生DDL事件和非InnoDB更改。您的备份和恢复工具应该为您提供恢复的最后一个二进制日志位置：例如，如果您使用mysqlbinlog 进行任务，检查二进制日志重放的停止位置；如果您使用的是 MySQL Enterprise Backup，则最后一个二进制日志位置已保存在您的备份中。请参阅 时间点恢复。

  1. 找到与您要将数据库还原到的时间点相对应的精确二进制日志事件位置。在我们的例子中，假设我们知道删除表的大致时间(tp)，我们可以通过使用mysqlbinlog实用程序检查该时间前后的日志内容来找到日志位置。使用--start-datetime和--stop-datetime选项指定围绕tp的较短时间段 ，然后在输出中查找事件。例如：
$> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \
                   --stop-datetime="2020-03-11 20:08:00" --verbose \
         /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"
 
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 232
#200311 20:06:20 server id 1  end_log_pos 355 CRC32 0x2fc1e5ea 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1583971580/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
DROP TABLE `pets`.`cats` /* generated by server */
/*!*/;
# at 355
#200311 20:07:48 server id 1  end_log_pos 434 CRC32 0x123d65df 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1583971668462467	immediate_commit_timestamp=1583971668462467	transaction_length=473
# original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
# immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
/*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 434
#200311 20:07:48 server id 1  end_log_pos 828 CRC32 0x57fac9ac 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 217
use `pets`/*!*/;
SET TIMESTAMP=1583971668/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE dogs

从mysqlbinlog的输出中，DROP TABLE `pets`.`cats`可以发现在二进制日志片段中的# at 232和# at 355之间，这意味着该语句发生 在日志位置 232 之后，日志位置 335在DROP TABLE语句之后。

注意 仅使用 --start-datetime和 --stop-datetime 选项来帮助您找到感兴趣的实际事件位置。不建议使用这两个选项来指定要应用的二进制日志段的范围：使用这些选项时丢失二进制日志事件的风险更高。使用 --start-positionand --stop-position 代替。

  1. 将二进制日志文件中的事件应用到服务器，从您在第 1 步中找到的日志位置开始（假设它是 155），并在您在第 2 步中找到的位置结束，即您感兴趣的时间点之前（这是232）
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
         | mysql -u root -p

该命令恢复从起始位置直到停止位置之前的所有事务。因为mysqlbinlog的输出在记录每个SQL语句之前都包含SET TIMESTAMP语句，因此恢复的数据和相关的 MySQL 日志反映了事务执行的原始时间。（所以不用担心sql中出现now()之类的函数）

您的数据库现在已恢复到感兴趣的时间点，tp，就在表tp``pets.cats被删除之前。

  1. 除了已经完成的时间点恢复之外，如果您还想重新执行 您感兴趣的时间点之后的所有语句，请再次使用mysqlbinlog将所有事件应用到服务器。我们在步骤 2 中注意到，在我们想要跳过的语句之后，日志位于位置 355；我们可以将它用于--start-position选项，以便包含该位置之后的任何语句：
$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \
         | mysql -u root -p

您的数据库已恢复二进制日志文件中记录的最新语句，但已跳过所选事件。

读者小结

此节展示了具体的删除恢复例子，使用 --start-datetime和 --stop-datetime 进行初略定位，找到误删除前后位置，再根据位置进行恢复跳过所选时间。

不同Binary Log格式的展示

下面展示下insert into test (name) values ('你是');插入语句的Binary Log。

直接使用SHOW MASTER STATUS;语句查询最近的binlog是哪一个，使用mysqlbinlog（加上--verbose, -v可以查看更详细的信息）。

Mysql 8.0默认的binlog_format为row，想查看不同的修改变量值即可。

row格式

# at 831
#220214  0:20:28 server id 1  end_log_pos 909 CRC32 0x8139b6cc 	Query	thread_id=8	exec_time=0	error_code=0
SET TIMESTAMP=1644769228/*!*/;
BEGIN
/*!*/;
# at 909
#220214  0:20:28 server id 1  end_log_pos 970 CRC32 0x88895721 	Table_map: `my_test`.`test` mapped to number 94
# at 970
#220214  0:20:28 server id 1  end_log_pos 1021 CRC32 0x5cb5c581 	Write_rows: table id 94 flags: STMT_END_F

BINLOG '
zC8JYhMBAAAAPQAAAMoDAAAAAF4AAAAAAAEAB215X3Rlc3QABHRlc3QAAgj+Av6AAAEBgAID/P8A
IVeJiA==
zC8JYh4BAAAAMwAAAP0DAAAAAF4AAAAAAAEAAgAC/wABAAAAAAAAAAbkvaDmmK+BxbVc
'/*!*/;
# at 1021
#220214  0:20:28 server id 1  end_log_pos 1052 CRC32 0x6c0bc906 	Xid = 27
COMMIT/*!*/;

statment格式

# at 1131
#220214  0:35:54 server id 1  end_log_pos 1219 CRC32 0xed38bc8d 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1644770154/*!*/;
BEGIN
/*!*/;
# at 1219
# at 1251
#220214  0:35:54 server id 1  end_log_pos 1251 CRC32 0x904331cd 	Intvar
SET INSERT_ID=2/*!*/;
#220214  0:35:54 server id 1  end_log_pos 1379 CRC32 0x874143f7 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1644770154/*!*/;
insert into `test` (`name`) values ('你是')
/*!*/;
# at 1379
#220214  0:35:54 server id 1  end_log_pos 1410 CRC32 0x51637eec 	Xid = 43
COMMIT/*!*/;

mixed格式

就是mysql根据语句进行选择是statement还是row，是statement和row的混合。