怎样理解mysql binlog

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

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'\G;

截取部分查询结果：

*************************** 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'\G;

截取部分查询结果：

*************************** 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'\G;

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有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。