Binlog

MySQL主从节点之间同步数据，备份点还原，会用到binary log，其负责记录数据更改的操作。因为Binlog在运用到数据页之前需要经过复杂的过程，没有redolog直接，所以性能比不上直接使用redo复制的方式（物理复制的优势），但是它也有不可或缺的作用。

最近接到一个任务，是将生产数据的数据同步到测试环境，以便在测试环境做备份以及方便测试进行测试，涉及的表的数量以及规模都是非常的大的，但是有特定的要求，线上数据无条件覆盖测试的冲突的数据，本地现有的不与线上冲突的数据共存，直接使用复制之类的方法不能够实现，通过学习了解到binlog 通过程序模拟从库，来实现更新数据。

记录格式

对于mysql来说主要有三种记录格式

1
2
3
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
  • STATEMENT: 基于语句的日志记录 记录原始的SQL语句，
  • ROW: 基于行的日志记录，记录基于行的改动记录（插入就是插入的数据行，更新就是原始行=>目标行）
  • MIXED：混合日志模式 (前两者混合起来的)

存储

binlog 实实在在的存储在文件系统之中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

| log_bin                                 | ON                                    |
| log_bin_basename                        | /var/lib/mysql/mysql_bin     |
| log_bin_compress                        | OFF                   |
| log_bin_compress_min_len                | 256                  |
| log_bin_index                           | /var/lib/mysql/mysql_bin.index           |

root@7d1202d6ca89:/var/lib/mysql# ls |grep mysql_bin
mysql_bin.000001
mysql_bin.000002
mysql_bin.000003
mysql_bin.000004
mysql_bin.000005
mysql_bin.000006
mysql_bin.index

对于一个文件

1
2
3
4
5
6
7
---------
 固定4字节
---------
   事件
---------
   事件
---------

事件主要由事件头 和事件数据构成

  • 事件头：记录事件创建事件，类型，产生事件的服务器id，事件长度，下一个事件位置，标识符
  • 事件数据：该事件特有信息。

ROW

基于行的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 开启事务
START TRANSACTION
/*!*/;
# at 1227
# at 1287
#240718 15:06:25 server id 1  end_log_pos 1287 CRC32 0x9068fefa     Annotate_rows:
#Q> update user set id=11021 where id=110
#240718 15:06:25 server id 1  end_log_pos 1333 CRC32 0xd3fb4d86     Table_map: `sia`.`user` mapped to number 18
# at 1333
#240718 15:06:25 server id 1  end_log_pos 1377 CRC32 0x4c763da0     Update_rows: table id 18 flags: STMT_END_F

BINLOG '
cS+ZZhMBAAAALgAAADUFAAAAABIAAAAAAAEAA3NpYQAEdXNlcgABAwABhk370w==
cS+ZZhgBAAAALAAAAGEFAAAAABIAAAAAAAEAAf///m4AAAD+DSsAAKA9dkw=
'/*!*/;
# 更新
### UPDATE `sia`.`user`
### WHERE
###   @1=110
### SET
###   @1=11021
# Number of rows: 1
# at 1377
#240718 15:06:25 server id 1  end_log_pos 1408 CRC32 0x949e85b8     Xid = 41
# 提交
COMMIT/*!*/;

STATEMENT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#240718 15:06:00 server id 1  end_log_pos 1050 CRC32 0x1804358d     GTID 0-1-123 trans
/*!100001 SET @@session.gtid_seq_no=123*//*!*/;
# 开启事务
START TRANSACTION
/*!*/;
# at 1050
#240718 15:06:00 server id 1  end_log_pos 1154 CRC32 0x6af8f617     Query	thread_id=4	exec_time=0	error_code=0	xid=0
# 更新语句
SET TIMESTAMP=1721315160/*!*/;
update user set id=110 where id=114515
/*!*/;
# at 1154
#240718 15:06:00 server id 1  end_log_pos 1185 CRC32 0x1d891147     Xid = 19
# 提交
COMMIT/*!*/;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
MariaDB root@127.0.0.1:(none)> show binlog events in 'mysql_bin.000001'
+------------------+------+-------------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name         | Pos  | Event_type        | Server_id | End_log_pos | Info                                                                 |
+------------------+------+-------------------+-----------+-------------+----------------------------------------------------------------------+
| mysql_bin.000001 | 4    | Format_desc       | 1         | 256         | Server ver: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204-log, Binlog ver: 4 |
| mysql_bin.000001 | 256  | Gtid_list         | 1         | 299         | [0-1-118]                                                            |
| mysql_bin.000001 | 299  | Binlog_checkpoint | 1         | 342         | mysql_bin.000001                                                     |
| mysql_bin.000001 | 342  | Gtid              | 1         | 384         | BEGIN GTID 0-1-119                                                   |
| mysql_bin.000001 | 384  | Query             | 1         | 475         | use `sia`; insert into user value(1)                                 |
| mysql_bin.000001 | 475  | Xid               | 1         | 506         | COMMIT /* xid=3 */                                                   |
| mysql_bin.000001 | 506  | Gtid              | 1         | 548         | BEGIN GTID 0-1-120                                                   |
| mysql_bin.000001 | 548  | Query             | 1         | 639         | use `sia`; insert into user value(1)                                 |
| mysql_bin.000001 | 639  | Xid               | 1         | 670         | COMMIT /* xid=14 */                                                  |
| mysql_bin.000001 | 670  | Gtid              | 1         | 712         | BEGIN GTID 0-1-121                                                   |
| mysql_bin.000001 | 712  | Query             | 1         | 808         | use `sia`; insert into user value(114514)                            |
| mysql_bin.000001 | 808  | Xid               | 1         | 839         | COMMIT /* xid=16 */                                                  |
| mysql_bin.000001 | 839  | Gtid              | 1         | 881         | BEGIN GTID 0-1-122                                                   |
| mysql_bin.000001 | 881  | Query             | 1         | 977         | use `sia`; insert into user value(114515)                            |
| mysql_bin.000001 | 977  | Xid               | 1         | 1008        | COMMIT /* xid=18 */                                                  |
| mysql_bin.000001 | 1008 | Gtid              | 1         | 1050        | BEGIN GTID 0-1-123                                                   |
| mysql_bin.000001 | 1050 | Query             | 1         | 1154        | use `sia`; update user set id=110 where id=114515                    |
| mysql_bin.000001 | 1154 | Xid               | 1         | 1185        | COMMIT /* xid=19 */                                                  |
| mysql_bin.000001 | 1185 | Gtid              | 1         | 1227        | BEGIN GTID 0-1-124                                                   |
| mysql_bin.000001 | 1227 | Annotate_rows     | 1         | 1287        | update user set id=11021 where id=110                                |
| mysql_bin.000001 | 1287 | Table_map         | 1         | 1333        | table_id: 18 (sia.user)                                              |
| mysql_bin.000001 | 1333 | Update_rows_v1    | 1         | 1377        | table_id: 18 flags: STMT_END_F                                       |
| mysql_bin.000001 | 1377 | Xid               | 1         | 1408        | COMMIT /* xid=41 */                                                  |
+------------------+------+-------------------+-----------+-------------+----------------------------------------------------------------------+

前半段是STATEMENT后半段是ROW 自己查看不同

问题

对于基于语句STATEMENT的操作，可能会导致数据的不一致问题

比如

1
insert into user（id,name,create_time) values(1,'114514',now());

可能由于时间差或者一些从库特有的数据导致数据的不一致问题

对于后面的主库以及从库两个如何跟踪进度的，对于这部分内容后面再更新

许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

分享文章