怎样理解mysql binlog

共计 9893 个字符，预计需要花费 25 分钟才能阅读完成。

今天就跟大家聊聊有关怎样理解 mysql binlog，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

Mysql binlog

1、mysql binlog 的类型：

mysql 的 binlog 按照生成方式，可以分为三种，分别是：

1）基于记录的复制 RBR(Row Based Replication) 或 Row：

优点：binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或 function，以及 trigger 的调用和触发无法被正确复制的问题。

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容, 比如一条 update 语句，修改多条记录，则 binlog 中每一条修改都会有记录，这样造成 binlog 日志量会很大，特别是当执行 alter
table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

2）基于语句的复制，简称 SBR(Statement Based
Replication) 或 Statement：

相比 row 能提高性能，减少日志量。但是这个是取决于应用的 SQL 情况，正常同一条记录修改或者插入 row 格式所产生的日志量会小于 Statement 产生的日志量，但是考虑到如果带条件的 update 操作，以及整表删除，alter 表等操作，ROW 格式会产生大量日志，因此在考虑是否使用 ROW 格式日志时应该根据应用的实际情况，考虑其所产生的日志量会增加多少，以及带来的 IO 性能问题。

优点：可以对任何语句都能正确工作，不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能，一些语句的效率更高。例如，一个更新 GB 的数据的查询仅需要几十个字节的二进制日志。

缺点：就是二进制日志可能会很大，而且不直观，所以，你不能使用 mysqlbinlog 来查看二进制日志。而且由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同的结果。另外 mysql 的复制, 像一些特定函数功能，slave 可与 master 上要保持一致会有很多相关问题 (如 sleep() 函数，last_insert_id()，以及 user-defined functions(udf)会出现问题)。此外，存储过程和触发器也是一个问题。另外一个问题就是基于语句的复制必须是串行化的。这要求大量特殊的代码，配置，例如 InnoDB 的 next-key 锁等。并不是所有的存储引擎都支持基于语句的复制。

使用以下函数的语句也无法被复制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

同时在 INSERT
…SELECT 会产生比 RBR 更多的行级锁

3）混合方式 MBR(Mixed
Based Replication)：

由于两种方式不能对所有情况都能很好的处理，所以，MySQL 5.1 以上支持在基于语句的复制和基于记录的复制之前动态交换。可以通过设置 session 变量 binlog_format 来进行控制。

2、Binlog 日志格式选择

Mysql 默认是使用 Statement 日志格式，推荐使用 MIXED.

由于一些特殊使用，可以考虑使用 ROW，如自己通过 binlog 日志来同步数据的修改，这样会节省很多相关操作。对于 binlog 数据处理会变得非常轻松, 相对 mixed，解析也会很轻松(当然前提是增加的日志量所带来的 IO 开销在容忍的范围内即可)。

mysql 对于日志格式的选定原则: 如果是采用 INSERT，UPDATE，DELETE 等直接操作表的情况，则日志格式根据 binlog_format 的设定而记录, 如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用 Statement 模式记录

3、Binlog 相关参数

如以下：

binlog_format = MIXED   //binlog 日志格式，可以选择为 mixed，statement，row

log_bin = 目录 /mysql-bin.log  //binlog 日志名

expire_logs_days = 7  //binlog 过期清理时间

max_binlog_size = 100m  //binlog 每个日志文件大小

binlog-do-db = 需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可

binlog-ignore-db = 不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可

4、binlog 相关文件

mysql-bin.index：

用于跟踪磁盘上存在哪些二进制日志文件。MySQL 用它来定位二进制日志文件。

mysql-relay-bin.index：

该文件的功能与 mysql-bin.index 类似，但是它是针对中继日志，而不是二进制日志。

master.info：

保存 master 的相关信息。不要删除它，否则，slave 重启后不能连接 master。

relay-log.info：

包含 slave 中当前二进制日志和中继日志的信息。

5、binlog 日志内容解析

1)在 mysql 命令界面中查看时：

如果是 statement 模式：

mysql show binlog events in mysql-bin.000021

截取部分查询结果：

*************************** 20. row
***************************

Log_name: mysql-bin.000021 
———————– 查询的 binlog 日志文件名

Pos: 11197 ————————————————————
pos 起始点:

Event_type: Query ———————————————- 事件类型：Query

Server_id: 1 ——————————————- 标识是由哪台服务器执行的

End_log_pos: 11308 —————— pos 结束点:11308(即：下行的 pos 起始点)

Info: use `zyyshop`; INSERT INTO `team2` VALUES
(0,345, asdf8er5)

— 执行的 sql 语句

*************************** 21. row
***************************

Log_name: mysql-bin.000021

Pos: 11308 —————————– pos 起始点:11308(即：上行的 pos 结束点)

Event_type: Query

Server_id: 1

End_log_pos: 11417

Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS
*/

*************************** 22. row
***************************

Log_name: mysql-bin.000021

如果是 row 模式：

mysql show binlog events in mysql-bin.000005

截取部分查询结果：

*************************** 2. row
***************************

Log_name: mysql-bin.000005

Pos: 120

Event_type: Query

Server_id: 3

End_log_pos: 191

Info: BEGIN

*************************** 3. row
***************************

Log_name: mysql-bin.000005

Pos: 191

Event_type: Table_map

Server_id: 3

End_log_pos: 234

Info: table_id: 87 (lxm.t)  — 这里看不到执行的 sql 语句，只能看到表名

*************************** 4. row
***************************

Log_name: mysql-bin.000005

Pos: 234

Event_type: Update_rows

Server_id: 3

End_log_pos: 280

Info: table_id: 87 flags: STMT_END_F

*************************** 5. row
***************************

Log_name: mysql-bin.000005

Pos: 280

Event_type: Xid

Server_id: 3

End_log_pos: 311

Info: COMMIT /* xid=249 */

*************************** 6. row
***************************

Log_name: mysql-bin.000005

2)用 mysqlbinlog 工具查看时：

如果是 statement 模式：

# /usr/local/mysql/bin/mysqlbinlog
/usr/local/mysql/data/mysql-bin.000013

  下面截取一个片段分析：

……………………………………………………………………………………………….

# at 552

#131128 17:50:46 server id 1 
end_log_pos 665  Query  thread_id=11  exec_time=0  error_code=0  —- 执行时间:17:50:46；pos 点:665

  SET
TIMESTAMP=1385632246/*!*/;

  update zyyshop.stu
set name= 李四 where id=4   —- 执行的 SQL

  /*!*/;

# at 665

#131128 17:50:46 server id 1 
end_log_pos 692  Xid = 1454
—- 执行时间:17:50:46；pos 点:692

……………………………………………………………………………………………….

注: server id 1    数据库主机的服务号；

   end_log_pos 665  pos 点

   thread_id=11 
  线程号

如果是 row 模式：

/*!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

#161022
15:59:31 server id 3  end_log_pos 120
CRC32 0x45d9e7a2  Start: binlog v 4,
server v 5.6.24-log created 161022 15:59:31

BINLOG

YxwLWA8DAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaLn

2UU=

/*!*/;

#
at 120

#161022
16:00:57 server id 3  end_log_pos 191
CRC32 0x37e11f27  Query  thread_id=1  exec_time=0  error_code=0

SET
TIMESTAMP=1477123257/*!*/;

SET
@@session.pseudo_thread_id=1/*!*/;

SET
@@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET
@@session.sql_mode=1073741824/*!*/;

SET
@@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C
utf8 *//*!*/;

SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET
@@session.lc_time_names=0/*!*/;

SET
@@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

#
at 191

#161022
16:00:57 server id 3  end_log_pos 234
CRC32 0xe2ba303b  Table_map: `lxm`.`t`
mapped to number 87

#
at 234

#161022
16:00:57 server id 3  end_log_pos 280
CRC32 0xdae765d4  Update_rows: table id
87 flags: STMT_END_F

……………………………………………………………………………………………….

是看不到明文的 DML 语句。

6、常用 binlog 日志操作命令

@查看所有 binlog 日志列表

mysql show master
logs; 或者 show binary logs;

@查看 master 状态，即最后 (最新) 一个 binlog 日志的编号名称，及其最后一个操作事件 pos 结束点 (Position) 值

mysql show master
status;

@刷新 log 日志，自此刻开始产生一个新编号的 binlog 日志文件

mysql flush logs;

注：每当 mysqld 服务重启时，会自动执行此命令，刷新 binlog 日志；在 mysqldump 备份数据时加 -F 选项也会刷新 binlog 日志；

@重置 (清空) 所有 binlog 日志

mysql reset
master;

7、如何查看 binlog 日志的内容

1)在 mysql 命令界面中查看：

mysql show binlog events [IN log_name] [FROM pos]
[LIMIT [offset,] row_count];

这条语句可以将指定的 binlog 日志文件，分成有效事件行的方式返回，并可使用 limit 指定 pos 点的起始偏移，查询条数；

选项解析：

IN log_name   指定要查询的 binlog 文件名(不指定就是第一个 binlog 文件)

FROM pos  指定从哪个 pos 起始点开始查起(不指定就是从整个文件首个 pos 点开始算)

LIMIT [offset,]   偏移量(不指定就是 0)

row_count  查询总条数(不指定就是所有行)

示例：

A. 查询第一个 (最早) 的 binlog 日志：

  mysql show
binlog events\G;

B. 指定查询 mysql-bin.000021 这个文件：

  mysql show
binlog events in mysql-bin.000021

C. 指定查询 mysql-bin.000021 这个文件，从 pos 点:8224 开始查起：

  mysql show
binlog events in mysql-bin.000021 from 8224\G;

D. 指定查询 mysql-bin.000021 这个文件，从 pos 点:8224 开始查起，查询 10 条

  mysql show
binlog events in mysql-bin.000021 from 8224 limit 10\G;

E. 指定查询 mysql-bin.000021 这个文件，从 pos 点:8224 开始查起，偏移 2 行，查询 10 条

  mysql show
binlog events in mysql-bin.000021 from 8224 limit 2,10\G;

2) 使用 mysqlbinlog：

binlog 是二进制文件，普通文件查看器 cat、more、vi 等都无法打开，必须使用自带的 mysqlbinlog 命令查看。binlog 日志与数据库文件在同目录中。

在 MySQL5.5 以下版本使用 mysqlbinlog 命令时如果报错，就加上“–no-defaults”选项。

a)如果是 ROW 模式的二进制日志文件，为了查看 mysql 具体执行了什么样的 sql 语句，需要使用 -v（–verbose）选项，该选项会将行事件重构成被注释掉的伪 SQL 语句，如果想看到更详细的信息可以将该选项给两次如 -vv，这样可以包含一些数据类型和元信息的注释内容。例如：

mysqlbinlog -v mysql-bin.000001

mysqlbinlog -vv mysql-bin.000001

b)mysqlbinlog 和可以通过 –read-from-remote-server 选项从远程服务器读取二进制日志文件，这时需要一些而外的连接参数，如 –host，–password，–port，–user，–socket，–protocol 等，这些参数仅在指定了 –read-from-remote-server 后有效。

c)无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被 mysqlbinlog 工具解析后都可直接应用与 MySQL Server 进行基于时间点、位置或数据库的恢复。

常见参数有:

1)  –database=db_name,
-d db_name

该参数使 mysqlbinlog 仅从本地二进制日志中输出指定的 db_name 被 use 命令选作默认数据库时产生的日志事件。行为类似于 mysqld 的 –binlog-do-db 命令。若该参数指定了多次那么只有最后一次指定的内容有效。参数具体的影响依赖于二进制日志格式，只有在使用行模式的日志格式时该参数才能保证一致性。基于语句或混合模式的二进制日志格式中因为可能存在跨库的更新导致 –database 参数表现不同的行为，从而不能保证数据一致性。例如：

mysqlbinlog  mysql-bin.000001  -d testDB | mysql -uusername -p

2)  –force-read,
-f

使用了该参数后 mysqlbinlog 工具在读取到不能识别的日志事件时会打印出 warning，忽略事件并继续执行，没有此参数的情况下 mysqlbinlog 会停止。

mysqlbinlog  mysql-bin.000001  -d testDB -f | mysql -uusername -p

3)  –no-defaults

阻止 mysqlbinlog 工具从任何配置文件读取参数，.mylogin.cnf 除外（以便于安全的保存密码）

mysqlbinlog mysql-bin.000001
-d testDB -f –no-defaults| mysql -uusername -p

4) –start-datetime=datetime 和 –stop-datetime=datetime

这两个参数用于指定恢复开始时间点和结束时间点，可以一起或单独给出，也可与 –start-position，–stop-position 混用。

mysqlbinlog  mysql-bin.000001  -d testDB -f –no-defaults
–start-datetime=datetime –stop-position=NNNNNN | mysql -uusername -p

5) –start-position=N, -j N 和 –stop-position=N

上边一组参数用于指定恢复开始位置和结束位置，可以一起或单独给出也可与 –start-datetime，–stop-datetime 混用

mysqlbinlog  mysql-bin.000001  -d testDB -f –no-defaults
–start-position=NNNNNN –stop-datetime=datetime | mysql -uusername -p

d)如果需要还原的二进制日志文件不止一个，安全的方式是多个二进制文件同时执行。

mysqlbinlog  mysql-bin.000001  mysql-bin.000002 mysql-bin.000003
–start-position=NNNNNN –stop-datetime=datetime | mysql -uusername -p

或

mysqlbinlog  mysql-bin.00000[1-3] –start-position=NNNNNN
–stop-datetime=datetime | mysql -uusername -p

当多个二进制日志文件同时执行时，–start-position 和 –stop-position 分别只应用于第一个列出的二进制日志文件和最后一个列出的二进制日志文件

  当然也可以先将多个二进制日志文件的输出导到同一个.sql 文件最后在执行该.sql 文件（适用于日志量不多的情况）。

8、binlog 的应用：

可以用 binlog 来恢复误操作的数据。

案例：

1)全备份

mysqldump -uroot -p123456 -lF –log-error=/root/myDump.err -B
zyyshop /root/BAK.zyyshop.sql

备份时使用 - F 选项，意味着备份工作刚开始时就会刷新 log 日志，产生新的 binlog 日志来记录备份之后的数据库的“增删改”操作。

2)备份之后，业务对数据库进行了大量的增删改查操作。然后数据库有张表被误删除了。此刻立即查看最后一个 binlog 日志，记录下关键的 pos 点，即是在哪个点上的操作导致了数据库的破坏。然后 flush logs，让 mysql 重新开始新的 binlog 日志记录文件。从理论上讲，此时旧的 binlog 日志是不会被继续写入了。此时，备份旧的 binlog 日志。

3)读取旧的 binlog 日志，分析问题。

方式一，用 mysqlbinlog 命令来读取 binlog 日志：

  mysqlbinlog  /usr/local/mysql/data/mysql-bin.000023

方式二，在 mysql 服务器中查看：

  mysql show
binlog events in mysql-bin.000023

在输出中找到误删除表的确切 pos 点。

4)首先用全备份进行恢复：

mysql -uroot -p123456
-v /root/BAK.zyyshop.sql;

5)从 binlog 日志中恢复数据：

mysqlbinlog
mysql-bin.0000xx | mysql - u 用户名 - p 密码数据库名

所谓恢复，就是让 mysql 将保存在 binlog 日志中指定段落区间的 sql 语句逐个重新执行一次而已。

看完上述内容，你们对怎样理解 mysql binlog 有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注丸趣 TV 行业资讯频道，感谢大家的支持。