Ghost 5+ -- How to Migrate From MariaDB 10.x to MySQL 8.x

MariaDB 10.x

If you've recently tried to upgrade to Ghost v5 you had most definitely got into a big, big mess. Database Compatibility Issues.

Just to point out, MariaDB is not an officially supported database for Ghost. It just happened to work given the similarities with MySQL, but they optimize and test for MySQL 5 and 8.

Ghost v5 will only support MySQL 8 in production, so I'd strongly suggest switching to MySQL 8 to ensure you're running on the recommended setup that kinda shitty but hey – and trust me upgrading from MariaDB is a breaking change.

Ghost 5.0

When to update?

The best time to do a major version update is shortly after the first minor version - so for Ghost 5.x, the best time to update will be when 5.1.0 is released.

Ghost 5.0 includes significant changes to the Ghost API and database support to ensure optimal performance.

Supported databases

MySQL 8 is the only supported database for both development and production environments.
SQLite3 is supported only in development environments where scalability and data consistency across updates is not critical. (during local theme development, for example)
MySQL 5 is no longer supported in any environment.

Migrate

This guide should work similarly well on other platforms.
Here are the steps to follow:

1. Clone your server

The importance of backup Cannot be STRESSED enough – backups, backups, backups!

2. Export the database(s)

To access the MySQL shell type the following command and enter your MySQL root user password when prompted:

First, stop the targeted services:

$ cd /var/www/ghost_location
ghost stop

##
#

## Stop nginx mariadb services
$ sudo systemctl stop nginx mariadb

## Check for state
$ sudo system status nginx mariadb

$ sudo mysql -u root -p

If you haven’t set a password for your MySQL root user, you can omit the -p option.

$ sudo mysql -u root -p

Output

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| your_database_name |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
15 rows in set (0.23 sec)

mysql>

Show All MySQL Users

To get a list of all MySQL user accounts, use the SELECT statement to retrieve all rows from the mysql.users table:

mysql> SELECT User, Host FROM mysql.user;

Output


+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| your_database_user_id localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
15 rows in set (0.21 sec)

mysql> quit;

Before we export the database let's:

1. Check for the host IP address or localhost as this is important when dealing with the database.
2. Confirm the database name, user ID, and password from config.production.json under /var/www/ghost/

Exporting:

$ mkdir ~/databases

$ cd ~/databases

$ mysqldump --opt -Q -u your_database_user_id -p your_database_name > ~/databases/your_database_name.sql

3. Remove MariaDB

Uninstall MariaDB.

$ sudo dpkg -l | grep mariadb

ii  libmariadb3:amd64                     1:10.8.2+maria~focal               amd64        MariaDB database client library
ii  mariadb-client                        1:10.8.2+maria~focal               all          MariaDB database client (metapackage depending on the latest version)
rc  mariadb-client-10.3                   1:10.3.34-0ubuntu0.20.04.1         amd64        MariaDB database client binaries
ii  mariadb-client-10.8                   1:10.8.2+maria~focal               amd64        MariaDB database client binaries
ii  mariadb-client-core-10.8              1:10.8.2+maria~focal               amd64        MariaDB database core client binaries
ii  mariadb-common                        1:10.8.2+maria~focal               all          MariaDB common configuration files
ii  mariadb-server                        1:10.8.2+maria~focal               all          MariaDB database server (metapackage depending on the latest version)
rc  mariadb-server-10.3                   1:10.3.34-0ubuntu0.20.04.1         amd64        MariaDB database server binaries
ii  mariadb-server-10.8                   1:10.8.2+maria~focal               amd64        MariaDB database server binaries
ii  mariadb-server-core-10.8              1:10.8.2+maria~focal               amd64        MariaDB database core server files

###
##
#


$ sudo dpkg -l | grep mysql

ii  automysqlbackup                       2.6+debian.4-2                     all          daily, weekly and monthly backup for your MySQL database
ii  libdbd-mysql-perl:amd64               4.050-3                            amd64        Perl5 database interface to the MariaDB/MySQL database
ii  libmysqlclient21:amd64                8.0.28-0ubuntu0.20.04.3            amd64        MySQL database client library
ii  mysql-client                          8.0.28-0ubuntu0.20.04.3            all          MySQL database client (metapackage depending on the latest version)
ii  mysql-client-8.0                      8.0.28-0ubuntu0.20.04.3            amd64        MySQL database client binaries
ii  mysql-client-core-8.0                 8.0.28-0ubuntu0.20.04.3            amd64        MySQL database core client binaries
ii  mysql-common                          1:10.8.2+maria~focal 

###
##
#

...

## Stop mariadb service
$ sudo systemctl stop mariadb

## purge mariadb confg 
$ sudo apt purge 'mariadb-*'

## check for any mariadb instances - result should be empty 
$ sudo dpkg -l | grep mariadb

... now, let's remove any MySQL instances:

## purge mysql confg 
$ sudo apt purge 'mysql-*'

## check for any mysql instances - result should be empty 
sudo dpkg -l | grep mysql

... apply to the following commands:

$ sudo apt autoremove

$ sudo apt autoclean

$ sudo rm /etc/apt/sources.list.d/mariadb.list

4. Install MySQL

$ sudo apt update

$ sudo apt install mysql-server

$ sudo systemctl start mysql.service

$ sudo mysql_secure_installation

Type n for password validation, and enter your password for root.
Then y for all other prompts.

Now let’s create the database user(s) and import your database(s).

$ sudo mysql -u root -p

##
#

mysql> create database your_database_name;

mysql> create user 'your_database_user_id'@'localhost' identified by 'password';

mysql> grant all privileges on your_database_name.* to 'your_database_user_id'@'localhost';

mysql> flush privileges;
mysql> quit;

5. Import your database(s)

$ mysql -u your_database_user_id -p your_database_name < /home/user/databases/your_database_name.sql

6. Test Ghost

$ sudo systemctl start nginx

$ cd /var/www/your_website_folder
ghost start

Check that Ghost starts correctly. If there are any issues, follow the guidance from the output or run ghost doctor. Do not perform any upgrades if you encounter an error.