dm 同步失败解决办法

张映 发表于 2021-01-08

分类目录: tidb

标签:, ,

通过dm,把mysql的数据同步到tidb中,到目前为止发生二次同步失败的情况

一,mysql修改表字段报错

"errors": [
{
"ErrCode": 44006,
"ErrClass": "schema-tracker",
"ErrScope": "internal",
"ErrLevel": "high",
"Message": "startLocation: [position: (mysql-bin.000022, 545530809), gtid-set: ], endLocation: [position: (mysql-bin.000022, 545530999), gtid-set: ]: cannot track DDL: ALTER TABLE `test`.`test` MODIFY COLUMN `advertiser_id` BIGINT(11) NOT NULL DEFAULT _UTF8MB4'0' COMMENT ''",
"RawCause": "[ddl:8200]Unsupported modify column: type bigint(11) not match origin varchar(36), and tidb_enable_change_column_type is false",
"Workaround": ""
}
],

解决办法:

1,把tidb中报错的表,只导出数据

2,把mysql中对应的表,导出结构

3,登录tidb数据库,把报错的表删除,导入mysql导出的表结构,然后在导入tidb导出的表数据

4,通过handle-error [task_name] skip跳过错误

[tidb@jiankong task]$ tiup dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.1/dmctl/dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "source": "mysql57",
            "worker": "dm-10.0.10.15-8262"
        }
    ]
}

这种方式个人觉得是最快的恢复的方式了。也有其他的方式,不过不推荐使用。例如:新建task,然后重新同步。

二,tidb报主键冲突

{
"ErrCode": 10006,
"ErrClass": "database",
"ErrScope": "not-set",
"ErrLevel": "high",
"Message": "startLocation: [position: (, 0), gtid-set: ], endLocation: [position: (mysql-bin.000026, 535844593), gtid-set: ]: execute statement failed: commit",
"RawCause": "Error 1062: Duplicate entry '96000' for key 'PRIMARY'",
"Workaround": ""
},

从上面报错看,没有表名和数据名,startLocation为0,endLocation是有的,mysql的binlog文件也有,就好办了。

# mysqlbinlog --base64-output=DECODE-ROWS --stop-position=535844593 -v -d test ./mysql-bin.000026 |grep -B 3 96000

如果知道数据名就加上-d,不确定就拿掉

查找mysql binlog的日志

查找mysql binlog的日志

一般来说都在最后,因为发错就会中止同步。确定表了以后,对比tidb中的表数据和mysql表数据,如果数据不一样,就可以确定不同步了。

解决办法:

删除tidb表中的报错id。然后重新启动

# tiup dmctl --master-addr 10.0.10.18:8261 start-task ./mysql57-task.yaml

handle-error是不能解决问题的。

[tidb@jiankong task]$ tiup dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.1/dmctl/dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": false,
            "msg": "only support to handle ddl error currently, see https://docs.pingcap.com/tidb-data-migration/stable/error-handling for other errors",
            "source": "mysql57",
            "worker": ""
        }
    ]
}

handle-error [task_name] skip只支持跳过ddl的错误



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/tidb/2476.html

留下评论

留下评论
  • (必需)
  • (必需) (will not be published)
  • (必需)   1X4=?