How to allow remote connections to a MySQL/MariaDB server

Post Reply
User avatar
Spray
Staff
Staff
Posts: 572
Joined: Wed Dec 28, 2011 10:41 pm
Location: Oregon

How to allow remote connections to a MySQL/MariaDB server

Post by Spray » Wed Jan 15, 2020 4:47 pm

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

Post Reply