Mysql 常用指令

MySQL 相关：https://blog.huixiangwuyou.com/2023/05/04/db/mysql/#%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4

创建用户

# 创建用户
CREATE USER 'xxx'@'xxx' IDENTIFIED BY 'xxxxxx';
# 授予权限
GRANT SELECT, CREATE, INSERT, UPDATE, DELETE ON *.* TO 'mn'@'%';
# 刷新权限
FLUSH PRIVILEGES;
# 查看用户的权限是否支持远程 %
SELECT user, host FROM mysql.user;
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mn               | %         |
| miss             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
# 给远程的用户设置权限
UPDATE mysql.user SET host = '%' WHERE user = 'soaeon';
# 查看权限
 SHOW GRANTS FOR "mn"@"%";
+-----------------------------------------------------------------+
| Grants for mn@%                                                 |
+-----------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON *.* TO `mn`@`%` |
+-----------------------------------------------------------------+

若是还是出现 缺少 ALTER 权限

-- 授予所有数据库的 ALTER 权限
GRANT ALTER ON *.* TO 'mn'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

# 修改权限网上解决办法：先撤销所有权限，然后再授权  结果还是不行
GRANT ALL PRIVILEGES ON database_name.* TO "mn"@"%";
REVOKE ALL PRIVILEGES ON database_name.* FROM "mn"@"%";

# 最好的是先撤销全部权限，然后再授权 以最小权限为原则
REVOKE ALL PRIVILEGES ON *.* FROM "mn"@"%";
# 给予一个数据库下表的查询权限
GRANT SELECT ON database_name1.* TO "mn"@"%";
# 给予另一个数据库表所有的权限
GRANT all ON database_name2.* TO "mn"@"%";
# 给予另一个数据库指定表所有的权限
GRANT all ON database_name2.table_name TO "mn"@"%";
# 修改权限要刷新
FLUSH PRIVILEGES;