Connect remote MySQL server to MySQL Workbench

Motivation:

We have an ubuntu server (in AWS, DigitalOcean, etc..) with a database running, we want to access it on our computer to see data more visual, execute queries without having to log in to the terminal, check connections, etc... We can do this by connecting this remote DB to our workbench, being one of the easiest way to interact with the database.

Prerequisites

To complete this tutorial, you will need:

• A server running MySQL that is accessible via SSH. For example, you can follow the tutorial How to Setup Ubuntu 16.04 to get up and running quickly an ubuntu server.
• MySQL Workbench installed on your local machine, which is available for all major platforms, including Windows, macOS, Ubuntu Linux, RedHat Linux, and Fedora. Visit the MySQL Workbench Downloads page to download the installer for your operating system.

You will also need the following information about the database server you plan to use:

• The public IP address of the server running MySQL.
• The server's SSH Port if configured differently than port 22.
• A user account with SSH access to the server, with a password or public key.
• The username and password for the MySQL account you wish to use.

Connecting to the Database Server With SSH

Once you've installed MySQL Workbench on your computer, launch the program. Create a new connection by clicking the + icon next to MySQL Connections in the main window.

You'll be presented with the Connect to Database window, which looks like the following figure:

To create the connection, enter the following details:

1. For Connection Name, enter any name you'd like that helps you identify the connection you're making later. This might be something like database_for_myapp or something more descriptive.
2. Change the Connection Method to Standard TCP/IP over SSH.
3. For SSH Hostname, enter your MySQL server's IP address. If your server accepts SSH connections on a different port, enter the IP address, followed by a colon and port number. For example, in the case of AWS, write your IPv4 Public IP.
4. For SSH Username, enter the username you use to log into the server via SSH. This could vary, for instance: ubuntu server on AWS uses: 'ubuntu', on other providers, like DigitalOcean, they use: 'root'.
5. For SSH Password, enter the password you use for your SSH user. If you use public keys instead of passwords, select an SSH key for authentication.
6. For MySQL Hostname and MySQL Server Port, use the default values.
7. For Username, enter the MySQL username.
8. For Password, you can either enter the password or leave it blank. If you do not store the MySQL password in MySQL Workbench, a prompt will request the password each time you attempt to connect to the database.
9. Choose Test Connection to ensure your settings are correct.
10. Choose OK to create the connection.

Conclusion

Using MySQL Workbench to access your remote MySQL database through an SSH tunnel is a simple and secure way to manage your databases from the comfort of your local computer. Using the connection method in this tutorial, you can bypass multiple network and security configuration changes normally required for a remote MySQL connection.