MySQL——二进制文件

Author：二滑大魔王
发布时间：February 18, 2020
904views
No comments
8352字数
Categories： Mysql DataBase

Mysql的二进制日志记录了数据库的修改事件,增删改查以及对表结构的修改.需要注意的是,二进制日志只记录成功的事件,对于执行失败或者回滚的操作是不会就记录的.

类型

基础命令

-- 查看日志类型
show variables like 'binlog_format';
-- 修改日志类型
set session binlog_format= 日志类型;

STATEMENT

段格式的二进制日志,记录每一个事件执行的sql语句,不会记录每一行的具体变化,日志的记录量相对较小,节约了磁盘及网络I/O. 假设在sql语句中执行的是特定的函数,会导致主从服务器数据不一样.

-- 设置日志类型
set session binlog_format=statement;
-- 验证是否修改成功
show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
-- 查看日志文件
show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
-- 刷新日志文件
show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
-- 可以看到出现了一个新的日志文件,方便查看内容
create database angel;
use angel
create table type(id int(11),name varchar(12));
insert into type values (1,'二滑大魔王'),(2,'二滑小天使'),(3,'zcx小傻逼');
update type set name='zcx大傻逼' where id=3;
-- 查看binlog文件位置
show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/mysql-bin       |
| log_bin_index                   | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
-- 退出进入日志目录

查看日志

# cd /data/mysql/
# mysqlbinlog mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200101 23:18:00 server id 1  end_log_pos 123 CRC32 0xf06067e9     Start: binlog v 4, server v 5.7.28-log created 200101 23:18:00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
KLgMXg8BAAAAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AelnYPA=
'/*!*/;
# at 123
#200101 23:18:00 server id 1  end_log_pos 154 CRC32 0xc04c47ca     Previous-GTIDs
# [empty]
# at 154
#200101 23:21:02 server id 1  end_log_pos 219 CRC32 0x249846d8     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200101 23:21:02 server id 1  end_log_pos 316 CRC32 0xefd7f328     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892062/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database angel
/*!*/;
# at 316
#200101 23:22:03 server id 1  end_log_pos 381 CRC32 0x5804a168     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 381
#200101 23:22:03 server id 1  end_log_pos 503 CRC32 0x3d09ff4b     Query    thread_id=4    exec_time=0    error_code=0
use `angel`/*!*/;
SET TIMESTAMP=1577892123/*!*/;
create table type(id int(11),name varchar(12))
/*!*/;
# at 503
#200101 23:23:36 server id 1  end_log_pos 568 CRC32 0x37ce31b5     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 568
#200101 23:23:36 server id 1  end_log_pos 649 CRC32 0x0b1bfbbb     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892216/*!*/;
BEGIN
/*!*/;
# at 649
#200101 23:23:36 server id 1  end_log_pos 811 CRC32 0x2e1fad41     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892216/*!*/;
insert into type values (1,'二滑大魔王'),(2,'二滑小天使'),(3,'zcx小傻逼')
/*!*/;
# at 811
#200101 23:23:36 server id 1  end_log_pos 842 CRC32 0xa377258c     Xid = 19
COMMIT/*!*/;
# at 842
#200101 23:24:47 server id 1  end_log_pos 907 CRC32 0x58db52e0     Anonymous_GTID    last_committed=3    sequence_number=4    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 907
#200101 23:24:47 server id 1  end_log_pos 988 CRC32 0xae8fceba     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892287/*!*/;
BEGIN
/*!*/;
# at 988
#200101 23:24:47 server id 1  end_log_pos 1110 CRC32 0xf5dacac1     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892287/*!*/;
update type set name='zcx大傻逼' where id=3
/*!*/;
# at 1110
#200101 23:24:47 server id 1  end_log_pos 1141 CRC32 0xc7c36777     Xid = 20
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

ROW

行格式的日志格式,Row格式可以避免Mysql复制中出现主从不一致的问题,记录的是增删改查行的信息,修改一行就会有一条记录.例如:一条sql语句修改了10000条记录,段日志格式只会记录这条SQL语句,而行的日志会记录10000行数据的修改.优点:使Mysql主从复制更加的安全.应用效率高.缺点:记录日志量较大.

在mysql5.6版本后新增了binlog_row_image参数来设置ROW格式日志的记录方式.bingo_row_image = FULL | MINIMAL | NOBLOB.其中默认格式为FULL

FULL(默认):记录被修改一行数据的所有列,包括没有修改的列也会被记录.
MINIMAL:记录被修改的列.
NOBLOB:和FULL相似,没有对数据类型为BLOB和TEXT进行修改就不会记录这两列的内容 .

FULL

-- 设置二进制文件日志格式
set session binlog_format=row;
-- 验证是否修改成功
show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
-- 刷新日志 | 生成mysql-bin.000006
flush logs;
-- 查看记录行格式的方式
show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
-- 执行操作
use angel
create table type1(id int(2),name(13));
insert into type1 values (1,'二滑大魔王'),(2,'二滑小天使'),(3,'zcx小傻逼');
update type1 set name='zcx大傻逼' where id=3;
alter table type1 add content text;
insert into type1 values(3,'row类型','oo');
select * from type1;
+------+-----------------+---------+
| id   | name            | content |
+------+-----------------+---------+
|    1 | 二滑大魔王        | NULL    |
|    2 | 二滑小天使        | NULL    |
|    3 | zcx大傻逼        | NULL    |
|    3 | row类型          | oo      |
+------+-----------------+---------+

查看日志

ROW格式的日志在查看的时候需要加上-vv参数才能转换成能看懂的形式.

cd /data/mysql/
mysqlbinlog -vv mysql-bin.000006

可以看到在修改数据的时候日志记录了修改行的每一列的数据,包括没有进行修改列的修改前后的数据.

### UPDATE `angel`.`type1`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zcx小傻逼' /* VARSTRING(52) meta=52 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zcx大傻逼' /* VARSTRING(52) meta=52 nullable=1 is_null=0 */
# at 919
#200102  1:02:17 server id 1  end_log_pos 950 CRC32 0x2ff24d01     Xid = 40

MINIMAL

-- 设置类型
set session binlog_row_image=minimal;

NOBOLb

-- 设置类型
set session binlog_row_image=noblob;

MIXED

混合日志格式的日子记录,会根据sql语句由系统决定是用段格式还是行格式的日志.

set binlog_format = mixed;

总结

格式对复制的影响:

日志格式	优点	缺点
STATEMENT(段格式)	日志量小,节省IO,不强制要求表定义完全相同	无法保存主从数据一致性(随机生成函)
ROW(行格式,推荐)	保证数据一致性,减少数据库锁的使用	若表结构不相同会中断复制,从服务器无法单独执行触发器

Last modification：February 18th, 2020 at 10:11 pm

MySQL——二进制文件

二滑大魔王 • 2020 年 02 月 18 日

类型

基础命令

-- 查看日志类型
show variables like 'binlog_format';
-- 修改日志类型
set session binlog_format= 日志类型;

STATEMENT

-- 设置日志类型
set session binlog_format=statement;
-- 验证是否修改成功
show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
-- 查看日志文件
show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
-- 刷新日志文件
show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
-- 可以看到出现了一个新的日志文件,方便查看内容
create database angel;
use angel
create table type(id int(11),name varchar(12));
insert into type values (1,'二滑大魔王'),(2,'二滑小天使'),(3,'zcx小傻逼');
update type set name='zcx大傻逼' where id=3;
-- 查看binlog文件位置
show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/mysql-bin       |
| log_bin_index                   | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
-- 退出进入日志目录

查看日志

# cd /data/mysql/
# mysqlbinlog mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200101 23:18:00 server id 1  end_log_pos 123 CRC32 0xf06067e9     Start: binlog v 4, server v 5.7.28-log created 200101 23:18:00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
KLgMXg8BAAAAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AelnYPA=
'/*!*/;
# at 123
#200101 23:18:00 server id 1  end_log_pos 154 CRC32 0xc04c47ca     Previous-GTIDs
# [empty]
# at 154
#200101 23:21:02 server id 1  end_log_pos 219 CRC32 0x249846d8     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200101 23:21:02 server id 1  end_log_pos 316 CRC32 0xefd7f328     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892062/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database angel
/*!*/;
# at 316
#200101 23:22:03 server id 1  end_log_pos 381 CRC32 0x5804a168     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 381
#200101 23:22:03 server id 1  end_log_pos 503 CRC32 0x3d09ff4b     Query    thread_id=4    exec_time=0    error_code=0
use `angel`/*!*/;
SET TIMESTAMP=1577892123/*!*/;
create table type(id int(11),name varchar(12))
/*!*/;
# at 503
#200101 23:23:36 server id 1  end_log_pos 568 CRC32 0x37ce31b5     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 568
#200101 23:23:36 server id 1  end_log_pos 649 CRC32 0x0b1bfbbb     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892216/*!*/;
BEGIN
/*!*/;
# at 649
#200101 23:23:36 server id 1  end_log_pos 811 CRC32 0x2e1fad41     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892216/*!*/;
insert into type values (1,'二滑大魔王'),(2,'二滑小天使'),(3,'zcx小傻逼')
/*!*/;
# at 811
#200101 23:23:36 server id 1  end_log_pos 842 CRC32 0xa377258c     Xid = 19
COMMIT/*!*/;
# at 842
#200101 23:24:47 server id 1  end_log_pos 907 CRC32 0x58db52e0     Anonymous_GTID    last_committed=3    sequence_number=4    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 907
#200101 23:24:47 server id 1  end_log_pos 988 CRC32 0xae8fceba     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892287/*!*/;
BEGIN
/*!*/;
# at 988
#200101 23:24:47 server id 1  end_log_pos 1110 CRC32 0xf5dacac1     Query    thread_id=4    exec_time=0    error_code=0
SET TIMESTAMP=1577892287/*!*/;
update type set name='zcx大傻逼' where id=3
/*!*/;
# at 1110
#200101 23:24:47 server id 1  end_log_pos 1141 CRC32 0xc7c36777     Xid = 20
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

ROW

在mysql5.6版本后新增了binlog_row_image参数来设置ROW格式日志的记录方式.bingo_row_image = FULL | MINIMAL | NOBLOB.其中默认格式为FULL

FULL(默认):记录被修改一行数据的所有列,包括没有修改的列也会被记录.
MINIMAL:记录被修改的列.
NOBLOB:和FULL相似,没有对数据类型为BLOB和TEXT进行修改就不会记录这两列的内容 .

FULL

-- 设置二进制文件日志格式
set session binlog_format=row;
-- 验证是否修改成功
show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
-- 刷新日志 | 生成mysql-bin.000006
flush logs;
-- 查看记录行格式的方式
show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
-- 执行操作
use angel
create table type1(id int(2),name(13));
insert into type1 values (1,'二滑大魔王'),(2,'二滑小天使'),(3,'zcx小傻逼');
update type1 set name='zcx大傻逼' where id=3;
alter table type1 add content text;
insert into type1 values(3,'row类型','oo');
select * from type1;
+------+-----------------+---------+
| id   | name            | content |
+------+-----------------+---------+
|    1 | 二滑大魔王        | NULL    |
|    2 | 二滑小天使        | NULL    |
|    3 | zcx大傻逼        | NULL    |
|    3 | row类型          | oo      |
+------+-----------------+---------+

查看日志

ROW格式的日志在查看的时候需要加上-vv参数才能转换成能看懂的形式.

cd /data/mysql/
mysqlbinlog -vv mysql-bin.000006

可以看到在修改数据的时候日志记录了修改行的每一列的数据,包括没有进行修改列的修改前后的数据.

### UPDATE `angel`.`type1`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zcx小傻逼' /* VARSTRING(52) meta=52 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zcx大傻逼' /* VARSTRING(52) meta=52 nullable=1 is_null=0 */
# at 919
#200102  1:02:17 server id 1  end_log_pos 950 CRC32 0x2ff24d01     Xid = 40

MINIMAL

-- 设置类型
set session binlog_row_image=minimal;

NOBOLb

-- 设置类型
set session binlog_row_image=noblob;

MIXED

混合日志格式的日子记录,会根据sql语句由系统决定是用段格式还是行格式的日志.

set binlog_format = mixed;

总结

格式对复制的影响:

日志格式	优点	缺点
STATEMENT(段格式)	日志量小,节省IO,不强制要求表定义完全相同	无法保存主从数据一致性(随机生成函)
ROW(行格式,推荐)	保证数据一致性,减少数据库锁的使用	若表结构不相同会中断复制,从服务器无法单独执行触发器

MySQL——二进制文件

类型

STATEMENT

ROW

FULL

MINIMAL

NOBOLb

MIXED

总结

Leave a Comment Cancel reply

ElasticSearch索引简单使用

使用LogStash插件将Mysql数据同步到ElasticSearch

MySQL高可用——MaxScale

ELK——日志可视化(Linux版)

ELK——日志可视化(Mac版)

kubernetes——版本迭代

PHP——编写一个简单框架

EasySwoole的基本使用

ElasticSearch7.1.0使用IK分词器

Linux下源码安装PHP和Swoole

MySQL——二进制文件

类型

STATEMENT

ROW

FULL

MINIMAL

NOBOLb

MIXED

总结