How to Allow Remote Connections to MySQL

The default MySQL server listens only on local host interface. Which means no one can connect MySQL from the remote system. This is a good practice for security purposes on productions environments. But in case of database and web servers are running separately, needs to allow MySQL remote connections.

MySQL allows us to define an IP address to listen. You can provide the IP address of LAN network, which allow access MySQL from local network only. To allow the public network, you can simply define all zero’s (0.0.0.0) as an IP address to allow MySQL remote connection for any host.

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.

Step 4 – Connect Remote MySQL

You must have mysql client packages installed on your system to connect remote MySQL server.

mysql -h 192.168.0.100 -p 3306 -u DBUSER -pPASSWORD 

where:

• -h Hostname or IP address of the remote MySQL server (default: localhost)
• -p Port number of the MySQL server (default: 3306)
• -u MySQL username
• -p Password for the defined username, No space between -p and the password

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.