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

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

24 Comments

    • Hi Fernando,
      Emotionally, because I’m MySQL Evangelist 😉
      Technically, MySQL 8.0 brings new features or more secure features like X Protocol, Document Store, Clone Plugin, JSON multi-valued indexes… and more.
      Also MySQL InnoDB Cluster is a full free Open Source solution for MySQL HA. It includes MySQL Server, Group Replication, MySQL Router and MySQL Shell, all open-source and free to use without any license other than the GPL. MariaDB doesn’t not offer that as MaxScale uses another License if you have 3 or more servers behind it…. 3 is the required number for HA.

      Thank you for reading the blog.
      Regards,

  1. hi, i was reading your post, i need to migrate from MariaDB 5.5 to MySQL 8. Would you say that performing a dump with mysqldump in MariaDB 5.5 and then restoring that Dump in MySQL 8 is OK? i’ve performed those steps, but then i read the official mysql docs and they say that to migrate you need to go from mysql 5.5 to 5.6 and then from 5.6 to 5.7 and then from 5.7 to 8.0.

    i think it’s a waste of time, specially because our database is pretty small and is not using any partitioning or any other “special” feature. I must say that when i restored the dump i made in mariadb 5.5 to MySQL 8, there wasn’t any problem. It restored just perfect.

    thanks for your time!

    • The documentation is right…. when performing physical upgrades (in-place). In your case, you did a logical one (sql dump and restore).

      Cheers,

  2. Hi, is there a way to run MySQL in addition to MariaDB? I have at the moment a small server (30 customers) setup with Centos 7 and MariaDB 10.3 and now 3 new customers need MySQL 5.7 or higher. Would you suggest going straight for MySQL 8 or will it not run parallel to MariaDB?

    • Hi,

      You have good customers 😉

      More seriously, it’s possible to have both but it’s complicated if you are not used to it. MariaDB uses MySQL’s TCP port by default (3306) and the same datadir (/var/lib/mysql). So you will need to specify those to be different. Another way to have both on the same server (or having also different version of MySQL in the same server) is to use @datacharmer‘s famous tool: dbdeployer.

      Cheers,

  3. Oh don’t worry.

    But I mean having dbdeployer that configures my.cnf with ports and path to have the process running on the same server seems easier and safer than mess up with Docker.

    Then it’s up to you to eventually create your systemd script to manage the process 😉

  4. Thanks. This made the migration from MariaDB 5.5.6 a snap. It did not work once I upgraded to MariaDB 10 and tried MariaDB 10 > MySQL 8.0 though. Thanks again!

  5. I know this is an old post, but I’ve recently been tasked with supporting a Jira instance that the original installers set up with MariaDB, even though anyone who knows Atlassian knows that they do not support MariaDB with their products.
    They’re using MariaDB 10.5 on a fairly current version of Jira. Will these instructions work in migrating off MariaDB to MySQL? Ideally, I would like for it to end up on MySQL 8. But I see why starting with MySQL 5.7 would probably be best.

  6. Some program design with MYSQL, when run on Mariadb, error 🙂
    Log.ERROR: PDO Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘1’ for key ‘PRIMARY’. For query ‘INSERT INTO locale (id, name, language, date_format, time_zone, week_start, time_format, thousand_separator, decimal_mark) VALUES (‘1’, ‘Main’, ‘en_US’, ‘DD.MM.YYYY’, ‘UTC’, ‘monday’, ‘HH:mm’, ‘.’, ‘,’)’ [] []

    So My Idea is install MySQL in ducker due to my (CWP) control panel only support for Mariadb

  7. Hey there, is this method good for migrating any MariaDB database to MySQL 8, or it just works for the WordPress database?

    • Hello Lars,
      It could work with other tables of course. But be aware that with the new versions, MariaDB is not anymore a dropping replacement of MySQL. They are using the same client protocol (when using the classic one) but that’s it. The table space format is different. If this is the case, you need to use logical dump and load. Take a look at https://lefred.be/?s=mariadb
      Cheers,

      • Hey lefred, thank you for your kind reply. Well, I need to migrate from a 5.5.64 MariaDB Server to the latest MySQL Community Server 8.0 in a CentOS 7 Server. Do you think I can use your method straigth away? Ty.

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.