Swap from SQLite to MySQL 8 for Ghost

I am releasing this post with no restrictions, which means you do not need an account to view! If you would like to register to get all future blog posts in your email OR (optionally) if you wish to support me, click on the 'Free Membership' button on the bottom right!

Whenever Ghost released 5.0 back in May, they made a change so that Ghost no longer supports SQLite3 or MySQL 5.

I didn't mind running SQLite until the recent 5.9 changes on the Docker image for Ghost changed all of that. So now, if you try to run Ghost via SQLite3 on your Docker container, it will stop working.

Obviously, if you still want your site to run, you would either need to continue to use a older version of Ghost (Eg. Anything <5.9) OR change to MySQL 8.

Unfortunely, if you are running a modern day control panel like Plesk or cPanel, you may not be able to use MySQL 8 as it may have only been distrbuted with MariaDB. MariaDB is not supported on Ghost either. Solely Oracle's MySQL 8.

So if you already have MariaDB installed and you do not want to go through the hassle of going through the task of switching database engines and potentially causing issues for non-Ghost sites, then carry out the following:

This will involve creating 2 Docker containers:

• A new Ghost docker container
• MySQL 8 Docker container.

We'll quicky go through the MySQL 8 Docker set up.

• Volume Mapping should set to /var/lib/mysql and the 2nd box should be set to /var/docker/<container_name>/mysql

  • You can put this anywhere that you like, but I advise keeping it within the /var/ directory for ease of access.

• Uncheck the 'Automatic Port mapping' and you will be presented with 2 ports.

  • Port 3306 would already be used by MariaDB so change this to another port. I chose 3307.
  • Port 33060 would also be used by MariaDB so change this to 33061

Where it has Environment Variables, add the following:

• MYSQL_ROOT_PASSWORD and in the 2nd box, add in your root password. This is caps sensitive, so ensure this is strong.

Now with the database server created, we will need to create the database, for this, you will need to drop into the shell for this container.

In SSH, connect to your server ensure that you are on root user.

• Type docker exec -it <mysql_docker_container> /bin/bash/ eg: docker exec -it mysqleight_docker /bin/bash and press enter.
• Type mysql -p and enter in your MYSQL_ROOT_PASSWORD that you set earlier. Now you should see mysql >
• Type CREATE DATABASE db_name; (Replace db_name with the new name of your database)
• Type exit; to drop out of the MySQL shell and then Ctrl/Cmd+D to drop out of the docker container.

Now to set up Ghost.

Create a Ghost container as per my guide here:

https://padraig.blog/how-to-install-ghost-5-on-plesk-obsidian/

Sidenote: The above link should be a bookmark and look fancy and shit, but because I am using a .blog TLD, it breaks! Mental! I have already reported it to the Ghost devs but they are not looking at it. 🙄

When you are setting the "Environment Variables", you will need to make the following additions:

• database__client - mysql
• database__connection__host - 123.123.123.123 (Your Server IP address)
• database__connection__user - root
• database__connection__password - **************
• database__connection__database - db_name
• database__connection__port - 3007

Press "OK" and this will create a blank new Ghost site.

Create your site as per normal, then go to https://<site>/ghost/#/settings/labs to import your content.

Then copy your content folder back to /var/ghost/my_new_awesome_ghost_container/content - Restart the Ghost container and most (if not all), your posts, content, themes and everything should be back.

You can update the Ghost container as you would have before.