仅记录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,这里记录下,方便下次使用。
(176)