转载

记录binlog内容浅析

问题背景

有客户咨询到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)
  1. 查看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)                    |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
  1. 往表中insert一条数据
MySQL [(none)]> insert into demo.userinfo() values(1,'张三',30);
Query OK, 1 row affected (0.01 sec)
  1. 执行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)]>
  1. 查看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格式记录的是数据的变化情况,由于数据没有变化,因此没有记录。

  1. 修改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)
  1. 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语句,不管数据是否有变化都会记录

正文到此结束
本文目录