When running multiple services on the same server, it can become cumbersome to keep track of all the local databases. It is thus sometimes easier to install databases on their own servers, and to use these external databases for many services instead of having one local database for each service.

An easy way to get started with external databases are the managed databases offered by Digital Ocean. They are easy to set up, easy to use and Digital Ocean ensures that databases are backed up automatically every day. They also keep backups for seven days.

For security reasons, managed databases run in a private network which isolates communication at the account level, i.e. by default only your own droplets can access the databases. Requests via the public internet can still reach the databases, but only if specific IPs are whitelisted specific inbound sources. The data is also encrypted, both in transit and at rest.

But enough marketing, let us see how to set up Ghost to use a managed database instead of a local one.

Warning: Installing a new Ghost 4 instance currently does not work with managed databases, as Digital Ocean requires indexes to be set, which the Ghost migration script currently does not do.

Managed MySQL 8 Database

If not yet done, set up a MySQL managed database and create a user and a database for the data of your blog. Make sure to select Legacy – MySQL 5.x for password encryption, as Ghost can't handle the newer MySQL 8 standard just yet.

CA Certificate

Once the managed database is set up, the CA certificate offered by Digital Ocean has to be downloaded and copied to the server hosting the Ghost instance. This can be done via scp or WinSCP for example.

Then move the certificate to /usr/local/share/ca-certificates/ directory:

sudo mv bell0bytes-mysql.crt /usr/local/share/ca-certificates/.

To update the internal directory all known certificates, the update-ca-certificates command can be used. This command finds new certificates in the /usr/local/share/ca-certificates/ directory and adds them to the /etc/ssl/certs/ directory.

sudo update-ca-certificates

Configuring Ghost

To tell Ghost to use the new external managed database, open the config.production.json file in the root directory of the Ghost installation, and change the database parameters as follows:

sudo vim /var/www/ghost/config.production.json

  "database": {
    "client": "mysql",
    "connection": {
      "host": "digital-ocean-host-string",
      "port": 25060,
      "user": "db-user",
      "password": "***",
      "database": "db-name"
    }
  },

The host string can be found on the Digital Ocean dashboard of the managed database, just make sure to select the correct user and database.

Backup, Restore and Run

As said above, the Ghost installer currently does not work with Digital Ocean's managed databases, but for an up and running Ghost instance, this is not a problem. Be warned, however, this is not supported by Ghost!

First, take a SQL dump of the current database with the following command (just be sure to replace ghost-db with the name of your database):

sudo mysqldump -h localhost -u root -p --single-transaction --set-gtid-purged=OFF ghost-db > ghost.sql

Once done, the backup can be restored or imported to the new managed database:

sudo mysql -u db-user -p -h digital-ocean-host-string -P 25060 db-name < ghost.sql

And that's it. You can now restart your Ghost instance and enjoy your easily manageable external database!

This blog actually is a proof that it works, as it is currently running on a MySQL 8 database managed by Digital Ocean.

GhostFOSSMySQLDatabase