Drainer不支持的DDL-1

版本3.0.5

1.rename table t1 to b_db.b_t1

已经做好TiDB->MySQL的同步, 开启了pump, drainer

问题复现

在TiDB创建fanboshi表

root@10.152.x.150 17:53:27 [sysbench]> show tables;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    76Current database: sysbench+--------------------+| Tables_in_sysbench |+--------------------+| sbtest1            || sbtest10           || sbtest2            || sbtest3            || sbtest4            || sbtest5            || sbtest6            || sbtest7            || sbtest8            || sbtest9            |+--------------------+10 rows in set (0.01 sec)root@10.152.x.150 18:31:19 [sysbench]> create table fanboshi like sbtest1;Query OK, 0 rows affected (0.13 sec)root@10.152.x.150 18:31:24 [sysbench]>  select count(*) from sbtest1;+----------+| count(*) |+----------+|   500456 |+----------+1 row in set (0.31 sec)root@10.152.x.150 18:31:42 [sysbench]> select count(*) from sbtest1;+----------+| count(*) |+----------+|   500757 |+----------+1 row in set (0.30 sec)

在下游MySQL查看, 同步没有问题

root@localhost 18:59:35 [sysbench]> show tables;+--------------------+| Tables_in_sysbench |+--------------------+| fanboshi           || sbtest1            || sbtest10           || sbtest2            || sbtest3            || sbtest4            || sbtest5            || sbtest6            || sbtest7            || sbtest8            || sbtest9            |+--------------------+11 rows in set (0.00 sec)root@localhost 18:59:37 [sysbench]> select count(*) from sbtest1;+----------+| count(*) |+----------+|   500757 |+----------+1 row in set (0.13 sec)

在TiDB执行

root@10.152.x.150 18:57:59 [sysbench]> create database tidb_sysbench;Query OK, 0 rows affected (0.10 sec)root@10.152.x.150 18:58:09 [sysbench]> rename table fanboshi to tidb_sysbench.tidb_fanboshi;Query OK, 0 rows affected (0.10 sec)

Drainer报错退出

[2019/12/02 18:59:14.979 +08:00] [INFO] [collector.go:284] ["get ddl job"] [job="ID:79, Type:rename table, State:synced, SchemaState:public, SchemaID:77, TableID:75, RowCount:0, ArgLen:0, start time: 2019-12-02 18:59:14.012 +0800 CST, Err:
   
    , ErrCount:0, SnapshotVersion:0"][2019/12/02 18:59:14.979 +08:00] [INFO] [syncer.go:400] ["add ddl item to syncer, you can add this commit ts to `ignore-txn-commit-ts` to skip this ddl if needed"] [sql="rename table fanboshi to tidb_sysbench.tidb_fanboshi"] ["commit ts"=412951341698121738][2019/12/02 18:59:19.993 +08:00] [ERROR] [load.go:594] ["exec failed"] [sql="rename table fanboshi to tidb_sysbench.tidb_fanboshi"] [error="Error 1017: Can't find file: './tidb_sysbench/fanboshi.frm' (errno: 2 - No such file or directory)"] [errorVerbose="Error 1017: Can't find file: './tidb_sysbench/fanboshi.frm' (errno: 2 - No such file or directory)\ngithub.com/pingcap/errors.AddStack\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).execDDL\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:337\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).execDDL\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:592\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).put\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:615\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).Run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:478\ngithub.com/pingcap/tidb-binlog/drainer/sync.(*MysqlSyncer).run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/drainer/sync/mysql.go:121\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337"][2019/12/02 18:59:19.994 +08:00] [INFO] [load.go:435] ["Run()... in Loader quit"][2019/12/02 18:59:19.994 +08:00] [INFO] [load.go:715] ["txnManager has been closed"][2019/12/02 18:59:19.994 +08:00] [INFO] [load.go:659] ["run()... in txnManager quit"][2019/12/02 18:59:19.994 +08:00] [INFO] [mysql.go:117] ["Successes chan quit"][2019/12/02 18:59:19.994 +08:00] [INFO] [syncer.go:251] ["write save point"] [ts=412951341357334529][2019/12/02 18:59:19.994 +08:00] [ERROR] [syncer.go:416] ["Failed to close syncer"] [error="Error 1017: Can't find file: './tidb_sysbench/fanboshi.frm' (errno: 2 - No such file or directory)"] [errorVerbose="Error 1017: Can't find file: './tidb_sysbench/fanboshi.frm' (errno: 2 - No such file or directory)\ngithub.com/pingcap/errors.AddStack\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).execDDL\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:337\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).execDDL\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:592\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).put\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:615\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).Run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:478\ngithub.com/pingcap/tidb-binlog/drainer/sync.(*MysqlSyncer).run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/drainer/sync/mysql.go:121\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337"][2019/12/02 18:59:19.996 +08:00] [INFO] [syncer.go:243] ["handleSuccess quit"][2019/12/02 18:59:19.996 +08:00] [ERROR] [server.go:270] ["syncer exited abnormal"] [error="Error 1017: Can't find file: './tidb_sysbench/fanboshi.frm' (errno: 2 - No such file or directory)"] [errorVerbose="Error 1017: Can't find file: './tidb_sysbench/fanboshi.frm' (errno: 2 - No such file or directory)\ngithub.com/pingcap/errors.AddStack\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).execDDL\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:337\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).execDDL\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:592\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*batchManager).put\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:615\ngithub.com/pingcap/tidb-binlog/pkg/loader.(*loaderImpl).Run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/loader/load.go:478\ngithub.com/pingcap/tidb-binlog/drainer/sync.(*MysqlSyncer).run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/drainer/sync/mysql.go:121\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337"][2019/12/02 18:59:19.997 +08:00] [INFO] [util.go:66] [Exit] [name=syncer][2019/12/02 18:59:19.997 +08:00] [INFO] [server.go:406] ["begin to close drainer server"][2019/12/02 18:59:20.001 +08:00] [INFO] [server.go:371] ["has already update status"] [id=knode10-152-1-166:8249][2019/12/02 18:59:20.001 +08:00] [INFO] [server.go:410] ["commit status done"][2019/12/02 18:59:20.001 +08:00] [INFO] [util.go:66] [Exit] [name=heartbeat][2019/12/02 18:59:20.001 +08:00] [INFO] [collector.go:130] ["publishBinlogs quit"][2019/12/02 18:59:20.001 +08:00] [INFO] [pump.go:72] ["pump is closing"] [id=knode10-152-1-166:8250][2019/12/02 18:59:20.001 +08:00] [INFO] [pump.go:72] ["pump is closing"] [id=knode10-152-1-150:8250][2019/12/02 18:59:20.001 +08:00] [INFO] [util.go:66] [Exit] [name=collect][2019/12/02 18:59:20.001 +08:00] [INFO] [main.go:73] ["drainer exit"]

问题修复

参考同步时出现上游数据库支持但是下游数据库执行会出错的 DDL，应该怎么办？

根据drainer.log中的日志信息我们获取这个ddl的commit ts为412951341698121738

在中控机配置文件或者直接在drainer所属服务器的配置文件中直接修改都可以, 我这里在中控机修改, 因为我只有一个drainer. 如果有多个drainer建议在drainer服务器修改配置文件

1.添加要忽略的commit-ts

# ignore syncing the txn with specified commit ts to downstreamignore-txn-commit-ts = [412951341698121738]

注意这个应该填写integer, 如果填写为"412951341698121738"drainer会起不来, 而且使用systemctl start drainer-8249.service无任何错误提示/DATA1/deploy/log/drainer_stderr.log日志业务内容

[root@knode10-152-1-166 deploy]# cat /etc/systemd/system/drainer-8249.service [Unit]Description=drainer-8249 serviceAfter=syslog.target network.target remote-fs.target nss-lookup.target[Service]LimitNOFILE=1000000#LimitCORE=infinityLimitSTACK=10485760User=tidbExecStart=/DATA1/deploy/scripts/run_drainer.shRestart=on-failureRestartSec=15sSendSIGKILL=no[Install]WantedBy=multi-user.target[root@knode10-152-1-166 deploy]# ./bin/drainer --addr="10.152.x.166:8249" --pd-urls="http://10.152.x.152:2379,http://10.152.x.146:2379,http://10.152.x.147:2379" --data-dir="/DATA1/deploy/data.drainer" --log-file="/DATA1/deploy/log/drainer.log" --config=conf/drainer.toml --initial-commit-ts="412950687779913731" 2>> "/DATA1/deploy/log/drainer_stderr.log"[2019/12/02 19:17:35.996 +08:00] [FATAL] [main.go:39] ["verifying flags failed, See 'drainer --help'."] [error="toml: cannot load TOML value of type string into a Go integer"] [errorVerbose="toml: cannot load TOML value of type string into a Go integer\ngithub.com/pingcap/errors.AddStack\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15\ngithub.com/pingcap/tidb-binlog/pkg/util.StrictDecodeFile\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/pkg/util/util.go:188\ngithub.com/pingcap/tidb-binlog/drainer.(*Config).configFromFile\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/drainer/config.go:226\ngithub.com/pingcap/tidb-binlog/drainer.(*Config).Parse\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/drainer/config.go:169\nmain.main\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/cmd/drainer/main.go:38\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:200\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337"] [stack="github.com/pingcap/log.Fatal\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/log@v0.0.0-20190715063458-479153f07ebd/global.go:59\nmain.main\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-binlog/cmd/drainer/main.go:39\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:200"]查看日志无内容[root@knode10-152-1-166 deploy]# cat /DATA1/deploy/log/drainer_stderr.log[root@knode10-152-1-166 deploy]#

2.在MySQL手动执行失败的DDL语句

root@localhost 19:09:27 [sysbench]> rename table fanboshi to tidb_sysbench.tidb_fanboshi;Query OK, 0 rows affected (0.00 sec)

3.启动drainer

systemctl start drainer-8249.service [2019/12/02 19:19:26.473 +08:00] [INFO] [version.go:50] ["Welcome to Drainer"] ["Release Version"=v3.0.5] ["Git Commit Hash"=88e58f165b6556ba084761c35b4be6db164a5c06] ["Build TS"="2019-10-25 03:24:55"] ["Go Version"=go1.12] ["Go OS/Arch"=linux/amd64][2019/12/02 19:19:26.474 +08:00] [INFO] [main.go:46] ["start drainer..."] [config="{\"log-level\":\"info\",\"node-id\":\"\",\"addr\":\"http://10.152.x.166:8249\",\"advertise-addr\":\"http://10.152.x.166:8249\",\"data-dir\":\"/DATA1/deploy/data.drainer\",\"detect-interval\":10,\"pd-urls\":\"http://10.152.x.152:2379,http://10.152.x.146:2379,http://10.152.x.147:2379\",\"log-file\":\"/DATA1/deploy/log/drainer.log\",\"initial-commit-ts\":412950687779913731,\"sycner\":{\"sql-mode\":null,\"ignore-txn-commit-ts\":[412951341698121738],\"ignore-schemas\":\"INFORMATION_SCHEMA,PERFORMANCE_SCHEMA,mysql\",\"ignore-table\":null,\"txn-batch\":20,\"worker-count\":4,\"to\":{\"host\":\"10.133.1.52\",\"user\":\"drainer\",\"password\":\"drainer123\",\"port\":3308,\"checkpoint\":{\"schema\":\"\"},\"dir\":\"\",\"time-limit\":\"\",\"size-limit\":\"\",\"zookeeper-addrs\":\"\",\"kafka-addrs\":\"\",\"kafka-version\":\"\",\"kafka-max-messages\":0,\"topic-name\":\"\"},\"replicate-do-table\":null,\"replicate-do-db\":null,\"db-type\":\"mysql\",\"disable-dispatch\":false,\"safe-mode\":false,\"disable-detect\":false},\"security\":{\"ssl-ca\":\"\",\"ssl-cert\":\"\",\"ssl-key\":\"\"},\"synced-check-time\":5,\"compressor\":\"\",\"EtcdTimeout\":5000000000,\"MetricsAddr\":\"\",\"MetricsInterval\":15}"][2019/12/02 19:19:26.474 +08:00] [INFO] [client.go:144] ["[pd] create pd client with endpoints"] [pd-address="[http://10.152.x.146:2379,http://10.152.x.147:2379,http://10.152.x.152:2379]"][2019/12/02 19:19:26.483 +08:00] [INFO] [client.go:252] ["[pd] switch leader"] [new-leader=http://10.152.x.152:2379] [old-leader=][2019/12/02 19:19:26.484 +08:00] [INFO] [client.go:163] ["[pd] init cluster id"] [cluster-id=6751370831202615818][2019/12/02 19:19:26.484 +08:00] [INFO] [server.go:111] ["get cluster id from pd"] [id=6751370831202615818][2019/12/02 19:19:26.491 +08:00] [INFO] [checkpoint.go:65] ["initialize checkpoint"] [name=mysql] [checkpoint=412951341357334529] [cfg="{\"Db\":{\"host\":\"10.133.1.52\",\"user\":\"drainer\",\"password\":\"drainer123\",\"port\":3308},\"Schema\":\"tidb_binlog\",\"Table\":\"checkpoint\",\"ClusterID\":6751370831202615818,\"InitialCommitTS\":412950687779913731,\"dir\":\"/DATA1/deploy/data.drainer/savepoint\"}"][2019/12/02 19:19:26.491 +08:00] [INFO] [store.go:69] ["new store"] [path="tikv://10.152.x.146:2379,10.152.x.147:2379,10.152.x.152:2379?disableGC=true"][2019/12/02 19:19:26.491 +08:00] [INFO] [client.go:144] ["[pd] create pd client with endpoints"] [pd-address="[10.152.x.146:2379,10.152.x.147:2379,10.152.x.152:2379]"][2019/12/02 19:19:26.495 +08:00] [INFO] [client.go:252] ["[pd] switch leader"] [new-leader=http://10.152.x.152:2379] [old-leader=][2019/12/02 19:19:26.496 +08:00] [INFO] [client.go:163] ["[pd] init cluster id"] [cluster-id=6751370831202615818][2019/12/02 19:19:26.497 +08:00] [INFO] [store.go:75] ["new store with retry success"][2019/12/02 19:19:26.517 +08:00] [INFO] [store.go:69] ["new store"] [path="tikv://10.152.x.146:2379,10.152.x.147:2379,10.152.x.152:2379?disableGC=true"][2019/12/02 19:19:26.518 +08:00] [INFO] [client.go:144] ["[pd] create pd client with endpoints"] [pd-address="[10.152.x.146:2379,10.152.x.147:2379,10.152.x.152:2379]"][2019/12/02 19:19:26.521 +08:00] [INFO] [client.go:252] ["[pd] switch leader"] [new-leader=http://10.152.x.152:2379] [old-leader=][2019/12/02 19:19:26.521 +08:00] [INFO] [client.go:163] ["[pd] init cluster id"] [cluster-id=6751370831202615818][2019/12/02 19:19:26.522 +08:00] [INFO] [store.go:75] ["new store with retry success"][2019/12/02 19:19:26.533 +08:00] [INFO] [server.go:246] ["register success"] ["drainer node id"=knode10-152-1-166:8249][2019/12/02 19:19:26.533 +08:00] [INFO] [server.go:296] ["start to server request"] [addr=http://10.152.x.166:8249][2019/12/02 19:19:26.537 +08:00] [INFO] [merge.go:222] ["merger add source"] ["source id"=knode10-152-1-150:8250][2019/12/02 19:19:26.537 +08:00] [INFO] [merge.go:222] ["merger add source"] ["source id"=knode10-152-1-166:8250][2019/12/02 19:19:26.537 +08:00] [INFO] [pump.go:133] ["pump create pull binlogs client"] [id=knode10-152-1-166:8250][2019/12/02 19:19:26.537 +08:00] [INFO] [pump.go:133] ["pump create pull binlogs client"] [id=knode10-152-1-150:8250][2019/12/02 19:19:27.545 +08:00] [INFO] [collector.go:284] ["get ddl job"] [job="ID:79, Type:rename table, State:synced, SchemaState:public, SchemaID:77, TableID:75, RowCount:0, ArgLen:0, start time: 2019-12-02 18:59:14.012 +0800 CST, Err:
   
    , ErrCount:0, SnapshotVersion:0"][2019/12/02 19:19:27.545 +08:00] [WARN] [syncer.go:317] ["skip txn"] [binlog="tp:Commit start_ts:412951341698121737 commit_ts:412951341698121738 prewrite_key:\"mDB:43\\000\\000\\000\\374\\000\\000\\000\\000\\000\\000\\000H\" ddl_query:\"rename table fanboshi to tidb_sysbench.tidb_fanboshi\" ddl_job_id:79 "][2019/12/02 19:19:30.855 +08:00] [INFO] [syncer.go:251] ["write save point"] [ts=412951660163760129]

5.验证同步

[root@node10-133-1-59 fanboshi]# sh sysbench_tidb_run.sh sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:Number of threads: 4Report intermediate results every 5 second(s)Initializing random number generator from current timeInitializing worker threads...Threads started![ 5s ] thds: 4 tps: 130.27 qps: 2613.67 (r/w/o: 1830.43/133.87/649.37) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00[ 10s ] thds: 4 tps: 136.41 qps: 2733.37 (r/w/o: 1913.32/141.41/678.64) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00

TiDB

root@10.152.x.150 19:20:43 [sysbench]> select count(*) from sbtest1;+----------+| count(*) |+----------+|   500882 |+----------+1 row in set (0.27 sec)

下游MySQL

root@localhost 19:20:08 [sysbench]> select count(*) from sbtest1;+----------+| count(*) |+----------+|   500757 |+----------+1 row in set (0.12 sec)root@localhost 19:20:13 [sysbench]> select count(*) from sbtest1;+----------+| count(*) |+----------+|   500882 |+----------+1 row in set (0.11 sec)

同步恢复

转载地址：http://kkvub.baihongyu.com/

你可能感兴趣的文章

Leetcode Go 《精选TOP面试题》20200628 69.x的平方根