Introduction
In the traditional deployment, people often install the MySQL server and use it from the same host. However, it’s highly recommended that we should separate the MySQL server and client, the MySQL server should be accessed from a remote host. This method helps the system become more secure and increases performance.
To make the above method possible, we have to configure the MySQL server to allow remote connection from its client.
Configure the MySQL server
Firstly, let’s configure the MySQL server to listen on a pre-defined IP or all IP addresses on your server.
If the MySQL server is in the same network as the clients, you should configure the listen port on the private IP.
On another hand, if you need to connect to the MySQL server through a public network, let’s configure the server following the below configuration:
Open the configuration file:
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Go to the line that begins with bind-address
By default, the value of bind-address is 127.0.0.1
If you want to set the MySQL server to listen on all of the IP addresses, configure the value of bind-address = 0.0.0.0
To make the change effect, let’s restart the MySQL service:
$ sudo systemctl restart mysql
Grant access to a user from remote hosts
In this part, we will show you the way to allow access to a database to remote users.
Log in to the MySQL server:
$ sudo mysql
Then,
mysql> GRANT ALL ON DATABASE-NAME.* TO username@IP-ADDRESS IDENTIFIED BY ‘USER-PASSWORD’;
For example:
mysql> GRANT ALL ON linuxways.* TO [email protected] IDENTIFIED BY ‘mysecuredpass’;
Configure Firewall
The MySQL server listens on port 3306. You have to open this port to allow traffic from the remote hosts. You can use one of these below methods:
iptables
$ sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT
ufw
If you use ufw to open port 3306, you can simply run the following command:
$ sudo ufw allow 3306/tcp
Conclusion
You’ve already go through the details of how to allow remote connections to a MySQL server.
If you have any concerns, please let me know. Thank you for reading.
Karim Buzdar holds a degree in telecommunication engineering and holds several sysadmin certifications including CCNA RS, SCP, and ACE. As an IT engineer and technical author, he writes for various websites.
