超级有用的15个mysqlbinlog命令

在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项，例如基于行的事件和格式描述事件。

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服务器用户）。

 
1
$ mysqlbinlog -R --host=192.168.101.2 -u root -p mysqld-bin.000001

 mysqlbinlog详解

mysqlbinlog用于处理二进制日志文件的实用工具详解mysqlbinlog
从二进制日志读取语句的工具。在二进制日志文件中包含的执行过的语句的日志可用来帮助从崩溃中恢复。

binlog日志打开方法

在my.cnf这个文件中加一行（Windows为my.ini）。

#vi /etc/my.cnf
[mysqld]
log-bin=mysqlbin-log #添加这一行就ok了=号后面的名字自己定义吧
然后我们可以对数据库做简单的操作后到mysql数据文件所在的目录来看binlog文件
[root@linux mysql]# ll
-rw-rw—- 1 mysql mysql 813255 Nov 25 18:14 mysqlbin-log.000001
搞定了

mysqlbinlog用法详细说明

服务器生成的二进制日志文件写成二进制格式。要想检查这些文本格式的文件，应使用mysqlbinlog实用工具。
应这样调用mysqlbinlog：
shell> mysqlbinlog [options] log-files…例如，要想显示二进制日志binlog.000003的内容，使用下面的命令：
shell> mysqlbinlog binlog.0000003输出包括在binlog.000003中包含的所有语句，以及其它信息例如每个语句花费的时间、客户发出的线程ID、发出线程时的时间戳等等。
通常情况，可以使用mysqlbinlog直接读取二进制日志文件并将它们用于本地MySQL服务器。也可以使用–read-from-remote-server选项从远程服务器读取二进制日志。
当读取远程二进制日志时，可以通过连接参数选项来指示如何连接服务器，但它们经常被忽略掉，除非你还指定了–read-from-remote-server选项。这些选项是–host、–password、–port、–protocol、–socket和–user。
还可以使用mysqlbinlog来读取在复制过程中从服务器所写的中继日志文件。中继日志格式与二进制日志文件相同。

mysqlbinlog支持下面的选项：
·
—help，-？
显示帮助消息并退出。
·
—database=db_name，-d db_name
只列出该数据库的条目(只用本地日志)。
·
–force-read，-f
使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。
·
–hexdump，-H
在注释中显示日志的十六进制转储。该输出可以帮助复制过程中的调试。在MySQL 5.1.2中添加了该选项。
·
–host=host_name，-h host_name
获取给定主机上的MySQL服务器的二进制日志。
·
–local-load=path，-l pat
为指定目录中的LOAD DATA INFILE预处理本地临时文件。
·
–offset=N，-o N
跳过前N个条目。
·
–password[=password]，-p[password]
当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中–password或-p选项后面没有 密码值，则提示输入一个密码。
·
–port=port_num，-P port_num
用于连接远程服务器的TCP/IP端口号。
·
–position=N，-j N
不赞成使用，应使用–start-position。
·
–protocol={TCP | SOCKET | PIPE | -position

使用的连接协议。
·
–read-from-remote-server，-R
从MySQL服务器读二进制日志。如果未给出该选项，任何连接参数选项将被忽略。这些选项是–host、–password、–port、–protocol、–socket和–user。
·
–result-file=name, -r name
将输出指向给定的文件。
·
–short-form，-s
只显示日志中包含的语句，不显示其它信息。
·
–socket=path，-S path
用于连接的套接字文件。
·
–start-datetime=datetime
从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：
shell> mysqlbinlog –start-datetime=”2004-12-25 11:25:56″ binlog.000003该选项可以帮助点对点恢复。
·
–stop-datetime=datetime
从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见–start-datetime选项。该选项可以帮助及时恢复。
·
–start-position=N
从二进制日志中第1个位置等于N参量时的事件开始读。
·
–stop-position=N
从二进制日志中第1个位置等于和大于N参量时的事件起停止读。
·
–to-last-logs，-t
在MySQL服务器中请求的二进制日志的结尾处不停止，而是继续打印直到最后一个二进制日志的结尾。如果将输出发送给同一台MySQL服务器，会导致无限循环。该选项要求–read-from-remote-server。
·
–disable-logs-bin，-D
禁用二进制日志。如果使用–to-last-logs选项将输出发送给同一台MySQL服务器，可以避免无限循环。该选项在崩溃恢复时也很有用，可以避免复制已经记录的语句。注释：该选项要求有SUPER权限。
·
–user=user_name，-u user_name
连接远程服务器时使用的MySQL用户名。
·
–version，-V
显示版本信息并退出。
还可以使用–var_name=value选项设置下面的变量：
·
open_files_limit
指定要保留的打开的文件描述符的数量。
可以将mysqlbinlog的输出传到mysql客户端以执行包含在二进制日志中的语句。如果你有一个旧的备份，该选项在崩溃恢复时也很有用：
shell> mysqlbinlog hostname-bin.000001 | mysql或：
shell> mysqlbinlog hostname-bin.[0-9]* | mysql如果你需要先修改含语句的日志，还可以将mysqlbinlog的输出重新指向一个文本文件。(例如，想删除由于某种原因而不想执行的语句)。编辑好文件后，将它输入到mysql程序并执行它包含的语句。
mysqlbinlog有一个–position选项，只打印那些在二进制日志中的偏移量大于或等于某个给定位置的语句(给出的位置必须匹配一个事件的开始)。它还有在看见给定日期和时间的事件后停止或启动的选项。这样可以使用–stop-datetime选项进行点对点恢复(例如，能够说“将数据库前滚动到今天10:30 AM的位置”)。
如果MySQL服务器上有多个要执行的二进制日志，安全的方法是在一个连接中处理它们。下面是一个说明什么是不安全的例子：

shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!使用与服务器的不同连接来处理二进制日志时，如果第1个日志文件包含一个CREATE TEMPORARY TABLE语句，第2个日志包含一个使用该临时表的语句，则会造成问题。当第1个mysql进程结束时，服务器撤销临时表。当第2个mysql进程想使用该表时，服务器报告 “不知道该表”。
要想避免此类问题，使用一个连接来执行想要处理的所有二进制日志中的内容。下面提供了一种方法：
shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql另一个方法是：
shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sqlshell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sqlshell> mysql -e “source /tmp/statements.sql”mysqlbinlog产生的输出可以不需要原数据文件即可重新生成一个LOAD DATA INFILE操作。mysqlbinlog将数据复制到一个临时文件并写一个引用该文件的LOAD DATA LOCAL INFILE语句。由系统确定写入这些文件的目录的默认位置。要想显式指定一个目录，使用–local-load选项。
因为mysqlbinlog可以将LOAD DATA INFILE语句转换为LOAD DATA LOCAL INFILE语句(也就是说，它添加了LOCAL)，用于处理语句的客户端和服务器必须配置为允许LOCAL操作。

警告：为LOAD DATA LOCAL语句创建的临时文件不会自动删除，因为在实际执行完那些语句前需要它们。不再需要语句日志后应自己删除临时文件。文件位于临时文件目录中，文件名类似original_file_name-#-#。
–hexdump选项可以在注释中产生日志内容的十六进制转储：

shell> mysqlbinlog –hexdump master-bin.000001上述命令的输出应类似：
;;# 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 startupROLLBACK;十六进制转储的输出包含下面的元素：
·
Position: The byte position within the log file.
·
Timestamp: The event timestamp. In the example just shown, ’9d fc 5c 43′ is the representation of ’051024 17:24:13′ in hexadecimal.
·
Type: The type of the log event. ’0f’ means that the example event is a FORMAT_DESCRIPTION_EVENT. The types are:
·
00  UNKNOWN_EVENT·
This event should never be present in the log.·
01  START_EVENT_V3·
This indicates the start of a log file written by MySQL 4 or earlier.·
02  QUERY_EVENT·
The most common type of events.  These contain queries executed·
on the master.·
03  STOP_EVENT·
Indicates that master has stopped.·
04  ROTATE_EVENT·
Written when the master switches to a new log file.·
05  INTVAR_EVENT·
Used mainly for AUTO_INCREMENT values and if the LAST_INSERT_ID()·
function is used in the statement.·
06  LOAD_EVENT·
Used for LOAD DATA INFILE in MySQL 3.23.·
07  SLAVE_EVENT·
Reserved for future use.·
08  CREATE_FILE_EVENT·
Used for LOAD DATA INFILE statements.  This indicates the start·
of execution of such a statement.  A temporary file is created·
on the slave.  Used in MySQL 4 only.·
09  APPEND_BLOCK_EVENT·
Contains data for use in a LOAD DATA INFILE statement.  The·
data is stored in the temporary file on the slave.·
0a  EXEC_LOAD_EVENT·
Used for LOAD DATA INFILE statements.  The contents of the·
temporary file is stored in the table on the slave.·
Used in MySQL 4 only.·
0b  DELETE_FILE_EVENT·
Rollback of LOAD DATA INFILE statement.  The temporary file·
should be deleted on slave.·
0c  NEW_LOAD_EVENT·
Used for LOAD DATA INFILE in MySQL 4 and earlier.·
0d  RAND_EVENT·
Used to send information about random values if the RAND()·
function is used in the query.·
0e  USER_VAR_EVENT·
Used to replicate user variables.·
0f  FORMAT_DESCRIPTION_EVENT·
This indicates the start of a log file written by MySQL 5 or later.·
10  XID_EVENT·
Event indicating commit of XA transaction·
11  BEGIN_LOAD_QUERY_EVENT·
Used for LOAD DATA statements in MySQL 5 and later.·
12  EXECUTE_LOAD_QUERY_EVENT·
Used for LOAD DATA statements in MySQL 5 and later.·
13  TABLE_MAP_EVENT·
Reserved for future use·
14  WRITE_ROWS_EVENT·
Reserved for future use·
15  UPDATE_ROWS_EVENT·
Reserved for future use·
16  DELETE_ROWS_EVENT·
Reserved for future use·
Master ID: The server id of the master that created the event.
·
Size: The size in bytes of the event.
·
Master Pos: The position of the event in the original master log file.
·
Flags: 16 flags.
·
01  LOG_EVENT_BINLOG_IN_USE_F·
Log file correctly closed (Used only in FORMAT_DESCRIPTION_EVENT)·
If this flag is set (if the flags are e.g. ’01 00′) in an·
FORMAT_DESCRIPTION_EVENT, then the log file has not been·
properly closed.  Most probably because of a master crash (for·
example, due to power failure).·
02  Reserved for future use.·
04  LOG_EVENT_THREAD_SPECIFIC_F·
Set if the event is dependent on the connection it was·
executed in (example ’04 00′), e.g. if the event uses·
temporary tables.·
08  LOG_EVENT_SUPPRESS_USE_F·
Set in some circumstances when the event is not dependent on·
the current database其它标志保留用于将来使用。
在以后的版本中十六进制转储输出的格式可能会改变。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/DataArt/p/10232374.html