专栏 - TiDB 3.0.2 手动指定 Drainer CommitTS

注意：

任何情况下都不要直接 kill Pump或Drainer
一但数据同步失败 Drainer就会进入 paused (暂停状态)

TiDB3.0.2同步 MariaDB 数据超长引发 binglog同步失败，解决方案如下：

修改TiDB错误例如：删除某个超长的列，然后重新创建一个与MySQL长度合理的列。删除表时不要忘记备份数据
查看 drainer.log 找到发生异常的时间点

[tidb@dev11 ~]$ tail -1000f /home/tidb/deploy/log/drainer.log
......
[2019/08/22 11:27:21.586 +08:00] [INFO] [syncer.go:254] ["write save point"] [ts=410634011353284609]
[2019/08/22 11:27:26.397 +08:00] [INFO] [syncer.go:254] ["write save point"] [ts=410634012205252609]
[2019/08/22 11:27:30.608 +08:00] [ERROR] [executor.go:85] ["exec fail"] [query="DELETE FROM `dev2_pfizer_activiti`.`ACT_RU_JOB` WHERE `ID_` = ? LIMIT 1"] [args="["ZTA4MjMyZDAtYzNmYS0xMWU5LWJkZjEtMDY4YWEwOWI2NzQy"]"] [error="Error 1213: Deadlock found when trying to get lock; try restarting transaction"]
......
# 异常的发生时间为  [2019/08/22 11:27:30.608 +08:00]

根据异常的发生时间 [2019/08/22 11:27:30.608 +08:00] 到 pump.log 找到这个时间相近的 下一个 MaxCommitTS的值

[tidb@dev11 ~]$ vim /home/tidb/deploy/log/pump.log

# 在vim中查找
/[2019/08/22 11:27:3

# 根据时间的定位，最相近的下一个 TS值是  [MaxCommitTS=410634015285444609]
[2019/08/22 11:27:36.411 +08:00] [INFO] [storage.go:381] [DBStats] [DBStats="{"WriteDelayCount":0,"WriteDelayDuration":0,"WritePaused":false,"AliveSnapshots":0,"AliveIterators":0,"IOWrite":5790744,"IORead":0,"BlockCacheSize":0,"OpenedTablesCount":0,"LevelSizes":null,"LevelTablesCounts":null,"LevelRead":null,"LevelWrite":null,"LevelDurations":null}"]
[2019/08/22 11:27:36.445 +08:00] [INFO] [server.go:522] ["server info tick"] [writeBinlogCount=8932] [alivePullerCount=3] [MaxCommitTS=410634015285444609]
[2019/08/22 11:27:46.411 +08:00] [INFO] [storage.go:381] [DBStats] [DBStats="{"WriteDelayCount":0,"WriteDelayDuration":0,"WritePaused":false,"AliveSnapshots":0,"AliveIterators":0,"IOWrite":5791236,"IORead":0,"BlockCacheSize":0,"OpenedTablesCount":0,"LevelSizes":null,"LevelTablesCounts":null,"LevelRead":null,"LevelWrite":null,"LevelDurations":null}"]
[2019/08/22 11:27:46.445 +08:00] [INFO] [server.go:522] ["server info tick"] [writeBinlogCount=8932] [alivePullerCount=3] [MaxCommitTS=410634017644740612]

修改下游数据库中的CommitTS

MariaDB [(none)]> show databases;
+-----------------------------+
| Database                    |
+-----------------------------+ |
| information_schema          |
| mysql                       |
| performance_schema          |
| tidb_binlog                 |
| tidb_loader                 |
+-----------------------------+
22 rows in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> use tidb_binlog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [tidb_binlog]>
MariaDB [tidb_binlog]>
MariaDB [tidb_binlog]> show tables;
+-----------------------+
| Tables_in_tidb_binlog |
+-----------------------+
| checkpoint            |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [tidb_binlog]> select * from checkpoint;
+---------------------+---------------------------------------------+
| clusterID           | checkPoint                                  |
+---------------------+---------------------------------------------+
| 6697082055159617667 | {"commitTS":410636899695198209,"ts-map":{}} |
+---------------------+---------------------------------------------+
1 row in set (0.00 sec)

MariaDB [tidb_binlog]>

启动 drainer

[tidb@test1 tidb-ansible]$ ansible-playbook start_drainer.yml

Congrats! All goes well. :-)
[tidb@test1 tidb-ansible]$