MySQL BinLog-2020-02

2020-02-07 本文已影响0人花神子

MySQL binlog的几种日志录入格式以及区别
binlog文件中存储的内容称之为二进制事件，简称事件。我们的每一个数据库更新操作(Insert、Update、Delete等)，都会对应的一个事件。
从大的方面来说，binlog主要分为2种格式：

Statement模式：binlog中记录的就是我们执行的SQL；
Row模式：binlog记录的是每一行记录的每个字段变化前后得到值。
Mixed(即混合模式)：这并不是一种新的binlog格式，只是结合了Statement和Row两种模式而已。

不同的binlog模式，在binlog文件包含的事件类型也不相同(Mysql中定义了30多个event类型)，如: 1)在Statement模式下，我们就看不到Row模式下独有的事件类型。2)有一些类型的event，必须在我们开启某些特定配置的情况下，才会出现；3)当然也会有一些公共的event类型，在任何模式下都会出现。

是否开启binlog

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |　　#默认是关闭的
+---------------+-------+

mysql> set @@global.log_bin=on;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

Statement

Statement模式下的binlog日志中记录每一条会修改数据的sql。

优点：

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

缺点：

由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。
另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

Statement模式下的事件

可以使用"show binlog events" 语法查看binary log中的内容，如下：

通过命令查看现在的binlog文件

show binary logs;
log_name File_size Encrypted
binlog.000083   178 No
binlog.000084   155 No
binlog.000085   178 No
binlog.000086   178 No
binlog.000087   178 No
binlog.000088   155 No
binlog.000089   178 No
binlog.000090   155 No
binlog.000091   155 No
binlog.000092   1189    No

通过指定查看某个binlog文件

show binlog events in 'binlog.000092';

log_name       pos  event_type  server_id  end_log_pos  info
binlog.000055   4   Format_desc 1   124 Server ver: 8.0.15, Binlog ver: 4
binlog.000055   124 Previous_gtids  1   155 ""
binlog.000055   155 Anonymous_Gtid  1   232 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.000055   232 Query   1   389 CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci /* xid=168 */
binlog.000055   389 Anonymous_Gtid  1   468 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.000055   468 Query   1   660 "use `test`; create table user (
id int(11) not null,
name varchar(10) not null,
primary key(id,name)
) engine=innodb /* xid=199 */"
binlog.000055   660 Anonymous_Gtid  1   739 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.000055   739 Query   1   814 BEGIN
binlog.000055   814 Table_map   1   870 table_id: 75 (test.user)
binlog.000055   870 Write_rows  1   917 table_id: 75 flags: STMT_END_F
binlog.000055   917 Xid 1   948 COMMIT /* xid=1126 */

关于"show binlog events"语法显示的每一列的作用说明如下：

Log_name：当前事件所在的binlog文件名称
Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数。细心的读者可以看到了，第一个事件位置并不是从0开始，而是从4。Mysql通过文件中的前4个字节，来判断这是不是一个binlog文件。这种方式很常见，很多格式的文件，如pdf、doc、jpg等，都会通常前几个特定字符判断是否是合法文件。
Event_type：表示事件的类型
Server_id：表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置。
End_log_position：下一个事件的开始位置
Info：当前事件的描述信息

在Event_Type列中，我们看到了三个事件类型：

Format_desc：也就是我们所说的Format Description Event，是binlog文件的第一个事件。在Info列，我们可以看到，其标明了Mysql Server的版本是5.7.10，Binlog版本是4。
Previous_gtids：该事件完整名称为，PREVIOUS_GTIDS_LOG_EVENT。熟悉Mysql 基于GTID复制的同学应该知道，这是表示之前的binlog文件中，已经执行过的GTID。需要我们开启GTID选项，这个事件才会有值，在后文中，将会详细的进行介绍。
Rotate：Rotate Event是每个binlog文件的结束事件。在Info列中，我们看到了其指定了下一个binlog文件的名称是mysql-bin.000004。

Row

不记录sql语句上下文相关信息，仅保存哪条记录被修改。(binlog记录的是每一行记录的每个字段变化前后得到值。)

优点：

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

缺点:

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

binlog_rows_query_log_events参数

在Statement模式下，直接记录SQL比较直观，事实上，在Row模式下，也可以记录。mysql提供了一个binlog_rows_query_log_events参数，默认为值为FALSE，如果为true的情况下，会通过Rows Query Event来记录SQL。

show variables like '%binlog_rows_query_log_events%'

可以在my.cnf中添加以下配置，来开启row模式下的原始sql记录(需要重启)：
(binlog-rows-query-log_events=1)

binlog_row_image参数

经常会看到一些Row模式和Statement模式的比较。ROW模式下，即使我们只更新了一条记录的其中某个字段，也会记录每个字段变更前后的值，binlog日志就会变大，带来磁盘IO上的开销，以及网络开销。事实上，这个行为可以通过binlog_row_image控制其有3个值，

命令如（show global variables like '%binlog_row_image%';）
默认为FULL：

show global variables like '%binlog_row_image%';

FULL : 记录列的所有修改，即使字段没有发生变更也会记录。
MINIMAL ：只记录修改的列。
NOBLOB :如果是text类型或clob字段，不记录这些日志。

Mixedlevel:

是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.
新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更

binlog 文件存储

mysql 将数据库更新操作对应的event记录到本地的binlog文件中，
mysql中，我们可以通过"show binary logs"语句，来查看当前有多少个binlog文件，以及每个binlog文件的大小，如下：

show binary  logs;

binlog.000049   155 No
binlog.000050   155 No
binlog.000051   178 No
binlog.000052   155 No
binlog.000053   155 No
binlog.000054   178 No
binlog.000055   3288    No

mysql提供了：

max_binlog_size配置项，用于控制一个binlog文件的大小，默认是1G
expire_logs_days配置项，可以控制binlog文件保留天数，默认是0，也就是永久保留。