Installing MySQL server and using it from the same host is a very common practice by Server Administrators. 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.
In this context, we shall look into how to configure the MySQL server to allow remote connection from its client.
Steps to Connect to a MySQL database remotely
In order to perform these steps, ensure that you log in as the root MySQL user and do the following.
1. Configure the MySQL server
To begin, we will 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.
You should Open the configuration file by running the below command:
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Then, 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
2. Grant access to a user from remote hosts
Here, you will see how to allow access to a database to remote users.
To do this, start by Logging in to the MySQL server with the command:
$ sudo mysql
Then run the below query:
mysql> GRANT ALL ON DATABASE-NAME.* TO username@IP-ADDRESS IDENTIFIED BY 'USER-PASSWORD';
mysql> GRANT ALL ON linuxapt.* TO firstname.lastname@example.org IDENTIFIED BY 'mysecuredpass';
3. 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:
When using iptables, run the below command:
$ sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT
When using ufw, open port 3306 by simply running the below command:
$ sudo ufw allow 3306/tcp