Linux: Iptables Allow MYSQL server incoming request on port 3306

by on July 28, 2005 · 9 comments· LAST UPDATED October 1, 2007

in , ,

MySQL is popular for web applications and acts as the database component of the LAMP, MAMP, and WAMP platforms. Its popularity as a web application is closely tied to the popularity of PHP, which is often combined with MySQL.

MySQL is open source database server and by default it listen on TCP port 3306.

Task: Open port 3306

In most cases following simple rule opens TCP port 3306:
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

Following iptable
rules allows incoming client request (open port 3306) for server IP address 202.54.1.20. Add rules to your iptables shell script:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 202.54.1.20 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT-p tcp -s 202.54.1.20 --sport 3306 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

However in real life you do not wish give access to everyone. For example in web hosting company, you need to gives access to MySQL database server from web server only. Following example allows MySQL database server access (202.54.1.20) from Apache web server (202.54.1.50) only:

iptables -A INPUT -p tcp -s 202.54.1.50 --sport 1024:65535 -d 202.54.1.20 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 3306 -d 202.54.1.50 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Please note if you follow above setup, then you need tell all your hosting customer to use 202.54.1.50 as MySQL host in PHP/Perl code. A better approach is to create following entry in /etc/hosts file or use fully qualified domain name (create dns entry) mysql.hostingservicecompany.com which points to 202.54.1.50 ip:
202.54.1.50 mysql

In shot MySQL database connection code from PHP hosted on our separate webserver would look like as follows:

// ** MySQL settings ** //
define('DB_NAME', 'YOUR-DATABASE-NAME');     // The name of the database
define('DB_USER', 'YOUR-USER-NAME');     // Your MySQL username
define('DB_PASSWORD', 'YOUR-PASSWORD''); // ...and password
define('DB_HOST', 'mysql'); 
// ** rest of PHP code ** //

Task: Allow outgoing MySQL request on TCP port 3306

Even you can allow outgoing MySql client request (made via mysql command line client or perl/php script), from firewall host 202.54.1.20:

iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 1024:65535 -d 0/0 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A INPUT -p tcp -s 0/0 --sport 3306 -d 202.54.1.20 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 9 comments… read them below or add one }

1 samsonasik July 3, 2009 at 5:03 pm

thanks for your post, my problem solved.

Reply

2 leila August 6, 2010 at 8:08 pm

hi
thanks it’s a great text, but when i tested the “iptables -A INPUT -i eth0 -p tcp -m tcp –dport 3306 -j ACCEPT” ,this error had appeared :
iptables v1.4.4: can’t initialize iptables table `filter': permission denied(you must be root)

while i’m root, what can i do with it ?
thanks

Reply

3 cjk September 21, 2010 at 6:07 pm

Your commands apply only to a very strict subset of possible iptables configurations. This advice is therefore practically useless.
Also you should be using -m conntrack –ctstate ..

Reply

4 vishal borade July 22, 2011 at 11:46 am

use
# sudo iptables -A INPUT -i eth0 -p tcp -m tcp –dport 3306 -j ACCEPT

Reply

5 SIva October 11, 2011 at 12:24 pm

Im using centos 6 and executed the 1’st line code given by you…and I still have issue.

Error : Unable to connect to any of the specified hosts.

When i disable Iptables I’m able to connect…What would be the issue and what needs to be done

Reply

6 Phoz January 8, 2013 at 2:25 pm

Flushing the existing rules solved it for me:
iptables -F

Reply

7 Charlie March 17, 2014 at 2:15 pm

Don’t use the -F command, this deletes all the rules. The other comment should be deleted.

Reply

8 Danny February 5, 2013 at 6:29 pm

If you use the -F argument, you’ll delete your firewall rules.

man shows this…
-F, –flush [chain]
Flush the selected chain (all the chains in the table if none is given). This is equivalent to deleting all the rules one by one.

Reply

9 bunga April 24, 2013 at 8:30 am

nice tutor..but i can not connect to my remote centos server, using navicat on my pc.
the server port has been opened.

Reply

Leave a Comment

Tagged as: , , ,

Previous post:

Next post: