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.
- 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.
- The second option is to open up your server firewall for your IP and configure MySQL to accept remote connections from your IP.
- 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
-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.