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