一、TiDB Binlog功能
TiDB Binlog功能
TiDB binlog提供准实时备份和同步功能，支持以下功能场景：
- 数据同步：同步 TiDB 集群数据到其他数据库
- 实时备份和恢复：备份 TiDB 集群数据，同时可以用于 TiDB 集群故障时恢复
TiDB Binlog 集群主要分为 Pump 和 Drainer 两个组件，以及 binlogctl 工具：
Pump，用于实时记录 TiDB 产生的 Binlog，并将 Binlog 按照事务的提交时间进行排序，再提供给 Drainer 进行消费。
Drainer，从各个 Pump 中收集 Binlog 进行归并，再将 Binlog 转化成 SQL 或者指定格式的数据，最终同步到下游。
binlogctl 工具，获取 TiDB 集群当前的 TSO，查看/修改/下线Pump/Drainer 状态。
如何开启TiDB Binlog
[tidb@tidb53 paul]$ tiup cluster edit-config paul_t
server_configs:
tidb:
binlog.enable: true
binlog.ignore-error: true
二、部署TiDB Binlog集群
场景一：部署TiDB binlog集群，drainer写入mysql数据库
[tidb@tidb53 paul]$ cat scale-out-binlog.toml
pump_servers:
- host: 172.20.12.52
ssh_port: 22
port: 8250
deploy_dir: /data2/tidb-deploy/pump-8250
data_dir: /data2/tidb-data/pump-8250
log_dir: /data2/tidb-deploy/pump-8250/log
arch: amd64
os: linux
drainer_servers:
- host: 172.20.12.70
ssh_port: 22
port: 8249
deploy_dir: /data2/tidb-deploy/drainer-8249
data_dir: /data2/tidb-data/drainer-8249
log_dir: /data2/tidb-deploy/drainer-8249/log
config:
syncer.db-type: mysql
syncer.relay.log-dir: /data2/tidb-data/drainer-8249
syncer.relay.max-file-size: 10485760
syncer.replicate-do-table:
- db-name: test
tbl-name: t1
syncer.to.checkpoint.host: 172.20.12.52
syncer.to.checkpoint.password: root
syncer.to.checkpoint.port: 3900
syncer.to.checkpoint.schema: tidb_binlog
syncer.to.checkpoint.table: checkpoint
syncer.to.checkpoint.type: mysql
syncer.to.checkpoint.user: root
syncer.to.host: 172.20.12.52
syncer.to.password: root
syncer.to.port: 3900
syncer.to.user: root
arch: amd64
os: linux
[tidb@tidb53 paul]$ tiup cluster scale-out paul_t scale-out-binlog.toml
tiup is checking updates for component cluster ...
Starting component `cluster`: /home/tidb/.tiup/components/cluster/v1.14.1/tiup-cluster scale-out paul_t scale-out-binlog.toml
Please confirm your topology:
Cluster type: tidb
Cluster name: paul_t
Cluster version: v6.5.1
Role Host Ports OS/Arch Directories
---- ---- ----- ------- -----------
pump 172.20.12.52 8250 linux/x86_64 /data2/tidb-deploy/pump-8250,/data2/tidb-data/pump-8250
drainer 172.20.12.70 8249 linux/x86_64 /data2/tidb-deploy/drainer-8249,/data2/tidb-data/drainer-8249
Attention:
1. If the topology is not what you expected, check your yaml file.
2. Please confirm there is no port/directory conflicts in same host.
Do you want to continue? [y/N]: (default=N) y
场景二：部署TiDB binlog集群，drainer写入file
[tidb@tidb53 paul]$ cat scale-out-binlogfile.toml
drainer_servers:
- host: 172.20.12.70
ssh_port: 22
port: 8249
deploy_dir: /data2/tidb-deploy/drainer-8249
data_dir: /data2/tidb-data/drainer-8249
log_dir: /data2/tidb-deploy/drainer-8249/log
config:
syncer.db-type: file
syncer.to.retention-time: 15
syncer.relay.log-dir: /data2/tidb-data/drainer-8249
syncer.relay.max-file-size: 10485760
arch: amd64
os: linux
[tidb@tidb53 paul]$ tiup cluster scale-out paul_t scale-out-binlogfile.toml
tiup is checking updates for component cluster ...
Starting component `cluster`: /home/tidb/.tiup/components/cluster/v1.14.1/tiup-cluster scale-out paul_t scale-out-binlogfile.toml
Please confirm your topology:
Cluster type: tidb
Cluster name: paul_t
Cluster version: v6.5.1
Role Host Ports OS/Arch Directories
---- ---- ----- ------- -----------
drainer 172.20.12.70 8249 linux/x86_64 /data2/tidb-deploy/drainer-8249,/data2/tidb-data/drainer-8249
Attention:
1. If the topology is not what you expected, check your yaml file.
2. Please confirm there is no port/directory conflicts in same host.
Do you want to continue? [y/N]: (default=N) y
三、运维TiDB Binlog集群
查看drainer
[tidb@tidb53 paul]$ binlogctl -pd-urls=http://172.20.12.70:2881 -cmd drainers --node-id=172.20.12.70:8249
[INFO] [nodes.go:53] ["query node"] [type=drainer] [node="{NodeID: 172.20.12.70:8249, Addr: 172.20.12.70:8249, State: online, MaxCommitTS: 448124127741739009, UpdateTime: 2024-03-03 17:24:46 +0800 CST}"]
暂停/下线drainer
[tidb@tidb53 paul]$ binlogctl -pd-urls=http://172.20.12.70:2881 -cmd pause-drainer --node-id=172.20.12.70:8249
[INFO] [nodes.go:123] ["Apply action on node success"] [action=pause] [NodeID=172.20.12.70:8249]
删除pump和drainer节点
//sacale-in pump
[tidb@tidb53 paul]$ tiup cluster scale-in paul_t -N 172.20.12.52:8250
tiup is checking updates for component cluster ...
Starting component `cluster`: /home/tidb/.tiup/components/cluster/v1.14.1/tiup-cluster scale-in paul_t -N 172.20.12.52:8250
This operation will delete the 172.20.12.52:8250 nodes in `paul_t` and all their data.
Do you want to continue? [y/N]:(default=N) y
The component `[pump]` will become tombstone, maybe exists in several minutes or hours, after that you can use the prune command to clean it
Do you want to continue? [y/N]:(default=N) y
Scale-in nodes...
//scale-in drainer
[tidb@tidb53 paul]$ tiup cluster scale-in paul_t -N 172.20.12.70:8249
tiup is checking updates for component cluster ...
Starting component `cluster`: /home/tidb/.tiup/components/cluster/v1.14.1/tiup-cluster scale-in paul_t -N 172.20.12.70:8249
This operation will delete the 172.20.12.70:8249 nodes in `paul_t` and all their data.
Do you want to continue? [y/N]:(default=N) y
The component `[drainer]` will become tombstone, maybe exists in several minutes or hours, after that you can use the prune command to clean it
Do you want to continue? [y/N]:(default=N) y
Scale-in nodes...
四、常规测试
DML操作
上游写入数据
mysql> INSERT INTO t1 SELECT NULL, RANDOM_BYTES(1024), RANDOM_BYTES(1024), RANDOM_BYTES(1024),null FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
Query OK, 10000 rows affected (0.80 sec)
Records: 10000 Duplicates: 0 Warnings: 0
drainer日志：
[INFO] [pump.go:168] ["receive big size binlog before unmarshal"] [id=172.20.12.52:8250] [size="31 MB"] ["start ts"=448123549203300353] ["commit ts"=448123549360586755]
[INFO] [binlogger.go:487] ["segmented binlog file is created"] [path=/data2/tidb-data/drainer-8249/binlog-0000000000000008-20240303164758]
[INFO] [binlogger.go:378] ["GC binlog file"] ["file name"=/data2/tidb-data/drainer-8249/binlog-0000000000000006-20240303164324]
[INFO] [syncer.go:279] ["write save point"] [ts=448123549360586755] [version=273]
DDL操作
增加字段：
上游tidb添加表t1 字段
mysql> alter table t1 add column nt int;
Query OK, 0 rows affected (0.52 sec)
drainer日志：
[[INFO] [collector.go:285] ["start query job"] [id=270] [binlog="tp:Commit start_ts:448100511433097220 commit_ts:448100511433097225 prewrite_key:\"mDB:85\\000\\000\\000\\374\\000\\000\\000\\000\\000\\000\\000hTable:26\\3775\\000\\000\\000\\000\\000\\000\\000\\370\" ddl_query:\"ALTER TABLE `t1` ADD COLUMN `n1` INT;\" ddl_job_id:270 ddl_schema_state:5 "]
[INFO] [collector.go:307] ["get ddl job"] [job="ID:270, Type:add column, State:synced, SchemaState:public, SchemaID:85, TableID:265, RowCount:0, ArgLen:0, start time: 2024-03-02 16:23:15.439 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[INFO] [schema.go:315] ["skip unsupported DDL job"] [job="ID:239, Type:alter table cache, State:synced, SchemaState:public, SchemaID:85, TableID:237, RowCount:0, ArgLen:0, start time: 2024-02-23 17:38:28.539 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[INFO] [syncer.go:524] ["add ddl item to syncer, you can add this commit ts to `ignore-txn-commit-ts` to skip this ddl if needed"] [sql="alter table t1 add column n1 int"] ["commit ts"=448100511433097225] [shouldSkip=false]
[2024/03/02 16:23:18.632 +08:00] [INFO] [load.go:451] ["exec ddl success"] [sql="ALTER TABLE `t1` ADD COLUMN `n1` INT;"]
[2024/03/02 16:23:18.640 +08:00] [INFO] [binlogger.go:378] ["GC binlog file"] ["file name"=/data2/tidb-data/drainer-8249/binlog-0000000000000002-20240301161117]
删除字段
上游tidb添加表t1 字段
mysql> alter table t1 drop column ;
Query OK, 0 rows affected (0.52 sec)
drainter 日志：
[INFO] [collector.go:285] ["start query job"] [id=271] [binlog="tp:Commit start_ts:448100530215714822 commit_ts:448100530215714825 prewrite_key:\"mDB:85\\000\\000\\000\\374\\000\\000\\000\\000\\000\\000\\000hTable:26\\3775\\000\\000\\000\\000\\000\\000\\000\\370\" ddl_query:\"ALTER TABLE `t1` DROP COLUMN `n1`;\" ddl_job_id:271 "]
[INFO] [collector.go:307] ["get ddl job"] [job="ID:271, Type:drop column, State:synced, SchemaState:none, SchemaID:85, TableID:265, RowCount:0, ArgLen:0, start time: 2024-03-02 16:24:27.089 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[INFO] [schema.go:550] ["Finished dropping column"] [job="ID:271, Type:drop column, State:synced, SchemaState:none, SchemaID:85, TableID:265, RowCount:0, ArgLen:0, start time: 2024-03-02 16:24:27.089 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[INFO] [syncer.go:524] ["add ddl item to syncer, you can add this commit ts to `ignore-txn-commit-ts` to skip this ddl if needed"] [sql="alter table t1 drop column n1"] ["commit ts"=448100530215714825] [shouldSkip=false]
[INFO] [load.go:451] ["exec ddl success"] [sql="ALTER TABLE `t1` DROP COLUMN `n1`;"]
[INFO] [syncer.go:279] ["write save point"] [ts=448100530215714825] [version=273]
[INFO] [syncer.go:279] ["write save point"] [ts=448100533033500673] [version=273]