How to Allow Remote Access to MySQL

Remote access to MySQL lets you administer and use databases from another computer or application outside the server.  This is especially useful when developing all kinds of systems (for example, websites, game servers and more), that need to reach a database server that isn’t on the local network.

A common problem when setting up a remote database is that MySQL is configured to accept only local connections. That is MySQL's default behavior, but it won’t work for a remote-database setup because MySQL must also accept external IP addresses. In this tutorial you’ll learn how to configure MySQL for remote access on Linux.

Preparation

Before you begin, SSH into your Linux server (e.g., with PuTTY). Enter your server’s IP address and log in as a regular user.

Editing the MySQL configuration

Open the MySQL server configuration file:

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

Inside the file, press CTRL + W, search for bind-address. You should see something like:

. . .
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
. . .

Default setting is 127.0.0.1, which allows the server to reach only local connections. Change this setting to 0.0.0.0 - this will let you reach your server over a remote network.

. . .
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
. . .

Some MySQL versions don't have a bind-address setting in the configuration file. In that case, at the end of the file add this line:

. . .
[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

After changing this setting, save the file with keyboard shortcuts: CTRL + X, then Y and Enter.

Restart the MySQL service for the configuration changes to take effect. Use the following command:

sudo systemctl restart mysql

MySQL User Configuration

If you already have a MySQL user account you plan to use for a remote server, reconfigure it to connect from that server instead of localhost. First, open the MySQL client as a root user (or other privileged account) by using this command:

sudo mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

In that case, use this command to access MySQL shell:

mysql -u root -p

To change MySQL user host from local to remote, you can use the RENAME USER command. Enter the following command (make sure to change "username" and "remote_server_ip" to your username and server address):

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

For example, if a MySQL user is - redfoxcloud, and the server's IP address is: 1.2.3.4, command should be this:

RENAME USER 'redfoxcloud'@'localhost' TO 'redfoxcloud'@'1.2.3.4';

Else, you can create a new account which can only connect remotely:

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

Note: This command will create a user which will be able to authenticate with default MySQL authentication plugin - caching_sha2_password. However, certain PHP versions have issues with it.

If you plan on using this database with PHP (for example, phpMyAdmin), you might create a user that uses an older (but still secure) - mysql_native_password plugin:

CREATE USER 'username'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

Next, grant the user the necessary privileges for your use case. The example below gives the user full rights: they will be able to create, modify, and drop databases, tables, and users, as well as add, update, and delete data in any table on the server. However, you should grant users only the permissions they actually need, so be sure to adjust privileges accordingly.

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

At the same time, it is recommended to run the FLUSH PRIVILEGES command, which frees any memory the server has cached after the earlier CREATE USER and GRANT commands.

mysql> FLUSH PRIVILEGES;

After these steps you can exit the MySQL prompt:

mysql> exit

Finally, if your server is running a firewall, you should also open port 3306 to allow MySQL traffic.

sudo ufw allow 3306

If you want MySQL to be reachable from only one external computer, you can open the port for a specific IP address:

sudo ufw allow from išorinis_serverio_ip to any port 3306

Testing the configuration

From another computer on the external network, try to reach the MySQL server by using this command:

mysql -u user -h išorinis_serverio_ip -p

If the connection is successful, you have completed all the steps correctly.

So, to enable remote access to MySQL you need to change one configuration setting and reconfigure the MySQL user for external connections.  
If you run into any issues, contact us through live chat or by email at [email protected].