操作系统: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.异常。从数据库安全的角度出现,也不建议设置空密码
暂无评论