CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass#2024';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `app_db`.* TO 'app_user'@'192.168.1.%';
FLUSH PRIVILEGES;
192.168.1.%:限定應用服務器網段,比 %' 更安全。GRANT ALL。CREATE USER 'root'@'%' IDENTIFIED BY 'VeryStrongPass#2024';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
事後務必刪除或改回 localhost。
REVOKE ALL PRIVILEGES ON *.* FROM 'app_user'@'192.168.1.%';
DROP USER 'app_user'@'192.168.1.%';
FLUSH PRIVILEGES;
bind-address = 0.0.0.0(/etc/my.cnf [mysqld]),重啟生效。skip-networking 必須**註釋/關閉**。skip-name-resolve 建議開啟(避免 DNS 延遲),授權用 IP。SELECT User, Host FROM mysql.user WHERE User='app_user';
-- 確認 Host 匹配客戶端 IP(或 %)
# firewalld (CentOS 7+)
firewall-cmd --permanent --add-port=3306/tcp --zone=public
firewall-cmd --reload
# iptables
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
service iptables save
# 客戶端機器執行
telnet db_host 3306
# 或
nc -zv db_host 3306
# 或
nmap -p 3306 db_host
若顯示 Connected/open,網路通;否則回查層級 3/4。
| 錯誤碼 | 含義 | 首要排查 |
|---|---|---|
| 1130 | Host not allowed | mysql.user Host 欄位、授權 IP/網段 |
| 2003 (111) | Connection refused | bind-address、防火牆、MySQL 是否運行 |
| 2003 (113) | No route to host | 路由、VPN、安全組、IP 變更 |
| 2013 | Lost connection during query | max_allowed_packet、wait_timeout、網路抖動 |
root 遠程:DELETE FROM mysql.user WHERE User='root' AND Host!='localhost'; FLUSH PRIVILEGES;GRANT ... REQUIRE SSL;,配置 ssl_ca/cert/key。validate_password:長度≥12、混合大小寫數字符號、過期 90 天。audit_log 插件,記錄所有連線/查詢/變更。SELECT User, Host, Db FROM mysql.db WHERE User NOT IN ('mysql.sys','root');