mysql8用户授权报错ERROR 1064 (42000)解决办法

Oct 1, 2023

—

from

mysql8如果采用原始的sql语句授权会报错：

mysql> grant all on moneyslow.* to moneyslow@127.0.0.1 identified by 'Nf12345';
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 'Nf12345'' at line 1

因为从mysql8开始，改为如下：

mysql> CREATE USER `moneyslow`@`127.0.0.1` IDENTIFIED BY 'Nf12345';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON moneyslow.* TO `moneyslow`@`127.0.0.1` WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

新版MySQL8中，添加用户并设定权限

MySQL8.0之前在添加用户的时候可以直接设置用户权限如：

GRANT ALL ON . TO moneyslow@ip IDENTIFIED BY ‘passowrd’ WITH GRANT OPTION;
但是在新版MySQL中这样就会报错，必须分开开，先创建用户，然后才能设置权限如下：

CREATE USER moneyslow@ip IDENTIFIED BY ‘moneyslow’ [PASSWORD EXPIRE INTERVAL 90 DAY];
GRANT ALL ON . TO moneyslow@127.0.0.1 WITH GRANT OPTION;
1.创建新用户
CREATE USER ‘user1’@’%’ IDENTIFIED BY ‘nf12345’;
如
CREATE USER ‘moneyslow’@’%’ IDENTIFIED BY ‘moneyslow’;
‘%’ – 所有情况都能访问 ‘localhost’ – 本机才能访问 ‘111.222.33.44’ – 指定 ip 才能访问

注：修改密码

update mysql.user set password=password(‘新密码’) where user=’user1′;
2.给该用户添加权限
grant all privileges on 想授权的数据库.* to ‘user1’@’%’;
如：
GRANT all PRIVILEGES on zldc.* to ‘moneyslow’@’%’;

flush privileges;
all 可以替换为 select,delete,update,create,drop

3、查看用户权限
show grants for test;

mysql