用户管理
- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'A##..123aaa';
# 修改密码
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');
- 删除用户
DROP USER 'username'@'localhost';
权限管理
-
8.0 以前版本
>> / * 可以直接创建用户的同时授权 * /
mysql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'pwd' WITH GRANT OPTION;
/ * 必须执行这个在退出 * /
mysql > FLUSH PRIVILEGES;
/ * 到这一步便可以远程登录 */
-
8.0 之后版本
- 先从本地登录mysql
mysql > use mysql- 创建准备使用的数据库以及管理用户
mysql > CREATE database nextcloud_db; / * localhost 只可以本地访问，需要开启远程访问，需要配置为%, 8.0 以后以后密码为强密码，需要使用大写数字及特殊符号同时组成 * / mysql > CREATE USER 'nextcloud-root'@'localhost' IDENTIFIED BY 'A##..123aaa'; / * 授权 * / mysql > GRANT ALL ON nextcloud_db.* TO 'nextcloud-root'@'localhost' WITH GRANT OPTION; / * 更新 * / mysql > FLUSH PRIVILEGES;- 查看user 插件
mysql > select Host,User,plugin from user; / * 如下 * / +-----------+------------------+-----------------------+ | Host | User | plugin | +-----------+------------------+-----------------------+ | % | nextcloud-root | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | | localhost | root | caching_sha2_password | +-----------+------------------+-----------------------+ 5 rows in set (0.00 sec)8.0 之后版本插件为caching_sha2_password 远程登录会报错，其实是对安全做了更加严格的限制
- 修改plugin
mysql > alter user 'nextcloud-root'@'%' identified with mysql_native_password by '123456'; mysql > select Host,User,plugin from user where User='nextcloud-root'; /* 如下 */ +------+----------------+-----------------------+ | Host | User | plugin | +------+----------------+-----------------------+ | % | nextcloud-root | mysql_native_password | +------+----------------+-----------------------+ 1 row in set (0.00 sec)
此时可以通过远程访问正常登录
- 如果需要收回权限
mysql > REVOKE privilege ON nextcloud_db.* FROM 'nextcloud-root'@'%';
小贴士 MySQL服务器允许的最大连接数16384
> / * 查看mysql最大连接数 */
show variables like 'max_connections';
/ * 查看所有的慢查询 */
show variables like '%slow%';
/ * 查看所有的临时表 */
show global status like 'created_tmp%';
