Connect to a Remote MySQL Instance Using MySQL Workbench
Connecting to a remote MySQL instance with MySQL Workbench is easy if your server has been set-up.
Step 1: If the server is a clean-install, the public SSH key for the machine remotely connecting to the server must be added to the authorized_keys file in ~/.ssh . If SSH keys are not setup on the machine remotely connecting, Digital Ocean has great tutorials.
Step 2: Create a user on the server which the remote machine will use to access the server
Step 3: Create a MySQL user with necessary database and table privileges. If MySQL is not installed on the server, again Digital Ocean is great.
At this point, only client-side implementations are required
Step 4: Open MySQL Workbench and use Ctrl + U or select in task-bar Database > Connect to Database`
Step 5:
Change Connection Method to Standard TCP/IP over SSH . Set SSH Hostname to IP iddress or hostname of the server which runs the MySQL instance. Set SSH Username to the username created in Step 2. Click the dots next to SSH Key File` and select the private key file located in ~/.ssh` (most Unix systems) e.g. ~/.ssh/id_rsa. If nothing was changed during the MySQL setup, changing MySQL Hostname and Port is not neessary.
Step 6: Enter the MySQL username in Username
Step 7: Click OK!
Step 8: First password pop-up is the Unix user
Step 9: Second password pop-up is the password for MySQL user
Sign-up here to receive automation tips, case studies, and guides on the mistakes you maybe making on your automation journey.
And follow me on Twitter where I post about my journey, automation, and business growth.
