【.NET 与树莓派】MPD 的 Mini-API
871 2023-04-03 03:48:30
前言:binlog 用于记录数据库执行写入性操作的日志信息,以二进制的形式保留在磁盘中。它是由 Server 层进行记录的,使用任何存储引擎都会产生 binlog。
我们通过 Docker 来对 MySQL binlog 进行一系列的研究。需要进行一些准备工作:
$ docker pull mysql:5.7
$ mkdir -p infra/mysql$ cd infra/mysql# 挂载目录$ mkdir data# 配置文件$ mkdir mysql.conf.d && cd mysql.conf.d$ echo "[mysqld]# 设置binlog的格式binlog_format = statement# 设置日志存储路径log-bin=/var/lib/mysql/mysql-binserver-id=12345" > mysqld.cnf
# 启动容器$ docker run -p3306:3306 -d --name mysql57 \-v /Users/zioyi/infra/mysql/mysql.conf.d:/etc/mysql/conf.d \-v /Users/zioyi/infra/mysql/data:/var/lib/mysql \-e MYSQL_ROOT_PASSWORD=root --restart=always mysql:5.6# 进入容器$ docker exec -it mysql57 sh$ [mysql57] mysql -uroot -prootmysql > show variable like "%log_bin%";+---------------------------------+--------------------------------+| Variable_name | Value |+---------------------------------+--------------------------------+| log_bin | ON || log_bin_basename | /var/lib/mysql/mysql-bin || log_bin_index | /var/lib/mysql/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+--------------------------------+6 rows in set (0.00 sec)
mysql > create database mall character set utf8mb4 collate utf8mb4_unicode_ci;Query OK, 1 row affected (0.01 sec)mysql > use mall;Database changedmysql > CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT '' COMMENT '商品名称', `price` int(10) unsigned DEFAULT '0' COMMENT '价格', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品清单';
基于 SQL 语句的复制(statment-based replication,SBR),每一条会修改数据的 SQL 语句都会复制到 binlog 中。
我们将 bin_log 的模式设置成 STATMENT,之后重启容器
$ echo "[mysqld]# 设置binlog的格式binlog_format = statement# 设置日志存储路径log-bin=/var/lib/mysql/mysql-binserver-id=12345" > mysqld.cnf
在数据库中插入一条数据
mysql> insert into item(name, price) values("book", 10);Query OK, 1 row affected (0.01 sec)# 知道最新的 binlog 文件进行查看show binlog events in 'mysql-bin.000004';+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| mysql-bin.000004 | 4 | Format_desc | 12345 | 120 | Server ver: 5.6.51-log, Binlog ver: 4 || mysql-bin.000004 | 120 | Query | 12345 | 263 | create database mall character set utf8mb4 collate utf8mb4_unicode_ci || mysql-bin.000004 | 263 | Query | 12345 | 446 | create database blog_service default character set utf8mb4 default collate utf8mb4_general_ci || mysql-bin.000004 | 446 | Query | 12345 | 758 | use `mall`; CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT '' COMMENT '', `price` int(10) unsigned DEFAULT '0' COMMENT '', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='' || mysql-bin.000004 | 758 | Query | 12345 | 837 | BEGIN || mysql-bin.000004 | 837 | Intvar | 12345 | 869 | INSERT_ID=1 || mysql-bin.000004 | 869 | Query | 12345 | 991 | use `mall`; insert into item(name, price) values("book", 10) || mysql-bin.000004 | 991 | Xid | 12345 | 1022 | COMMIT /* xid=15 */ |+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
可以看到,我们的插入已经被记录,我们再往表中多插入几条数据:
mysql > insert into item(name, price) values("fruit", 12);mysql > insert into item(name, price) values("rice", 2);# 向表中加入要给新字段mysql > alter table item add column type smallint default 0;mysql > update item set type = 1 where type = 0;# 在看一下binlog中mysql > show binlog events in 'mysql-bin.000004';| mysql-bin.000004 | 1022 | Query | 12345 | 1101 | BEGIN || mysql-bin.000004 | 1101 | Intvar | 12345 | 1133 | INSERT_ID=2 || mysql-bin.000004 | 1133 | Query | 12345 | 1256 | use `mall`; insert into item(name, price) values("fruit", 12) || mysql-bin.000004 | 1256 | Xid | 12345 | 1287 | COMMIT /* xid=23 */ || mysql-bin.000004 | 1287 | Query | 12345 | 1366 | BEGIN || mysql-bin.000004 | 1366 | Intvar | 12345 | 1398 | INSERT_ID=3 || mysql-bin.000004 | 1398 | Query | 12345 | 1519 | use `mall`; insert into item(name, price) values("rice", 2) || mysql-bin.000004 | 1519 | Xid | 12345 | 1550 | COMMIT /* xid=25 */ || mysql-bin.000004 | 1550 | Query | 12345 | 1675 | use `mall`; alter table item add column type smallint default 0 || mysql-bin.000004 | 1675 | Query | 12345 | 1754 | BEGIN | | mysql-bin.000004 | 1754 | Query | 12345 | 1867 | use `mall`; update item set type = 1 where type = 0 || mysql-bin.000004 | 1867 | Xid | 12345 | 1898 | COMMIT /* xid=31 */ |
可以看到,insert
、alert
、update
等 SQL 语句都会被记录在 binlog 中,并且都和执行时的语句一模一样。
总结一下 STATEMENT 这种方式的优缺点:
基于行的复制(row-based repication,RBR),不记录每天 SQL 语句的上下文信息,仅需记录哪条语句被修改了。
更改 binlog 的模式为 ROW:
mysql > set global binlog_format = ROW;mysql > select @@global.binlog_format;+------------------------+| @@global.binlog_format |+------------------------+| ROW |+------------------------+1 row in set (0.00 sec)# 更新mysql > update item set type = 1 where type = 2;Query OK, 3 rows affected (0.01 sec)Rows matched: 3 Changed: 3 Warnings: 0
对于 STATEMENT 格式的 binlog ,所有的 DML 操作都记录在 QUERY_EVENT 中,所以我们直接通过
show binlog events in 'mysql-bin.000004';
就可以看到;而对于 ROW 格式的 binlog ,所有的 DML 操作都记录在 ROWS_EVENT 中,ROWS_EVENT 分为三种:WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT,分别对应 insert,update 和 delete 操作。我们需要通过 mysqlbinlog 工具来查看
来查看一下 ROW 内容:
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows#211219 15:07:12 server id 12345 end_log_pos 2162 CRC32 0xb0fc9915 Update_rows: table id 73 flags: STMT_END_F### UPDATE `mall`.`item`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=10 /* INT meta=0 nullable=1 is_null=0 */### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */### SET### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=10 /* INT meta=0 nullable=1 is_null=0 */### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */### UPDATE `mall`.`item`### WHERE### @1=2 /* INT meta=0 nullable=0 is_null=0 */### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=12 /* INT meta=0 nullable=1 is_null=0 */### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */### SET### @1=2 /* INT meta=0 nullable=0 is_null=0 */### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=12 /* INT meta=0 nullable=1 is_null=0 */### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */### UPDATE `mall`.`item`### WHERE### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */### SET### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */# at 2162#211219 15:07:12 server id 12345 end_log_pos 2193 CRC32 0x44ee46b2 Xid = 44COMMIT/*!*/;DELIMITER ;
在 ROW 模式中,将update item set type = 1 where type = 2;
语句记录成了对 3 条记录的变更操作,分别是对于每条满足 where 条件的记录的独立 update 语句。
delete 语句也是一样
mysql> update item set type = 1 where id = 3;Query OK, 1 row affected (0.04 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from item ;+----+-------+-------+------+| id | name | price | type |+----+-------+-------+------+| 1 | book | 10 | 2 || 2 | fruit | 12 | 2 || 3 | rice | 2 | 1 |+----+-------+-------+------+3 rows in set (0.00 sec)mysql> delete from item where type = 2;Query OK, 2 rows affected (0.01 sec)
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows### DELETE FROM `mall`.`item`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=10 /* INT meta=0 nullable=1 is_null=0 */### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */### DELETE FROM `mall`.`item`### WHERE### @1=2 /* INT meta=0 nullable=0 is_null=0 */### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=12 /* INT meta=0 nullable=1 is_null=0 */### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */# at 2612#211220 1:18:03 server id 12345 end_log_pos 2643 CRC32 0x435b7a4b Xid = 55COMMIT/*!*/;
再看一下 alter 语句
mysql> alter table item add column updated datetime default CURRENT_TIMESTAMP;
mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows# at 2612#211220 1:18:03 server id 12345 end_log_pos 2643 CRC32 0x435b7a4b Xid = 55COMMIT/*!*/;# at 2643#211220 1:24:43 server id 12345 end_log_pos 2795 CRC32 0x68a196c8 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1639963483/*!*/;SET @@session.time_zone='SYSTEM'/*!*/;alter table item add column updated datetime default CURRENT_TIMESTAMP/*!*/;
binlog 中记录了当前的时间戳 1639963483。
mysql> select sysdate();+---------------------+| sysdate() |+---------------------+| 2021-12-20 01:31:26 |+---------------------+1 row in set (0.00 sec)mysql> update item set updated = sysdate() where id = 3;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from item;+----+------+-------+------+---------------------+| id | name | price | type | updated |+----+------+-------+------+---------------------+| 3 | rice | 2 | 1 | 2021-12-20 01:32:15 |+----+------+-------+------+---------------------+1 row in set (0.00 sec)
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows# at 2875#211220 1:32:15 server id 12345 end_log_pos 2929 CRC32 0x1f777374 Table_map: `mall`.`item` mapped to number 74# at 2929#211220 1:32:15 server id 12345 end_log_pos 3009 CRC32 0x312870a3 Update_rows: table id 74 flags: STMT_END_F### UPDATE `mall`.`item`### WHERE### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 01:24:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */### SET### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */# at 3009#211220 1:32:15 server id 12345 end_log_pos 3040 CRC32 0xb49295bc Xid = 59COMMIT/*!*/;
在 binlog 中,会直接把 sysdate() 当时返回的值记录在 update 语句中。
基于 STATMENT 和 ROW 两种模式的混合复制(mixed-based replication,MBR),一般的复制使用 STATMENT 模式保存 binlog,对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog。
mysql > set global binlog_format = MIXED;mysql > select @@global.binlog_format;+------------------------+| @@global.binlog_format |+------------------------+| ROW |+------------------------+1 row in set (0.00 sec)# 更新mysql > update item set type = 1 where type = 2;Query OK, 3 rows affected (0.01 sec)Rows matched: 3 Changed: 3 Warnings: 0
插入语句1
mysql> insert into item(name, price, type) values("pencil", 1, 3);Query OK, 1 row affected (0.00 sec)
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows#211220 1:39:45 server id 12345 end_log_pos 3233 CRC32 0xfcb23be0 Write_rows: table id 74 flags: STMT_END_F### INSERT INTO `mall`.`item`### SET### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */# at 3233#211220 1:39:45 server id 12345 end_log_pos 3264 CRC32 0x1ee1856c Xid = 62COMMIT/*!*/;
通过 ROW 模式记录,因为 updated 字段是通过 current_timestamp 来得到的
update 语句1
mysql> update item set type = 4;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows### UPDATE `mall`.`item`### WHERE### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */### SET### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */### UPDATE `mall`.`item`### WHERE### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */### SET### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */### UPDATE `mall`.`item`### WHERE### @1=5 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */### SET### @1=5 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */### @5='2021-12-20 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */# at 3782
还是使用 ROW 模式,看来只要表中有default current_timestamp
字段,都是这样的。
我们把 updated 字段删掉,再来更新试试:
mysql> alter table item drop column updated;Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from item;+----+--------+-------+------+| id | name | price | type |+----+--------+-------+------+| 3 | rice | 2 | 4 || 4 | pencil | 1 | 4 || 5 | pencil | 1 | 4 |+----+--------+-------+------+3 rows in set (0.00 sec)mysql> update item set type = 5;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0
mysql> show binlog events in 'mysql-bin.000004';| mysql-bin.000004 | 3813 | Query | 12345 | 3923 | use `mall`; alter table item drop column updated || mysql-bin.000004 | 3923 | Query | 12345 | 4002 | BEGIN || mysql-bin.000004 | 4002 | Query | 12345 | 4100 | use `mall`; update item set type = 5 || mysql-bin.000004 | 4100 | Xid | 12345 | 4131 | COMMIT /* xid=82 */ |
此时就是使用 STATMENT 模式来存储 binlog了。
如果使用系统函数呢
mysql> update item set type = ceil(rand() * 10);Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from item;+----+--------+-------+------+| id | name | price | type |+----+--------+-------+------+| 3 | rice | 2 | 4 || 4 | pencil | 1 | 10 || 5 | pencil | 1 | 8 |+----+--------+-------+------+3 rows in set (0.00 sec)
每行的 type 都不一样,看起来这只能通过 ROW 模式来就记录了吧?来看看 binlog
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows#211220 1:57:57 server id 12345 end_log_pos 4401 CRC32 0x95bf290e Update_rows: table id 75 flags: STMT_END_F### UPDATE `mall`.`item`### WHERE### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */### SET### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=2 /* INT meta=0 nullable=1 is_null=0 */### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */### UPDATE `mall`.`item`### WHERE### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */### SET### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=10 /* SHORTINT meta=0 nullable=1 is_null=0 */### UPDATE `mall`.`item`### WHERE### @1=5 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */### SET### @1=5 /* INT meta=0 nullable=0 is_null=0 */### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */### @3=1 /* INT meta=0 nullable=1 is_null=0 */### @4=8 /* SHORTINT meta=0 nullable=1 is_null=0 */# at 4401
果真如此。
在MySQL 5.7.7之前,默认的格式是STATEMENT,MySQL 5.7.7之后,默认值是ROW。
对于 InnoDB 存储引擎而言,只有在事务提交时才会记录 binlog,此时记录还在内存中,那么 binlog 是什么时候刷到磁盘中的呢?MySQL 通过 sync_binlog 参数控制 binlog 的刷盘时机,取值范围是0-N:
从上面可以看出,sync_binlog 最安全的是设置是 1,这也是 MySQL 5.7.7 之后版本的默认值。但是设置一个大一些的值可以提升数据库性能,因此实际情况下也可以将值适当调大,牺牲一定的一致性来获取更好的性能。
在实际应用中,binlog 的主要使用场景有:
__EOF__