How to run MySQL server on specific IP

The appropriate solution I found for running MySQL server on specific IP addresses is to run it at all and then filter the connected clients through iptables.

For the test, I used Ubuntu Server 16.04.5 LTS, which had more than 200 external white IPs and was highly loaded.

MySQL server was installed like this:

sudo apt-get install mysql-server mysql-client
mysql -V
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

The MySQL server needed access from localhost and several addresses on the Internet.
So I started the MySQL server on all IPs, commenting out the “bind-address” in the configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address = 127.0.0.1
#skip-networking
sudo service mysql restart

Created users for hosts from which the connections will be made, see my article for more details – How to create a MySQL user and configure access rights

Then through iptables I allowed connections only with the necessary IP:

/sbin/iptables -A INPUT -s 127.0.0.1 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -s 192.168.1.5 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP

The DROP rule should only be the last one, if you still need to add an IP to iptables, then delete the DROP and at the end, add:

/sbin/iptables -D INPUT -p tcp --dport 3306 -j DROP
/sbin/iptables -A INPUT -s 192.168.5.33 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP

By the way, on the test when trying to specify several addresses in the bind-address, MySQL was launched only on the last one:

bind-address = 192.168.1.11
bind-address = 127.0.0.1

Alternatively, I have another idea, run MySQL only on 127.0.0.1 (bind-address = 127.0.0.1), redirect it to iptables to any external IP, and then you can still restrict access to the rules above):

sudo sysctl -w net.ipv4.conf.all.route_localnet=1
sudo iptables -t nat -A PREROUTING -i enp0s3 -p tcp -d 192.168.1.11 --dport 3306 -j DNAT --to-destination 127.0.0.1:3306

See also my articles:
IPTables rules for MySQL
Installing and configuring a MySQL server on Ubuntu

Подписаться на IT Blog (RU) по Email
Subscribe to IT Blog (EN) by Email

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a Reply