Accessing MySQL/MariaDB from outside the server

By default, a MySQL/MariaDB database server only allows connections from the local network. If you want to make the server available on all interfaces, you need to make a few changes to its settings.

Configuration file

First, you need to make a change in the configuration file.

Go to its location.

cd /etc/mysql/mariadb.conf.d

Then open it for editing.

sudo nano 50-server.cnf

First, make sure you do not have the setting below. If you do, remove it or comment it out with #. Older versions of MySQL included this entry by default.

#skip-networking

Next, find the bind-address entry and replace its value, which points to the local network,

bind-address = 127.0.0.1

with one that allows connections from any device on the network.

bind-address = 0.0.0.0

Unfortunately, you cannot add several addresses or an address range here. To do that, you need to configure the firewall accordingly. Also remember that on a server storing important data, such a configuration is absolutely necessary! To keep this tutorial simple, I will not cover it here.

Permissions

By default, the MySQL server should already have the 0.0.0.0 configuration (but check it to make sure). If the problem still occurs, it is probably caused by permissions. To fix this, start the server command line by entering:

sudo mysql -u root -p

Then grant permissions to the appropriate database:

GRANT ALL PRIVILEGES ON *.* TO 'YOUR_MYSQL_USERNAME'@'192.168.1.%' IDENTIFIED BY 'YOUR_MYSQL_PASSWORD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'YOUR_MYSQL_USERNAME'@'10.8.0.%' IDENTIFIED BY 'YOUR_MYSQL_PASSWORD' WITH GRANT OPTION;

You can check the added entries with:

SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

If you made a mistake, no problem. You can remove these permissions with a similar command.

DROP USER 'YOUR_MYSQL_USERNAME'@'192.168.1.%';

You can now save the settings and exit the MySQL console.

FLUSH PRIVILEGES;
exit

The only thing left is to restart the service.

sudo service mysql restart
#or
sudo service mariadb restart