Secure Remote Database Connections on  WPEngine

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:

1. There is no security concern
2. No IP whitelist is needed
3. 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:

1. Log in to my.wpengine.com
2. Click the profile icon at the top right
3. Select SSH Keys
4. 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.

Citations & Related Info

• Ed25519 Cryptographic Algorithm
• SSH Gateways on WPEngine
• Remote Database Access at WPEngine
• Remote Database Connections on Kinsta
• SequelPro Mac MySQL Client