Page 1 of 1

How to allow remote connections to a MySQL/MariaDB server

Posted: Wed Jan 15, 2020 4:47 pm
by Spray
First, you'll need to ensure that the database server is configured to listen to remote connections. To do this, we'll need to adjust the my.cnf file. This file is normally located at /etc/my.cnf or /etc/mysql/my.cnf

To edit this file in a text editor such as nano, you'd use the command:

Code: Select all

nano /etc/mysql/my.cnf
The easiest way to ensure that remote connections are allowed is to add the following to the bottom of the my.cnf file:

Code: Select all

[mysqld]
skip-networking=0
skip-bind-address
Once you're finished editing the file in nano, you can save the file using Ctrl+ X, Y, then Enter.

After this, restart MySQL/MariaDB, using a command such as:

Code: Select all

service mysql restart

You'll now need to create a remote user account for the server, which is allowed to connect either from a specific IP address (recommended), a range of IP addresses, or any IP address.

First, open a local database connection using:

Code: Select all

sudo mysql
This will open a Mysql> or MariaDb> prompt, allowing you to query and execute commands for the database server.

To create a user that can connect from a specific IP address (1.2.3.4 in this example), you'd execute the following commands (filling in the 1.2.3.4 IP, username, and password):

Code: Select all

CREATE USER 'username'@'1.2.3.4' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'1.2.3.4';

To create a user that can connect from a range of IPs that share a common domain, such as IPs on our network using our default rDNS entry, you'd execute the following commands (filling in the username and password):

Code: Select all

CREATE USER 'username'@'%.nfoservers.com' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%.nfoservers.com';

To create a user that can connect from any IP address, you'd execute the following commands (filling in the username and password):

Code: Select all

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

After you create the user, you'll want to flush privileges for this to take effect:

Code: Select all

FLUSH PRIVILEGES;
Finally, you can exit MySQL/MariaDB, using:

Code: Select all

exit