How to Migrate From MariaDB 10 to MySQL 8

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:

  1. Clone the server
  2. Export the database(s)
  3. Remove MariaDB
  4. Install MySQL
  5. Setup databases and users
  6. Import the database(s)
  7. Test Ghost
  8. Change DNS
  9. 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.

5 Likes

These instructions are fabulously detailed, thank you for taking the time to share this here @mjw :heart:

2 Likes

thanks for this. have successfully migrated to mysql. I use docker if anyone has questions on how I did it, happy to share. cheers!

1 Like

Great instructions - thank you.

If you are Debian self-hosted and the database (MariaDB) is also being used by your online-shops, photo-journal publications and any other software - you will have to migrate all the other databases on the server too.

I’ve tried this a few times - in all cases, migration from MariaDB to Community MySQL8 on Debian Stable aka Bullseye (Ubuntu upstream) is bumpy. There are conflict issues with mysql-common Debian vs Community. There seem to also be differences in server authentication defaults etc.

You have to basically:

  1. Catalog ALL database USERS - e.g. echo "select concat(user,'@',host) from mysql.user" | sudo mysql
  2. Dump ALL non-infra databases and immediately stop the server - see below
  3. Remove all traces of MariaDB and old MySQL config - /etc/mysql
  4. Move the existing databases sideways - /var/lib/mysql
  5. Install a clean MySQL8
  6. Restore ALL databases
  7. Recreate ALL database USERS and grant them rights

As an assist this is a dump command for ALL non-infra databases on a server:

alldb=$(echo "show databases" | sudo mysql | grep -Ev "^(Database|mysql|performance_schema|information_schema|sys)$")
sudo mysqldump --opt --dump-date --result-file=alldb-`date +%Y%m%d-%H%M%S`.sql --routines --events --single-transaction --verbose --databases $alldb

Note that this may need tweaking for the specific version of your original database.

You should sanity check the .sql file e.g.

sudo du -sh /var/lib/mysql/
804M    /var/lib/mysql

ls -lh alldb-20220625-120156.sql
-rw-r--r-- 1 root root 154M Jun 25 12:02 alldb-20220625-120156.sql
# a reasonable size... though a lot smaller than the 804M data/indexes...

grep 'CREATE DATABASE .* IF NOT EXISTS' alldb-20220625-120156.sql
# contains all the expected databases...

view alldb-20220625-120156.sql
# content looks sane
# last line indicates that Dump completed

The MySQL Community have a package that updated apt for the install - you can start here: MySQL :: Download MySQL APT Repository etc. etc. etc.

1 Like