How to connect to remote MySQL using SSH tunnel

During local development, you may need to test something against your production or remote databases. There are a couple of things you can do to achieve this.

1. Copy the remote database to local - backing up your server database, fetching it, and importing it locally. This is one of the best methods to develop applications locally. But it has some drawbacks. For example, it can be cumbersome if you want to test something quickly. Also, if your database is big > 5GB, backing up, downloading, and importing can be slow.
2. The second option is to open up your server firewall for your IP and configure MySQL to accept remote connections from your IP. 
3. The third option is to use an SSH tunnel from your local to your remote machine. Note that you need SSH access to use this method.

How to create a MySQL SSH tunnel?

To create a MySQL SSH tunnel, execute the following command:

$ ssh -NL 3307:localhost:3306 REMOTE_USER@REMOTE_IP

3307 is the port you'd want your newly created tunnel to listen on. 

3306 is the remote port - this is the MySQL port on your remote machine.

REMOTE_USER is your SSH user

REMOTE_IP is your server IP

Flags used:

     -N      Do not execute a remote command.  This is useful for just forwarding ports.  Refer to the description
             of SessionType in ssh_config(5) for details.

-L Is used to specify the address.

Test if your tunnel works

To test if your tunnel works, try to connect to your remote database locally.

$ mysql -uroot -h 127.0.0.1 -p -P 3307

This example presumes that root is your remote user and your MySQL user has a password.

You should omit the -p flag if your MySQL user doesn't have a password.

If your connection works, you should see

➜  ~ mysql -uroot -h 127.0.0.1 -P 3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60324943
Server version: XXXXX-log Percona Server (GPL), Release 34, Revision 2e68637

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If if doesn't work, you'll see the following error:

➜  ~ mysql -uroot -h 127.0.0.1 -P 3307
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3307' (61)

Go back to the example and make sure your user, port and IP are correct.