修改 inventory 文件中 mysql 密码配置（encrypt 未加引号，特殊字符），task 状态异常

davidvon · 2020 年6 月 4 日 10:18

password 是对【存在@字符】字符串加密生成，但bin/dm-worker --config conf/dm-worker.toml 执行异常

dm-worker.toml 部分配置

[from] 
  host = "172.19.161.101" 
  user = "program"
  password = "4s5dGFmQRQSjvC3G6INqt0Dx3mqb2wrrQ3cFbkAD0EPX"
  port = 33146

[2020/06/04 18:08:59.193 +08:00] [ERROR] [relay.go:171] [“process error”] [component=“relay holder”] [type=“msg:”[code=30012:class=relay-unit:scope=upstream:level=high] start reader for UUID 382960fa-ee68-11e9-aa04-02420a0a0302.000001: start sync from position (, 4): ERROR 1045 (28000): Access denied for user ‘program’@’%!!(MISSING)’(MISSING) (using password: YES) github.com/pingcap/dm/pkg/terror.(*Error).Delegate \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267 github.com/pingcap/dm/pkg/binlog/reader.(*TCPReader).StartSyncByPos \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/binlog/reader/tcp.go:79 github.com/pingcap/dm/relay/reader.(*reader).setUpReaderByPos \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/reader/reader.go:166 github.com/pingcap/dm/relay/reader.(*reader).Start \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/reader/reader.go:111 github.com/pingcap/dm/relay.(*Relay).setUpReader \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/relay.go:586 github.com/pingcap/dm/relay.(*Relay).process \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/relay.go:256 github.com/pingcap/dm/relay.(*Relay).Process \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/relay.go:191 github.com/pingcap/dm/dm/worker.(*realRelayHolder).run \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/worker/relay.go:165 github.com/pingcap/dm/dm/worker.(*realRelayHolder).Start.func1 \t/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/worker/relay.go:141 runtime.goexit \t/usr/local/go/src/runtime/asm_amd64.s:1337" "]

yilong · 2020 年6 月 4 日 10:24

请问是哪里修改了密码？ mysql吗？看下inventory.ini中的密码是否添加修改了，多谢。

davidvon · 2020 年6 月 4 日 11:13

Tidb版本：4.0 DM版本：1.0.5

在dm-worker.toml填写password参数时，先把密码加密填入的 ./dmctl -encrypt oofe@7N****

dm-worker执行后的打屏日志： [root@localhost dm-v1.0.1-linux-amd64]# bin/dm-worker -config conf/dm-worker.toml [2020/06/04 19:05:17] [info] binlogsyncer.go:144 create BinlogSyncer with config {102 mysql 172.19.161.101 33146 program false true false UTC true 0 30s 1m0s 0 true true 0} [2020/06/04 19:05:17] [info] binlogsyncer.go:359 begin to sync binlog from position (, 4)

这个跟inventory.ini没关系了吧？

davidvon · 2020 年6 月 5 日 03:08

再同步下最新信息：

今天更换另一套使用无特殊字符密码的Mysql环境，在DM1.0.1版本中测试正常。
昨天DM版本1.0.5，会不会版本引起的？

GangShen · 2020 年6 月 5 日 03:25

DM 1.0.1 环境使用特殊字符 @ 密码的，使用是正常的吗？

davidvon · 2020 年6 月 5 日 03:52

为了规避特殊字符可能性，今天采用不带特殊字符密码的环境测试，在1.0.1版本环境测试是正常的

GangShen · 2020 年6 月 5 日 04:23

麻烦在上游执行一下 select * from mysql.user where user=‘program’ 看下，看报错中 ‘program’@’%!!(MISSING)’(MISSING) 这个比较奇怪

davidvon · 2020 年6 月 5 日 08:05

谢谢哈，每次响应很及时。

这个问题在1.0.1，1.0.5 版本中都存在；

    root@gp-master:/opt/tidb/dm# bin/dm-worker -config conf/dm-config.toml 
    [2020/06/05 08:17:29] [info] binlogsyncer.go:144 create BinlogSyncer with config {101 mysql 172.19.161.101 33146 program    false true <nil> false UTC true 0 30s 1m0s 0 true true 0}
    [2020/06/05 08:17:29] [info] binlogsyncer.go:359 begin to sync binlog from position (, 4)
    root@gp-master:/opt/tidb/dm# bin/dm-worker -V
    Release Version: v1.0.5
    Git Commit Hash: a8e9f53f91e29756b09a22cdc37a6a6efcdfe55b
    Git Branch: release-1.0
    UTC Build Time: 2020-04-27 06:56:51
    Go Version: go version go1.13 linux/amd64

[2020/06/05 08:17:29.520 +00:00] [ERROR] [relay.go:194] ["process exit"] [component="relay log"] [error="[code=30012:class=relay-unit:scope=upstream:level=high] start reader for UUID 382960fa-ee68-11e9-aa04-02420a0a0302.000002: start sync from position (, 4): ERROR 1045 (28000): Access denied for user 'program'@'%!!(MISSING)'(MISSING) (using password: YES)"] [errorVerbose="[code=30012:class=relay-unit:scope=upstream:level=high] start reader for UUID 382960fa-ee68-11e9-aa04-02420a0a0302.000002: start sync from position (, 4): ERROR 1045 (28000): Access denied for user 'program'@'%!!(MISSING)'(MISSING) (using password: YES)\ github.com/pingcap/dm/pkg/terror.(*Error).Delegate\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\ github.com/pingcap/dm/pkg/binlog/reader.(*TCPReader).StartSyncByPos\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/binlog/reader/tcp.go:79\ github.com/pingcap/dm/relay/reader.(*reader).setUpReaderByPos\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/reader/reader.go:166\ github.com/pingcap/dm/relay/reader.(*reader).Start\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/reader/reader.go:111\ github.com/pingcap/dm/relay.(*Relay).setUpReader\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/relay.go:610\ github.com/pingcap/dm/relay.(*Relay).process\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/relay.go:256\ github.com/pingcap/dm/relay.(*Relay).Process\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/relay/relay.go:191\ github.com/pingcap/dm/dm/worker.(*realRelayHolder).run\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/worker/relay.go:167\ github.com/pingcap/dm/dm/worker.(*realRelayHolder).Start.func1\ \t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/worker/relay.go:143\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357"]

mysql>  select * from mysql.user where user='program'
    -> ;
+------+---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User    | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+------+---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| %    | program | Y           | Y           | Y           | Y           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *8FD7CF109D0E7691FD5EBEBAF3DF597DD5D63B48 | N                |   2019-10-14 09:55:10 | NULL              | N              |
+------+---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
1 row in set

mysql>

GangShen · 2020 年6 月 6 日 06:14

意思是在 1.0.1 以及 1.0.5 版本的时候，当上游 MySQL 账户密码中含有 @ 特殊字符，启动 dm-worker 就会失败，报这个错？

davidvon · 2020 年6 月 10 日 03:28

是的，你们可以测试下

来了老弟 · 2020 年6 月 10 日 09:18

你好，

这边复习了你的问题，原因为 encrypt 遇到特殊字符需要添加引号来保证加密的正确性

文档改进已经提了 pr：zh: frame special characters with quotes by glkappe · Pull Request #105 · pingcap/docs-dm · GitHub

改为：./dmctl -encrypt 'oofe@7N****' 将可避免这个错误。

复现过程：

无引号：

修改当前密码
无引号加密

image858×49 3.2 KB
修改 inventory 文件

image1160×115 6.69 KB
deploy -t dm-worker

image1162×84 2.85 KB
查看 dm-worker config 是否已经刷新
启动 dm 集群

image1151×95 2.58 KB
task 任务报错，帐号密码不正确。

image1156×604 16.4 KB

有引号

有引号加密 & 修改 inventory 文件

image1158×254 14.3 KB
deploy dm-worker

image1166×132 4.24 KB
查看 config 是否被修改
启动 dm 集群

image1157×131 3.95 KB
检查 task 状态

image917×609 19 KB

gzp1 · 2020 年6 月 17 日 16:14

DM V1.0.6 有同样的问题…

 RawCause:\"ERROR 1045 (28000): Access denied for user 'dm-syxx'@'%' (using password: YES)\" > "]

试了引号也不行。

密码是pwgen 命令生成的，没有特殊字符

来了老弟 · 2020 年6 月 18 日 02:07

你好，

关注下你的帖子即可，我们会积极为你解决的

system · 2022 年10 月 31 日 19:07

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