MySQL: Allow access from specific IP address

9 February 2022 by Korbin Brown

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



Creating a new MySQL user that can only be accessed from a specific IP
address

Software Requirements and Linux Command Line Conventions 


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


Step 1 We will start by opening the /etc/mysql/mysql.cnf file. With root
permissions, open this in nano or your favorite text editor.
$ sudo nano /etc/mysql/mysql.cnf

Step 2 Find the setting that says bind-address 
underneath the [mysqld] section. By default, this should currently be configured
to the loopback address 127.0.0.1. Delete that address and put your server’s 
public IP address in its place. We will just use 10.1.1.1 for the sake of the
example.
[mysqld]
    bind-address = 10.1.1.1

If you want, you can instead use 0.0.0.0 as 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 mysqld instead:
$ 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.
Step 1 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

Step 2 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 USER command. We will make our linuxconfig user accessible from 
the IP address 10.150.1.1 in the example command below, but adapt this as 
needed for your own configuration.
mysql> RENAME USER 'linuxconfig'@'localhost' TO 'li
nuxconfig'@'10.150.1.1';

Or, if you are creating this user for the first time, we will use the
CREATE USER command. 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.