MySQL 5.7 GTID主从复制模式root密码为空的问题

作者: Huang Jinqiang 分类: MySQL 发布时间: 2019-03-12 12:15
操作系统: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.异常。从数据库安全的角度出现,也不建议设置空密码

发表评论

电子邮件地址不会被公开。 必填项已用*标注