After installing a MySQL server on a Linux system, by default it will only accept incoming connections from itself (i.e. the loopback address 127.0.0.1).
This default configuration works perfectly fine if you are only trying to read or write information from the database on the same server. So users that host their website and MySQL server on the same box won’t need to do any extra configuration to allow remote connections.
If you want to allow remote connections to your MySQL server, because you have other computers and/or users that need to access that data, you will need to bind the MySQL service to a public IP address on your server, and perhaps allow incoming MySQL connections through your system firewall.
In this tutorial, we will take you through the step by step instructions to allow remote connections to a MySQL server 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
- How to create or alter a MySQL user to allow remote connections
| 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 remote connections step by step instructions
Allowing remote connections to your MySQL database 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.
Third, we will need to create a new user or edit an existing one to make it accessible from remote IP addresses. We can choose to allow all IP addresses or just particular ones.
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.
On Ubuntu systems and others that use ufw (uncomplicated firewall):
$ sudo ufw allow mysql
On Red Hat, CentOS, Fedora, and derivative systems that use firewalld:
$ 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 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT
Allow remote connections to a particular user
Now that the MySQL service can accept incoming connections and our firewall will allow them through, we just need to configure our user to accept remote connections.
- 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 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';
- If you would rather your user be accessible from ANY IP address, then you can use
%in your command instead of a specific IP address. Here’s how to edit the existing user to be accessible from any IP:mysql> RENAME USER 'linuxconfig'@'localhost' TO 'linuxconfig'@'%';
And here is how to create a new user that can be accessed from any IP address:
mysql> CREATE USER 'linuxconfig'@'%' 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.
Closing Thoughts
In this tutorial, we saw how to allow remote connections to the MySQL service on a Linux system. This was a three part process of making the service accessible, allowing connections 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.
