A distributed environment has become a requirement for many projects. Separate database servers can increase security and allow you to scale up resources quickly. Remote Access to MySQL is not required if your web application and database reside on the same machine. If your web application or database is hosted on separate servers, you will need to enable Remote Access to MySQL.
The default setting of the MySQL server is to listen for connections from localhost. This means that it can only be accessed by applications running on the same machine. However, there are some circumstances where it may be necessary to connect from a remote location. You may have to connect to the remote MySQL Server from your local system, or from a multi-server installation where the application runs on a different machine than the database server. You can either access the MySQL server via SSH Tunnel or configure the MySQL server so that remote connections are accepted.
This article explains how to make remote connections to a MySQL Server.
Prerequisites
Access the following before you start this guide:
- A command-line or terminal window.
- A remote MySQL server.
- Root privileges for remote and local machines
Configuring MySQL Server – Editing Your MySQL Configuration File
First, set the MySQL server up to listen to a particular IP address or all IP addresses. The best way to ensure that clients and the MySQL server can communicate over a private network is to make the MySQL server listen on only the private IP.
If you wish to connect to the server via a public network, then set the MySQL server so that it listens on all IP addresses. Edit the MySQL configuration file to change or add the value of the binding-address option. You can specify a single IP address or multiple IP ranges. If the address is 0.0.0.0 the MySQL server will accept connections from all host IPv4 interfaces. Instead of 0.0.0.0, you can use :: if IPv6 is configured on your system.
Depending on which distribution you are using, the location of the MySQL configuration files will vary. In Ubuntu and Debian the file is located at /etc/mysql/mysql.conf.d/mysqld.cnf
Open the document with your text editor:
osboxes@osboxes:~$ sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf
Find a line beginning with bind-address, and set its value as the IP address to which a MySQL server should be listening.
The default value for is 127.0.0.1 (listens in localhost only).
In the following example, we will set the MySQL server so that it listens on all IPv4 interfaces. We’ll change the value of bind-address to 0.0.0.0.
If you see a line that contains skip-networking in it, delete it. Or comment it out by inserting # at the end of the line.
Note: MySQL 8.0 and above may not include the bind-address directive by default in the mysqld.cnf files. In such cases, you should add the bind address directive line to the bottom file in the [mysqld] subsection.
Save and exit the configuration file after you have made the changes.
For changes to take effect, restart MySQL. Services can only be restarted by root users or sudo users.
Type below command to restart MySQL on Ubuntu or Debian.
osboxes@osboxes:~$ sudo systemctl restart mysql
Set up Firewall to Allow Remote MySQL Connection
Your MySQL database should now allow remote connections from devices using your IP address. This value can be set in your MySQL configuration file or from all devices if this value is 0.0.0.0. Your device firewall or network firewall will still block connections.
Servers and computers use firewalls to prevent connections from being blocked unless access is granted to a particular port.
Linux servers often use iptables to act as their default firewall.
If you have set up a firewall to protect your database server’s data, you will need to open port 3306 (MySQL’s default port) to allow traffic to MySQL.
Currently the remote server is running at ip address 10.0.2.15. So, you can only access the database server remotely from one machine by using the command below.
osboxes@osboxes:~$ sudo ufw allow from 10.0.2.15 to any port 3306
Alternately, you can also allow connections to your MySQL databases from any IP address by using the following command:
sudo ufw allow 3306
The command below allows access from any IP address to the MySQL port if you use iptables firewall. This is extremely insecure.
sudo iptables -A INPUT -p tcp –destination-port 3306 -j ACCEPT
Allow Remote Access to a MySQL Database
If you don’t have a database, you will need one to start. Log in to MySQL first with the following command:
osboxes@osboxes:~$ sudo mysql -u root -p
Once you login, create a new database named testdb using the following command:
mysql> CREATE DATABASE testdb;
Next, create a new user named testuser using the following command:
mysql> CREATE USER ‘testuser’@’10.0.2.15’ IDENTIFIED BY ‘password’;
Next, grant remote user access to a new database created using the following command:
mysql> GRANT ALL PRIVILEGES ON testdb.* TO ‘testuser’@’10.0.2.15’ WITH GRANT OPTION;
Next, flush the privileges with the following command:
mysql> FLUSH PRIVILEGES;
Next, verify the privileges you have been granted using this command:
mysql> SHOW GRANTS FOR ‘testuser’@’10.0.2.15’;
Finally, exit from the MySQL with the following command:
mysql> EXIT;
Verify MySQL Remote Connection
MySQL now supports remote connections. Now it is time to verify that the MySQL connection has been established from a remote server.
To connect to the MySQL server from a remote server, use the following command:
osboxes@osboxes:~$ mysql -u testuser -p -h 10.0.2.15
Your MySQL username is represented by the -u username parameter in the command. The -h mysql_server_ip option is your MySQL server’s IP or hostname. You will be asked to enter your password for your MySQL username using the -p option.
You will be asked to provide a MySQL user password as shown below. Since, we have set “password” as password for testuser, you need to enter that to start mysql on the remote server.
Grant Read and Write Remote Access to MySQL
To grant remote MySQL users write and read access, use the following command:
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON testdb.* TO ‘testuser’@’10.0.2.15’ WITH GRANT OPTION;
To grant append-only access to users, use the following command:
mysql> GRANT SELECT,INSERT ON testdb.* TO ‘testuser’@’10.0.2.15’ WITH GRANT OPTION;
To revoke all privileges of testuser, use the following command:
mysql> REVOKE ALL PRIVILEGES on testdb.* FROM ‘testuser’@’10.0.2.15’;
If you are able to access your database it means that your configuration file contains the bind-address directive. It is important to note that setting bind address to 0.0.0.0 can allow connections to your server from any IP address. You can specify the exact ip address of the remote server instead of 0.0.0.0 in your MySQL configuration file. This allows you to specify the exact IP address of the remote server so that connection is restricted to that address.
If you still have trouble accessing the database remotely, it is possible that you missed one of these steps. Retry and grant remote access to MySQL
Conclusion
If you wish to host your database on a different server, we hope you found this article helpful. We have provided a guide on how to grant remote access to MySQL. To summarize, firstly, you have to edit the mysql configuration file and add the bind address. Then you have to set up a firewall to allow remote access to MySQL. Once the firewall is in place, allow remote access to a MySQL database and finally verify the MySQL remote connection. You can now connect to your MySQL server remotely from any machine with the correct credentials.
