Moving Ghost to MySQL

The blog you are reading now has moved from Ghost's default SQLite database to MySQL (actually MariaDB). Do you care? As a reader, my best guess is: no. As a blogger with a self-hosted Ghost blog: you should.

SQLite that Ghost uses by default is a serverless, file-based database. It is super easy to set up as it basically just needs a file in a file system. Setting up a MySQL is a lot more work, requires a separate server process etc. Not ideal for Docker deployment if you just want a single container. However, it's not really easy to make backups, query/manipulate data etc so it's not a great fit for a more permanent installation of your blog.

But first - configurations

Trying to move this blog from SQLite to MySQL was confusing at first. I kept experimenting with settings in the file config.js that resides in /var/lib/ghost/conent. After seeing that no changes I made actually affected the blog I learned that since version 1.x of Ghost config.js isn't used anymore. The config file is now json instead of js, it's in the root folder instead of in content (which makes it really hard to map as a volume in docker) and named config.production.json.

However, Ghost now supports config using environment variables, which is a lot more suitable for Docker deployments. Ghost uses nconf, so nested objects are coded using double underscores.

A config object for MySQL looks like this:

database: {
  client: 'mysql',
  connection: {
      host: 'hostname',
      port: 3306,
      user: 'db username',
      password: 'db user password',
      database: 'database name'
  }
}

Encoded as enviroment variables this becomes:

 database__client = "mysql"
 database__connection__host = "hostname"
 database__connection__port = 3306
 database__connection__user = "db username"
 database__connection__password = "db user password"
 database__connection__database = "database name"

Doing the move

There is no simple migration between SQLite and MySQL, so the best option is to use Ghost's export/import function.

1. Create a MySql with a new database user and assign user rights
2. Log in to your blog backend
3. Go to Labs and choose Export your content
4. Go to Design and download your theme
5. Update the environment variables (or config.production.json) to point to your MySQL
6. Restart Ghost
7. Log in to the backend
8. Go to Labs and choose Import content and select the file from point 3
9. Re-enable your theme (re-upload it if needed).