MySQL8.0新增用户及修改加密规则

Mysql8.0相较于5.6版本在新增用户以及加密规则都有更改，记录下踩坑

新增用户#

错误方式

Copy
mysql> grant all on admin.* TO admin@127.0.0.1 identified by '123456';
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 '123456'' at line 1

正确方式
需要先创建用户，再赋权

Copy
mysql> create user 'admin'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'admin'@'%' ;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

修改加密规则#

Mysql8.0之前的版本中，加密规则是mysql_native_password，而在Mysql8之后,加密规则是caching_sha2_password
当使用低版本客户端登陆时会报错，因此有两种解决方式：1、升级客户端；2、更改Mysql加密方式

Copy
mysql> ALTER USER 'admin'@'%' 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)