1. 用户创建
创建用户的操作已经不支持grant的同时创建用户的方式，需先创建用户再进行授权
mysql> grant all on *.* to ‘admin‘@‘%‘ identified by ‘admin123‘; 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 ‘identified by ‘admin123‘‘ at line 1 mysql> create user ‘admin‘@‘%‘ identified by ‘admin123‘; Query OK, 0 rows affected (0.06 sec) mysql> grant all on *.* to ‘admin‘@‘%‘ ; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
2. 用户登录1
当用户密码含有字母或数字外的特殊符号登录时，原先使用双引号或单引号都可以登录，但在mysql8.0登录时遇到问题，如下
[root@gjc18 lib]# /usr/local/mysql8.0/bin/mysql -uroot -p"root!@#123" --socket=/data/mysql/mysql3310/tmp/mysql3310.sock -bash: !@#123": event not found [root@gjc18 lib]# /usr/local/mysql8.0/bin/mysql -uroot -p‘root!@#123‘ --socket=/data/mysql/mysql3310/tmp/mysql3310.sock 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 22 Server version: 8.0.12 MySQL Community Server - GPL
3.低版本客户端登录异常
错误号码 2058：Plugin caching_sha2_password could not be loaded
出现这个原因是mysql8.0 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决此问题方法有两种,一种是升级客户端驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password。
如果修改用户密码加密规则可使用如下方式：
1）. 修改加密方式：
-- 修改密码为用不过期 mysql> ALTER USER ‘root‘@‘%‘ IDENTIFIED BY ‘password‘ PASSWORD EXPIRE NEVER; Query OK, 0 rows affected (0.02 sec) -- 修改密码并指定加密规则为mysql_native_password mysql> ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘123456‘; Query OK, 0 rows affected (0.01 sec) -- 刷新权限 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql>
修改完毕后再次登录即可成功
2）.使用高版本客户端
linux低版本客户端登录时也会出现此情况，因此需使用高版本的客户端
[root@gjc18 lib]# mysql -uroot -p‘123456‘ --socket=/data/mysql/mysql3310/tmp/mysql3310.sock mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2059 (HY000): Authentication plugin ‘caching_sha2_password‘ cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory [root@gjc18 lib]# /usr/local/mysql8.0/bin/mysql -uroot -p‘123456‘ --socket=/data/mysql/mysql3310/tmp/mysql3310.sock 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 26 Server version: 8.0.12 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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.
除了密码插件调整外，MySQL8.0其他几个主要的新密码策略有：
- 支持密码过期策略，需要周期性修改密码
- 增加历史密码校验机制，防止近几次的密码相同（次数可以配置）
- 修改密码是需要验证旧密码，防止被篡改风险
- 支持双密码机制，即新密码与修改前的旧密码同时可以使用，且可以选择采用主密码还是第二个密码
- 增加密码强度约束，避免使用弱密码