MySQL binlog原理及应用

原创
11/20 00:27
阅读数 7.4K

01 概述

    Binlog它记录了所有的DDL和DML(除了数据查询语句)语句,以事件(EVENT)形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。

    一般来说开启二进制日志大概会有1%的性能损耗(MySQL官方测试数据),虽然会降低性能,但是binlog可以用于主从复制(replication)和point-in-time的恢复,所以还是很有必要开启的。

    查看:

    SHOWBINLOGEVENTIN'mysqld.00001'\G;

    配置:

    通过参数log-bin[=name]可以开启二进制日志,如果不指定name,默认二进制日志文件名为主机名,后缀名为二进制日志的序列号,所在路径为数据库所在目录(datadir)。

02 分类

    二进制日志包括两类文件:

    1、二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。 

    2、二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。 

03 分类

    binlog有三种格式:

    1、基于SQL语句的复制(statement-based replication, SBR)

    2、基于行的复制(row-based replication, RBR)

    3、混合模式复制(mixed-based replication, MBR)

3.1 Statement

    记录的是逻辑SQL,每一条修改操作的sql都会记录在binlog中。

    优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO, 提高了性能。

    缺点:无法完全保证slave节点与master节点数据完全一致。像一些特定函数的功能,slave可与master上要保持一致会有很多相关问题。

3.2 Row

    5.1.5版本的MySQL开始支持row level的复制,它不记录sql语句上下文相关信息,仅保存哪条记录被修改。

    优点:binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。row格式的日志内容会非常清楚的记录下每一行数据修改的细节,不会出现某些特定情况下的存储过程。

    缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容。

3.3 Mixed

    从5.1.8版本开始,MySQL提供了Mixed格式,实际上就是Statement与Row的结合。

    在Mixed模式下,(默认情况下)一般的语句修改使用statment格式保存binlog,下面的情况会采用row格式的情况包括:

    1、表的存储引擎为NDB;

    2、使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数;

    3、使用了INSERT DELAY语句;

    4、使用了用户定义函数(UDF);

    5、使用了临时表(temporary table)。

    注:不需要死记硬背这些情况会使用row,之所以不使用statement模式,就是因为简单的逻辑SQL无法实现数据的回放,比如使用UUID(),这个是随机的,无法保证每次结果都一样,为了保证主从复制数据一致性,则必须是记录数据的变化信息。

04 参数

4.1 max_binlog_size

    指定了单个二进制日志文件最大值,如果超过该值,则产生新的二进制日志文件后缀名+1,并记录到.index文件。

4.2 binlog_cache_size

    控制缓冲大小,默认大小32K,基于会话的,因此每开启一个事务就分配一个binlog_cache_size大小的缓存,所以不能设置过大。

    当一个事务的记录大于binlog_cache_size时,MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设置太小(否则会频繁刷盘)。

4.3 sync_binlog

    表示每写缓冲多少次就要同步到磁盘。如果设置为1,表示采用同步写磁盘的方式来写二进制日志,这时候写操作不使用操作系统的缓冲来写二进制日志。sync_binlog的默认值为0,如果使用InnoDB存储引擎进行复制,并且想得到最大的可用性,建议将该值设置为ON(对数据库IO系统带来一定影响)。

4.4 binlog-do-db

    表示需要写入哪些库的日志,默认为空,表示需要同步所有库的日志到二进制日志。

4.5 binlog-ignore-db

    表示需要忽略写入哪些库的日志,默认为空,表示需要同步所有库的日志到二进制日志。

4.6 log-slave-update

    若当前数据库是主从复制架构中的slave节点,则它不会将从master取得并执行的binlog写入自己的二进制文件中。但是,如果需要搭建master->slave->slave这种架构的复制,则必须设置该参数。

4.7 binlog_format

    记录二进制日志的格式。

05 操作

5.1 开启

    1、vi编辑打开mysql配置文件

    # vi /usr/local/mysql/etc/my.cnf在[mysqld] 区块设置/添加 log-bin=mysql-bin

    重启mysqld服务使配置生效

    # /usr/local/mysql/bin/mysqld_safe --user=mysql &

    2、也可登录mysql服务器,通过mysql的变量配置表,查看二进制日志是否已开启 

5.2 查看日志列表

    mysql> show master logs;

5.3 查看节点状态

    查看主节点状态信息:

    mysql> show master status;

    查看从节点状态信息:  

    mysql> show slave status;

5.4 刷新log日志

    mysql> flush logs;

    注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

5.5 清空日志

    mysql> reset master;

5.6 启动复制

    mysql> start slave

    可以指定线程类型:IO_THREAD,SQL_THREAD,如果不指定,两个都启动。

06 应用场景

    binlog主要使用场景:

    1、主从复制:在master节点开启binlog,并把它的二进制日志传递给slaves,slave节点通过复制和执行二进制日志进行实时同步。

    注:具体工作流程参考《MySQL主从复制》。

    2、数据恢复:某些数据的恢复需要二进制日志,例如,如果需要恢复数据库全量备份的文件,可以通过二进制日志进行point-in-time恢复。

    3、审计(audit):用户通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入的攻击。

展开阅读全文
打赏
1
5 收藏
分享
加载中
更多评论
打赏
0 评论
5 收藏
1
分享
返回顶部
顶部