MySQL: Allow all hosts

If you wish to access your MySQL server remotely, it will be necessary to configure one or more users to allow access from remote hosts. If you do not know all the IP addresses of the connecting hosts, then you can simply allow connections from all hosts.

In this tutorial, we will take you through the step by step instructions of allowing remote connections to a MySQL server on a Linux system from all hosts. These instructions should work independently of whichever Linux distro you are using.

In this tutorial you will learn:

• How to allow remote connections from all hosts to MySQL account
• How to allow remote connections to MySQL through system firewall

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 all hosts 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. We have a separate guide for this process, which you should follow here: MySQL: Allow remote connections.

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 all remote hosts.

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.

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 all hosts, we can use the MySQL RENAME USER command. We will make our linuxconfig user accessible from all hosts by using the wildcard % in the example command below, but adapt this as needed for your own configuration.

   mysql> RENAME USER 'linuxconfig'@'localhost' TO 'linuxconfig'@'%';
   mysql> flush privileges;
   

   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 and password with your own. Once again we will use % as the wildcard that represents all remote hosts.

   mysql> CREATE USER 'linuxconfig'@'%' IDENTIFIED BY 'password_here';
   mysql> flush privileges;
   

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 all hosts.

Closing Thoughts

---

---

In this tutorial, we saw how to allow remote connections from all hosts 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.