针对mysql不同binlog模式的案例分析
发布时间：2021-12-26 13:15:24 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要为大家展示了针对mysql不同binlog模式的示例分析，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下针对mysql不同binlog模式的示例分析这篇文章吧。 我们都知道，mysql binlog有三种格式： 一：Statem
|
这篇文章主要为大家展示了“针对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; 实验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 (1.29 sec) 实验2说明：当主库binlog采用MIXED 模式的时候，虽然默认会使用Statement模式，但是当遇到uuid()函数的时候，因为Statement模式不能准确复制，所以会自动转换成使用row模式，这样把具体的变化写进binlog，传给从库，实现准确复制； 实验3：自增导致mixed转换成rowed； +----------+ | 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模式的示例分析”这篇文章的所有内容，感谢各位的阅读！ （编辑：汉中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐