MySQL中怎么修改账号的IP限制条件，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

创建一个测试用户LimitIP，只允许192.168段的IP地址访问，具体权限如下所示：

mysql>GRANTSELECTONMyDB.*TOLimitIP@'192.168.%'IDENTIFIEDBY'LimitIP';QueryOK,0rowsaffected(0.01sec)mysql>GRANTINSERT,UPDATE,DELETEONMyDB.kkkTOLimitIP@'192.168.%';QueryOK,0rowsaffected(0.00sec)mysql>mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>mysql>showgrantsforLimitIP@'192.168.%';+----------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.%|+----------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168.%'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'||GRANTSELECTON`MyDB`.*TO'LimitIP'@'192.168.%'||GRANTINSERT,UPDATE,DELETEON`MyDB`.`kkk`TO'LimitIP'@'192.168.%'|+----------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)mysql>

假设现在收到一个需求：这个用户只允许这个IP地址192.168.103.17访问，于是我打算更新mysql.user表，如下所示：

mysql>selectuser,hostfrommysql.userwhereuser='LimitIP';+---------+-----------+|user|host|+---------+-----------+|LimitIP|192.168.%|+---------+-----------+1rowinset(0.00sec)mysql>updatemysql.usersethost='192.168.103.17'whereuser='LimitIP';QueryOK,1rowaffected(0.02sec)Rowsmatched:1Changed:1Warnings:0mysql>flushprivileges;QueryOK,0rowsaffected(0.01sec)mysql>selectuser,hostfromuserwhereuser='LimitIP';ERROR1046(3D000):Nodatabaseselectedmysql>usemysql;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>selectuser,hostfromuserwhereuser='LimitIP';+---------+----------------+|user|host|+---------+----------------+|LimitIP|192.168.103.17|+---------+----------------+1rowinset(0.00sec)mysql>showgrantsforLimitIP@'192.168.103.17';+---------------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.103.17|+---------------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168.103.17'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'|+---------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>

上面测试发现，如果这样只修改mysql.user表， 那么之前的权限没有了，如下所示，如果你查询mysql.db、 mysql.tables_priv 发现Host的字段值依然为192.168.%

mysql>select*frommysql.dbwhereuser='LimitIP'\G;***************************1.row***************************Host:192.168.%Db:MyDBUser:LimitIPSelect_priv:YInsert_priv:NUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NGrant_priv:NReferences_priv:NIndex_priv:NAlter_priv:NCreate_tmp_table_priv:NLock_tables_priv:NCreate_view_priv:NShow_view_priv:NCreate_routine_priv:NAlter_routine_priv:NExecute_priv:NEvent_priv:NTrigger_priv:N1rowinset(0.00sec)ERROR:Noqueryspecifiedmysql>select*frommysql.tables_privwhereuser='LimitIP'\G;***************************1.row***************************Host:192.168.%Db:MyDBUser:LimitIPTable_name:kkkGrantor:root@localhostTimestamp:0000-00-0000:00:00Table_priv:Insert,Update,DeleteColumn_priv:1rowinset(0.00sec)ERROR:Noqueryspecified

所以我继续修改 mysql.db、 mysql.tables_priv 表，然后测试验证终于OK了(请见下面测试步骤)，当然如果账户的权限不止这几个层面，你可能还必须修改例如mysql.columns_priv、mysql.procs_priv等表

mysql>showgrantsforLimitIP@'192.168.%';ERROR1141(42000):Thereisnosuchgrantdefinedforuser'LimitIP'onhost'192.168.%'mysql>mysql>mysql>updatemysql.dbsethost='192.168.103.17'whereuser='LimitIP';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>updatemysql.tables_privsethost='192.168.103.17'whereuser='LimitIP';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>showgrantsforLimitIP@'192.168.103.17';+---------------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.103.17|+---------------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168.103.17'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'||GRANTSELECTON`MyDB`.*TO'LimitIP'@'192.168.103.17'||GRANTINSERT,UPDATE,DELETEON`MyDB`.`kkk`TO'LimitIP'@'192.168.103.17'|+---------------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)mysql>

如果需要修改用户的IP限制，其实更新mysql相关权限表不是上上策，其实有更好的方法，那就是RENAME USER Syntax

mysql>RENAMEUSER'LimitIP'@'192.168.103.17'TO'LimitIP'@'192.168.103.18';QueryOK,0rowsaffected(0.00sec)mysql>FLUSHPRIVILEGES;QueryOK,0rowsaffected(0.00sec)mysql>showgrantsfor'LimitIP'@'192.168.103.18';+---------------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.103.18|+---------------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168.103.18'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'||GRANTSELECTON`MyDB`.*TO'LimitIP'@'192.168.103.18'||GRANTINSERT,UPDATE,DELETEON`MyDB`.`kkk`TO'LimitIP'@'192.168.103.18'|+---------------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)mysql>

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。