适用范围

MySQL 8.0 数据库

测试验证

1.正常登录后修改用户密码

1.1. alter user

更新密码成功,使用修改的密码登录成功。

mysql> alter user root@'localhost' identified by 'root1';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;

# /usr/local/mysql/bin/mysql -uroot -hlocalhost -proot1
mysql> 

1.2. set password

更新密码成功,使用修改的密码登录成功。

mysql> set password for 'root'@'localhost'='root2';
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;

# /usr/local/mysql/bin/mysql -uroot -hlocalhost -proot2 
mysql> 

1.3. update mysql.user

1.3.1. password()测试

更新密码直接报错，mysql 8.0 不支持password() 函数

mysql> update mysql.user set authentication_string=password('root3') where user='root' and host='localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('root') where user='root' and host='localhost'' at line 1

1.3.2. md5()测试

更新密码成功，但是登录不上，与caching_sha2_passowrd插件的密码要求不一致

mysql> update mysql.user set authentication_string=md5('root4') where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# /usr/local/mysql/bin/mysql -uroot -S/usr/local/mysql/data/mysql.sock -proot4
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

1.3.3. sha1()测试

更新密码成功，但是登录不上，与caching_sha2_passowrd插件的密码要求不一致

mysql> update mysql.user set authentication_string=sha1('root5') where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# /usr/local/mysql/bin/mysql -uroot -S/usr/local/mysql/data/mysql.sock -proot5
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

1.3.4. sha2(‘root6’,256)测试

更新密码成功，但是登录不上，与caching_sha2_passowrd插件的密码要求不一致

mysql> update mysql.user set authentication_string=sha2('root6',256) where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# /usr/local/mysql/bin/mysql -uroot -S/usr/local/mysql/data/mysql.sock -proot5
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

1.3.5. caching_sha2_password(‘root7’)测试

mysql 8.0 中使用了caching_sha2_password 插件，但是不支持caching_sha2_password函数，所以更新密码直接报错

mysql> update mysql.user set authentication_string=caching_sha2_password('root7') where user='root' and host='localhost';
ERROR 1305 (42000): FUNCTION mysql.caching_sha2_password does not exist

2.忘记密码后修改用户密码

2.1.跳过密码验证

忘记root@'localhost’密码，没有高权限的用户可用；这时需要启动时添加–skip-grant-tables选项或者在配置文件中添加skip-grant-tables,然后重启mysql实例，从而跳过密码验证。

mysql> shutdown;
# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --skip_grant_tables &

2.2. alter user

无法修改密码。

# /usr/local/mysql/bin/mysql -uroot -S/usr/local/mysql/data/mysql.sock
mysql> alter user root@'localhost' identified by 'root8';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> 

2.3. set password

无法修改密码。

mysql> set password for root@'localhost'='root9';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

2.4. update mysql.user

authentication_string=’’ 需要填写hash加密后的内容，给空来让用户登录时可用不填写密码进行登录；不能给authentication_string值，如 authentication_string='xxx’是不可以的。

mysql> update mysql.user set authentication_string='' where user='root' and host='localhost';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.5.去除免密登录并修改密码

可以登录成功，并且修改用户密码成功。

mysql> shutdown;
# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

# /usr/local/mysql/bin/mysql -uroot -S/usr/local/mysql/data/mysql.sock
mysql> alter user root@'localhost' identified by 'root10';
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

[root@haproxy2 data]# /usr/local/mysql/bin/mysql -uroot -S/usr/local/mysql/data/mysql.sock -proot10
mysql> 

知识总结

1. 在8.0 中正常情况修改密码时使用alter user和set password的方式，推荐alter user的方式修改密码
2. 在使用–skip-grant-tables后修改密码的方式可以使用update mysql.user的方式，但是authentication_string需要设置为空，因为如果设置为指定的字符串是无法满足密码插件要求的
3. caching_sha2_password 插件生成的哈希密码与纯 SHA-256 哈希不同，所以本文使用sha2()函数修改的密码也无法使用