Accessing MySQL Databases

Configure and troubleshoot your Pantheon website's MySQL database connections.

Discuss in our Forum Discuss in Slack

Pantheon provides direct access for your MySQL databases, both for debugging and for importing large databases. Each site environment (Dev, Test and Live) has a separate database, so credentials for one cannot be used on another. The credentials are automatically included in your site configuration.

 Note

Due to the nature of our platform, the connection information will change from time to time due to server upgrades, endpoint migrations, etc. You will need to check the Dashboard periodically or when you can’t connect.

Database Connection Information

Platform Considerations

Due to the containerized nature of the platform, connections will change from time to time. For security reasons, using the $_ENV superglobal inside PHP applications is not supported. As an alternative, consider using a Bash script and Terminus connection; an example can be found in the Create Secure Connection to MySQL using TLS documentation.

Accessing the Database Directly

This option is not available if you have purchased Secure Runtime Access.

MySQL credentials for each site environment are located in the Dashboard:

MySQL Credentials

The following required fields are provided:

  • Server: The hostname of the MySQL server.
  • Port: The TCP/IP port number to use for the connection. The platform randomly selects the port number. Port numbers will differ in every environment for each site, and might not match what PHP reports.
  • Username: MySQL user name to use when connecting to server.
  • Password: The password to use when connecting to the server.
  • Database: The database to use; the value will always be pantheon and cannot be altered.

As each database server is in the cloud, the credentials will occasionally be updated and may change without notice. Normally, this is transparent to a site, as the credentials are automatically included by the server. However, if you've saved the credentials in a local client and a month later you can't connect, check your Dashboard for the current credentials.

There's a wide array of MySQL clients that can be used, including:

and others. See the documentation or issue queue of your software to learn more about how to configure a connection.

Open Sequel Ace Database Connection

Drupal users can create spf-template.spf and use the following script to establish a database connection in Sequel Ace via Terminus and Drush:

establish-db-connection.sh
#!/bin/bash

# exit on any errors:
set -e

if [ $# -lt 1 ]
then
  echo "Usage: $0 @pantheon-alias"
  exit 1
fi

# Path to drush goes here:
DRUSH='/usr/local/bin/drush'

# Authenticate with Terminus
terminus auth:login --email <email>


# see the following file:
TEMPLATE='spf-template.spf'

# may need to change this:
TMP_SPF='/tmp/tmp.spf'

# Update aliases
terminus aliases

echo "fetching connection string"
CONNECTION_STRING=`$DRUSH $1 sql-connect`
echo $CONNECTION_STRING
DATABASE=`echo $CONNECTION_STRING | sed -e 's/.*--database=\([^\\ ]*\).*/\1/g'`
HOST=`echo $CONNECTION_STRING | sed -e 's/.*--host=\([^\\ ]*\).*/\1/g'`
PORT=`echo $CONNECTION_STRING | sed -e 's/.*--port=\([^\\ ]*\).*/\1/g'`
PASSWORD=`echo $CONNECTION_STRING | sed -e 's/.*--password=\([^\\ ]*\).*/\1/g'`
USER=`echo $CONNECTION_STRING | sed -e 's/.*--user=\([^\\ ]*\).*/\1/g'`

# This is for Sequel Ace:
eval "echo \"$(< $TEMPLATE)\""
# For some reason, Sequel Ace or Open do not behave the same way given the -f
# flag compared to opening a file from file system. So, we write to a tmp file.
eval "echo \"$(< $TEMPLATE)\"" > $TMP_SPF

# Swap this out to fit your system:
open $TMP_SPF

Props to Aaron Bauman for writing this script!

Accessing the Database via Your Application using PHP

In this scenario, the application connects using our internal network. Use the following variables in your application to access the database:

  • DB_HOST: Name of the MySQL server.
  • DB_PORT: Database port used.
  • DB_USER: MySQL user name to use when connecting to server.
  • DB_PASSWORD: The password to use when connecting to the server.
  • DB_NAME: The database to use; the value will always be pantheon and cannot be altered.
  • REPLICA_DB_HOST: Database IP address. This will be changing to have the same value as DB_HOST, and route to a proxy server. We will no longer support connections via IP Address.
  • REPLICA_DB_PORT: Replica database port.
  • REPLICA_DB_USER: MySQL replica user name to use when connecting to server.
  • REPLICA_DB_PASSWORD: The password to use when connecting to the server.
  • REPLICA_DB_NAME: The replica database to use; the value will always be pantheon and cannot be altered.

For example:

DB_HOST=dbhost
DB_PORT=6033
DB_USER=21b24cbb5bb44b9988a6112f46558b06
DB_PASSWORD=e064b4a6223149c3812810e232b88eb5
DB_NAME=pantheon
REPLICA_DB_HOST=10.73.1.226
REPLICA_DB_PORT=14097
REPLICA_DB_USER=59e14dd2cedd4188848b6b4aed6fb5f5
REPLICA_DB_PASSWORD=e35eff3a5a9f4c29b9044df35ad004e7
REPLICA_DB_NAME=pantheon

SSH Tunneling

By default, MySQL connections made to Pantheon are encrypted:

mysql> SHOW STATUS LIKE "Ssl_cipher";
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+

Developers can use SSH tunnels to add additional layers of encryption to remote MySQL connections, or tunnel the connection across non-standard ports. For more information on how to set up tunnels for databases, see Secure Connections to Pantheon Services via TLS or SSH Tunnels.

Troubleshooting MySQL Connections

Lost Connection to MySQL Server

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Or

ERROR 2003 (HY000): Can't connect to MySQL server on 'dbserver.$ENV.$SITE.drush.in' (111)

This error occurs when a request is sent to a database server that is in sleep mode. Pantheon containers spin down after about one hour of idle time. Live environments on a paid plan spin down after 12 hours of idle time. Environments usually spin up within 30 seconds of receiving a request. To resolve this error, wake environments by loading the home page or with the following Terminus command:

terminus env:wake <site>.<env>

Can't Connect to Local MySQL Server Through Socket

See Database Connection Errors to troubleshoot connection errors like the following:

Can’t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'...).

Frequently Asked Questions

How can I access my MySQL Slow Query logs?

Pantheon logs underperforming database queries using the MySQL Slow Query Log.

To access the log for your database:

  1. Get the SFTP connection info for the environment in question.
  2. Replace the word appserver with dbserver in the connection string.
  3. The MySQL slow query logs are in the logs subdirectory.

How can I access MySQL binary logs?

These logs are generally not used for development but may be useful to troubleshoot disk quota issues.

To access MySQL binary logs ("binlogs"):

  1. Get the SFTP connection info for the environment in question.
  2. Replace the word appserver with dbserver in the connection string.
  3. The MySQL slow query logs are in the data subdirectory.

Are table prefixes supported?

Table prefixes are not supported or recommended by Pantheon. While the server will not prevent their creation or use, managing and supporting tables with prefixes is the developer's responsibility.

Can I create a database in addition to the Pantheon database?

No, only one database per site is provided. While create privileges are granted, any additional database will not survive regular maintenance operations.

Can I put unique tables in the Pantheon database?

Pantheon places no restrictions on the contents of the database.

Can I create another database user?

No, Pantheon only provides one database user. Some customers have asked about creating a read-only user to provide read but not write access to the database. Consider creating an API or JSON-request application to provide access to the required information.

How do I convert output from hexadecimal to a binary data?

When updating your MySQL client (CLI) from 5.x to 8.x, reading data from DB columns with BLOB types (such as the variable table in Drupal 7.x) may change data from binary to hexadecimal (ex: 0×1f34c9).

To disable hexadecimal notation, add --skip-binary-as-hex to the database connection when you connect from the command line:

mysql -u pantheon --skip-binary-as-hex -p02f7b34a02…

For more information on this behavior change, refer to the MySQL 8.0 Reference Manual.