How to allow remote connections to a MySQL/MariaDB server
Posted: 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:
The easiest way to ensure that remote connections are allowed is to add the following to the bottom of the my.cnf file:
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:
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:
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):
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):
To create a user that can connect from any IP address, you'd execute the following commands (filling in the username and password):
After you create the user, you'll want to flush privileges for this to take effect:
Finally, you can exit MySQL/MariaDB, using:
To edit this file in a text editor such as nano, you'd use the command:
Code: Select all
nano /etc/mysql/my.cnf
Code: Select all
[mysqld]
skip-networking=0
skip-bind-address
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
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;
Code: Select all
exit