TiDB Binlog 功能使用实践

[tidb@tidb53 paul]$ tiup cluster edit-config paul_tserver_configs:tidb:  binlog.enable: true   binlog.ignore-error: true​

[tidb@tidb53 paul]$ cat scale-out-binlog.tomlpump_servers:- host: 172.20.12.52ssh_port: 22port: 8250deploy_dir: /data2/tidb-deploy/pump-8250data_dir: /data2/tidb-data/pump-8250log_dir: /data2/tidb-deploy/pump-8250/logarch: amd64os: linuxdrainer_servers:- host: 172.20.12.70ssh_port: 22port: 8249deploy_dir: /data2/tidb-deploy/drainer-8249data_dir: /data2/tidb-data/drainer-8249log_dir: /data2/tidb-deploy/drainer-8249/logconfig:  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: rootarch: amd64os: 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.tomlPlease confirm your topology:Cluster type:   tidbCluster name:   paul_tCluster version: v6.5.1Role     Host         Ports OS/Arch       Directories----     ----         ----- -------       -----------pump     172.20.12.52 8250   linux/x86_64 /data2/tidb-deploy/pump-8250,/data2/tidb-data/pump-8250drainer 172.20.12.70 8249   linux/x86_64 /data2/tidb-deploy/drainer-8249,/data2/tidb-data/drainer-8249Attention:  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@tidb53 paul]$ cat scale-out-binlogfile.toml drainer_servers:- host: 172.20.12.70ssh_port: 22port: 8249deploy_dir: /data2/tidb-deploy/drainer-8249data_dir: /data2/tidb-data/drainer-8249log_dir: /data2/tidb-deploy/drainer-8249/logconfig:  syncer.db-type: file   syncer.to.retention-time: 15  syncer.relay.log-dir: /data2/tidb-data/drainer-8249  syncer.relay.max-file-size: 10485760arch: amd64os: 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.tomlPlease confirm your topology:Cluster type:   tidbCluster name:   paul_tCluster version: v6.5.1Role     Host         Ports OS/Arch       Directories----     ----         ----- -------       -----------drainer 172.20.12.70 8249   linux/x86_64 /data2/tidb-deploy/drainer-8249,/data2/tidb-data/drainer-8249Attention:  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@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}"]

[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]

//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:8250This 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) yThe component `[pump]` will become tombstone, maybe exists in several minutes or hours, after that you can use the prune command to clean itDo you want to continue? [y/N]:(default=N) yScale-in nodes...​//scale-in drainer[tidb@tidb53 paul]$ tiup cluster scale-in paul_t -N 172.20.12.70:8249tiup 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:8249This 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) yThe component `[drainer]` will become tombstone, maybe exists in several minutes or hours, after that you can use the prune command to clean itDo you want to continue? [y/N]:(default=N) yScale-in nodes...​

上游写入数据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]​

上游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]