Remote Database Access
WP Engine provides access to an install’s database via phpMyAdmin in the User Portal, instead of offering cPanel. In some cases though, an alternate method of running SQL queries remotely on a website’s database may be preferred. The database can be access remotely with SSH Gateway, or with a remote database management tool that supports SSH connections.
SSH Gateway
SSH Gateway is secure shell access which allows a user to run wp db query
without allowlisting any IPs or using any additional MySQL programs. The wp db query
command is part of the open source WordPress® software package, and allows the running of a SQL query against the database information supplied in the wp-config.php
file.1
With this connection method you do not need to retrieve your database username and password, as you will be connecting to a secured tunnel specific to your user.
Learn more about wp db query here.
Before a query can be run, connect to the website’s root directory using SSH Gateway. The step-by-step process for new connections can be found in the SSH Gateway article.
Running a query on SSH Gateway will look like this:
- Connect to SSH Gateway:
ssh environmentname@environmentname.ssh.wpengine.net
- Move to the environment’s root directory:
cd sites/environmentname
- Run a database query:
wp db query "some query here;"
- For example, this will list all post titles:
wp db query 'SELECT post_title FROM wp_posts;'
To learn more about MySQL syntax and variables, see the official documentation here and the Getting Started guide here.
MySQL Workbench
Open MySQL Workbench and create a new connection by clicking on Database → Manage Connections in the menu, then click New.
Give the connection a name and use the following values to configure the connection:
- Connection Method: Standard (TCP/IP) over SSH
- SSH Hostname: environmentname.ssh.wpengine.net (EX: acoolsite.ssh.wpengine.net)
- SSH Username: environmentname
- SSH Password: Leave this blank
- SSH Key File: Use the key file created here
- MySQL Host Name: 127.0.0.1
- MySQL Server Port: 3306
- Database Username: environmentname
- Database Password: Use the database password for the website. Found in the
wp-config.php
file on the line DB_PASSWORD. - Default Schema: wp_environmentname (EX: wp_acoolsite)
Click Test Connection to be prompted for the database password retrieved above. This can also be found in ./_wpeprivate/config.json
as WPENGINE_SESSION_DB_PASSWORD
.
NOTE
Where the word environmentname is shown, be sure to replace it with the unique environment name of the website.
Sequel Ace
Open Sequel Ace and create a new connection by clicking on MySQL in the menu.
Give the connection a name and use the following values to configure the connection:
- Connection Method: SSH
- MySQL Hostname: 127.0.0.1
- Database Username: environmentname
- Database Password: Use the database password for the website. Found in the
wp-config.php
file on the line DB_PASSWORD. - Database: wp_environmentname (EX: wp_acoolsite)
- Port: 3306
- SSH Host: environmentname.ssh.wpengine.net (EX: acoolsite.ssh.wpengine.net)
- SSH User: environmentname
- SSH Password: Click the Key, and Use the key file created here
- SSH Port: Leave blank
Other Database Management Tools
If a different tool is needed to access the database, the first step is to start an SSH session with local port forwarding.
ssh -L 3307:127.0.0.1:3306 environmentname@environmentname.ssh.wpengine.net
Now retrieve the MySQL database’s password. Once the connection has been made, enter the following:grep WPENGINE_SESSION_DB_PASSWORD ./sites/environmentname/_wpeprivate/config.json
After local port forwarding has been configured, the database management application will require inputting connection information. Here are the standard set of values that should be entered:
- Hostname: 127.0.0.1
- Port: 3307
- Database Username: environmentname (EX:
acoolsite
) - Database Password: Use the database password retrieved above.
- Database: wp_environmentname (EX:
wp_acoolsite
)
NOTE
Where word environmentname is shown, replace it with the unique environment name of the website.
NEXT STEP: Learn about SSH Gateway at WP Engine