Allow Remote Access to MySQL Database

Many websites and applications begin with the web server and database backend running on the same machine. A setup like this, on the other hand, can become complicated and challenging to scale over time. The solution is to separate these functions and establishing a remote database. Permitting the server and database to grow independently on their respective machines.

To enable this, open up your mysqld.cnf file:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

or

$ sudo nano /etc/mysql/my.cnf

or

$ sudo nano /etc/my.cnf

Find the line that starts with the bind-address directive. It will appear as follows:

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1

This value is set to 127.0.0.1 by default, which means that the server will only look for local connections. This directive must be modified to refer to an external IP address. For troubleshooting purposes, you could set this directive to a wildcard IP address, such as * , : :, or 0.0.0.0:

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

Save and close the file after changing this parameter.

Then restart the MySQL service to apply the changes made to mysqld.cnf:

$ sudo systemctl restart mysql

If you already have a MySQL user account that you intend to use to connect to the database from a remote host, you must reconfigure that account to connect from the remote server rather than localhost. Open the MySQL client as the root MySQL user or another privileged user account:

$ sudo mysql

if User root has a password use:

$ mysql -u root -p

With the following command, we can create a new user account that will only connect from the remote host:

mysql> CREATE USER 'bob'@'r_server_ip' IDENTIFIED BY 'password';

Or To change an existing user’s host, you can use MySQL’s RENAME USER command. Run the following command:

mysql> RENAME USER 'bob'@'localhost' TO 'bob'@'r_server_ip';

Then, for your specific needs, assign the new user the relevant rights. The following example allows a user to CREATE, ALTER, and DROP databases, tables, and users, as well as INSERT, UPDATE, and DELETE data from any table on the server. It also allows the user to query data using SELECT, construct foreign keys using the REFERENCES keyword, and conduct FLUSH operations using the RELOAD permission. You should, however, only provide people the access they require, so feel free to change your own user’s privileges as required.

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'bob'@'r_server_ip' WITH GRANT OPTION;

After that, it’s a good idea to execute the FLUSH PRIVILEGES command. This will release any memory cached by the server as a result of the previous CREATE USER and GRANT statements:

mysql> FLUSH PRIVILEGES;

The MySQL client can then be closed:

mysql> exit;

Finally, if you have a firewall installed on your database server, you must open port 3306 – MySQL’s default port – to enable communication to MySQL.

If you only want to connect to the database server from one machine, use the following command to grant that machine exclusive authorization to connect to the database remotely. Replace r_server_ip with the real IP address of the computer to which you want to connect:

Or to enable anyone to access your MySQL database in use:

UFW

$ sudo ufw allow from r_server_ip to any port 3306

Iptables

$ sudo iptables -A INPUT -p tcp -s r_server_ip --dport 3306 -j ACCEPT
$ sudo iptables-save

firewalld

$ sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp

After that, try remotely accessing your database from another machine:

Note: If you configured your firewall to only accept connections from a single IP address, you must attempt to access the database using the machine associated with that address.

$ mysql -u user -h db_server_ip -p

If you can access your database, it demonstrates that the bind-address directive in your configuration file was the source of the problem. However, setting bind-address to 0.0.0.0 is unsafe since it permits connections to your server from any IP address.

Alternative Solutions for Allowing Remote Access to MySQL Database

While the method described above is a common way to enable remote access to your MySQL database, it has inherent security risks, especially when using a wildcard IP address or opening the port to all incoming connections. Here are two alternative solutions that offer improved security and management:

1. Using SSH Tunneling (Port Forwarding)

SSH tunneling, also known as port forwarding, provides a secure and encrypted channel for accessing your MySQL database remotely. Instead of directly exposing port 3306 to the internet, you create an SSH tunnel that forwards a local port on your client machine to port 3306 on the database server. This means all traffic to the database is encrypted via SSH, protecting it from eavesdropping and unauthorized access.

Explanation:

• Security: SSH tunnels are encrypted, adding a layer of security that’s crucial when transmitting sensitive database information.
• Access Control: You don’t need to modify the bind-address in the MySQL configuration. The database remains bound to localhost. Only users with valid SSH credentials can access the database remotely.
• Flexibility: You can easily manage access by controlling SSH user accounts and permissions.

Implementation:

1. Ensure SSH is configured: Make sure SSH is running and properly configured on both the client and server machines. This usually involves setting up SSH keys for passwordless login for ease of use, but password authentication is also possible.

2. Create the SSH Tunnel: On your client machine, use the following command to create the SSH tunnel:

   ssh -L 3307:127.0.0.1:3306 user@db_server_ip

   • -L 3307:127.0.0.1:3306: This option specifies the local port forwarding.
     • 3307: This is the local port on your client machine that you will use to connect to the database. You can choose any available port.
     • 127.0.0.1: This is the address of the MySQL server from the perspective of the server itself which will always be localhost or 127.0.0.1.
     • 3306: This is the standard port MySQL listens on.
   • user@db_server_ip: Replace user with your SSH username on the database server, and db_server_ip with the IP address of the database server.

3. Connect to MySQL: Once the SSH tunnel is established, you can connect to your MySQL database using the local port you specified (3307 in this example). Use a MySQL client on your local machine and connect to 127.0.0.1:3307.

   mysql -u user -p -h 127.0.0.1 -P 3307

   • -h 127.0.0.1: Connect to localhost (your client machine).
   • -P 3307: Specify the local port you forwarded to the database server.

Case Study:

A small startup, "CodeCrafters," needed to allow their developers to access a remote MySQL database for debugging purposes. They implemented SSH tunneling. Each developer was given an SSH account on the database server. Instead of opening port 3306 directly, they used SSH tunnels to forward a local port to the database server. This allowed secure access without exposing the database to the entire internet.

2. Using a VPN (Virtual Private Network)

Another secure alternative is to establish a VPN connection between your client machine and the network where the MySQL server resides. A VPN creates an encrypted tunnel for all network traffic, effectively extending your local network to include the remote server.

Explanation:

• Security: All traffic between your client and the server is encrypted, protecting data from interception.
• Network-Level Access: A VPN provides access to all resources within the VPN’s network, not just the MySQL database. This can be beneficial if you need to access other services on the same network.
• Centralized Management: VPN solutions often provide centralized management features for user authentication, access control, and monitoring.

Implementation:

1. Choose a VPN Solution: Select a VPN server software (e.g., OpenVPN, WireGuard, strongSwan) and install it on a server within the same network as your MySQL database.
2. Configure the VPN Server: Configure the VPN server with appropriate security settings, user authentication methods (e.g., usernames and passwords, certificates), and network routing rules.
3. Install a VPN Client: Install a VPN client on your local machine.
4. Connect to the VPN: Connect to the VPN server using the VPN client. You will typically need to provide your username and password or a certificate.

5. Connect to MySQL: Once connected to the VPN, you can connect to the MySQL database using its private IP address within the VPN network. You will no longer need to modify the bind-address in the MySQL configuration, provided the server is configured to listen on the private network IP.

   mysql -u user -p -h db_server_private_ip

   • -h db_server_private_ip: Connect to the private IP address of the MySQL server within the VPN network.

Code Example (OpenVPN Client Configuration):

A simplified OpenVPN client configuration file (client.ovpn) might look like this:

client
dev tun
proto udp
remote your_vpn_server_ip 1194  # Replace with your VPN server's IP and port
resolv-retry infinite
nobind
user nobody
group nobody
persist-key
persist-tun
remote-cert-tls server
tls-client
<ca>
-----BEGIN CERTIFICATE-----
... (Your CA Certificate) ...
-----END CERTIFICATE-----
</ca>
<cert>
-----BEGIN CERTIFICATE-----
... (Your Client Certificate) ...
-----END CERTIFICATE-----
</cert>
<key>
-----BEGIN PRIVATE KEY-----
... (Your Client Private Key) ...
-----END PRIVATE KEY-----
</key>
cipher AES-256-CBC
verb 3

Case Study:

"GlobalTech Solutions," a company with employees working remotely from various locations, used a VPN to provide secure access to their internal resources, including a MySQL database. They deployed an OpenVPN server on their corporate network. Employees connected to the VPN using OpenVPN clients on their laptops. Once connected, they could access the MySQL database using its internal IP address, as if they were on the same local network. This provided a secure and centrally managed solution for remote access.

In conclusion, while directly modifying the bind-address and opening port 3306 might seem like the simplest way to allow remote access to MySQL database, it introduces significant security risks. SSH tunneling and VPNs offer more secure and manageable alternatives that should be considered for production environments. These methods for allow remote access to MySQL database are also preferable as they don’t necessitate the database being available to the entire internet. Remember to always prioritize security when configuring remote access to your database.