针对mysql不同binlog模式的示例分析

这篇文章主要为大家展示了“针对mysql不同binlog模式的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“针对mysql不同binlog模式的示例分析”这篇文章吧。

我们都知道，mysql binlog有三种格式：

一：Statement：每一条会修改数据的sql语句都会记录在binlog中。

优点：不需要记录每一行的变化，减少了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)会出现问题).

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

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

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

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

二：Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

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

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

三：Mixed模式: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

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

Binlog日志格式选择：

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

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

针对binlog的三种格式而产生相应的主从复制的三种方式：

（１）：基于语句（Statement）的复制：  在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。

（２）：基于行（row)的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

（３）：混合类型（mixed)的复制: 默认采用基于语句（Statement）的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

如果 binlog 采用了 Mixed 模式，那么在以下几种情况下会自动将 binlog 的模式由 statement 模式变为 row 模式（摘自网络）

1）当 DML 语句更新一个NDB存储引擎的表时；

2）当函数中包含 UUID() 时；

3）2 个及以上字段，并且包含 AUTO_INCREMENT 字段的表被更新时；

4）执行 INSERT DELAYED 语句时；

5）用 UDF（user defined function） 时；

6）视图中必须要求运用 row 时，例如建立视图时使用了 UUID() 函数；

实验1 binlog 采用了Statement模式，导致主从两边不一样。

192.168.0.144：

MariaDB [log]> show variables like 'binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

MariaDB [log]> select count(*) from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (3.65 sec)

MariaDB [log]> insert into  pvlogs2  select * from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

Query OK, 1462803 rows affected (31.69 sec)

Records: 1462803  Duplicates: 0  Warnings: 0

MariaDB [log]> select count(*) from  pvlogs2;

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (0.00 sec)

192.168.0.143：

MariaDB [log]> select count(*) from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  111848 |

+----------+

1 row in set (3.65 sec)

MariaDB [log]>  select count(*) from  pvlogs2;

+----------+

| count(*) |

+----------+

|   111848 |

+----------+

1 row in set (0.00 sec)

实验1说明：当主库binlog采用MIXED 模式的时候，默认会使用Statement模式，由于Statement模式是仅仅是把执行的sql记录进binlog中，然后传给从库，然而由于本身主从两边pvlogs表不一样，进而导致pvlogs2表数据不一样。

实验2：因为涉及到了uuid()函数，导致mixed方式自动转换成row模式，来精确复制。

192.168.0.144：

MariaDB [log]> show variables like 'binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

MariaDB [log]>insert into  test_log select uuid() id, member_id,jsession,ip,search_id,info_id,lastmodify,disc,status  from  pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

MariaDB [log]> select count(*) from  pvlogs2 where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (0.00 sec)

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (1.29 sec)

192.168.0.143：

MariaDB [log]> select count(*) from    pvlogs where lastmodify>'2017-07-19' and  lastmodify<'2017-07-20';

+----------+

| count(*) |

+----------+

|  111848 |

+----------+

1 row in set (3.65 sec)

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  1462803 |

+----------+

1 row in set (1.29 sec)

实验2说明：当主库binlog采用MIXED 模式的时候，虽然默认会使用Statement模式，但是当遇到uuid()函数的时候，因为Statement模式不能准确复制，所以会自动转换成使用row模式，这样把具体的变化写进binlog，传给从库，实现准确复制；

实验3：自增导致mixed转换成rowed；

192.168.0.144：

MariaDB [log]> show variables like 'binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

MariaDB [log]>  select count(*) from pvlogs_back where lastmodify>'2017-08-20' and  lastmodify<'2017-08-22';

+----------+

| count(*) |

+----------+

|  2638555 |

+----------+

1 row in set (4.24 sec)

MariaDB [log]> insert into  test_log select '' , member_id,jsession,ip,search_id,info_id,lastmodify,disc,status  from  pvlogs_back where lastmodify>'2017-08-20' and  lastmodify<'2017-08-22';

Query OK, 2638555 rows affected, 65535 warnings (1 min 27.18 sec)

Records: 2638555  Duplicates: 0  Warnings: 2638555

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  2638555 |

+----------+

1 row in set (0.93 sec)

192.168.0.143：

MariaDB [log]>  select count(*) from pvlogs_back where lastmodify>'2017-08-20' and  lastmodify<'2017-08-22';

+----------+

| count(*) |

+----------+

|  111848 |

+----------+

1 row in set (4.24 sec)

MariaDB [log]> select count(*) from  test_log;

+----------+

| count(*) |

+----------+

|  2638555 |

+----------+

1 row in set (0.93 sec)

实验3说明：当主库binlog采用MIXED 模式的时候，虽然默认会使用Statement模式，但2 个及以上字段，并且包含 AUTO_INCREMENT 字段的表被更新时，因为Statement模式不能准确复制，所以会自动转换成使用row模式，这样把具体的变化写进binlog，传给从库，实现准确复制；

总结：mysql的binlog三种模式，各有优缺点，row下可能会产生大量的日志内容，但是可以做到任何情况都可以被复制，这对复制来说是最安全可靠的，并且执行 INSERT，UPDATE，DELETE 语句时锁更少；Statement模式下产生的binlog大部分情况下比较小，但是有的一些情况是不能被准确复制的，推荐使用MIXED模式。

以上是“针对mysql不同binlog模式的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！