CDB关于记录binlog内容浅析
原创CDB关于记录binlog内容浅析
原创
HappyLau谈云计算
修改于 2019-10-10 20:16:29
修改于 2019-10-10 20:16:29
代码可运行
运行总次数：0
代码可运行
问题背景
有客户咨询到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-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中；
- ROW，基于行的复制(row-based replication, RBR)：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了；
- MIXED，混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式；
3. binlog记录内容分析
回到文章开始提到的内容，执行update语句更新一条不存在的记录是否会记录binlog文件中，我们先创建一张userinfo表，包含id，name，age三个字段来验证
1. 创建userinfo表
MySQL [(none)]> create database 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 database 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 database 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_database=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系列教程目录
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
