问题背景
有客户咨询到update一条没有记录的数据是否会记录到binlog文件中，按照不同的binlog_format记录的方式有所不同，STATEMENT记录完整的SQL语句，ROW格式记录变化内容，本文来探究CDB binlog相关问题
1. binlog是什么
binlog是MySQL二进制文件，用于记录数据库的变化情况如UPDATE，DELETE，INSERT，CREATE等操作，binlog的主要用途：1. 数据恢复，冷备文件+binlog实现实时的数据恢复，2. 主从复制，通过记录binlog文件和回放relay log实现数据库的主从复制。
2. binlog格式
binlog有三种格式：ROW，STATEMENT和MIXED，每种格式有不同的特点
- STATEMENT，基于SQL语句的复制(statement- d replication, SBR)，每一条会修改数据的sql语句会记录到binlog中；
- ROW，基于行的复制(row- d replication, RBR)：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了；
- MIXED，混合模式复制(mixed- d replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式；
3. binlog记录内容分析
回到文章开始提到的内容，执行update语句更新一条不存在的记录是否会记录binlog文件中，我们先创建一张userinfo表，包含id，name，age三个字段来验证
1. 创建userinfo表
MySQL [(none)]> create data demo; Query OK, 1 row affected (0.05 sec) MySQL [(none)]> create table demo.userinfo(id int not null primary key,name varchar(64) not null default '',age tinyint unsigned not null default 0); Query OK, 0 rows affected (0.07 sec) MySQL [(none)]> show create table demo.userinfo; +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | userinfo | CREATE TABLE `userinfo` ( `id` int(11) NOT NULL, `name` varchar(64) NOT NULL DEFAULT '', `age` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.05 sec)
2. 查看binlog内容，有两种方式查看：1. 在CDB内部查看，2. 将binlog下载通过mysqlbinlog命令解析查看，此处我们采用第一种方式
MySQL [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 1153 | | mysql-bin.000003 | 1545 | +------------------+-----------+ 2 rows in set (0.06 sec) MySQL [(none)]> show binlog events in 'mysql-bin.000003'; +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000003 | 1169 | Gtid | 113707 | 1213 | SET @@SESSION.GTID_NEXT= '88bc3908-cf83-11e9-ac05-6c92bf621508:13' | | mysql-bin.000003 | 1213 | Query | 113707 | 1303 | create data demo | | mysql-bin.000003 | 1303 | Gtid | 113707 | 1347 | SET @@SESSION.GTID_NEXT= '88bc3908-cf83-11e9-ac05-6c92bf621508:14' | | mysql-bin.000003 | 1347 | Query | 113707 | 1545 | create table demo.userinfo(id int not null primary key,name varchar(64) not null default '',age tinyint unsigned not null default 0) | +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
3. 往表中insert一条数据
MySQL [(none)]> insert into demo.userinfo() values(1,'张三',30); Query OK, 1 row affected (0.01 sec)
4. 执行update语句，设置更新条件为where id=0，即一条不存在的数据，观察binlog pos的变化
MySQL [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 561 | | mysql-bin.000003 | 2809 | +------------------+-----------+ 2 rows in set (0.01 sec) MySQL [(none)]> update demo.userinfo set name='李四' where id=0; Query OK, 0 rows affected (0.01 sec) Rows matched: 0 Changed: 0 Warnings: 0 #受影响的数据为0条 MySQL [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 561 | | mysql-bin.000003 | 2809 | #binlog大小没有改变 +------------------+-----------+ 2 rows in set (0.01 sec) MySQL [(none)]> show binlog events in 'mysql-bin.000003'; #binlog文件中没有记录变化 +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 151951 | 123 | Server ver: 5.7.18-txsql-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 151951 | 230 | 33361554-dec5-11e9-b92f-246e96754b22:1-3, 48db5c40-cd1b-11e9-b19c-6c92bf5c366e:30 | | mysql-bin.000003 | 230 | Gtid | 151951 | 291 | SET @@SESSION.GTID_NEXT= '33361554-dec5-11e9-b92f-246e96754b22:4' | | mysql-bin.000003 | 291 | Query | 151951 | 458 | ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*5719ABB5520BA7056A0D8B15A9ECA6DE85D5A773' | | mysql-bin.000003 | 458 | Gtid | 151951 | 519 | SET @@SESSION.GTID_NEXT= '33361554-dec5-11e9-b92f-246e96754b22:5' | | mysql-bin.000003 | 519 | Query | 151951 | 583 | BEGIN | | mysql-bin.000003 | 583 | Table_map | 151951 | 751 | table_id: 398 (mysql.user) | | mysql-bin.000003 | 751 | Update_rows | 151951 | 1943 | table_id: 398 flags: STMT_END_F | | mysql-bin.000003 | 1943 | Query | 151951 | 2008 | COMMIT | | mysql-bin.000003 | 2008 | Gtid | 151951 | 2069 | SET @@SESSION.GTID_NEXT= '33361554-dec5-11e9-b92f-246e96754b22:6' | | mysql-bin.000003 | 2069 | Query | 151951 | 2152 | flush privileges | | mysql-bin.000003 | 2152 | Gtid | 151951 | 2213 | SET @@SESSION.GTID_NEXT= '33361554-dec5-11e9-b92f-246e96754b22:7' | | mysql-bin.000003 | 2213 | Query | 151951 | 2303 | create data demo | | mysql-bin.000003 | 2303 | Gtid | 151951 | 2364 | SET @@SESSION.GTID_NEXT= '33361554-dec5-11e9-b92f-246e96754b22:8' | | mysql-bin.000003 | 2364 | Query | 151951 | 2562 | create table demo.userinfo(id int not null primary key,name varchar(64) not null default '',age tinyint unsigned not null default 0) | | mysql-bin.000003 | 2562 | Gtid | 151951 | 2623 | SET @@SESSION.GTID_NEXT= '33361554-dec5-11e9-b92f-246e96754b22:9' | | mysql-bin.000003 | 2623 | Query | 151951 | 2687 | BEGIN | | mysql-bin.000003 | 2687 | Table_map | 151951 | 2738 | table_id: 507 (demo.userinfo) | | mysql-bin.000003 | 2738 | Write_rows | 151951 | 2782 | table_id: 507 flags: STMT_END_F | | mysql-bin.000003 | 2782 | Xid | 151951 | 2809 | COMMIT /* xid=1815277 */ | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------+ 20 rows in set (0.01 sec) MySQL [(none)]>
5. 查看binlog_format的格式，此时为ROW格式
MySQL [(none)]> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec)
小结：通过上面的验证可知，当CDB的binlog格式为ROW时，更新一条不存在记录时没有记录到binlog中，由于ROW格式记录的是数据的变化情况，由于数据没有变化，因此没有记录。
6. 修改binlog_format格式为MIXED模式，注：set的修改访问是临时生效，适用于当前会话，永久配置请在控制台参数修改处修改，修改完毕后重新登录。
MySQL [demo]> set binlog_format=MIXED; Query OK, 0 rows affected (0.01 sec) MySQL [demo]> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.02 sec)
7. MIXED模式下默认会以STATEMENT的方式写入数据，和上面步骤类似，执行一条不存在记录更新操作语句，查看binlog文件内容
MySQL [demo]> update demo.userinfo set name='李四' where id=0; Query OK, 0 rows affected (0.01 sec) Rows matched: 0 Changed: 0 Warnings: 0 #有记录对应的SQL语句 MySQL [demo]> show binlog events in 'mysql-bin.000003'; +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------+ | | mysql-bin.000003 | 2809 | Gtid | 151951 | 2870 | SET @@SESSION.GTID_NEXT= '33361554-dec5-11e9-b92f-246e96754b22:10' | | mysql-bin.000003 | 2870 | Query | 151951 | 2945 | BEGIN | | mysql-bin.000003 | 2945 | Query | 151951 | 3064 | use `demo`; update demo.userinfo set name='李四' where id=0 | | mysql-bin.000003 | 3064 | Query | 151951 | 3140 | COMMIT | +------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------+ 24 rows in set (0.02 sec) MySQL [demo]>
4. binlog如何解析
binlog是一个二进制文件，解析binlog文件分为两种：
- 在CDB内部，通过show binlog events in 'binlog-filename';方式查看
- 下载分析binlog，安装mysqlbinlog工具进行解析
mysqlbinlog工具使用说明：
[root@node-1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000004 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191010 20:12:11 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.64-MariaDB created 191010 20:12:11 # Warning: this binlog is either in use or was not closed properly. BINLOG ' GyCfXQ8BAAAA8QAAAPUAAAABAAQANS41LjY0LU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAPJIFEQ== '/*!*/; # at 245 #191010 20:13:31 server id 1 end_log_pos 312 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1570709611/*!*/; 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=0/*!*/; 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_data =DEFAULT/*!*/; BEGIN /*!*/; # at 312 #191010 20:13:31 server id 1 end_log_pos 420 Query thread_id=3 exec_time=0 error_code=0 use `abc`/*!*/; SET TIMESTAMP=1570709611/*!*/; #执行的时间戳，用于数据恢复 insert into username() values(2,'happylau',22) #STATEMENT格式下记录完整的SQL语句 /*!*/; # at 420 #191010 20:13:31 server id 1 end_log_pos 447 Xid = 36 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
总结
1. ROW格式记录文件的变化情况，有变化则记录，没有变化则不记录
2. STATEMENT记录是完整的SQL语句，不管数据是否有变化都会记录
当你的才华撑不起你的野心时，你就应该静下心来学习
更多返回kubernetes系列教程目录
继续阅读与本文标签相同的文章
上一篇 :
python3中函数参数的四种简单用法
下一篇 :
Android添加指纹解锁功能的实现代码
-
Daydream成弃子 “造梦”救不了移动VR
2026-05-15栏目： 教程
-
史基浦机场推出自己的物联网应用网络
2026-05-15栏目： 教程
-
看懂打印机“红绿灯”，文印轻松上路！
2026-05-15栏目： 教程
-
遛店：新零售万亿市场开启新消费时代
2026-05-15栏目： 教程
-
澳大利亚大学将使用VR和AR技术为学生教授解剖学
2026-05-15栏目： 教程