Allow All Hosts
From:     https://linuxconfig.org/mysql-allow-all-hosts


Intro Allow All Hosts Step by Step Allow Remote Acc Via FW Allow Rmt Conns to Part Sys
Step 1 Step 2 Closing Thoughts Related Tuts 



MySQL: Allow all hosts
12 February 2022 by Korbin Brown

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:



Allowing MySQL connections through firewall and creating a new user that
allows connections from all hosts
Software Requirements and Linux Command Line Conventions 



 
CategoryRequirements, 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.




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




Related Linux Tutorials: