MySQL is one of the most popular and powerful open-source relational database management system. It is used to power from small blogs to extensive applications with complex data modelling requirements.
By default, MySQL is configured to only accept connections from the local host, which means that remote connections from other are rejected. This enhances security of the MySQL server by ensuring that no users from outside the local machine can connect to it.
However, in some cases, you may need to allow remote connections to your MySQL server, such as when you want to connect to your database from a different computer or server.
This tutorial requires you to have the following:
- A MySQL server installed and running on your host machine.
- Access to the MySQL server with administrative privileges (typically the
- A remote machine or server from which you want to connect to the MySQL server.
Configure MySQL to Allow Remote Connections
By default, the MySQL server is configured to bind to the local address or
127.0.0.1 which restricts all the connections to the local machine.
To allow remote connections on the MySQL server, we need to change the bind address.
We can do this by editing the MySQL configuration file. In most Linux systems, it is typically located in
sudo vim /etc/mysql/mysql.conf.d/mysql.cnf
In the configuration file, locate the
bind-address directive. By default, this is set to
127.0.0.1. To allow remote connections, change the value to
bind-address = 0.0.0.0
Once you have made the changes, save the file and close. Next, you need to restart the MySQL server by running the command:
sudo systemctl restart mysql
Create User For Remote Access
Once we have configured MySQL to accept remote connections, we need to create an account that is allowed remote access.
NOTE: Please avoid using the root user for remote connections. This poses a heavy security risk, especially in production environments.
Open the terminal and connect to the MySQL instance:
sudo mysql -uroot -p
Provide the password for the root user when prompted and run the command below to create a new user.
CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';
Ensure to replace the
password with your desired username and password. An example is as shown:
CREATE USER 'geekbits'@'%' IDENTIFIED BY 'password';
The command above should create a user that can connect from remote host as specified by the
Next, grant full access to a specific database to the created user as shown:
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'%';
GRANT ALL PRIVILEGES ON public.* TO 'geekbits'@'%';
This should grant all permissions to the
geekbits user on the
Configuring the Firewall.
To allow remote connections to the MySQL server, we need to configure the server's firewall to permit incoming MySQL traffic.This may vary depending on the Firewall you are using.
For our case, we will demonstrate how to allow MySQL access on UFW.
Run the command:
sudo ufw allow 3306/tcp
Reload the firewall with the command:
sudo ufw reload
Test MySQL Remote Connection
To ensure that remote connections are working correctly, you can test the connection from a remote machine or server using a MySQL client.
On the remote machine, open a terminal
Use the MySQL client to connect to the MySQL server on your host machine. Replace
<host> with the IP address or hostname of your MySQL server,
<username> with the MySQL username you created, and
<password> with the corresponding password:
mysql -h <host> -u <username> -p
mysql -h 126.96.36.199 -u geekbits -p
Enter the password for the specified user to login.
If the connection is successful, you should now have access to the MySQL server from the remote machine.
In this tutorial, you learned how to configure MySQL to allow remote connections, create a MySQL user for remote access, grant remote access permissions, and test the remote connection