Mysql作为常用的关系型数据库，其权限管理功能可以精细化控制用户的访问来源，通过限制用户只能从指定IP地址操作数据库，能够有效降低数据库被非法访问的概率，提升数据存储的安全性。
设置用户指定IP操作数据库的核心逻辑
Mysql的用户权限信息存储在mysql系统库的user表中，其中host字段用于标识允许该用户登录的IP地址或地址段。当我们创建用户或者修改用户权限时，只需要正确设置host字段的值，就可以实现指定IP访问的限制。
不同场景下的具体配置步骤
1. 允许本地指定IP操作数据库
如果需要限制用户只能从数据库所在的服务器本地某个IP登录，比如本地回环地址127.0.0.1，可以使用如下语句创建用户并授权：
-- 创建用户，host设置为127.0.0.1，密码为test123 CREATE USER 'test_user'@'127.0.0.1' IDENTIFIED BY 'test123'; -- 授予该用户操作test_db数据库的所有权限 GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'127.0.0.1'; -- 刷新权限使配置生效 FLUSH PRIVILEGES;
2. 允许远程单个指定IP操作数据库
如果需要让远程某台固定服务器（比如IP为192.168.0.100的服务器）访问数据库，只需要将host设置为对应的远程IP即可：
-- 创建允许192.168.0.100访问的用户 CREATE USER 'remote_user'@'192.168.0.100' IDENTIFIED BY 'remote123'; -- 授予该用户操作test_db数据库的查询、插入、更新、删除权限 GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO 'remote_user'@'192.168.0.100'; -- 刷新权限 FLUSH PRIVILEGES;
3. 允许指定IP段操作数据库
如果有一批IP地址在同一个网段内都需要访问数据库，比如192.168.0.0到192.168.0.255的IP段，可以使用百分号通配符来设置host：
-- 创建允许192.168.0网段所有IP访问的用户 CREATE USER 'segment_user'@'192.168.0.%' IDENTIFIED BY 'segment123'; -- 授予该用户操作test_db数据库的所有权限 GRANT ALL PRIVILEGES ON test_db.* TO 'segment_user'@'192.168.0.%'; -- 刷新权限 FLUSH PRIVILEGES;
已存在用户的IP权限修改方法
如果已经有创建好的用户，需要修改其允许访问的IP地址，可以直接更新mysql.user表的host字段，或者先删除旧用户再创建新用户：
-- 方法1：直接更新user表的host字段 UPDATE mysql.user SET host='192.168.0.100' WHERE user='test_user' AND host='127.0.0.1'; FLUSH PRIVILEGES; -- 方法2：删除旧用户后创建新IP权限的用户 DROP USER 'test_user'@'127.0.0.1'; CREATE USER 'test_user'@'192.168.0.100' IDENTIFIED BY 'test123'; GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'192.168.0.100'; FLUSH PRIVILEGES;
权限验证与注意事项
配置完成后，可以通过以下方式验证用户的IP权限是否生效：
- 在目标IP的服务器上使用配置的用户名和密码尝试连接Mysql，检查是否能正常登录
- 登录后执行
SELECT user(), current_user();语句，查看当前登录用户对应的host是否为设置的指定IP
需要注意的事项：
- 每次修改用户权限或者
host字段后，必须执行FLUSH PRIVILEGES;语句刷新权限，否则配置不会立即生效 - 如果用户需要同时从多个不同IP访问数据库，需要为每个IP分别创建对应的用户记录，或者使用通配符覆盖多个IP
- 生产环境中尽量不要授予用户
ALL PRIVILEGES权限，按照最小权限原则只授予对应业务需要的操作权限即可