用戶管理
- 創(chuàng)建用戶
CREATE USER 'username'@'localhost' IDENTIFIED BY 'A##..123aaa';
# 修改密碼
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');
- 刪除用戶
DROP USER 'username'@'localhost';
權限管理
-
8.0 以前版本
>> / * 可以直接創(chuàng)建用戶的同時授權 * /
mysql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'pwd' WITH GRANT OPTION;
/ * 必須執(zhí)行這個在退出 * /
mysql > FLUSH PRIVILEGES;
/ * 到這一步便可以遠程登錄 */
-
8.0 之后版本
- 先從本地登錄mysql
mysql > use mysql- 創(chuàng)建準備使用的數(shù)據(jù)庫以及管理用戶
mysql > CREATE database nextcloud_db; / * localhost 只可以本地訪問噪生，需要開啟遠程訪問，需要配置為%, 8.0 以后以后密碼為強密碼，需要使用大寫數(shù)字及特殊符號同時組成 * / 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服務器允許的最大連接數(shù)16384
> / * 查看mysql最大連接數(shù) */
show variables like 'max_connections';
/ * 查看所有的慢查詢 */
show variables like '%slow%';
/ * 查看所有的臨時表 */
show global status like 'created_tmp%';
