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.