Access MySQL remotely

There are two ways to access MySQL remotely:

1. Connect to MySQL through an SSH tunnel.
2. Expose MySQL to external traffic to connect directly to MySQL.

Use an SSH tunnel

An SSH tunnel is where an SSH connection is configured to forward traffic from a port on one system to a port on the other system.

Run the following command from the remote system to create an SSH tunnel that forwards traffic from port 2000 on the client’s system to the MySQL port (3306) on the server. Replace SYSUSER with the SSH system user’s name and replace ADDRESS with the server’s IP address

ssh -L localhost:2000:localhost:3306 SYSUSER@ADDRESS

You can then use the MySQL CLI on the remote system to access MySQL over the SSH tunnel by connecting to the local port that is being forwarded to the server. Replace DB_USER in the command below with the name of the database user.

# The flag --protocol=tcp ensures the --host and --port flags won't be ignored.
mysql --protocol=tcp --host=localhost --port=2000 --user=DB_USER -p

Use a direct connection

Caution

MySQL does not use encrypted communication by default. See the MySQL manual for how to configure MySQL to use SSL.

To configure MySQL to listen for remote connections, do the following.

1. SSH into the server as the system user root.
2. Edit the file:

   /etc/mysql/mysql.conf.d/mysqld.cnf

   Change the bind-address to 0.0.0.0 and then save the file.

   /etc/mysql/mysql.conf.d/mysqld.cnf

   bind-address = 127.0.0.1
   bind-address = 0.0.0.0

3. Restart MySQL.

   sudo service mysql restart

4. Open TCP port 3306 in your server’s firewall. See how to customize the firewall.
5. Configure your cloud provider’s firewall so traffic to the server on TCP port 3306
   is only allowed from the IP addresses of the remote clients.

The remote clients can now connect directly to MySQL by using the server’s IP address as the MySQL host.