-- 允許192.168.1.100通過(guò)用戶名user1和密碼password123訪問(wèn)所有數(shù)據(jù)庫(kù)
CREATE USER 'user1'@'192.168.1.100' IDENTIFIED BY 'password123';

-- 授權(quán)該用戶對(duì)所有數(shù)據(jù)庫(kù)的所有權(quán)限（生產(chǎn)環(huán)境建議按需授權(quán)）
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.1.100';

-- 刷新權(quán)限使設(shè)置立即生效
FLUSH PRIVILEGES;

-- 允許192.168.1.%整個(gè)網(wǎng)段訪問(wèn)特定數(shù)據(jù)庫(kù)
CREATE USER 'user2'@'192.168.1.%' IDENTIFIED BY 'password456';
GRANT SELECT, INSERT ON mydb.* TO 'user2'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 將用戶user3的訪問(wèn)IP從原地址改為新地址
UPDATE mysql.user SET host='10.0.0.50' WHERE user='user3' AND host='192.168.2.100';
FLUSH PRIVILEGES;

-- 更規(guī)范的修改方式（MySQL 5.0.2+支持）
RENAME USER 'user4'@'old_ip' TO 'user4'@'new_ip';

# 只允許特定IP訪問(wèn)MySQL默認(rèn)端口(3306)
iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.100 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

# 保存規(guī)則（CentOS/RHEL）
service iptables save

-- 在MySQL中查看當(dāng)前連接的客戶端IP
SELECT host FROM information_schema.processlist WHERE ID=CONNECTION_ID();

[mysqld]
# 只監(jiān)聽(tīng)內(nèi)網(wǎng)IP（替換為你的實(shí)際IP）
bind-address = 192.168.1.10

# 禁止域名解析（提高性能和安全）
skip-name-resolve

# 從客戶端測(cè)試指定IP是否可連接
mysql -u user1 -p -h 192.168.1.10

grant all on *.* to 'test'@'%' identified by 'pwd';

grant select,insert,update,delete on mydb.* to 'test'@'1.1.1.1' identified by 'pwd';

grant update(name,age) on mydb.stu to 'test'@'1.1.1.1' identified by 'pwd';