|
关于 binlog 是什么，以及作用、格式等基本信息，可以查看上一篇文章关于MySQL binlog二进制日志。
本文主要介绍增、删、改操作时，具体记录的binlog格式。
binlog 格式有三种：statement, row,以及mixed，下文基于row的格式进行说明。
首先，使用flush logs命令，新建binlog 文件。
mysql >show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2
1 row in set (0.00 sec)
mysql >flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql >show master status\G
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2
1 row in set (0.00 sec)
查看当前服务的 server uuid
mysql >select @@server_uuid;
+
| @@server_uuid |
+
| ab53b640-a1a5-11eb-936b-0a580abd9262 |
+
1 row in set (0.00 sec)
创建db
创建db school：
create database school;
接着查看binlog。
查看binlog，不能使用vim 等文本编辑工具，需要使用mysql或 mysqlbinlog解析后查看。
# mysqlbinlog -vvv mysql-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211226 22:02:43 server id 189146098 end_log_pos 123 CRC32 0xf2ce8fbb Start: binlog v 4, server v 5.7.26-29-log created 211226 22:02:43
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
A3bIYQ/yI0YLdwAAAHsAAAABAAQANS43LjI2LTI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbuPzvI=
'/*!*/;
# at 123
#211226 22:02:43 server id 189146098 end_log_pos 194 CRC32 0x0b053ce1 Previous-GTIDs
# ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2
# at 194
#211226 22:11:40 server id 189146098 end_log_pos 259 CRC32 0x4c0611d1 GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:1'/*!*/;
# at 259
#211226 22:11:40 server id 189146098 end_log_pos 359 CRC32 0x43e3e3ea Query thread_id=97708 exec_time=0 error_code=0
SET TIMESTAMP=1640527900/*!*/;
SET @@session.pseudo_thread_id=97708/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
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=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database school
/*!*/;
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*/;
从上面的解析结果可以得到以下信息：
# at 4 - 123 文件开头的第一个event FORMAT_DESCRIPTION_EVENT，定义了binlog格式版本等信息。
# at 123 - 194 声明了前面定义的GTID(event类型 Previous-GTIDs)，这里是
ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2。每个文件的开头都有一个PREVIOUS_GTIDS_LOG_EVENT
# at 194 - 259 定义当前的GTID(event类型GTID)。
# at 259 - 359 是创建db的SQL(event类型Query)。
创建表
建表SQL:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`age` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
对应的binlog
# at 359
#211226 22:25:53 server id 189146098 end_log_pos 424 CRC32 0xb659ddb1 GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:2'/*!*/;
# at 424
#211226 22:25:53 server id 189146098 end_log_pos 753 CRC32 0xa0440382 Query thread_id=97708 exec_time=0 error_code=0
use `school`/*!*/;
SET TIMESTAMP=1640528753/*!*/;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`age` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4
/*!*/;
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*/;
从上面的解析结果可以得到以下信息：
# at 359 - 424 定义当前的GTID(event类型GTID)。
# at 424 - 753 定义创建表SQL(event类型Query)。
插入数据
插入语句SQL：
insert into student(name, age) values('Kirk', 12);
对应的binlog
# at 753
#211226 22:35:12 server id 189146098 end_log_pos 818 CRC32 0x42b8da60 GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:3'/*!*/;
# at 818
#211226 22:35:12 server id 189146098 end_log_pos 892 CRC32 0xd00763cc Query thread_id=97708 exec_time=0 error_code=0
SET TIMESTAMP=1640529312/*!*/;
BEGIN
/*!*/;
# at 892
#211226 22:35:12 server id 189146098 end_log_pos 965 CRC32 0xf39d9aaf Rows_query
# insert into student(name, age) values('Kirk', 12)
# at 965
#211226 22:35:12 server id 189146098 end_log_pos 1021 CRC32 0xd5989628 Table_map: `school`.`student` mapped to number 109
# at 1021
#211226 22:35:12 server id 189146098 end_log_pos 1067 CRC32 0x87226373 Write_rows: table id 109 flags: STMT_END_F
BINLOG '
oH3IYR3yI0YLSQAAAMUDAACAADFpbnNlcnQgaW50byBzdHVkZW50KG5hbWUsIGFnZSkgdmFsdWVz
KCdLaXJrJywgMTIpr5qd8w==
oH3IYRPyI0YLOAAAAP0DAAAAAG0AAAAAAAEABnNjaG9vbAAHc3R1ZGVudAADAw8BArQAACiWmNU=
oH3IYR7yI0YLLgAAACsEAAAAAG0AAAAAAAEAAgAD//gJAAAABEtpcmsMc2Mihw==
'/*!*/;
### INSERT INTO `school`.`student`
### SET
### @1=9 /* INT meta=0 nullable=0 is_null=0 */
### @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
### @3=12 /* TINYINT meta=0 nullable=0 is_null=0 */
# at 1067
#211226 22:35:12 server id 189146098 end_log_pos 1098 CRC32 0x216f2a1f Xid = 4030264
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*/;
从上面的解析结果可以得到以下信息：
# at 753 - 818 定义当前的GTID(event类型GTID)。
# at 818 - 892 声名事务的开始语句BEGIN(event类型Query)。
# at 892 - 965 定义insert语句(event类型Rows_query)。
# at 965 - 1021 定义表映射(event类型Table_map)。
# at 1021 - 1067 定义insert ROWS_EVENT(event类型Write_rows)。
# at 1067 - 1098 定义事务的结束语句COMMIT(event类型Xid)。
对于ROW格式的binlog，所有的DML语句都是记录在ROWS_EVENT中。
ROWS_EVENT分为三种：
- WRITE_ROWS_EVENT，对应insert操作，包含了要插入的数据
- UPDATE_ROWS_EVENT，对应update操作，包括了修改前、后的数据
- DELETE_ROWS_EVENT，对应delete操作，包括要删除的数据
对于QUERY_EVENT事件，是以文本形式记录DML操作的。而对于ROWS_EVENT事件，不是以文本格式记录的，这也是会使用mysqlbinlog解析的原因。
更新数据
更新SQL:
>update student set age=13 where name='Kirk';
对应的binlog
# at 1098
#211226 23:33:03 server id 189146098 end_log_pos 1163 CRC32 0xb8d0196a GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:4'/*!*/;
# at 1163
#211226 23:33:03 server id 189146098 end_log_pos 1237 CRC32 0x961aeabd Query thread_id=97708 exec_time=0 error_code=0
SET TIMESTAMP=1640532783/*!*/;
BEGIN
/*!*/;
# at 1237
#211226 23:33:03 server id 189146098 end_log_pos 1304 CRC32 0xde123000 Rows_query
# update student set age=13 where name='Kirk'
# at 1304
#211226 23:33:03 server id 189146098 end_log_pos 1360 CRC32 0x78aa0f0d Table_map: `school`.`student` mapped to number 109
# at 1360
#211226 23:33:03 server id 189146098 end_log_pos 1418 CRC32 0x9f7989f9 Update_rows: table id 109 flags: STMT_END_F
BINLOG '
L4vIYR3yI0YLQwAAABgFAACAACt1cGRhdGUgc3R1ZGVudCBzZXQgYWdlPTEzIHdoZXJlIG5hbWU9
J0tpcmsnADAS3g==
L4vIYRPyI0YLOAAAAFAFAAAAAG0AAAAAAAEABnNjaG9vbAAHc3R1ZGVudAADAw8BArQAAA0Pqng=
L4vIYR/yI0YLOgAAAIoFAAAAAG0AAAAAAAEAAgAD///4CQAAAARLaXJrDPgJAAAABEtpcmsN+Yl5
nw==
'/*!*/;
### UPDATE `school`.`student`
### WHERE
### @1=9 /* INT meta=0 nullable=0 is_null=0 */
### @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
### @3=12 /* TINYINT meta=0 nullable=0 is_null=0 */
### SET
### @1=9 /* INT meta=0 nullable=0 is_null=0 */
### @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
### @3=13 /* TINYINT meta=0 nullable=0 is_null=0 */
# at 1418
#211226 23:33:03 server id 189146098 end_log_pos 1449 CRC32 0x0b69970c Xid = 4069613
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*/;
从上面的解析结果可以得到以下信息：
# at 1098 - 1163 定义当前的GTID(event类型GTID)。
# at 1163 - 1237 声名事务的开始语句BEGIN(event类型Query)。
# at 1237 - 1304 定义update语句(event类型Rows_query)。
# at 1304 - 1360 定义表映射(event类型Table_map)。
# at 1360 - 1418 定义update ROWS_EVENT(event类型Write_rows)。
# at 1418 - 1449 定义事务的结束语句COMMIT(event类型Xid)。
删除数据
删除SQL:
mysql > delete from student where name='Kirk';
对应的binlg
# at 1449
#211226 23:43:40 server id 189146098 end_log_pos 1514 CRC32 0x1247a93c GTID last_committed=4 sequence_number=5 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:5'/*!*/;
# at 1514
#211226 23:43:40 server id 189146098 end_log_pos 1588 CRC32 0x7bf526b3 Query thread_id=97708 exec_time=0 error_code=0
SET TIMESTAMP=1640533420/*!*/;
BEGIN
/*!*/;
# at 1588
#211226 23:43:40 server id 189146098 end_log_pos 1649 CRC32 0x559aca06 Rows_query
# delete from student where name='Kirk'
# at 1649
#211226 23:43:40 server id 189146098 end_log_pos 1705 CRC32 0x5d32bd8c Table_map: `school`.`student` mapped to number 109
# at 1705
#211226 23:43:40 server id 189146098 end_log_pos 1751 CRC32 0xed427cf3 Delete_rows: table id 109 flags: STMT_END_F
BINLOG '
rI3IYR3yI0YLPQAAAHEGAACAACVkZWxldGUgZnJvbSBzdHVkZW50IHdoZXJlIG5hbWU9J0tpcmsn
BsqaVQ==
rI3IYRPyI0YLOAAAAKkGAAAAAG0AAAAAAAEABnNjaG9vbAAHc3R1ZGVudAADAw8BArQAAIy9Ml0=
rI3IYSDyI0YLLgAAANcGAAAAAG0AAAAAAAEAAgAD//gJAAAABEtpcmsN83xC7Q==
'/*!*/;
### DELETE FROM `school`.`student`
### WHERE
### @1=9 /* INT meta=0 nullable=0 is_null=0 */
### @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
### @3=13 /* TINYINT meta=0 nullable=0 is_null=0 */
# at 1751
#211226 23:43:40 server id 189146098 end_log_pos 1782 CRC32 0x3e95500a Xid = 4076811
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*/;
从上面的解析结果可以得到以下信息：
# at 1449 - 1514 定义当前的GTID(event类型GTID)。
# at 1514 - 1588 声名事务的开始语句BEGIN(event类型Query)。
# at 1588 - 1649 定义delete语句(event类型Rows_query)。
# at 1649 - 1705 定义表映射(event类型Table_map)。
# at 1705 - 1751 定义delete ROWS_EVENT(event类型Delete_rows)。
# at 1751 - 1782 定义事务的结束语句COMMIT(event类型Xid)。
以上，本文主要介绍了基本的增、删、改操作对应的binlog格式。
一个事务是由很多event组成的，其中开始event是BEGIN，结束event是COMMIT(event 类型XID)。
在基于主从复制的架构中，如果COMMIT在传输到从库的过程中丢失，会出现什么问题呢？后面有时间再具体说明这个问题。
参考
MySQL binlog中的事件类型
|