WPCloudDeploy Documentation

Using Remote Databases

Introduction

The default database server for new WordPress sites is the local MYSQL / MARIADB server.  For most sites, this is more than sufficient.  However, you can choose to use a remote database such as AWS RDS, DIGITALOCEAN or VULTR database or even a database server on a custom server.

As long as the site is able to connect to the remote database with a user that has all read, write, create, drop permissions for the database and the remote database is a MYSQL or MARIADB server, there should be few issues with using one.

Using A Remote Database

Options for connecting to a remote database are under the DATABASE tab for a site.

There are two options:

  • Switch To Remote Database
  • Copy Database – Local To Remote

In most cases you will want to use the COPY DATABASE – LOCAL TO REMOTE option to seed your remote database with a snapshot of the data in the local database.

Once the remote database has been seeded with a copy of the local database you can then use the SWITCH TO REMOTE DATABASE option to complete the switch.

New Sites

New sites will always be installed on the local database server. After installation you can connect them up to the remote server if you like using the two options described in the prior section.

Limitations

There are some limitations when using remote databases – primarily because of limited permissions for db users.

When using local databases we usually have full control over the database server including root control.  But for remote databases the db user does not usually have this type of high level access.

Some limitations are:

Copy To Remote

If you create a remote database and then delete the site, in many cases the remote data is NOT deleted because the user does not have the permissions to drop the database.  This presents two issues:

  1. You will have to manually remove the remote database
  2. If you create the same site (or a new site) that uses the same database name, you might end up with unwanted data in the new site that are remnants from the original deleted site.

Deleting Sites

If you delete a site connected to a remote database it is possible the database might not be dropped and/or the tables might not be removed if the database user in wp-config.php does not have the proper permissions to drop the database or remove tables.

Statistics

On the STATISTICS tab for an app we will not show database space used for remote databases.

Restoring Data

In many cases, the remote user does not have the rights to drop a database.  Dropping the database is the cleanest way to make sure the old data goes away.  But, during the restore process we’ll also attempt to drop individual tables before restoring them.   This should, in most cases, result in your restored site having the correct data.  But there are still two issues here:

  1. Any tables that are not explicitly referenced in the original backup will NOT be dropped and
  2. If any table has foreign key constraints or trigger constraints that prevents them from being dropped, we will not be able to detect this and you might end up with duplicate data or missing data.  Note that core WP does not have FOREIGN KEY constraints or TRIGGER constraints but some plugins will add them (eg: real-time backup plugins).

Cloning Sites

Cloning sites will still use the LOCAL database server, not the remote server. Data from the remote server will be cloned to the local server.  If you do not have a local server the cloning operation will fail.

Staging Sites

Staging sites will still use the LOCAL database server, not the remote server. Data from the remote server will be cloned to the local server. if you do not have a local server the cloning operation will fail.

Copy To Server

As with Cloning & Staging, the target database server will be set to local host.  If there is no localhost server the operation will fail.

Data Transfers

When copying data to remote databases (or from remote databases) as well as when copying data during cloning and staging operations, the following elements might not be copied:

  • Foreign key relationships
  • Triggers
  • Stored Procedures
  • Views

Notes

  1. We have tested this feature using the DigitalOcean and Vultr database services.  We fully expect other database services to work such as RDS and AZURE but have not completed testing those internally.
  2. For performance reasons we strongly recommend that you establish a local network VPC between your WordPress server and your primary Remote Database server instead of sending the traffic across the internet.
  3. For the same reasons, we recommend that your database server provider and WordPress server provider are the same and that both servers are in the same region or availability zone.

Easy Mistakes

It is going to be very easy to make certain kinds of mistakes.  Imagine the following scenario:

  • You create a site yourdomain.com 
  • You configure it to use a remote database
  • You change the domain to yourproductiondomain.com
  • You then decided to switch back to the local database but you use the original local database

With this scenario your site will not work because the original local database is still setup for yourdomain.com, not yourproductiondomain.com.

Using Remote Databases On Servers Earlier Than WPCD 5.0

Using a Remote Database is a new feature in WPCD 5.0. If you decide to setup a remote database connection on a server that was initially deployed with a version of WPCD earlier than 5.0 then you must do the following as well:

  • Remove and reinstall callbacks
  • Remove and reinstall backups

 

Share: