Migrate from MariaDB to MySQL on CentOS

On this article, I will show you how to migrate your wordpress database from the MariaDB on CentOS to the real MySQL.

Why migrating to MySQL 8.0 ?

MySQL 8.0 brings a lot of new features. These features make MySQL database much more secure (like new authentication, secure password policies and management, …) and fault tolerant (new data dictionary), more powerful (new redo log design, less contention, extreme scale out of InnoDB, …), better operation management (SQL Roles, instant add columns), many (but really many!) replication enhancements and native group replication… and finally many cool stuff like the new Document Store, the new MySQL Shell and MySQL InnoDB Cluster that you should already know if you follow this blog (see these TOP 10 for features for developers and this TOP 10 for DBAs & OPS).

Starting Situation

So first before we do our upgrade, let’s verify what we have:

> select version();
+----------------+
| version()      |
+----------------+
| 5.5.60-MariaDB |
+----------------+
1 row in set (0.01 sec) 

Let’s verify the tables we also have:

> show tables;
+-----------------------+
| Tables_in_wp          |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.00 sec) 

And in /var/lib/mysqlwe can see:

[root@mysql4 mysql]# ls -l
 total 28704
 -rw-rw----. 1 mysql mysql    16384 Mar 25 20:08 aria_log.00000001
 -rw-rw----. 1 mysql mysql       52 Mar 25 20:08 aria_log_control
 -rw-rw----. 1 mysql mysql 18874368 Mar 25 20:12 ibdata1
 -rw-rw----. 1 mysql mysql  5242880 Mar 25 20:12 ib_logfile0
 -rw-rw----. 1 mysql mysql  5242880 Mar 25 20:08 ib_logfile1
 drwx------. 2 mysql mysql     4096 Mar 25 20:08 mysql
 srwxrwxrwx. 1 mysql mysql        0 Mar 25 20:08 mysql.sock
 drwx------. 2 mysql mysql     4096 Mar 25 20:08 performance_schema
 drwx------. 2 mysql mysql        6 Mar 25 20:08 test
 drwx------. 2 mysql mysql     4096 Mar 25 20:11 wp
 [root@mysql4 mysql]# ls -l wp
 total 164
 -rw-rw----. 1 mysql mysql    65 Mar 25 20:08 db.opt
 -rw-rw----. 1 mysql mysql  8688 Mar 25 20:11 wp_commentmeta.frm
 -rw-rw----. 1 mysql mysql 13380 Mar 25 20:11 wp_comments.frm
 -rw-rw----. 1 mysql mysql 13176 Mar 25 20:11 wp_links.frm
 -rw-rw----. 1 mysql mysql  8698 Mar 25 20:11 wp_options.frm
 -rw-rw----. 1 mysql mysql  8682 Mar 25 20:11 wp_postmeta.frm
 -rw-rw----. 1 mysql mysql 13684 Mar 25 20:11 wp_posts.frm
 -rw-rw----. 1 mysql mysql  8682 Mar 25 20:11 wp_termmeta.frm
 -rw-rw----. 1 mysql mysql  8666 Mar 25 20:11 wp_term_relationships.frm
 -rw-rw----. 1 mysql mysql  8668 Mar 25 20:11 wp_terms.frm
 -rw-rw----. 1 mysql mysql  8768 Mar 25 20:11 wp_term_taxonomy.frm
 -rw-rw----. 1 mysql mysql  8684 Mar 25 20:11 wp_usermeta.frm
 -rw-rw----. 1 mysql mysql 13064 Mar 25 20:11 wp_users.frm

We can see that we have .frm files and one InnoDB table space: ibdata1.

> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+ 

We can also see some aria files.

Now it’s time to upgrade to the official and original MySQL. We would like to use MySQL 8.0 of course, but the default version of MariaDB in CentOS is very old and MySQL 8.0 requires newer version of InnoDB files (undo logs, …). There for we will move first to the latest MySQL 5.7 and then to MySQL 8.0.

MySQL 5.7

The first step is to install the yum repository for MySQL Community:

# yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm

Now we can stop properly the system and then upgrade the packages.

When upgrading a MySQL system, I always recommend before stopping mysqldto set innodb_fast_shutdown to 0. This will force the dirty pages in the InnoDB Buffer Pool to be written to disk and bypass InnoDB Recovery from undo logs at the mysqld’s boot process.

> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.01 sec) 

# systemctl stop mariadb

And now we can install MySQL 5.7 (we enable 5.7 repo and disable 8.0 repo):

# yum install -y mysql-community-server mysql-community-client \
 --enablerepo=mysql57-community --disablerepo=mysql80-community

We can see that MySQL 5.7 is now replacing the old MariaDB:

...
Installed:
   mysql-community-client.x86_64 0:5.7.25-1.el7                                  
   mysql-community-libs.x86_64 0:5.7.25-1.el7                                    
   mysql-community-libs-compat.x86_64 0:5.7.25-1.el7                             
   mysql-community-server.x86_64 0:5.7.25-1.el7                                  
 Dependency Installed:
   mysql-community-common.x86_64 0:5.7.25-1.el7                                  
 Replaced:
   mariadb.x86_64 1:5.5.60-1.el7_5         mariadb-libs.x86_64 1:5.5.60-1.el7_5 
   mariadb-server.x86_64 1:5.5.60-1.el7_5 
 Complete!

We can start mysqld run the mysql_upgrade process:

# systemctl start mysqld
# mysql_upgrade

You should get some non fatal errors related on corrupted tables that mysql_upgradefixes.

After any mysql_upgradeit’s always advised to restart MySQL

You can visite your worpress site, it’s perfectly working:

MySQL 8.0

upgrade checker utility

The following step is not mandatory but highly recommended. Before upgrading to MySQL 8.0, you should install the new MySQL Shell and see the new upgrade checker tool in action ! (see also this previous article)

# yum install -y mysql-shell

Please note that you should always use the latest MySQL Shell independently of your MySQL version. In this case I’m using MySQL 5.7.25 and MySQL Shell 8.0.15.

# mysqlsh root@localhost
 MySQL JS > util.checkForServerUpgrade()
 ...

1) Usage of old temporal type
   No issues found
 2) Usage of db objects with names conflicting with reserved keywords in 8.0
   No issues found
 3) Usage of utf8mb3 charset
   No issues found
....
Errors:   0
 Warnings: 1
 Notices:  0
 No fatal errors were found that would prevent an upgrade, but some potential issues 
were detected. Please ensure that the reported issues are not significant before upgrading.

We don’t have any incompatibilities with MySQL 8.0 we can then install it without worry

MySQL 8.0 installation

Now, if you want to keep upgrading to MySQL 8.0. Just stop again mysqld as previously and install MySQL 8.0 binaries:

# mysql -e 'set global innodb_fast_shutdown=0';
# systemctl stop mysqld

Let’s run the new binaries installation:

# yum upgrade -y mysql-community-server mysql-community-client
...
Updated:
   mysql-community-client.x86_64 0:8.0.15-1.el7                                  
   mysql-community-server.x86_64 0:8.0.15-1.el7                                  
 Dependency Updated:
   mysql-community-common.x86_64 0:8.0.15-1.el7                                  
   mysql-community-libs.x86_64 0:8.0.15-1.el

Now we can start again MySQL and run mysql_upgrade (won’t be required anymore from 8.0.16):

# systemctl start mysqld
# mysql_upgrade

We can finally restart mysqld for the last time and enjoy again our WordPress site using MySQL 8.0!

Final check

We can also now verify MySQL’s datadir and see that now .frm files are gone as MySQL 8.0 uses the new Data Dictionary:

# ls -lh wp/
total 656K
-rw-r-----. 1 mysql mysql 192K Mar 25 21:13 wp_comments.ibd
-rw-r-----. 1 mysql mysql 128K Mar 25 21:13 wp_links.ibd
-rw-r-----. 1 mysql mysql 176K Mar 25 21:15 wp_posts.ibd
-rw-r-----. 1 mysql mysql 160K Mar 25 21:13 wp_users.ibd

We can notice that not all tables have their own tablespace. This can be verified with the following query:

mysql> select NAME, ROW_FORMAT, SPACE_TYPE from INNODB_TABLES where name like 'wp/%';
 +--------------------------+------------+------------+
 | NAME                     | ROW_FORMAT | SPACE_TYPE |
 +--------------------------+------------+------------+
 | wp/wp_commentmeta        | Compact    | System     |
 | wp/wp_comments           | Dynamic    | Single     |
 | wp/wp_links              | Dynamic    | Single     |
 | wp/wp_options            | Compact    | System     |
 | wp/wp_postmeta           | Compact    | System     |
 | wp/wp_posts              | Dynamic    | Single     |
 | wp/wp_term_relationships | Compact    | System     |
 | wp/wp_term_taxonomy      | Compact    | System     |
 | wp/wp_termmeta           | Compact    | System     |
 | wp/wp_terms              | Compact    | System     |
 | wp/wp_usermeta           | Compact    | System     |
 | wp/wp_users              | Dynamic    | Single     |
 +--------------------------+------------+------------+
 12 rows in set (0.24 sec)

Summary

To upgrade from MariaDB to MySQL you need to perform the follow simple steps:

• stop MariaDB’s mysqld process
• install the binary files of 5.7
• start mysqld & run mysqld_upgrade
• run MySQL Shell’s upgrade checker utility
• stop mysqld
• upgrade the binaries to MySQL 8.0
• start mysqld & run mysql_upgrade and restart mysqldif <8.0.16
• just start mysqld if >= 8.0.16