How to Allow Remote Connections to MySQL

The default MySQL server listen on localhost only. Which means no one can connect MySQL from the remote system. Which is also a good practice for the security purposes.

In many cases we needs to access MySQL database from remote systems. In the most possible cases of production environment where we use separate instance for database server. Also in case of application hosted under load balance environment, where we create a single centralize database server accessible from multiple systems.

This tutorial will help you to configure MySQL server to allow connections from remote systems.

Step 1 – Configure MySQL Service

The default MySQL server listen on localhost interface only. You need to change this to listen on all interface.

Edit MySQL database server configuration file in a text editor. The Debian based systems have MySQL configuration fiel at /etc/mysql/mysql.conf.d/mysqld.cnf. Let’s Edit MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf 

Search for the bind-address setting in file. The default bind-address is set to 127.0.0.1 as below:

Change bind-address value to 0.0.0.0 to enable MySQL database server listen on all network interfaces.

After change the value will look like above.

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address    = 0.0.0.0

Save the configuration file and close it.

Restart MySQL service to apply changes.

systemctl restart mysql.service 

Now, verify the changes and MySQL is listenning on from all remote hosts. Execute the following command to view service status:

netstat -tulpn  | grep -e "Local Address" -e "mysql" 

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1698595/mysqld
tcp6       0      0 :::33060                :::*                    LISTEN      1698595/mysqld

You will see that MySQL is now listening on 0 0.0.0.0:3306 local address. It means, server is now listening on all network interfaces on port 3306.

Step 2 – Create MySQL User with Remote Access

Next, you need to create MySQL user to connect from remote host.

mysql> CRATE USER 'username'@'remote_server_ip_or_hostname' IDENTIFIED BY 'pa$$word';

• username – The name of user account used for login to MySQL server
• remote_server_ip_or_hostname – This is the ip address or hostname of remote host from where user is allowed to connect mysql server.

You can also use ‘%’ instead of remote host to allow any remote host. For example:

mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'pa$$word';

Alternatively, you can create a new user account that will only connect from the remote host with the following command:

mysql> GRANT ALL on *.* TO 'username'@'remote_server_ip_or_hostname';

Reload the privileges to running MySQL server.

mysql> FLUSH PRIVILEGES;

At this point, you have configured your MySQL server to listen from remote hosts. Created a MySQL user account used for authentication from remote hosts.

Next, is to adjust security groups or system firewall to open MySQL port for remote systems.

Step 3 – Adjust Firewall

Before changing system firewall, the system running in cloud hosting environments must have update security group to open MySQL port based on hosting services.

Then, you need to open port in system firewall (if active). For example, most of the Linux system opted firewalld and some of system like Debian based systems uses UFW firewall.

Update the firewall based on your operating systems.

Using FirewallD

The FirewallD is the default firewall services in CentOS and RedHat based systems. You can use the following command to open MySQL port 3306 for everyone, but it it not recommended for production servers.

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp 
sudo firewall-cmd --reload 

The production server are recommended to open port for required hosts only. The FirewallD uses can use rich rules to open specific port for specific ip address or network only. For example:

firewall-cmd --permanent --zone=public --add-rich-rule='
  rule family="ipv4"
  source address="192.168.0.0/24"
  port protocol="tcp" port="3306" accept' 

After adding required rule in firewalld, make sure to reload to apply changes at runtime.

sudo firewall-cmd --reload 

Using UFW

The UFW (Uncomplicated Firewall) is the default firewall tool in Ubuntu and Debian systesm. You can use the following command to open port 3306 from everyone.

sudo ufw allow 3306/tcp 

But the productions users are recommended to open port for specific ip or network only. To Allow access from a specific IP addresses use command like:

sudo ufw allow from 192.168.0.0/24 to any port 3306 

Make sure to change 192.168.0.0/24 with your network or system’s IP address.

Conclusion

This tutorial helped you to setup MySQL serer to accept remote connection from remote hosts. Also created a MySQL user account allowed to connect from remote hosts.