How to allow remote access to MySQL

Introduction

Many websites and applications start with their web server and supporting database hosted on a single machine. Over time, however, a setup like this can become cumbersome and difficult to scale. A common solution is to separate these functions by running a remote database and allowing the server and database to grow at their own pace on their own machines.

One of the most common problems users encounter when trying to set up a MySQL database remotely is that their MySQL instance is configured to only listen for local connections. This is the default MySQL setting, but it won't work for setting up a remote database, as MySQL needs to be able to listen on an external IP address where the server can be reached. To enable this, open your mysqld.cnf file:

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

Go to the line that starts with the bind-address command. It will look like this:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
. . .

By default, this value is set to 127.0.0.1, which means the server will only listen for local connections. You will need to change this directive to refer to an external IP address. For troubleshooting purposes, you can set this directive to a wildcard IP address, either *, ::, or 0.0.0.0:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
. . .

After changing this line, save and close the file (CTRL + X, Y, then ENTER if you edited it with nano).

Then restart the MySQL service to apply the changes you made to mysqld.cnf:

sudo systemctl restart mysql

If you have a MySQL user account that you plan to use to connect to the database from your remote host, you need to reconfigure that account to connect from the remote server instead of the local host. To do this, open the MySQL client as the MySQL root user or with another privileged account:

sudo mysql

If you have password authentication enabled for root, you should use the following command to access the MySQL shell instead:

mysql -u root -p

To change the user host, you can use the RENAME USER MySQL command. Run the following command, making sure to replace sammy with your MySQL user account name and remote_server_ip with the IP address of your remote server:

RENAME USER 'sammy'@'localhost' TO 'sammy'@'remote_server_ip';

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

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED BY 'password';

Then grant the new user the appropriate privileges for your specific needs. The following example gives the user global privileges to create, alter, and drop databases, tables, and users, as well as the power to insert, update, and delete data from any table on the server. It also allows the user to query data with SELECT, create foreign keys with the REFERENCES keyword, and perform FLUSH operations with the RELOAD privilege. However, you should only grant users the permissions they need, so you can adjust your user privileges as needed.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;

After this, it is good practice to run the FLUSH PRIVILEGES command. This will free up any memory the server has reserved as a result of previous CREATE USER and GRANT commands:

FLUSH PRIVILEGES;

Then you can exit the MySQL client:

exit

Finally, assuming you have configured a firewall on your database server, you will also need to open port 3306 – the default MySQL port – to allow MySQL traffic.

If you only want to access the database server from a specific machine, you can give that machine exclusive permission to connect to the remote database with the following command. Make sure to replace remote_IP_address with the actual IP address of the machine you want to connect to:

sudo ufw allow from remote_IP_address to any port 3306

If you need to access the database from other machines in the future, you can use this command to temporarily grant them access. Just remember to include your respective IP address.

Alternatively, you can allow connections to your MySQL database from any IP address with the following command:

sudo ufw allow 3306

Following this, try accessing your database remotely from another device:

mysql -u user -h database_server_ip -p

If you can access your database, it confirms that the bind-address directive in your configuration file was the problem. Please note that setting bind-address to 0.0.0.0 is insecure as it allows connections to your server from any IP address. On the other hand, if you still cannot access the database remotely, there may be another issue causing the problem.