If you need to allow remote access to your MySQL server, a good security practice is to only allow access from one or more specific IP addresses. This way, you are not needlessly exposing an attack vector to the entire internet.
In this tutorial, we will take you through the step by step instructions to allow remote connections to a MySQL server from a specific IP address on a Linux system. These instructions should work independently of whichever Linux distro you are using.
In this tutorial you will learn:
- How to allow remote connections to MySQL server
- How to allow remote connections to MySQL through system firewall from specific IP
- How to create or alter a MySQL user to allow remote connections from specific IP
| Category | Requirements, Conventions or Software Version Used |
|---|---|
| System | Linux system |
| Software | MySQL |
| Other | Privileged access to your Linux system as root or via the sudo command. |
| Conventions |
# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command$ – requires given linux commands to be executed as a regular non-privileged user |
MySQL: Allow access from specific IP address step by step instructions
By default, the MySQL service is configured to only accept connections coming from the same computer. In other words, the bind address is set to local loopback address
127.0.0.1. Before we can accept connections from any other IP address, we will need to change this setting in the MySQL configuration file.
Therefore, allowing remote connections to your MySQL database from a specific IP address is a three step process.
First, we will need to setup the MySQL service to be accessible from remote machines by configuring a public bind address in the MySQL configuration file.
Second, we will need to allow remote access through our system firewall. By default, MySQL runs on port 3306, so connections to this port will need allowed through, and it is no problem to only allow those connections from the IP addresses we specify.
Third, we will need to create a new user or edit an existing one to make it accessible from a specific IP address.
Configure MySQL bind address
- We will start by opening the
/etc/mysql/mysql.cnffile. With root permissions, open this in nano or your favorite text editor.$ sudo nano /etc/mysql/mysql.cnf
- Find the setting that says
bind-addressunderneath the[mysqld]section. By default, this should currently be configured to the loopback address127.0.0.1. Delete that address and put your server’s public IP address in its place. We will just use10.1.1.1for the sake of the example.[mysqld] bind-address = 10.1.1.1
If you want, you can instead use
0.0.0.0as your bind address, which is a wildcard and should bind the service to all reachable interfaces. This is not recommended, but can be good for troubleshooting if you encounter problems later on.[mysqld] bind-address = 0.0.0.0
- After you have made that change, save your changes to the file and exit it. Then, you will need to restart the MySQL service for the changes to take effect.
$ sudo systemctl restart mysql
On some distros, the service may be called
mysqldinstead:$ sudo systemctl restart mysqld
Allow remote access through firewall
Assuming you are using port 3306 for your MySQL server, we will need to allow this through the system firewall. The command you need to execute is going to depend on the distribution you are using. Refer to the list below or adapt the command as needed to adhere to your own system’s firewall syntax.
In the examples below, we are allowing remote access from IP address 10.150.1.1. Simply drop your own IP address into this spot that you want to allow remote access for.
On Ubuntu systems and others that use ufw (uncomplicated firewall):
$ sudo ufw allow from 10.150.1.1 to any port 3306
On Red Hat, CentOS, Fedora, and derivative systems that use firewalld:
$ sudo firewall-cmd --zone=public --add-source=10.150.1.1 --permanent $ sudo firewall-cmd --zone=public --add-service=mysql --permanent $ sudo firewall-cmd --reload
And the good old iptables command that should work on any system:
$ sudo iptables -A INPUT -p tcp -s 10.150.1.1 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT
Allow remote connections to a particular user from a specific IP
Now that the MySQL service can accept incoming connections and our firewall will allow a specific IP through, we just need to configure our user to accept remote connections from that IP.
- Start by opening up MySQL with the root account.
$ sudo mysql
Or, on some configurations you may be required to enter the following command and provide your root password:
$ mysql -u root -p
- If you already have a user created and you need to configure that user to be accessible from a remote IP address, we can use the MySQL
RENAME USERcommand. We will make ourlinuxconfiguser accessible from the IP address10.150.1.1in the example command below, but adapt this as needed for your own configuration.mysql> RENAME USER 'linuxconfig'@'localhost' TO 'linuxconfig'@'10.150.1.1';
Or, if you are creating this user for the first time, we will use the
CREATE USERcommand. Be sure to substitute the following username, IP address, and password with your own.mysql> CREATE USER 'linuxconfig'@'10.150.1.1' IDENTIFIED BY 'password_here';
That’s all there is to it. After granting your user access to one or more databases, you will be able to use the account credentials to access the database remotely from the IP you specified.
Closing Thoughts
In this tutorial, we saw how to allow remote connections to the MySQL service from a specific IP on a Linux system. This was a three part process of making the service accessible, allowing connections from the specific IP through the firewall, and making an accessible MySQL account. Since MySQL works basically the same across all distributions, these steps should be usable for everyone.
