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:
Back it up.
To back it up, you will want to go to
https://<yoursite>ghost/#/settings/labs
and click on the 'Export' button to export the CSV. This will not export images. To do that, go to your
content
folder via SSH. You would have set this when you installed Plesk if you followed my guide!Copy this out to a folder in your home directory
/home/<username>/ghost_backup
If you have memberships, this may break some or all functions with it. I don't have any paid members to know whether this does or not.
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.
- You can put this anywhere that you like, but I advise keeping it within the
-
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 chose3307
. - Port
33060
would also be used by MariaDB so change this to33061
- Port
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.
root
as the username is bad practice. If I knew how to improve this, I would!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 yourMYSQL_ROOT_PASSWORD
that you set earlier. Now you should seemysql >
- Type
CREATE DATABASE db_name;
(Replacedb_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.