MySQL: Allow access from specific IP address

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
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



Twitter icon Follow LinuxConfig.org on Twitter for the latest tips and tricks about Linux!


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

  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
    
  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
    
  3. 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.

  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
    
  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 'linuxconfig'@'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.



Comments and Discussions
Linux Forum