Binlog日志的三种模式

1, Binlog日志的三种模式

1.1 Statement Level模式

每一条修改数据的SQL都会记录到master的Binlog中，slave在复制的时候，SQL进程会将它们解析出来，在slave库上再次执行。

优点：statement level下的优点首先就是它只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文信息。解决了row level下的缺点，不需要记录每一行的变化，较少Binlog日志量，节约IO，提高性能。

缺点：由于它是记录执行语句，所以，为了让这些语句在slave端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，来保证所有语句在slave端能够得到和在master端相同的执行结果。由于MySQL更新较快，使mysql的赋值遇到了不小的挑战，自然赋值的时候就会涉及到越复杂的内容，bug也就容易出现。在statement level下，目前就已经发现了不少情况会造成mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现。比如：sleep()函数在有些版本中就不能正确赋值，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行记录的变化，所以不会出现类似的问题。

总结：

优点：解决了row level的缺点，不需要记录每一行的变化。日志量少，节约IO，从库应用日志块。

缺点：一些新功能同步可能会有障碍,比如函数、触发器等。

 

1.2 Row Level模式

日志中会记录成每一行数据修改的形式，然后在slave端再对相同的数据进行修改。

优点：在row level的模式下，binlog中可以不记录执行的SQL语句的上下文信息，仅仅只需要记录哪一条记录被修改，修改成什么样。所以row level的日志内容会非常清楚的记录每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或fuction，以及trigger的调用或触发无法被正确复制的问题。

缺点：row level模式下，所有的执行语句都会记录到日志中，同时都会以每行记录修改的来记录，这样可能会产生大量的日志内容。

总结：

优点：记录详细。解决statement level模式无法解决的复制问题。

缺点：日志量大，因为是按行来拆分。

 

1.3 Mixed模式（混合模式）

实际上就是前两种模式的结合，在mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种。

新版本中的mysql中对row level模式也做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果SQL语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

 

2，调整binlog日志模式

2.1 binlog日志默认模式：

mysql> show variables like '%version%';
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.27                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.27-0ubuntu0.16.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+
8 rows in set (0.02 sec)

select version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.01 sec)

mysql> show variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

2.2 在线修改立即生效

mysql> set global binlog_format='MIXED';
Query OK, 0 rows affected (0.00 sec)

退出MySQL，查看当前MySQL日志模式。

show variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

修改成功。

2.3 在配置文件中修改

vi /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log-bin=/var/lib/mysql/mysql-bin
#binlog_format="ROW"
binlog_format="MIXED"   #开启MIXED模式
#binlog_format="STATEMENT"

修改后重启MySQL服务即可。

 

3， 查看binlog日志

在日志模式当前为Mixed的模式下，记录日志的形式内容如下:

3.1 先查看binlog日志存放地址(mysql.conf配置文件中的log-bin设置)

3.2 查看binlog文件名

mysql> lock tables t1 read ;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 200965844 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.3 查看binlog日志列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       177 |
| mysql-bin.000005 |       177 |
| mysql-bin.000006 | 200965844 |
+------------------+-----------+
6 rows in set (0.02 sec)

3.4 使用mysqlbinlog工具查看日志

3.4.1 如果需要查询2017-09-17 07:21:09到2017-09-19 07:59:50 数据库为geeRunner 的操作日志，输入如下命令将数据写入到一个备用的txt即可。

mysqlbinlog --no-defaults --database=geeRunner --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" binlogs.000006 > abc.txt

3.4.2 如果需要过滤，只查询insert，update，delete的语句，可以这样写：

mysqlbinlog --no-defaults --database=raceEnroll  binlogs.000006 |grep update |more

3.4.3 基于起始结束位置进行查看，注：hadoop是库名，/var/lib/mysql/mysql-bin.000001是二进制文件路径

[root@c80k2 ~]# mysqlbinlog --start-position=2098 --stop-position=2205 -d hadoop /var/lib/mysql/mysql-bin.000001

 

转载于:https://my.oschina.net/u/3412738/blog/3094682