MySQL IP Whitelisting

🔒 MySQL IP Whitelisting with iptables on a VPS

This guide documents how to allow specific IPs to access MySQL on port 3306 and block all others using iptables.

📋 Step 1: View Current iptables Rules

sudo iptables -L -n --line-numbers

To filter only MySQL (port 3306) rules:

sudo iptables -L INPUT -n --line-numbers | grep 3306

➕ Step 2: Add a New IP Address to Allow MySQL Access

Replace YOUR.IP.ADD.RESS with the IP you want to allow:

sudo iptables -A INPUT -p tcp -s YOUR.IP.ADD.RESS --dport 3306 -j ACCEPT

Example:

sudo iptables -A INPUT -p tcp -s 152.58.69.149 --dport 3306 -j ACCEPT

⚠️ Step 3: Check for DROP Rule Interference

Run:

sudo iptables -L INPUT -n --line-numbers | grep 3306

If you see a rule like:

17   DROP   tcp  --  0.0.0.0/0   0.0.0.0/0   tcp dpt:3306

That means rules below this DROP will not work — they are ignored.

🔁 Step 4: Reorder the DROP Rule

🧽 Delete the DROP rule (replace 17 with actual line number):

sudo iptables -D INPUT 17

💡 Use this again if numbers have shifted:

sudo iptables -L INPUT -n --line-numbers | grep 3306

✅ Re-add the DROP rule at the end:

sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

This ensures all allowed IPs are evaluated before blocking others.

✅ Step 5: Verify Final iptables Rule Order

sudo iptables -L INPUT -n --line-numbers | grep 3306

Example output:

1    ACCEPT     tcp  --  152.58.69.149    0.0.0.0/0    tcp dpt:3306
2    ACCEPT     tcp  --  103.162.217.218  0.0.0.0/0    tcp dpt:3306
...
10   DROP       tcp  --  0.0.0.0/0        0.0.0.0/0    tcp dpt:3306

💾 Step 6: Save iptables Rules (Persistent After Reboot)

Ubuntu/Debian:

sudo apt install iptables-persistent
sudo netfilter-persistent save