环境:MySQL: 8.0.26-0, Ubnutu: 20.04.3
1. [$]默认安装
- 默认安装过程无配置提示,密码为空
sudo apt update
sudo apt install mysql-server
2. [$]配置 MySQL
- 运行安全脚本,以修改安全等级低的默认配置
# 根据提示,选择/填写需要的配置
sudo mysql_secure_installation
3. 创建用户并授予权限
3.1 [$]登入 root 账户
mysql -u root -p
- 返回
mysql>
3.2 [mysql>]使用 CREATE USER
创建用户
CREATE USER '[username]'@'[host]' IDENTIFIED WITH [authentication_plugin] BY '[password]';
- username: 定义用户名
- host: 定义该用户的主机名
- authentication_plugin:
caching_sha2_password
(默认,官方文档推荐) ,auth_socket
(不需要输密码,阻止远程连接) ,mysql_native_password
(PHP使用,PHP使用默认配置可能出错)
例:
CREATE USER 'TEST01'@'%' IDENTIFIED BY 'password';
可使用 ALTER
修改 authentication_plugin
例:
bash ALTER USER 'TEST01'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
3.3 [mysql>]给用户授权
GRANT [PRIVILEGE] ON [database].[table] TO `[username]`@`[host]`;
- PRIVILEGE:
CREATE
,ALTER
,DROP
,INSERT
…
(可参考:MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL : https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#privileges-provided-summary)
例:# 允许用户授权 GRANT ALL PRIVILEGES ON *.* TO 'TEST01'@'localhost' WITH GRANT OPTION;
刷新权限
FLUSH PRIVIEGES;
4. [$]测试 MySQL 服务
systemctl status mysql.service
5. [mysql>]TIPS
查看 MySQL 初始密码策略
SHOW VARIABLES LIKE 'validate_password%';
修改用户密码
set password for '[username]'@'[localhost]' = '[newpwd]';
查看用户名,身份验证插件等
select user, host, plugin, authentication_string from mysql.user;
删除用户
DROP USER '[username]'@'[localhost]';
【参考】How To Install MySQL on Ubuntu 20.04 | DigitalOcean : https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04
【参考】MySQL :: MySQL 8.0 Reference Manual :: 13.7.1.10 SET PASSWORD Statement : https://dev.mysql.com/doc/refman/8.0/en/set-password.html