15 个 MySQL 二进制日志文件的 mysqlbinlog 命令示例

举报
Tiamo_T 发表于 2021/11/25 21:54:56 2021/11/25
【摘要】 在 MySQL 或 MariaDB 中，无论何时对数据库进行更改，都会记录该特定事件。 例如，当您创建新表或更新现有表上的数据时，这些事件将存储在二进制日志中。

在 MySQL 或 MariaDB 中，无论何时对数据库进行更改，都会记录该特定事件。

例如，当您创建新表或更新现有表上的数据时，这些事件将存储在二进制日志中。

二进制日志在 MySQL 复制中非常有用，主服务器会将二进制日志中的数据发送到远程服务器。

当您在 MySQL 中执行任何类型的恢复操作时，您还将处理二进制日志文件。

mysqlbinlog 命令用于以可读的用户友好格式查看二进制日志的内容。您还将使用 mysqlbinlog 命令读取内容并将其通过管道传输到其他 mysql 实用程序。

在本教程中，我们将使用 mysqlbinlog 命令讨论以下示例：

  1. 获取当前二进制日志的列表
  2. Mysqlbinlog 默认行为
  3. 获取特定数据库的条目
  4. 禁用二进制日志进行恢复
  5. 在输出中控制 base-64 BINLOG
  6. mysqlbinlog 输出中的调试消息
  7. 跳过前 N 个条目
  8. 将输出保存到文件
  9. 从特定位置开始提取条目
  10. 将条目提取到特定位置
  11. 清除二进制日志输出的刷新日志
  12. 在输出中仅显示 SQL 查询
  13. 查看从特定时间开始的条目
  14. 查看特定时间之前的条目
  15. 从远程服务器获取二进制日志

1. 获取当前二进制日志列表

从 mysql，执行以下 show binary logs 命令，它将显示系统中的所有二进制日志。

mysql> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| mysqld-bin.000001 |     15740 |
| mysqld-bin.000002 |      3319 |
..
..

如果您的系统没有启用二进制日志记录，那么您将看到以下错误消息。

mysql> SHOW BINARY LOGS;
ERROR 1381 (HY000): You are not using binary logging

默认情况下，二进制日志文件位于 /var/lib/mysql 目录下，如下所示。

# ls -l /var/lib/mysql/
-rw-rw----. 1 mysql mysql 15740 Aug 16 14:57 mysqld-bin.000001
-rw-rw----. 1 mysql mysql  3319 Aug 16 14:57 mysqld-bin.000002
..
..

2. mysqlbinlog 默认行为

下面将以用户友好的格式显示指定的 mysql 二进制日志文件（例如：mysqld-bin.000001）的内容。

# mysqlbinlog mysqld-bin.000001

以下是上述命令的部分输出：

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170726 14:57:37 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.73-log created 170726 14:57:37 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 14:59:31 server id 1  end_log_pos 182   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1501095571/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
..
..
..
# at 14191
#170726 15:20:38 server id 1  end_log_pos 14311         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1501096838/*!*/;
insert into salary(name,dept) values('Ritu', 'Accounting')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

上面的命令将显示该系统上所有数据库上发生的事件。

注意：当你执行mysqlbinlog 命令时，你有时可能会得到这个错误信息“unknown variable default-character-set=utf8”，在这种情况下，使用mysqlbinlog 中的–set-charset 选项来设置合适的字符集。另一种选择是将“loose-default-character-set=utf8”添加到 [client] 部分下的 /etc/my.cnf 文件中。

如果您不熟悉 MySQL，这将有所帮助：在 Linux 上安装和配置 MariaDB MySQL 的 6 个步骤

3. 获取特定数据库的条目

mysqlbinlog 的默认输出可能会让人不知所措，因为它将显示来自所有语句的大量数据。

使用 -d 选项，您还可以指定数据库名称，该名称将显示在给定数据库上发生的事件上。

以下命令将所有属于“crm”数据库的事件转储到 crm-events.txt 文件中

mysqlbinlog -d crm mysqld-bin.000001 > crm-events.txt

除了 -d 选项，您还可以使用 –database 选项，如下所示。

mysqlbinlog --database crm mysqld-bin.000001 > crm-events.txt

4. 禁用二进制日志进行恢复

当您使用 mysqlbinlog 从数据库崩溃中恢复时，您不希望恢复过程创建二进制日志。如果是这样，那么您将处于循环中，您将继续进行恢复，因为恢复本身会生成新的二进制日志文件。

因此，要在使用 mysqlbinlog 命令时禁用二进制日志，请使用 -D 选项，如下所示：

mysqlbinlog -D mysqld-bin.000001

您还可以使用 –disable-log-bin，如下所示。以下与上述命令完全相同。

mysqlbinlog --disable-log-bin mysqld-bin.000001

注意：在输出中，当您指定 -D 选项时，您将在输出中看到额外的第二行。即表示 SQL_LOG_BIN=0 的那个。

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

当您使用 –to-last-log 选项时，此选项也将很有帮助。另外，请记住，您需要 root 权限才能执行此命令。

对于备份和恢复，您通常会使用mysqldump 命令，但有时在您必须从崩溃中恢复的情况下，mysqlbinlog 会有所帮助。

5. 在输出中控制 base-64 BINLOG

使用 base64-output 选项，您可以控制何时输出语句应该是 base64 编码的 BINLOG 语句的行为。

以下是 base64-output 的可能值：

  • never
  • always
  • decode-rows
  • auto (this is default)

never: 当您指定“从不”时，如下所示，这将在输出中显示 base64 编码的 BINLOG 语句。

mysqlbinlog --base64-output=never mysqld-bin.000001

即，当您使用“never”时，在 mysqlbinlog 命令的输出中，您将不会出现任何类似于以下具有 base64 编码 BINLOG 的行。

BINLOG '
IeZ4WQ8BAAAAZgAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

请注意，当二进制日志文件不包含基于行的事件时，base64 输出的上述“从不”选项将适用于二进制日志文件。

always：当您指定“always”选项时，这将尽可能只显示 BINLOG 条目。因此，仅当您专门调试某些问题时才使用它。

mysqlbinlog --base64-output=always mysqld-bin.000001

以下是上面带有“always”的输出，它只显示了 BINLOG 条目。

BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 14:59:31 server id 1  end_log_pos 182
BINLOG '
k+Z4WQIBAAAATAAAALYAAAAIAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBCRUdJTg==
'/*!*/;
# at 182
#170726 14:59:30 server id 1  end_log_pos 291
BINLOG '
kuZ4WQIBAAAAbQAAACMBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBJTlNFUlQgSU5UTyB0IFZBTFVFUygxLCAnYXBwbGUnLCBOVUxMKQ==
'/*!*/;
# at 291
#170726 14:59:30 server id 1  end_log_pos 422
BINLOG '
kuZ4WQIBAAAAgwAAAKYBAAAAAAIAAAAAAAAADAAAGgAAAEAAAAEAAAAAAAAAAAYDc3RkBAgACAAI
AHRoZWdlZWtzdHVmZgBVUERBVEUgdCBTRVQgbmFtZSA9ICdwZWFyJywgZGF0ZSA9ICcyMDA5LTAx
LTAxJyBXSEVSRSBpZCA9IDE=

decode-rows：此选项将基于行的事件解码为注释的 SQL 语句，特别是当您同时指定 –verbose 选项时，如下所示。

mysqlbinlog --base64-output=decode-rows --verbose mysqld-bin.000001

auto：这是默认选项。当您不指定任何 base64-decode 选项时，它将使用 auto。在这种情况下，mysqlbinlog 将仅为某些事件类型（如基于行的事件和格式描述事件）打印 BINLOG 条目。

以下两种说法完全相同。

mysqlbinlog --base64-output=auto mysqld-bin.000001

mysqlbinlog mysqld-bin.000001

6. mysqlbinlog 输出中的调试消息

以下调试检查选项将在完成处理给定的二进制日志文件后检查打开的文件和内存使用情况。

mysqlbinlog --debug-check mysqld-bin.000001

以下 debug-info 选项将在完成处理给定的二进制日志文件后显示其他调试选项，如下所示。

# mysqlbinlog --debug-info mysqld-bin.000001 > /tmp/m.di

User time 0.00, System time 0.00
Maximum resident set size 2848, Integral resident set size 0
Non-physical pagefaults 863, Physical pagefaults 0, Swaps 0
Blocks in 0 out 48, Messages in 0 out 0, Signals 0
Voluntary context switches 1, Involuntary context switches 2

7. 跳过前 N 个条目

除了读取整个 mysql 二进制日志文件，您还可以通过指定偏移量仅读取其中的某些部分。

为此，请使用 -o 选项。-o 代表偏移。

以下将跳过指定 mysql bin 日志中的前 10 个条目。

mysqlbinlog -o 10 mysqld-bin.000001

为了确保它正常工作，请为偏移量提供一个拥抱编号，并且您将看不到任何条目。以下内容将跳过日志中的前 10,000 个条目（事件）。

在此示例中，由于此特定日志文件没有 10,000 个条目，因此它不会在输出中显示任何数据库事件。

# mysqlbinlog -o 10000 mysqld-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
..
..
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

8. 将输出保存到文件

您可以使用简单的 Linux 重定向命令 > 并将输出存储到一个文件中，如下所示。

mysqlbinlog mysqld-bin.000001 > output.log

或者，您可以使用 -r（结果文件）选项，如下所示将输出存储在文件中。请注意 -r 和 –result-file 是相同的。

mysqlbinlog -r output.log mysqld-bin.000001

注意：您还可以指定 –server-id 并提取由具有给定服务器 ID 的 mysql 服务器生成的日志条目。

mysqlbinlog --server-id=1 -r output.log mysqld-bin.000001

9.从特定位置开始提取条目

通常在 mysql 二进制日志文件中，您会看到如下所示的位置编号。下面是mysqlbinlog的部分输出，你看到“15028”是一个位置号。

#170726 15:38:14 server id 1  end_log_pos 15028         Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1501097894/*!*/;
insert into salary values(400,'Nisha','Marketing',9500)
/*!*/;
# at 15028
#170726 15:38:14 server id 1  end_log_pos 15146         Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1501097894/*!*/;
insert into salary values(500,'Randy','Technology',6000)

以下命令将开始读取位置编号为 15028 的二进制日志条目。

mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out

当您在命令行中指定多个二进制日志文件时，起始位置将仅应用于给定列表中的第一个二进制日志。

您可以使用 -H 选项获取给定二进制日志文件的十六进制转储，如下所示。

mysqlbinlog -H mysqld-bin.000001 > binlog-hex-dump.out

10. 将条目提取到特定位置

就像前面的例子一样，你也可以从 mysql 二进制日志中读取条目到特定位置，如下所示。

mysqlbinlog --stop-position=15028 mysqld-bin.000001 > upto-15028.out

上面的例子将从 binlog 准确地停止在位置 15028。当您在命令行中指定多个二进制日志文件时，停止位置将仅应用于给定列表中的最后一个二进制日志。

11. 清除二进制日志输出的刷新日志

当二进制日志未正确关闭时，您将在输出中看到一条警告消息，如下所示。

# mysqlbinlog mysqld-bin.000001 > output.log

正如你在这里看到的，它说 binlog 文件没有正确关闭。

# head output.log
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
..
..
# Warning: this binlog is either in use or was not closed properly.
..
..
..
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

当你看到它时，然后连接到 mysql 并刷新日志，如下所示。

mysql> flush logs;

一旦您刷新日志，并再次执行您的 mysqlbinlog 命令，您将不会在 mysqlbinlog 输出中看到 binlog 未正确关闭警告消息。

12. 在输出中只显示 SQL 查询

默认情况下，正如您在前面示例的输出中看到的那样，除了 SQL 查询之外，您还将在 mysqlbinlog 输出中看到一些附加信息。

如果您只需要常规 SQL 查询而不需要其他任何内容，请使用 -s 选项，如下所示。

-s 这里代表缩写形式。您还可以使用 –short-form 选项。以下两个示例完全相同。

mysqlbinlog -s mysqld-bin.000001 

mysqlbinlog -short-form mysqld-bin.000001

以下是上述命令的部分输出。正如您在此处看到的，它仅显示来自给定二进制日志文件的 SQL 查询。

SET TIMESTAMP=1501096106/*!*/;
insert into employee values(400,'Nisha','Marketing',9500)
/*!*/;
SET TIMESTAMP=1501096106/*!*/;
insert into employee values(500,'Randy','Technology',6000)
..
..
..

您不会在简短格式中看到如下内容：

# at 1201
#170726 15:08:26 server id 1  end_log_pos 1329  Query   thread_id=3     exec_time=0     error_code=0

13.查看从特定时间开始的条目

以下将仅提取从指定时间开始的条目。在此之前的任何条目都将被忽略。

mysqlbinlog --start-datetime="2017-08-16 15:00:00" mysqld-bin.000001

当您只想从特定时间范围内从二进制文件中提取数据以使用它来恢复或重建该时间段内发生的某些数据库活动时，这非常有用。

时间戳的格式可以是 MYSQL 服务器 DATETIME 和 TIMESTAMP 类型可以理解的任何格式。所以，你在这里有很大的灵活性。

14.查看特定时间的条目

就像前面的开始时间示例一样，您还可以指定停止时间，如下所示。

mysqlbinlog --stop-datetime="2017-08-16 15:00:00" mysqld-bin.000001

上面的命令将读取到给定停止时间的条目。将不会处理 mysql 二进制日志文件中超出给定停止时间的任何条目。

15. 从远程服务器获取二进制日志

从您的本地机器，您还可以读取位于远程服务器中的 mysql 二进制日志。

为此，您需要为远程服务器指定 IP 地址、用户名和密码，如下所述。

为此使用 -R 选项。-R 选项与 –read-from-remote–server 相同。

mysqlbinlog -R -h 192.168.101.2 -p mysqld-bin.000001

在上面：

  • -R 选项指示 mysqlbinlog 命令从远程服务器读取日志文件
  • -h 指定远程服务器的 IP 地址
  • -p 这将提示您输入密码。默认情况下，它将使用“root”作为用户名。您还可以使用 -u 选项指定用户名。
  • mysqld-bin.000001 这是我们在这里读取的来自远程服务器的二进制日志文件的名称。

下面的命令和上面的命令完全一样：

mysqlbinlog --read-from-remote-server --host=192.168.101.2 -p mysqld-bin.000001

以下是上述命令的部分输出：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170726 13:57:37 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.73-log created 170726 13:57:37 at startup
ROLLBACK/*!*/;
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 13:59:31 server id 1  end_log_pos 182   Query   thread_id=2     exec_time=0     error_code=0
..
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

如果仅指定 -h 选项，则会收到以下错误消息。

# mysqlbinlog -h 192.168.101.2 mysqld-bin.000001
mysqlbinlog: File 'mysqld-bin.000001' not found (Errcode: 2)

当您对远程数据库没有足够的权限时，您将收到以下“不允许连接”的错误消息。在这种情况下，请确保为本地客户端（即运行 mysqlbinlog 命令的位置）授予对远程数据库的适当权限

# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Host '216.172.166.27' is not allowed to connect to this MySQL server

如果您没有使用 -p 选项指定正确的密码，您将收到以下“访问被拒绝”错误消息

# mysqlbinlog -R --host=192.168.101.2 mysqld-bin.000001
ERROR: Failed on connect: Access denied for user 'root'@'216.172.166.27' (using password: YES)

以下示例显示您还可以使用 -u 选项指定 mysqlbinlog 用于连接到远程 MySQL 数据库的用户名。请注意，此用户是 mysql 用户（不是 Linux 服务器用户）。

mysqlbinlog -R --host=192.168.101.2 -u root -p mysqld-bin.000001
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入