MySQL 二进制日志格式
来源:互联网 发布:网盘源码系统 编辑:程序博客网 时间:2024/06/05 17:43
MySQL 二进制日志格式
日志分类
- MySQL存储引擎层日志
- innodb
- 重做日志
- 回滚日志
- MySQL服务层日志
- 二进制日志
- 慢查日志
- 通用日志
二进制日志介绍
记录了所有对MySQL数据库的修改事件, 包括DDL和DML操作. 其中binlog仅记录成功执行的日志, 对于回滚或者Syntax Error而未执行的事件并不记录.
启用二进制日志
MariaDB [(none)]> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | OFF |+---------------+-------+1 row in set (0.00 sec)
log_bin为OFF说为未启用二进制日志, 我们尝试通过以下指令打开
MariaDB [(none)]> set global log_bin = 'mariadb-bin';ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
好吧, 其实仍然是要修改my.cnf配置文件, 在my.cnf或自定义配置中添加[mysqld]组, 设置log_bin属性的值为mariadb-bin
以开启binlog.
[mysqld]log_bin = /var/log/mysql/mariadb-bin
以后我们查看目录/var/log/mysql/的时候就会发现有如mariadb-bin.000001
的一系列日志.待重启MySQL后执行
MariaDB [(none)]> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+1 row in set (0.00 sec)
现在也可以正常显示binary的信息
MariaDB [(none)]> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 313 |+------------------+-----------+1 row in set (0.00 sec)
NOTE: mysql> flush logs; 用于产生新的二进制日志, 方便调试.
二进制日志的格式
基于段的日志格式 - statement
这是MySQL 5.7之前默认的日志格式, 记录CUD的SQL语句.
MariaDB [(none)]> show variables like 'binlog_format';+---------------+-----------+| Variable_name | Value |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+1 row in set (0.00 sec)
- 查看日志
mysqlbinlog工具可直接用于查看日志
mysqlbinlog mysql-bin.000001
- 优点:
记录每个事件所执行的SQL语句, 故不需要记录每一行的具体变化, 所以日志记录量相对较少, 节约磁盘IO与网络IO(如果只对一条记录修改或者插入, ROW格式的日志量有可能少于STATEMENT格式).
- 缺点:
为了确保这些SQL语句能在从库中正确地执行, 所以要记录上下文信息, 以保证重放时的行为一致. 但如果使用UUID()这类非确定性函数, 可能会造成主从的数据不一致.
基于行的日志格式 - row
在MySQL 5.7后的默认格式, 每当进行CUD操作修改行记录时都会将数据写入binlog.
如果我们有一个修改了10k条数据的情况下, 基于段的日志格式仅会记录该条SQL, 而基于行的日志则会有10k条记录
- 查看日志
mysqlbinlog -vv mysql-bin.000001
优点
- 使主从复制更加安全, 由于ROW格式记录了每一行的更改, 当日志被从库重放时仅需应用该条更改即可, 使得不确定性函数也能安全地使用. 更大程度上减少由于主从数据不一致而造成复制链路中断的情况.
- 基于行的复制比基于段的复制要高效
缺点
- 记录的日志量较大
混合日志格式 - mixed
混合使用STATEMENT和ROW, 根据SQL语句由系统决定使用基于段还是基于行的日志格式, 如非确定性函数则会以ROW格式记录, 其他的以STATEMENT记录.
修改binlog格式
MariaDB [(none)]> set binlog_format = 'statement';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show variables like 'binlog_format';+---------------+-----------+| Variable_name | Value |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+1 row in set (0.00 sec)
ROW日志格式的相关优化
由于ROW格式记录的日志量巨大, 在MySQL 5.6以后, 官方增加了binlog_row_image
参数改善其记录方式.
MariaDB [(none)]> show variables like 'binlog_row_image';+------------------+-------+| Variable_name | Value |+------------------+-------+| binlog_row_image | FULL |+------------------+-------+1 row in set (0.00 sec)
FULL
为默认值, 意思是记录一行纪录里面的所有内容, 无论该列是否被修改.
MINIMAL
仅记录被修改的列, 这样就可以大大减少记录量.
NOBLOB
与FULL类型, 但是如果没有修改BLOB或TEXT类型的列, 就不会记录该大数据类型的列.
实验
假如有表结构如下
CREATE TABLE `t_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(32) NOT NULL DEFAULT '', `content` text, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表中有记录
id->1, title->'prontera', content->'hey guy'
FULL (全记录)
我们修改id为1的记录, 然后查看/var/log/mysql中的binlog
MariaDB [employees]> update t_test set title = 'solar' where id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
可以看出, 我们仅仅set title, 但是binlog_row_image为FULL的时候会记录所有的列
BINLOG 'HPyWWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==HPyWWBgBAAAASQAAABgCAAAAACEAAAAAAAEAA///+AEAAAAIcHJvbnRlcmEHAGhleSBndXn4AQAAAAVzb2xhcgcAaGV5IGd1eQ=='/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='solar' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
MINIMAL (按需记录)
同样地, 将id为1的记录的title更新为原先的’prontera’, 对比binlog的确是有效地减少了日志量
BINLOG 'rv2WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==rv2WWBgBAAAALQAAAPwBAAAAACEAAAAAAAEAAwEC/gEAAAD+CHByb250ZXJh'/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### SET### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
NOBLOB (除去BLOB&TEXT的全纪录)
更新时不包含text类型的列MariaDB [employees]> update t_test set title = 'juno' where id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0
类似于FULL模式, 但是binlog不会记录没有被更新的大数据类型(BLOB或TEXT类型)
BINLOG 'hP+WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==hP+WWBgBAAAAMwAAAAICAAAAACEAAAAAAAEAAwMD/AEAAAAFcGF5b278AQAAAARqdW5v'/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='payon' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
更新时包含text列
MariaDB [employees]> update t_test set title = 'prontera', content = 'google' where id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
当有大数据类型的列被更新时, 该列才会被记录, 一定程度上减少了日志量.
BINLOG 'PQCXWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==PQCXWBgBAAAAPgAAAA0CAAAAACEAAAAAAAEAAwMH/AEAAAAEanVub/gBAAAACHByb250ZXJhBgBnb29nbGU='/*!*/;### UPDATE `employees`.`t_test`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */### @3='google' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
通过实验, 在基于ROW格式情况下, 即使处于同一IDC, 也建议使用minimal模式以大量减少日志量.
Github
基于Docker Compose构建的MySQL MHA集群
- MySQL 二进制日志格式
- mysql二进制日志格式
- MySQL二进制日志格式类型详解
- mysql日志-二进制日志
- MySQL二进制日志格式对复制的影响
- 清除 MySQL 二进制日志
- mysql 二进制日志文件
- mysql 二进制日志
- 浅析MySQL二进制日志
- 查看Mysql二进制日志
- mysql二进制日志分析
- MySQL复制 二进制日志
- MySQL二进制日志
- MySQL启用二进制日志
- MySQL开启二进制日志
- mysql二进制日志详解
- MySQL二进制日志
- Mysql 二进制日志
- PHP面向对象编程设计模式(二)单例模式
- java使用反射给对象属性赋值的两种方法
- HRBUST-1315 火影忍者之~大战之后
- 没打开coredump,调试core的方法
- 形式化说明技术
- MySQL 二进制日志格式
- 操作文件流的类,读写类StreamReader,StreamWriter和File类以及Directory类的操作
- 加壳原理与简单实现加壳(delphi源码)
- 方法和作用域中的内部类
- C# Redis之ServiceStack
- Android的系统架构
- 关于弱监督学习
- 不足30行代码给家里的男友查岗
- 群智能算法code分享(MATLAB版),超级全,顶一下