So, you likely already know that I use the Ghost blogging system. I absolutely love it. For a while, I hosted my blog with Midnight. I migrated away and now run it on my own VPS. There's nothing wrong with Midnight, they're an amazing service and I will always recommend them over the official Ghost hosting. However, I like to have full control of what I run, so here we are.
Now, the thing that's great about managed hosting like Midnight? I don't have to worry about upgrading anything. My most recent escapade of upgrading Ghost reminded me why I switched to Midnight in the first place, and made me consider switching back. Ghost can sometimes be so smooth and easy to upgrade, in fact it usually is. Then sometimes... it's terrible. My most recent upgrade was one of those experiences.
I ran the normal upgrade process, and went to start my Ghost blog back up, and was greeted with a beautiful error message.
Wonderful. I love SQL errors. They're my favorite.
Or perhaps they are not.
It was time to do some research. I came across this wonderful post by Michael Freidrich and this ended up being the solution, although I needed to tweak it. I don't use Docker for my Ghost installation, so the commands won't work as he wrote them. So let's walk through the modifications I needed to do.
First, I needed to change the directory to my ghost installation. Note, the following is not the actual directory, it's just a representation.
Once I was in my directory, I needed to create my backups directory:
Then I needed to get the username and password for my ghost user.
Once I grabbed the username and password from this file, I had to dump the database. So I first confirmed my database name.
mysql -u USERNAME -p
Once I had that, it was time to back it up.
mysqldump -h localhost -u USERNAME -pPASSWORD --databases DATABASENAME > backup-ghost-db.sql
Now, if I've learned anything in my career in IT, it's never directly modify the backup file. Make changes to a copy of it. So I moved it to the backups folder, and made a copy
mv backup-ghost-db.sql backups
cp backup-ghost-db.sql backup-ghost-db-new.sql
Run the SED command to change the collation of the database on the copy of the backup:
sed -i 's/utf8mb4_general_ci/utf8mb4_0900_ai_ci/g' backup-ghost-db-new.sql
You can run the diff he recommends to confirm that the changes were properly made, and I did... and I'd recommend you do:
diff -ur backup-ghost-db.sql backup-ghost-db-new.sql
If all looks good, go back in to your MySQL server as the root account:
mysql -u root -p
Drop the database and create it again:
DROP DATABASE databasename;
CREATE DATABASE databasename;
Then restore the copied/modified database backup:
mysql -h localhost -u root -pPASSWORD databasename < backups/backup-ghost-db-new.sql
Once it finishes, start your Ghost blog:
And I was golden! Back online. But good lord, I wish Ghost was always a smooth upgrade...