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.
Options for connecting to a remote database are under the DATABASE tab for a site.
There are two options:
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 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.
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:
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:
On the STATISTICS tab for an app we will not show database space used for remote databases.
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:
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 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.
As with Cloning & Staging, the target database server will be set to local host. If there is no localhost server the operation will fail.
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:
It is going to be very easy to make certain kinds of mistakes. Imagine the following scenario:
With this scenario your site will not work because the original local database is still setup for yourdomain.com, not yourproductiondomain.com.
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: