This how-to will guide you through the steps to migrate from MariaDB 10 to MySQL 8.
Ghost 5+ requires MySQL 8, as it’s a breaking change, so upgrade now if you’re running MariaDB.
I use an Ubuntu 20.04 LTS server hosted on Linode. However, the guide should work equally well on other platforms.
These are the steps:
- Clone the server
- Export the database(s)
- Remove MariaDB
- Install MySQL
- Setup databases and users
- Import the database(s)
- Test Ghost
- Change DNS
- Final comments
1. Clone the server
To avoid downtime while making the changes, I recommend cloning the server. This is straightforward with Linode: from the Linode screen, click on the ellipsis, and select ‘Clone’.
2. Export the database(s)
Open an ssh session and use the following commands to list your database and database user. Note that I have removed some lines in the output.
sudo mysql -u root -p
SHOW databases;
+----------------------------+
| Database |
+----------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| your_database_name |
+----------------------------+
9 rows in set (0.002 sec)
SELECT user, host, password FROM mysql.user;
+---------------------------------+----------------+-------------------------------------------+
| User | Host | Password |
+---------------------------------+----------------+-------------------------------------------+
| root | localhost | |
| your_database_user_id | IP address | * |
| mariadb.sys | localhost | |
+---------------------------------+----------------+-------------------------------------------+
9 rows in set (0.001 sec)
quit
Now let’s export the database. But first a couple of gotchas to be mindful of: 1. Check for 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.
mkdir ~/Databases
cd ~/Databases
mysqldump --opt -Q -u your_database_user_id -p your_database_name > ~/Databases/your_database_name.sql
ls
total 16400
1730043 drwxrwxr-x 2 user user 4096 Apr 14 14:44 ./
1441794 drwxrwxr-x 15 user user 4096 Apr 14 14:39 ../
1730046 -rw-rw-r-- 1 user user 1001188 Apr 14 14:43 your_database_name.sql
exit
Copy the file(s) to the local machine for safe keeping.
mkdir ~/Databases
cd ~/Databases
scp user@IP address:/home/user/Databases/* /home/user/Databases/
your_database_name.sql 100% 10MB 4.8MB/s 00:02
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 all MariaDB database common files (e.g. /etc/mysql/my.cnf)
systemctl status mariadb
sudo systemctl stop mariadb
sudo apt purge mariadb-*
sudo dpkg -l | grep mariadb
[An empty response if uninstalled]
Now remove any mysql
sudo apt purge mysql-*
sudo cp -R /var/lib/automysqlbackup/ ~
sudo dpkg -l | grep mysql
[An empty response if uninstalled]
Tidy up…
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 database(s).
sudo mysql
create database your_database_name;
create user 'your_database_user_id'@'localhost' identified by 'password';
grant all privileges on your_database_name.* to 'your_database_user_id'@'localhost';
flush privileges;
quit;
6. Import the database(s)
mysql -u your_database_user_id -p your_database_name < /home/user/Databases/your_database_name.sql
7. 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.
For example, I needed to do one of the following on a couple of blogs:
sudo find ./ ! -path "./versions/*" -type f -exec chmod 664 {} \;
ghost update 4.39.0 --force
This reinstalled binary dependencies.
8. Change DNS
When you are satisfied that Ghost is running okay, stop Ghost (ghost stop) and change the A and AAAA records for your site. You can get the IP addresses from the Linode console.
Wait for the changes to propagate, and refresh your home page in a browser. When everything has switched, you’ll see a 502 bad gateway error.
As soon as you see this, start Ghost: ghost start.
Your site has successfully switched to MySQL.
9. Final comments.
Once the site has migrated, you can power down the original Linode and remove. Or, if you have backups associated with the Linode, or other services, e.g., analytics, repeat the above steps on your production server. Linode will only charge a few Dollars while the clone is running.
