How to migrate sqlite3 to MySQL for Ghost v5 with Docker Compose

If you've been following Ghost Pi for a while, you'll have seen that getting Ghost running via Docker is a doddle and is a great way of self-hosting on a range of devices, including a Raspberry Pi, ASUS Tinkerboard, a Synology NAS or even your own DigitalOcean droplet.

Not only does Docker allow you to update your Ghost blog relatively easily (and quickly), it also gives you the ability to switch back to an earlier version if things go wrong as a result, simply by pulling the previous Docker image and restarting your container(s) (assuming you have configured Docker volumes for persistent data storage).

However, with the recent update to Ghost v5, there are some breaking changes that can cause issues with your Ghost blog, such as error: connect ECONNREFUSED IP.ADD.RE.SS:3306 and then your container fails to start again. The main reason for this is that Ghost now only supports the use of MySQL 8 as the database.

error: connect ECONNREFUSED - uh oh!

If you're seeing this error message, then it is more than likely you've been using sqlite3 and have just updated your Docker image to the latest one. As mentioned above, only MySQL 8 is the supported database for Ghost blogs, so there's a little work needed to resolve this issue.

Option one: Explicitly tell Ghost to stay with sqlite3 (not ideal)

The first option is to tell Ghost to continue to use sqlite3 for the database. Whilst this option works, it is not recommended as MySQL 8 is the only supported database, so moving to that is better in the long run.

However, if you just want to get back up-and-running, then you can insert a couple of Docker environment variables in your Docker Compose file to force your Ghost container to use sqlite3:

version: '3.5'
services:
  ghostpi:
    image: ghost:alpine
    restart: unless-stopped
    environment:
    ## For sqlite3 - this forces Ghost to use sqlite3
      - database__client=sqlite3
      - database__connection__filename=/var/lib/ghost/content/data/ghost.db
    ## END sqlite3

The above code snippet includes the two additional Docker environment variables needed to force Ghost to use sqlite3, so if you add those in your Docker Compose file and recreate the container, your Ghost blog should be working again.

But as we've already mentioned, this is kind of like sticking a plaster on a gaping wound to your arm and will not really solve the problem moving forward.

Option two: Migrate to MySQL (recommended)

If you want to future-proof your Docker based Ghost blog, then the recommended option here is to make the jump to MySQL. Thankfully, this is not as daunting as it seems!

The first thing you should do is make a backup of your Ghost blog and Docker volumes! Whilst we did not experience any issues doing this, if you plough straight into this without making a backup and things go wrong, you are likely to have to start again! You have been warned!

Do this as follows:

1. Go to Settings > Labs in the Ghost admin
2. Select the Export your content option to download a json file - this is an important file so don't touch or lose this!
3. If you have any members subscribed to your blog, export those too by going to Members in the Ghost admin then clicking the cog in the top right before selecting Export all members from the drop-down menu. This will give you a csv file of all your members.
4. Should you have used a different theme for your Ghost blog, other than the default Casper theme, you should also download this just in case. Do this by going to Settings > Design, click on the theme name in the bottom left corner of the screen, click the Advanced button in the top right before finally clicking on the ellipses (...) next to theme name and then Download.
5. Once you've done steps 1-4, stop your Ghost Docker container (usually done by issuing a docker-compose stop command via the command line in your Ghost blog's folder). This is only to ensure that no content changes between now and the next step.
6. Assuming you have a Docker bind for a volume (i.e. a folder on your machine where your Ghost blog's content is stored), make a copy of the entire folder and keep this as your backup.

You should now have your Ghost blog backed up and ready to go to the next step.

Update your Docker Compose file

Now you've made a backup (you have right?), you can now update your Docker Compose file to use MySQL instead of sqlite3 for the database. The following example shows what additional values are needed:

version: '3.5'
services:
  ghostpi:
    image: ghost:alpine
    restart: unless-stopped
    environment:
      - database__client=mysql
      - database__connection__host=ghostpi-db
      - database__connection__user=ghost
      - database__connection__password=yourSecurePasswordGoesHere
      - database__connection__database=ghost
      - NODE_ENV=production
      - url=https://${DOMAIN}
    volumes:
      - /location/of/your/volume/folder/content:/var/lib/ghost/content
    depends_on: ## This ensures your Ghost blog waits for the database conatiner to run
      - ghostpi-db

  ghostpi-db:
    image: mysql:8.0
    restart: unless-stopped
    container_name: ghostpi-db
    cap_add:
      - SYS_NICE
    environment:
      - MYSQL_ROOT_PASSWORD=YourSecureRootPasswordGoesHere
      - MYSQL_DATABASE=ghost
      - MYSQL_USER=ghost
      - MYSQL_PASSWORD=YourSecurePasswordGoesHere
      - MYSQL_ROOT_HOST=172.*.*.* ## Ensures the Docker network has access
    volumes:
    ## You'll need to create a new folder on your system for the Docker volume
      - /location/of/your/volume/folder/db:/var/lib/mysql

In the above example, you should see that we've added a new service to the Docker Compose file - which in the above is the ghostpi-db service. This is your MySQL database service.

Be sure that you've created a location on your system for the database volume, as this is mapped in the Docker Compose file above and is needed so your database can be stored. Once everything has been updated, you can spin up your new Docker containers with a docker-compose up.

On the first boot, you may see a few errors whilst the database is created - give it a couple of minutes and you should start to see the Ghost container creating the necessary database entries before finally telling you the blog is available via the URL you specified.

Where is all my content?!

When you start the new container up and browse to your blog's URL, it will ask you to set it all up again - don't panic! This is to be expected, but we can now begin importing everything we need again.

Give the blog the same name and create your user account with the same login details, then head to the Settings > Posts section. Why here I hear you say? Well, it's a new blog and Ghost creates a default post so you may want to remove that.

Next, you can start importing your json file by going to Settings > Labs then following the instructions on the import section. You may see a couple of errors, but this is more than likely down to your existing user already existing so don't worry too much.

Then you can import all your members by going to Settings > Members before clicking on the cog in the top right corner of the screen and finally selecting Import members and selecting your csv file you exported earlier.

Lastly, you might need to switch themes (if you are not using the default Casper one). If the theme you used is not already there, you can upload the theme you downloaded earlier by going to Settings > Design, clicking on the Casper theme in the bottom left and then selecting Upload theme in the top right before selecting your zip file of the theme. Don't forget to activate the theme afterwards!

Has it all worked?

If all has gone to plan, you should now have your Ghost blog back up-and-running but now using MySQL as the database instead of sqlite3!

A few extra tips

When we were working through this, we didn't initially wait for the database container to create the necessary entries so kept shutting down the container and pulling our hair out. Be sure to give the newly created containers time to do what they need to do first!

If you did shut down and try to spin up the containers again, you may see the database container complaining about the folder not being empty. Check the path where you created the volume for your database and ensure it is empty - be sure to remove any hidden files too (as there is often a mysql.sock file that is hidden and easy to miss!) and try again.

For those who are overly cautious, you may wish to set up a development version of your Ghost blog and test things out on there first. We did this by creating a copy of the content folder, but in a separate location - for example:

mydisk/ghost/ghostpi/development/content

And similarly for production:

mydisk/ghost/ghostpi/production/content

This allows us to keep two version separate with just a few tweaks to the Docker Compose file.