Connect Remotely to MySQL / MariaDB

Categories:

This short tutorial explains how to connect to a MySQL database server from a different machine. While the tutorial was created for MySQL, if you have MariaDB you can follow the same steps.

Why connect remotely to a MySQL server?

Most of the time both the MySQL server and client will be running on the same machine.
However, when you want to connect to your MySQL server running on your Raspberry PI or virtual machine from your MySQL Workbench running on your desktop, you will need the MySQL server to accept connections from other machines.
Another scenario is when you have a more complex setup when your MySQL database is running on one machine and your application server is running on a different machine (I doubt there are many WordPress sites running like this but I’ve seen this setup many times for large enterprise-level applications).
Since usually you do not need to connect remotely, the default mysql configuration is to block connections from other machines.

How to configure MySQL to accept remote connections?

The configuration is quite simple, you will need to add/change a maximum of two lines (depending on your MySQL version)

In the [mysqld] section of your mysql config file, you will need to comment skip-networking directive and set the bind-address directive to allow external incoming connections by setting it to 0.0.0.0 instead of 127.0.0.1 (this will allow connections from any IP; you can also set a specific IP for increased security).
In MySQL 8 the skip-networking directive is no longer present and you can simply comment the bind-address directive (remember, this means anybody that can connect to that IP will also be able to connect to MySQL).

Ubuntu

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

CentOS / Rocky Linux

sudo vi /etc/my.cnf

And make the required changes (you may not see both lines)

# skip-networking
# bind-address           = 127.0.0.1

Restart MySQL after saving the changes.

sudo systemctl restart mysql

How to configure the firewall to allow MySQL connections?

Below are the Ubuntu and CentOS commands to allow MySQL remote access. This is the fastest but least secure setup.

Ubuntu

sudo ufw allow mysql

CentOS / Rocky Linux

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

NOTE: If this is not a private test server (without a public IP), I highly recommend setting your firewall more restrictive to allow access only from specific IPs that you trust.

How to create a user that can connect remotely to MySQL?

For testing purposes we will create a user that has full access to all databases. Type the following commands in MySQL:

CREATE USER 'remoteUser'@'%' IDENTIFIED BY 'password-to-change';
GRANT ALL PRIVILEGES ON *.* TO 'remoteUser'@'%';
FLUSH PRIVILEGES;

If this is not a test/local server you should restrict the privileges as much as possible. For increased security you should avoid allowing remote connections as much as possible. I don’t have and don’t recommend allowing remote connections to a public MySQL server.

Now go to your desktop and create a new connection in MySQL Workbench and verify it works.

Leave a Reply

Your email address will not be published. Required fields are marked *