mysql数据库技巧

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

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;