MySQL 5.7 GTID主从复制模式root密码为空的问题
操作系统:CentOS7.6
数据库版本: MySQL 5.7.25
有两个实例,端口分别是3307和3308
用xtrabackup做的复制结构,默认密码原来为空,这种情况下是没问题的,两个实例都可以正常连接和执行SQL语句,但是如果新添加的节点或执行ALTER USER current_user() IDENTIFIED BY '';修改密码,那么其他节点将会出现ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.报错
#3307节点修改密码
root@localhost:mysql3307.sock [(none)]> ALTER USER current_user() IDENTIFIED BY '';
Query OK, 0 rows affected (0.01 sec)

#3308节点登录
[keung@hknode ~]$ mysql -S /tmp/mysql3308.sock -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.25-log

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:mysql3308.sock [(none)]> show master status;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

此时3307实例把root密码修改为非空密码,3308实例就变正常了
root@localhost:mysql3307.sock [(none)]> ALTER USER current_user() IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)

[keung@hknode ~]$ mysql -S /tmp/mysql3308.sock -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:mysql3308.sock [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                     |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
| mysql-bin.000005 |     2260 |              |                  | 5471af42-ff4b-11e8-ae36-00163e016708:1-132,
ab43a829-ff4b-11e8-b4ea-00163e016708:1-12 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

其他普通用户也是一样的情况
root@localhost:mysql3307.sock [(none)]> ALTER USER keung@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0.01 sec)

[keung@hknode ~]$ mysql -S /tmp/mysql3308.sock -ukeung 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.25-log

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

keung@localhost:mysql3308.sock [(none)]> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

root@localhost:mysql3307.sock [(none)]> ALTER USER keung@'%' IDENTIFIED BY 'keung';
Query OK, 0 rows affected (0.00 sec)

[keung@hknode ~]$ mysql -S /tmp/mysql3308.sock -ukeung -pkeung
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

keung@localhost:mysql3308.sock [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
+--------------------+
2 rows in set (0.00 sec)

小结:
用户密码不能为空,否则可能出现ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.异常。从数据库安全的角度出现,也不建议设置空密码
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇