Tips - Allow Remote connections to mySQL Database Server

in IT on Linux, Tips Last modified at:
Tips - Allow Remote connections to mySQL Database Server

In this Tips, we will go through the steps necessary to allow remote connections to a mySQL server. The same instructions can be apply for MariaDB.
Mika Baumeister©

By default, the mySQL allow connections only from localhost (127.0.0.1), which means it can be accessed only by applications running on the same host.

However, in some situations, it is necessary to access the mySQL server from remote location. For example, when you want to connect to the remote mySQL server from your local system, or when using a multi-server deployment where the application is running on a different machine from the database server. One option would be to access the mySQL server through SSH Tunnel and another is to configure the mySQL server to accept remote connections.

Test on Debian 10 (buster) and mySQL v10.3.2.2

Note that the port number used for accepting incoming TCP connections is the same for every IP address listed in the –bind-address option.

  1. in /etc/mysql/mariadb.conf.d/50-server.cnf change line bind-address = 127.0.0.1 to bind-address = 0.0.0.0. The address 0.0.0.0 allow all. If there is a line containing skip-networking, delete it or comment it out by adding # at the beginning of the line.
     bind-address = 0.0.0.0
 #skip-networking
  2. On mySQL server host :
     sudo mysql --user=root mysql
 GRANT ALL ON <database_name>.* TO <user_name>@<ip_address> IDENTIFIED BY <user_password>;

    Where:

    • <database_name> is the name of the database that the user will connect to.
    • <user_name> is the name od the mySQL user.
    • <ip_address> is the IP address from which the user will connect. Use % to allow the user to connect from any IP address.
    • <user_password> is the user password.

    ie. <ip_address> : 192.168.% for allow the range of IP 192.168.0.0 to 192.168.255.255 or % for allow all IP

  3. Restart mysqld
     sudo systemctl restart mysqld
  4. Configuring Firewall The last step is to configure your firewall to allow traffic on port 3306 (mySQL default port) from the remote machines.
    • If you are using iptables as your firewall, the command bellow will allow access from private IP to the mySQL port.
       # Block all connections to 3306 except for localhost and all private IPs
 /sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 127.0.0.0/8 -j DROP
 /sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 10.0.0.0/8 -j DROP
 /sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 172.16.0.0/12 -j DROP
 /sbin/iptables -A INPUT -p tcp --dport 3306 ! -s 192.168.0.0/16 -j DROP
    • Save the settings:
       sudo /sbin/iptables-save

Since mySQL Server v8.0.13, It’s possible to set more than one specific eth interface. So if you have a server with addresses 192.168.1.1 and 10.0.0.1, you can specify the binding like: bind-address=192.168.1.1,10.0.0.1. With 192.168.1.1 for eth0 and 10.0.0.1 for eth1

Share it :