In this guide, we’ll explore what it’s like to connect to a remote database using MySQL, through an SSH tunnel. WP Engine recently stopped allowing direct remote SQL connections to their servers for security reasons, and switched to allowing customers remote database connections through an SSH tunnel.
Connecting through an SSH tunnel can be tricky, especially if you’re not used to it. I had my own issues as I fumbled my way through figuring out how to connect for the first time, but after I understood how SSH tunneling works, it allowed me to have a deeper understanding of how remote database connections work through SSH tunneling. I’m writing this guide in hopes that it will help demystify the set up process.
What is SSH tunnel?
Let’s start off by giving a brief understanding of SSH tunneling. If you’re already familiar with this, and just need to know how to connect on WP Engine, see the Connecting to the MySQL Database section below.
Start the SSH Connection
You can read WP Engine’s official guide on remote database connections by clicking this link. However, I think it’s important to know the pieces to connect.
ssh -L 3307:127.0.0.1:3306 env@env.ssh.wpengine.net -oStrictHostKeyChecking=no
The aforementioned command creates an SSH connection, forwarding port 3307 on the local machine to port 3306 on the remote server; meanwhile, -oStrictHostKeyChecking=no flag tells SSH to ignore the HostKeyCheck (the part you’re usually asked to accept or deny the connection; i.e removes unnecessary pop-ups, instructions or dialogue). Once successfully connected, you should see a message or prompt like what I have below:
___ _________ __________ _____
__ | / /__ __ \ ___ ____/_____________ ___(_)___________
__ | /| / /__ /_/ / __ __/ __ __ \_ __ `/_ /__ __ \ _ \
__ |/ |/ / _ ____/ _ /___ _ / / / /_/ /_ / _ / / / __/
____/|__/ /_/ /_____/ /_/ /_/_\__, / /_/ /_/ /_/\___/
/____/
WP Engine Shell - PHP 8.0
* WP-CLI Commands: https://developer.wordpress.org/cli/commands/
*** NOTE ***
This is a sandboxed environment that interacts with the production server for
your website. Most resource usage commands (i.e. top, vmstat, free, etc.) do not
reflect the usage of your production server environment.
wpe-user@env.ssh.wpengine.net:~$Connecting to the MySQL Database
Once the SSH tunnel is established, the next step is to connect to the MySQL database using the local port. Your SSH connection will need to run in the background. This can be done using any MySQL client software or terminal program. This guide covers connecting via terminal. Below are what you’ll need for your connection settings. The information for Username, Password and Database can all be found in your wp-config.php file:
- Hostname: 127.0.0.1
- Port: 3307
- Username: WordPress database user
- Password: WordPress database user password
- Database: WordPress database user
With the above settings, the MySQL client will connect to the database through the SSH tunnel, providing a secure remote connection. Once successful, you should get something like I have below.
mysql -h 127.0.0.1 -P 3307 -u username -p wp_database_name Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12856769 Server version: 5.7.40-43-log Percona Server (GPL), Release '43', Revision 'c1b94a6cfd7' Copyright (c) 2000, 2022, 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>
Closing the SSH Tunnel
After the MySQL connection is no longer needed, the SSH tunnel can be closed. This can be done by pressing Ctrl + D in the terminal window where the SSH tunnel was created.
Conclusion
Creating a secure remote MySQL connection through an SSH tunnel is an effective way to protect the database from unauthorized access. By following the steps outlined in this blog post, remote access to a MySQL database can be established securely.