超级有用的15个mysqlbinlog命令

2018/07 作者：ihunter 0 次 0

在MySQL或MariaDB中，任意时间对数据库所做的修改，都会被记录到日志文件中。例如，当你添加了一个新的表，或者更新了一条数据，这些事件都会被存储到二进制日志文件中。二进制日志文件在MySQL主从复合中是非常有用的，主服务器会发送其数据到远程服务器中。

当你需要恢复MySQL时，也会需要使用到二进制日志文件。

mysqlbinlog 命令，以用户可视的方式展示出二进制日志中的内容。同时，也可以将其中的内容读取出来，供其他MySQL实用程序使用。

在此示例中，我们将会涉及以下内容：

获取当前二进制日志列表
mysqlbinlog默认行为
获取特定数据库条目
禁止恢复过程产生日志
在输出中控制base-64 BINLOG
mysqlbinlog输出调试信息
跳过前N个条目
保存输出到文件
从一个特定位置提取条目
将条目截止到一个特定的位置
刷新日志以清除Binlog输出
在输出中只显示语句
查看特定开始时间的条目
查看特定结束时间的条目
从远程服务器获取二进制日志

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

在mysql中执行以下命令，即可查看二进制日志文件的列表。

如果熊没有开启此功能，则会显示：

二进制日志文件默认会存放在 /var/lib/mysql 目录下

2 mysqlbinlog 默认行为

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

mysqlbinlog默认会显示为以下内容：

上面的命令将会显示出，在该系统上数据库发生的所有改变事件。

3 获取特定数据库条目

默认情况下，mysqlbinlog会显示所有的内容，太过于杂乱。使用 -d 选项，可以指定一个数据库名称，将只显示在该数据库上所发生的事件。

也可以使用 --database 命令，效果相同。

4 禁止恢复过程产生日志

在使用二进制日志文件进行数据库恢复时，该过程中也会产生日志文件，就会进入一个循环状态，继续恢复该过程中的数据。因此，当使用mysqlbinlog命令时，要禁用二进制日志，请使用下面所示的-D选项：

也可以使用 --disable-log-bin 命令，效果相同。

备注：在输出中，当指定-D选项时，将看到输出中的第二行。也就是SQL_LOG_BIN=0

当使用-to-last-log选项时，这个选项也会有所帮助。另外，请记住，该命令需要root权限来执行。

5 在输出中控制base-64 BINLOG

使用base64-output选项，可以控制输出语句何时是输出base64编码的BINLOG语句。以下是base64输出设置的可能值：

never
always
decode-rows
auto（默认）

never：当指定如下所示的“never”时，它将在输出中显示base64编码的BINLOG语句。

将不会有任何与下面类似的行，它具有base64编码的BINLOG。

always：当指定“always”选项时，只要有可能，它将只显示BINLOG项。因此，只有在专门调试一些问题时才使用它。

下面是“always”的输出，它只显示了BINLOG项。

decode-rows：这个选项将把基于行的事件解码成一个SQL语句，特别是当指定-verbose选项时，如下所示。

auto：这是默认选项。当没有指定任何base64解码选项时，它将使用auto。在这种情况下，mysqlbinlog将仅为某些事件类型打印BINLOG项，例如基于行的事件和格式描述事件。

MySQL主从复制异常，定位具体的SQL

show binlog events 找到对应的position：

MySQL bin_log 日志row格式，如何转换为SQL：

show binlog events in 'mysql-bin.007064' from 0 limit 165000, 100;

可以看到一个语句跨越了四段position：

| mysql-bin.007064 | 309483688 | Query | 1 | 309483760 | BEGIN |
| mysql-bin.007064 | 309483760 | Table_map | 1 | 309483828 | table_id: 71 (guba.guba_threads_all) |
| mysql-bin.007064 | 309483828 | Delete_rows | 1 | 309483892 | table_id: 71 flags: STMT_END_F |
| mysql-bin.007064 | 309483892 | Xid | 1 | 309483923 | COMMIT / xid=2760796538 /

通过mysqlbinlog 来分析下具体执行的SQL语句内容：

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=309483688 --stop-position=309483923 mysql-bin.007064
DELETE FROM guba.guba_threads_all
WHERE
@1=16923125 / INT meta=0 nullable=0 is_null=0 /
@2='zp' / VARSTRING(90) meta=90 nullable=0 is_null=0 /

6 mysqlbinlog输出调试信息

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

如下所示，在完成处理给定的二进制日志文件之后，下面的调试信息选项将显示额外的调试信息。

7 跳过前N个条目

除了读取整个mysql二进制日志文件外，也可以通过指定偏移量来读取它的特定部分。可以使用 -o 选项。o代表偏移。

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

为了确保它正常工作，给偏移量提供一个巨大的数字，将看不到任何条目。下面的内容将从日志中跳过10,000个条目(事件)。

在本例中，由于这个特定的日志文件没有10,000个条目，所以在输出中没有显示任何数据库事件。

8 保存输出到文件

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

或者也可以使用 -r （结果文件）选项，如下所示，将输出存储到一个文件中。

备注：还可以使用 -server-id 指定mysql服务器，确保是由给定服务器id的mysql服务器所生成的日志。

9 从一个特定位置提取条目

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

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

当在命令行中指定多个二进制日志文件时，开始位置选项将仅应用于给定列表中的第一个二进制日志文件。还可以使用 -H 选项来获得给定的二进制日志文件的十六进制转储，如下所示。

10 将条目截止到一个特定的位置

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

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

11 刷新日志以清除Binlog输出

当二进制日志文件没有被正确地关闭时，将在输出中看到一个警告消息，如下所示。

如下所示，报告中提示binlog文件没有正确地关闭。

当看到这个提示时，需要连接到mysql并刷新日志，如下所示。

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

12 在输出中只显示语句

默认情况下，正如在前面的示例输出中看到的一样，除了SQL语句之外，在mysqlbinlog输出中还会有一些附加信息。如果只想查看常规的SQL语句，而不需要其他内容，那么可以使用 -s 选项，如下所示。

也可以使用 --short-form 选项，效果相同。

下面是上述命令的部分输出。在这里，它将只显示来自给定二进制日志文件的SQL语句。

不会显示像下面这样的条目：

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

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

当你想要从一个二进制文件中提取数据时，这是非常有用的，因为你希望使用它来恢复或重构在某个时间段内发生的某些数据库活动。时间戳的格式可以是MySQL服务器所理解的DATETIME和timestamp中的任何类型。

14 查看特定结束时间的条目

与前面的开始时间示例一样，这里也可以指定结束时间，如下所示。

上面的命令将读取到给定结束时间的条目。任何来自于超过给定结束时间的mysql二进制日志文件的条目都不会被处理。

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

在本地机器上，还可以读取位于远程服务器上的mysql二进制日志文件。为此，需要指定远程服务器的ip地址、用户名和密码，如下所示。

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

在上面命令中：

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

下面命令与上面的命令完全相同：

如果只指定 -h 选项，将会得到下面的错误消息。

当你在远程数据库上没有足够的特权时，将得到以下“不允许连接”错误消息。在这种情况下，确保在远程数据库上为本地客户机授予适当的特权。

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

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

标签：日志条目文件选项命令语句数据库

赞(0) 更多分享

本文链接地址： https://b.htmltoo.com/blog-p605.html

上篇： Mycat学习实战-Mycat读写分离
下篇： mysqladmin命令用法

分类

精选

导航

标签云

搜索

会员中心

关于我们

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

2 mysqlbinlog 默认行为

3 获取特定数据库条目

4 禁止恢复过程产生日志

5 在输出中控制base-64 BINLOG

MySQL主从复制异常，定位具体的SQL

MySQL bin_log 日志row格式，如何转换为SQL：

可以看到一个语句跨越了四段position：

通过mysqlbinlog 来分析下具体执行的SQL语句内容：

DELETE FROM `guba`.`guba_threads_all`

WHERE

@1=16923125 / INT meta=0 nullable=0 is_null=0 /

@2='zp' / VARSTRING(90) meta=90 nullable=0 is_null=0 /

6 mysqlbinlog输出调试信息

7 跳过前N个条目

8 保存输出到文件

9 从一个特定位置提取条目

10 将条目截止到一个特定的位置

11 刷新日志以清除Binlog输出

12 在输出中只显示语句

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

14 查看特定结束时间的条目

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

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

2 mysqlbinlog 默认行为

3 获取特定数据库条目

4 禁止恢复过程产生日志

5 在输出中控制base-64 BINLOG

MySQL主从复制异常，定位具体的SQL

MySQL bin_log 日志row格式，如何转换为SQL：

可以看到一个语句跨越了四段position：

通过mysqlbinlog 来分析下具体执行的SQL语句内容：

DELETE FROM guba.guba_threads_all

WHERE

@1=16923125 / INT meta=0 nullable=0 is_null=0 /

@2='zp' / VARSTRING(90) meta=90 nullable=0 is_null=0 /

6 mysqlbinlog输出调试信息

7 跳过前N个条目

8 保存输出到文件

9 从一个特定位置提取条目

10 将条目截止到一个特定的位置

11 刷新日志以清除Binlog输出

12 在输出中只显示语句

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

14 查看特定结束时间的条目

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

DELETE FROM `guba`.`guba_threads_all`