Remotely connecting to a WordPress database is tremendously valuable in many situations. It allows developers to connect to the database from anywhere using a SQL client of their choice, many of which provide substantial benefits over a command line interface or server level UI like PHPMyAdmin.
Standard Approach
The typical way to do this involves opening a port on your server to the outside work, and then whitelisting IP addresses to allow access to connect remotely. On WPEngine this means reaching out over live chat anytime you need to whitelist an IP address.
This general approach has it’s issues:
- Security – Exposing a port is a security concern, and should be avoided
- Management – Many developers connecting to the database have a dynamic IP that routinely changes, requiring frequent updates to whitelisted IP’s
This is the approach used for remote database access at WPEngine, and each time you need to change an IP address you’ll have to open up a support chat and have a team member update the IP whitelist rules. It slows down workflow by adding unnecessary barriers.
The Better Way
Connecting to MySQL using an SSH tunnel is a better approach for many reasons:
- There is no security concern
- No IP whitelist is needed
- It can be setup by developers directly, no need for a support chat
Creating an SSH Tunnel to WPEngine’s MySQL Database
Follow these steps to create an SSH tunnel that securely connects to a WPEngine hosted MySQL database remotely.
Generate an SSH Key for WPEngine
If you already have an SSH key generated that you’d like to use you can copy it with the pbcopy portion below, you don’t need to generate a new SSL certificate specifically for WPEngine, though it’s a good idea.
If you are generating a new SSL certificate I recommend using the Ed25519 cryptographic algorithm instead of the standard RSA, it’s much more secure and is a better standard for the future.
ssh-keygen -t ed25519 -f ~/.ssh/wpengine_ed25519
Once you’ve generated a key copy or dump the contents of the public key, we’ll add this into the WPEngine user portal in the next step.
Copy Public Key
To copy the public key to the clipboard, use the pbcopy command:
pbcopy < ~/.ssh/id_ed25519.pub
Dump Public Key
To display the public key in the terminal, use the cat command:
cat < ~/.ssh/id_ed25519.pub
Add Your SSH Key to Your WPEngine Account
If you don’t already have one, you’ll need to set up an SSH key and add it to WPEngine. This will allow you to connect to your WPEngine server over SSH.
Detailed instruction are provided by WPEngine, but the general process involves:
- Log in to my.wpengine.com
- Click the profile icon at the top right
- Select SSH Keys
- Click New SSH Key
Paste your public key into the field shown, and add whatever label you’d like as the name, then save it.
SSH Tunnel Alias
Now that we have a public key in my.wpengine.com, we can set up a new SSH configuration alias for connecting to our MySQL tunnel. Open up the SSH config file in a text editor:
open ~/.ssh/config
Add a new host configuration to the file. In this example, it’s wpengine-mysql, but you can name is whatever you’d like.
Host wpengine-mysql
Hostname {SITENAME}.ssh.wpengine.net
User {SITENAME}
Port 22
LocalForward 3307 127.0.0.1:3306
The LocalForward option is what’s sets up a tunnel. When you connect to this SSH host, the 3307 port will be opened locally on your machine, pointing to WPEngine’s server.
Connect to the MySQL SSH Tunnel
Once the host configuration is set up you can open a tunnel with the following simple command:
ssh wpengine-mysql
This opens the tunnel, when you want to close it you can press Ctrl + C to clear the terminal.
Connect to the Database
Now that we have an SSH tunnel open and connected to WPEngine we can connect to the database locally using a MySQL client with the following connection information:
- Type: TCP/IP
- Host: 127.0.0.1
- Database: (database name from
wp-config.php) - User: (database username from
wp-config.php) - Password: (database password from
wp-config.php) - Port: 3307
This will work as long as you have the SSH tunnel connection open. When you’re done working with the remote database, you can clear the tunnel in the terminal to close it.
Conclusion
Using an SSH tunnel to connect to a remote MySQL database on WPEngine is a much better approach than IP whitelisting for many reasons. Once you understand the steps involved, it’s pretty simple, and even simpler to work with in practice. Hopefully this help you avoid the headaches of standard remote database connections on WPEngine. Worth noting: the same approach can work on other hosts like Kinsta as well.