DM mydumper MySQL server has gone away Error

Hacker_Rc5f1tYG · 2020 年5 月 19 日 07:25

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：TiDB 3.0.12 DM 1.0.4
【问题描述】：

[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.apilogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.orderitemsex): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:49:00.412 +08:00] [ERROR] [mydumper.go:164] ["Could not read data from pupu_log.categoryviewlogs: Lost connection to MySQL server during query"] [task=pupu_log] [unit=dump]
[2020/05/19 11:49:45.926 +08:00] [ERROR] [mydumper.go:164] ["Could not read data from pupu_log.arrivalreminders: Lost connection to MySQL server during query"] [task=pupu_log] [unit=dump]
[2020/05/19 11:54:48.460 +08:00] [ERROR] [subtask.go:255] ["unit process error"] [subtask=pupu_log] [unit=Dump] ["error information"="{\"msg\":\"[code=32001:class=dump-unit:scope=internal:level=high] mydumper runs with error: exit status 1. \\
\\
\"}"]
[2020/05/19 11:58:34.231 +08:00] [ERROR] [mydumper.go:164] ["Could not read data from pupu_log.categoryviewlogs: Lost connection to MySQL server during query"] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.products) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

Hacker_Rc5f1tYG · 2020 年5 月 19 日 07:25

其他库是成功，就做这库的全量出先这个错误。

yilong · 2020 年5 月 19 日 07:49

能否发送完整的日志，多谢。

2.上游是否有防火墙

Hacker_Rc5f1tYG · 2020 年5 月 19 日 08:44

同一个数据源(MySQL ) 这是其他任务

» query-status
{
    "result": true,
    "msg": "",
    "tasks": [
        {
            "taskName": "pupu_account_pre",
            "taskStatus": "Running",
            "workers": [
                "192.168.0.41:8262"
            ]
        },
        {
            "taskName": "pupu_auth",
            "taskStatus": "Running",
            "workers": [
                "192.168.0.41:8262"
            ]
        },
        {
            "taskName": "pupu_behavior",
            "taskStatus": "Running",
            "workers": [
                "192.168.0.41:8262"
            ]
        },
        {
            "taskName": "pupu_coupon",
            "taskStatus": "Running",
            "workers": [
                "192.168.0.41:8262"
            ]
        },
        {
            "taskName": "pupu_main",
            "taskStatus": "Running",
            "workers": [
                "192.168.0.41:8262"
            ]
        },
        {
            "taskName": "pupu_transaction",
            "taskStatus": "Running",
            "workers": [
                "192.168.0.41:8262"
            ]
        },
        {
            "taskName": "pupu_wallet",
            "taskStatus": "Running",
            "workers": [
                "192.168.0.41:8262"
            ]
        }
    ]
}

pupu_log 任务日志，仅截图错误部分 – 目前无法从服务器下载完整log，正想办法。

[2020/05/19 11:48:29.358 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.storeproductincomelogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.358 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.usergroup_tmp): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.358 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.weixinpaylogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.dispatchlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.inventorychecklog): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.knightdispatchlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.tokenlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.userupdatelogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.adminuseractionlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.inventorycheckplan): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.recproducts): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.recproducttoproducts): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.salelogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.storeincomelogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.inventorycheckproductbatch): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.productcollectionactlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.pushdayreportlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.financialreportfields): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.placeorderdatas): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.saleforecast): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.storeproductsaleaffectlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.aliappdevicestatlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.messagesmsreceipt): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.pushtasks): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.messagesmsmasterplatechannelscale): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.smsreceiptreportlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.deliverytimeactionlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.userloginlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.customerpaymentdailylogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.pushlogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.storeschedulingrankings): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.apilogs): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:48:29.359 +08:00] [ERROR] [mydumper.go:164] ["Error dumping schemas (pupu_log.orderitemsex): MySQL server has gone away"] [task=pupu_log] [unit=dump]
[2020/05/19 11:49:00.412 +08:00] [ERROR] [mydumper.go:164] ["Could not read data from pupu_log.categoryviewlogs: Lost connection to MySQL server during query"] [task=pupu_log] [unit=dump]
[2020/05/19 11:49:45.926 +08:00] [ERROR] [mydumper.go:164] ["Could not read data from pupu_log.arrivalreminders: Lost connection to MySQL server during query"] [task=pupu_log] [unit=dump]
[2020/05/19 11:54:48.460 +08:00] [ERROR] [subtask.go:255] ["unit process error"] [subtask=pupu_log] [unit=Dump] ["error information"="{\"msg\":\"[code=32001:class=dump-unit:scope=internal:level=high] mydumper runs with error: exit status 1. \\
\\
\"}"]
[2020/05/19 11:58:34.231 +08:00] [ERROR] [mydumper.go:164] ["Could not read data from pupu_log.categoryviewlogs: Lost connection to MySQL server during query"] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.products) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.userbehaviorlimits) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.trackprocesslogs) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.usergroupusermap_tmp) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.arrivalreminders) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.messagechannelpushprogress) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]
[2020/05/19 11:58:34.232 +08:00] [ERROR] [mydumper.go:164] ["Error dumping table (pupu_log.place_route_repetition) data: MySQL server has gone away "] [task=pupu_log] [unit=dump]

yilong · 2020 年5 月 19 日 09:34

参考 https://blog.csdn.net/helloxiaozhe/article/details/78116871 先排查下参数问题等，多谢

Hacker_Rc5f1tYG · 2020 年5 月 19 日 09:37

MySQL 参数已排查， wait_timeout/interactive_timeout 我们是设置为10min。

max_allowed_packet 是设置为最大值。

来了老弟 · 2020 年5 月 19 日 09:44

你好，

请反馈下 dm-woker.log 文件这边分析下。

yilong · 2020 年5 月 19 日 09:58

请检查当开始备份的时候，到出错，是否超过了 10分钟，如果是尝试把参数值调大，足够导出库为止，多谢。

Hacker_Rc5f1tYG · 2020 年5 月 19 日 09:59

使用mydumper 命令行导入也报同样的错误

tidb@test-tidb1:/data/pupu_behavior_light$ /home/tidb/dm-ansible-v1.0.4/resources/bin/mydumper -V
mydumper 0.9.5 (ba3946edc087e87bba7cb2728bc2407f6dbd0a8b), built against MySQL 5.7.24

nohup /home/tidb/dm-ansible-v1.0.4/resources/bin/mydumper -h xxxx-P 3306 -u root -p xxxx -t 16 -F 256 -B pupu_log --skip-tz-utc -o /data/pupu_behavior_light/ >/data/pupu_behavior_light/log.log  &

错误日志

idb@test-tidb1:/data/pupu_behavior_light$  cat log.log

** (mydumper:29064): CRITICAL **: 17:23:36.109: Couldn't acquire global lock, snapshots will not be consistent: Access denied for user 'root'@'%' (using password: YES)

** (mydumper:29064): CRITICAL **: 17:41:11.564: Could not read data from pupu_log.salelogs: Lost connection to MySQL server during query
tidb@test-tidb1:/data/pupu_behavior_light$

yilong · 2020 年5 月 19 日 10:01

dm 启动，dump 阶段报错：["Couldn't acquire global lock, snapshots will not be consistent: Access denied for user 'tidb_slave'@'%' (using password: YES)"] [task=pupu_wallet] [unit=dump] 参考这个帖子设置下 no-lock 参数，试试，多谢

Hacker_Rc5f1tYG · 2020 年5 月 19 日 10:30

参数 wait_timeout/interactive_timeout的更改能设置为mydumper的session参数？全局DBA不让改。

虽然mydumper 报 lock权限，但导出数据都是成功的。仅是大表会失败

yilong · 2020 年5 月 19 日 10:47

根据你们的mysql 版本，查一下mysql吧。

Hacker_Rc5f1tYG · 2020 年5 月 20 日 05:01

MySQL 版本是5.7.26 的，需要查MySQL什么信息呢

yilong · 2020 年5 月 20 日 06:01

查一下mysql是否能够修改session信息的wait_timeout等参数，多谢。
或者找个业务低峰期，修改下参数，导出后，再修改回来

Hacker_Rc5f1tYG · 2020 年5 月 25 日 07:49

调整了mysql
net_read_timeout/net_read_timeout 能解决这个问题

yilong · 2020 年5 月 25 日 08:38

感谢反馈

system · 2022 年10 月 31 日 19:03

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。