Mysql, 原创

mysql8.0授权用户,数据库,修改密码验证方式

仅记录mysql8.0授权用户,授权数据库表,修改连接数据库的验证格式

mysql8.0不能直接在授权数据库时创建新用户,需分开设置。

//错误执行代码
# grant all privileges on windigniter.* to 'windigniter'@'127.0.0.%' identified by 'windigniter.com' with grant option;
//mysql8授权数据库的报错信息
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 'windigniter.com' with grant option' at line 1

1.创建用户并设置密码。第一种简单操作, 已存在的用户用 alter user , 这种授权方式不能正常访问,除非 my.conf 里设置了

default_authentication_plugin=mysql_native_password

否则连接数据库时可能会报 The server requested authentication method unknown to,因为默认mysql8.0的默认加密方式是 caching_sha2_password

# create user 'windigniter'@'127.0.0.%' identified by 'windigniter.com';

使用一个能正常使用的授权语句 create/alter user

# create user 'windigniter'@'127.0.0.%' identified with mysql_native_password by 'windigniter.com';

2.授权数据库,授权数据库时不要带 identified 了,可以带 grant option

grant all privileges on yourdatabase.* to 'windigniter'@'127.0.0.%' with grant option;

单独修改账号的密码类型和创建账号如出一辙。

# alter user 'windigniter'@'127.0.0.%' identified with mysql_native_password by 'windigniter.com';

近期搭建服务器配置了几次mysql8.0,这里记录下,方便下次使用。

(28)

Author Since: Jul 05, 2018

Related Post