mysql日志文件相关的配置【2】

时间:2022-10-24 09:19:59

1、二进制日志是什么?

  mysql 的二进制日志用于记录数据库上做的变更、

2、二进制日志什么时间写到磁盘

  1、总的来说二进制日志会在释放锁之前就写入磁盘、也就是说在commit完成之前;client还没发送commit这个时候mysql并不把binlog写入磁盘、

  别一方面mysql保证在commit完成之前会把二进制日志写入磁盘。

  2、如果表不支持事务、那么二进制日志会在执行语句的一开始就写入磁盘。

  3、对于innodb表在client没有发送commit的时候、所有的执行结果将会被cache住;当commit语句下达到mysql时,mysql会马上写入二进制日志。

  4、由1+2+3可以看出来二进制日志只的顺序就是事务commit的顺序。

3、binlog_cache_size=【缓存大小】

  二进制日志写缓存的大小

4、max_binlog_cache_size=【最大缓存大小】

  如果单个事务的二进制日志的大小大于这个值、那么这个事务就会被回滚。

5、log_bin=【二进制文件的全路径】

  这个参数用于指定二进制日志文件的路径、同时还有另一层意思、如果这个参数没有被设置那么表示不开启二进制日志。

6、max_binlog_sizse=【单个二进制日志文件的最大大小】

  这个参数用于控制单个二进制文件的最大大小、当然你也有可能看到二进制日志会比这个设定要大、是因为一个事务只会记录在一个

  文件中、不会刻意追求文件大小固定而把一个事务分别写入两个文件中去。

7、sql_log_bin=【0 | 1】

  这个参数控制当前session 的SQL语句是否写入二进制日志

8、binlog_checksum=【1 | 0 】

  默认情况下mysql在记录二进制日志时会把事件的长度也写进去、通过分析写入的长度值与真正的长度值是否相等、来确定

  二进制日志是否被正确的写入;然而这种通过事件长度来检验二进制日志是否正确的方法,强度不高;开启这个参数后就可以

  通过检验和来完成检验。

9、master_verify_checksum=【1 | 0 】

  设置在master的reading back阶段是否采用checksum的方案来校验

10、slave_sql_verify_checksum =【1 | 0 】

  默认情况下slave的IO_thread会通过事件长度来校验二进制日志的正确性、slave_sql_verify_checksum 用于控制IO_thread的校验方式

11、binlog_do_db=【数据库名】

  用一个例子做为说明:如binlog_do_db=master 那么只有在你use master 之后的语句才会记录到二进制日志中去;也就是说只有在

  select database(); 返回的是master的情况下才会记录二进制日志。

  注意1:就算是你的SQL语句操作的是别的库、也会被记录的。如:insert into test.t(x) values(1);虽然是操作的test库,但是还是会记录二进制日志

  注意2:不一定你所执行的所有写入特性的SQL都会被记录。如:create database db001;

  

12、binlog_ignore_db

  正好于binlog_do_db的过滤规则相反;

13、replication_do_db

  于binlog_do_db相近,只是它起作用的地方是在mysql群集环境中的slave端

14、replication_ignore_db

  于replication_do_db相反

15、发现一个问题

  show variables like xxx ; 语句并看不到binlog_do_db、binlog_ignore_db、replicate_do_db、replicate_ignore_db这四个参数

16、log_slave_updates=【1 | 0】

  通常来说发生在master 的变更经由replication传播到slave ;进而引起slave的变更、那么问题就来了slave上由replication所引起的变更是不是也要记录

  到它的binlog日志中去呢?如果要记录的话就把log_slave_updates=1

17、sync_binlog=【0、1、2、3、... ...】

  由2可以知道binlog的写入与commit是两个不同的操作、sync_binlog用于向mysql说明是不是每一次commit都把二进制日志写入磁盘。

  1、sync_binlog=0 不对二进制日志刷盘做什么限制、把这个刷盘操作交给linux系统。

  2、sync_binlog=1 每次commit都对应一次刷盘操作。

  3、sync_binlog=【{n | n>=2}】 mysql会累计n个commit操作后才刷一次盘。

18、innodb_support_xa=【1 | 0 】

  还是再说一下二进制日志刷盘(二进制日志从缓存写到磁盘文件);mysql大致上可以分成3个层次

  1、connection 层 这个位于mysql是上层;这个层用于管理client的连接。

  2、sql 层 像词发分析、SQL执行计划的生成和优化都在这一层解决。

  3、store engine层 存储引擎层

  一个commit操作是mysql各个层之间的穿越顺序是connection layer --> sql layer --> strore engine layer ;当sql layer 收到commit的时候就开始

  把二进制日志写入磁盘了(我这里假设sync_binlog=1);之后是stroe engine layer 收到commit 这个commit在store engine layer 也会触发相应的刷

  盘操作(通常来说这个store engine layer 上的实现会是innodb); 下面我们把问题深入一点,假设sql layer上的操作成功了,之后mysql被kill了,也就是说

  stroe engine layer 上的commit没有正确的执行完成;然后再启动mysql这个时候由于之前在store engine layer上的操作并没有被commit所以它会被

  回滚掉、所以这样就造成了binlog中的记录与innodb中的数据不一致的情况;为了使两者是一致的我们要把innodb_support_xa=1

19、binlog_format=【row | statement | mixed 】

  1、二进制日志里面记录的是什么?

      记录的是数据库上的变更、或是可能的变更(如一条update语句影响了0行)

  2、二进制日志可以怎么记录?

      1、一种可能的记法是直接把SQL语句记上去如:udpate user set birthday='xxxx' where id =2048;

      2、别一种可能的记法类似于:把表user里id=2048的那一行的birthday设置为'xxxx';

  3、这两种记录方式进一步认识?

      1、第一种记录方式、slave就相当到是把主库上执行的SQL语句重新执行一遍;想想这种情况一条SQL有非常复杂的逻辑,但是最后只改了一行数据。

      2、第二种记录方式、slave并没有去执行主库上执行的SQL而是直接把结果改成主库的样子。

  4、看起来十分完美、但是事实上并不是这么一回事

    1、在master-->slave的环境下、statement可能会引起主从的不一致问题;如果在库上的时间是1号、而在slave主机上的时间是2号;主库上执行

      了一条插入当前时间的操作、总的来看master & slave 上执行的SQL语句是一样的,但是它们自身的时间不一样使都它们的结果不一样、这样

      就造成了主从的不一致。

    2、在master-->slave环境下、row虽然消除了主从不一致的问题、但是它的使用上有一些要注意的地方、如一定要给表加上主键:由于row是基于

      行的、如果mysql能越快的定位行,那么它执行的就越快;有人会问“我给表上加上相应的索引还不行吗”,我告诉你啊,这还真不行,一定要是主

      键,我见过一个delete 操作引起主从延时1周的。以下是mysql官方文档上的一段

      mysql日志文件相关的配置【2】

    3、那有没有把这两个结合一下的呢?这个还真的有它就是mixed模式。mysql从来没有把这个模式设置为默认模式,我也不知道是出于什么

      考虑