基于段的日志格式
基于段的日志格式:binlog_format=STATEMENT
这是mysql5.7之前默认的二进制日志格式,记录的是mysql执行的sql语句
优点
- 日志记录量相对较小,节约磁盘及网络I/O
缺点
- 必须要记录上下文信息,保证在从服务器上执行结果和住服务器上相同
- 对一些非确定性函数无法进行正确复制,比如UUID(), user()等
- 可能造成mysql复制的主备服务器数据不一致
查看当前二进制日志记录格式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
修改记录格式为statement,即改为基于段的格式
mysql> set session binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)
上面是在运行时动态修改 binlog 的格式 。也可以/etc/my.cnf中修改二进制日志记录格式,直接添加binlog_format=statement即可。
此时可用查看格式命令来确认一下,是否修改成功
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
查看当前二进制日志信息
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
刷新日志,通过这个操作会产生一个新的log文件
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
4 rows in set (0.00 sec)
现在进行一个测试操作,创建一个新的数据库及表,并插入更新一些数据。
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t(id int, c1 varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t values(1, 'aa'),(2, 'bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update t set c1='dd' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这个时候可以进入存放日志的文件夹进行查看二进制日志的具体内容, 不同的操作系统存放路径不同。我的路径在/var/lib/mysql 目录下。
刚才的操作已经写入新生成的binlog文件中,使用mysqlbinlog命令打开最新的log文件
mysqlbinlog mysql-bin.000004
在此文件中可以清晰的查看到我们所操作过的sql语句
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200208 16:17:57 server id 1 end_log_pos 123 CRC32 0xa4adfac4 Start: binlog v 4, server v 5.7.27-log created 200208 16:17:57
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
tW4+Xg8BAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcT6raQ=
'/*!*/;
# at 123
#200208 16:17:57 server id 1 end_log_pos 154 CRC32 0xb68d7c78 Previous-GTIDs
# [empty]
# at 154
#200208 16:18:51 server id 1 end_log_pos 219 CRC32 0xb49e97f4 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200208 16:18:51 server id 1 end_log_pos 313 CRC32 0x4f941ba5 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581149931/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 313
#200208 16:19:15 server id 1 end_log_pos 378 CRC32 0xc8467f01 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 378
#200208 16:19:15 server id 1 end_log_pos 490 CRC32 0xb1954974 Query thread_id=3 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1581149955/*!*/;
create table t(id int, c1 varchar(10))
/*!*/;
# at 490
#200208 16:19:28 server id 1 end_log_pos 555 CRC32 0x9d27e5a0 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 555
#200208 16:19:28 server id 1 end_log_pos 634 CRC32 0xc7c62b80 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581149968/*!*/;
BEGIN
/*!*/;
# at 634
#200208 16:19:28 server id 1 end_log_pos 747 CRC32 0x22abcbc8 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581149968/*!*/;
insert into t values(1, 'aa'),(2, 'bb')
/*!*/;
# at 747
#200208 16:19:28 server id 1 end_log_pos 778 CRC32 0x45ca9f4a Xid = 19
COMMIT/*!*/;
# at 778
#200208 16:19:40 server id 1 end_log_pos 843 CRC32 0x9344a047 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 843
#200208 16:19:40 server id 1 end_log_pos 922 CRC32 0x8a5aae3a Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581149980/*!*/;
BEGIN
/*!*/;
# at 922
#200208 16:19:40 server id 1 end_log_pos 1027 CRC32 0xd5a6f93f Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581149980/*!*/;
update t set c1='dd' where id=1
/*!*/;
# at 1027
#200208 16:19:40 server id 1 end_log_pos 1058 CRC32 0x6f824b2a 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*/;
基于行的日志格式
基于行的日志格式 binlog_format=ROW
这是mysql5.7之后默认的二进制日志格式,记录的是增删改查的数据行信息
优点
- 使mysql主从复制更加安全
- 对每一行数据的修改比基于段的复制高效
- 由于误操作修改数据库信息,且没有备库可恢复时,可通过对日志文件数据操作反向处理恢复数据
缺点
记录日志量较大,ROW格式下又有三种模式来控制ROW格式记录日志的方式,binlog_row_image =[FULL|MINIMAL|NOBLOB]
FULL:数据修改时会记录一行数据所有列的内容,无论这些列是否被修改过。比如某张表有20列,只对其中一列进行了update操作,二进制日志会记录一行中所有20列被修改前与修改后的所有内容。
MINIMAL:比如某张表有20列,只对其中一列进行了update操作,二进制日志只会记录这一列被修改前与修改后的内容。
NOBLOB: 和FULL方式相似,仅仅是当blog或text这些列没有进行修改时,不会记录这些属性的列 。
切换日志格式并刷新日志
mysql> set session binlog_format=row;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
查看binlog_row_image参数默认值
mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+
为方便演示NOBLOB参数效果,修改数据库表信息,增加一列text字段
mysql> alter table t add c2 text;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+------+------+
| id | c1 | c2 |
+------+------+------+
| 1 | dd | NULL |
| 2 | bb | NULL |
+------+------+------+
2 rows in set (0.00 sec)
随意执行一些sql操作
mysql> insert into t values(3, 'haha', 'llala');
Query OK, 1 row affected (0.01 sec)
mysql> delete from t where id=1;
Query OK, 1 row affected (0.01 sec)
单纯的使用mysqlbinlog命令,我们无法直观的看懂日志中保存的信息,所以加参数打开
mysqlbinlog -vv mysql-bin.000005
文件内容如下
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200208 16:59:38 server id 1 end_log_pos 123 CRC32 0xb1c00252 Start: binlog v 4, server v 5.7.27-log created 200208 16:59:38
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
eng+Xg8BAAAAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVICwLE=
'/*!*/;
# at 123
#200208 16:59:38 server id 1 end_log_pos 154 CRC32 0x0fc391c3 Previous-GTIDs
# [empty]
# at 154
#200208 17:01:29 server id 1 end_log_pos 219 CRC32 0x875df050 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200208 17:01:29 server id 1 end_log_pos 318 CRC32 0x98107fd5 Query thread_id=3 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1581152489/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
alter table t add c2 text
/*!*/;
# at 318
#200208 17:02:30 server id 1 end_log_pos 383 CRC32 0x8289b5b2 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 383
#200208 17:02:30 server id 1 end_log_pos 455 CRC32 0x875b090d Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581152550/*!*/;
BEGIN
/*!*/;
# at 455
#200208 17:02:30 server id 1 end_log_pos 504 CRC32 0x96aa5684 Table_map: `test`.`t` mapped to number 100
# at 504
#200208 17:02:30 server id 1 end_log_pos 556 CRC32 0x0c0110c1 Write_rows: table id 100 flags: STMT_END_F
BINLOG '
Jnk+XhMBAAAAMQAAAPgBAAAAAGQAAAAAAAEABHRlc3QAAXQAAwMP/AMKAAIHhFaqlg==
Jnk+Xh4BAAAANAAAACwCAAAAAGQAAAAAAAEAAgAD//gDAAAABGhhaGEFAGxsYWxhwRABDA==
'/*!*/;
### INSERT INTO `test`.`t`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='haha' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='llala' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
# at 556
#200208 17:02:30 server id 1 end_log_pos 587 CRC32 0xd80d63f6 Xid = 31
COMMIT/*!*/;
# at 587
#200208 17:02:38 server id 1 end_log_pos 652 CRC32 0xc1b90a72 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 652
#200208 17:02:38 server id 1 end_log_pos 724 CRC32 0x3117d0b6 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581152558/*!*/;
BEGIN
/*!*/;
# at 724
#200208 17:02:38 server id 1 end_log_pos 773 CRC32 0x4c828282 Table_map: `test`.`t` mapped to number 100
# at 773
#200208 17:02:38 server id 1 end_log_pos 816 CRC32 0x613d473f Delete_rows: table id 100 flags: STMT_END_F
BINLOG '
Lnk+XhMBAAAAMQAAAAUDAAAAAGQAAAAAAAEABHRlc3QAAXQAAwMP/AMKAAIHgoKCTA==
Lnk+XiABAAAAKwAAADADAAAAAGQAAAAAAAEAAgAD//wBAAAAAmRkP0c9YQ==
'/*!*/;
### DELETE FROM `test`.`t`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='dd' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
# at 816
#200208 17:02:38 server id 1 end_log_pos 847 CRC32 0x8258a233 Xid = 32
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*/;
这时,我们就可以找到我们所执行过的sql语句,同时这种格式的日志还记录了修改过的每一行数据的信息。
执行一个update语句
mysql> update t set c2='this 2 full' where id=2;
Query OK, 1 row affected (0.01 sec)
查看binlog记录内容,这里只查看新增的部分
'/*!*/;
### UPDATE `test`.`t`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='bb' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='this 2' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='bb' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='this 2 full' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
# at 1376
#200208 17:14:59 server id 1 end_log_pos 1407 CRC32 0xef7a5c98 Xid = 36
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*/;
可以看到,update语句只修改了c2字段,但是其它字段也被记录下来了。
继续 修改binlog_row_image参数值,更新一条记录
mysql> set session binlog_row_image=minimal;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set c2='this 2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看binlog记录内容,这里只查看新增的部分
# at 847
#200208 17:08:00 server id 1 end_log_pos 912 CRC32 0xb23b3882 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 912
#200208 17:08:00 server id 1 end_log_pos 984 CRC32 0xe3ad7e44 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1581152880/*!*/;
BEGIN
/*!*/;
# at 984
#200208 17:08:00 server id 1 end_log_pos 1033 CRC32 0x355dfc96 Table_map: `test`.`t` mapped to number 100
# at 1033
#200208 17:08:00 server id 1 end_log_pos 1086 CRC32 0xb0ab22b7 Update_rows: table id 100 flags: STMT_END_F
BINLOG '
cHo+XhMBAAAAMQAAAAkEAAAAAGQAAAAAAAEABHRlc3QAAXQAAwMP/AMKAAIHlvxdNQ==
cHo+Xh8BAAAANQAAAD4EAAAAAGQAAAAAAAEAAgAD/wT8AgAAAAJiYv4GAHRoaXMgMrciq7A=
'/*!*/;
### UPDATE `test`.`t`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='bb' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
### SET
### @3='this 2' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
# at 1086
#200208 17:08:00 server id 1 end_log_pos 1117 CRC32 0x4a893d22 Xid = 34
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*/;
继续修改binlog_row_image参数值,更新一条记录
mysql> set session binlog_row_image=noblob;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set c1='woqu' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看看binlog文件记录内容,从记录内容可以发现,日志只记录了第一列和第二列的数据,并没有记录第三列text属性的值
'/*!*/;
### UPDATE `test`.`t`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='haha' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='llala' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='woqu' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
# at 1656
#200208 17:18:30 server id 1 end_log_pos 1687 CRC32 0xfda73f53 Xid = 38
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*/;
混合日志格式
混合日志格式 binlog_format=MIXED
根据sql语句由系统决定在基于段和基于行的日志格式中进行选择
总结
在选择二进制日志格式时,通常选择基于行或者混合日志格式。然而对于数据复制安全性要求高的情况下,更建议使用基于行的日志格式。另外,如果选择这种格式,务必设置binlog_row_image=minimal。