Replace MariaDB 10.3 by MySQL 8.0

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).

Not anymore a drop in replacement !

We saw in this previous post how to migrate from MariaDB 5.5 (default on CentOS/RedHat 7) to MySQL. This was a straight forward migration as at the time MariaDB was a drop in replacement for MySQL…but this is not the case anymore since MariaDB 10.x !

Lets get started with the migration to MySQL 8.0 !

Options

Two possibilities are available to us:

  1. Use logical dump for schemes and data
  2. Use logical dump for schemes and transportable InnoDB tablespaces for the data

Preparing the migration

Option 1 – full logical dump

It’s recommended to avoid to have to deal with mysql.* tables are they won’t be compatible, I recommend you to save all that information and import the required entries like users manually. It’s maybe the best time to do some cleanup.

As we are still using our WordPress site to illustrate this migration. I will dump the wp database:

# mysqldump -B wp > wp.sql

MariaDB doesn’t provide mysqlpump, so I used the good old mysqldump. There was a nice article this morning about MySQL logical dump solutions, see it here.

Option 2 – table design dump & transportable InnoDB Tables

First we take a dump of our database without the data (-d):

# mysqldump -d -B wp > wp_nodata.sq

Then we export the first table space:

[wp]> flush tables wp_comments for export;
Query OK, 0 rows affected (0.008 sec

We copy it to the desired location (the .ibd and the .cfg):

# cp wp/wp_comments.ibd ~/wp_innodb/
# cp wp/wp_comments.cfg ~/wp_innodb/

And finally we unlock the table:

[wp]> unlock tables;

These operation above need to be repeated for all the tables ! If you have a large amount of table I encourage you to script all these operations.

Replace the binaries / install MySQL 8.0

Unlike previous version, if we install MySQL from the Community Repo as seen on this post, MySQL 8.0 won’t be seen as a conflicting replacement for MariaDB 10.x. To avoid any conflict and installation failure, we will replace the MariaDB packages by the MySQL ones using the swap command of yum:

# yum swap -- install mysql-community-server mysql-community-libs-compat -- \ 
remove MariaDB-server MariaDB-client MariaDB-common MariaDB-compat

This new yum command is very useful, and allow other dependencies like php-mysql or postfix for example to stay installed without breaking some dependencies

The result of the command will be something similar to:

Removed:
MariaDB-client.x86_64 0:10.3.13-1.el7.centos
MariaDB-common.x86_64 0:10.3.13-1.el7.centos
MariaDB-compat.x86_64 0:10.3.13-1.el7.centos
MariaDB-server.x86_64 0:10.3.13-1.el7.centos
Installed:
mysql-community-libs-compat.x86_64 0:8.0.15-1.el7
mysql-community-server.x86_64 0:8.0.15-1.el7
Dependency Installed:
mysql-community-client.x86_64 0:8.0.15-1.el7
mysql-community-common.x86_64 0:8.0.15-1.el7
mysql-community-libs.x86_64 0:8.0.15-1.el7

Now the best is to empty the datadir and start mysqld:

# rm -rf /var/lib/mysql/*
# systemctl start mysql

This will start the initialize process and start MySQL.

As you may know, by default MySQL is now more secure and a new password has been generated to the root user. You can find it in the error log (/var/log/mysqld.log):

2019-03-26T12:32:14.475236Z 5 [Note] [MY-010454] [Server] 
A temporary password is generated for root@localhost: S/vfafkpD9a

At first login with the root user, the password must be changed:

# mysql -u root -p
mysql> set password='Complicate1#'

Adding the credentials

Now we need to create our database (wp), our user and its credentials.

Please, note that the PHP version used by default in CentOS might now be yet compatible with the new default secure authentication plugin, therefor we will have to create our user with the older authentication plugin, mysql_native_password. For more info see these posts:

Migrating to MySQL 8.0 without breaking old application

Drupal and MySQL 8.0.11 – are we there yet ?

Joomla! and MySQL 8.0.12

PHP 7.2.8 & MySQL 8.0

mysql> create user 'wp'@'127.0.0.1' identified with 
'mysql_native_password' by 'fred';

By default, this password (fred) won’t be allowed with the default password policy.

To not have to change our application, it’s possible to override the policy like this:

mysql> set global validate_password.policy=LOW;
mysql> set global validate_password.length=4


It’s possible to see the user and its authentication plugin easily using the following query:

mysql> select Host, User, plugin,authentication_string from mysql.user where User='wp';
+-----------+------+-----------------------+-------------------------------------------+
| Host | User | plugin | authentication_string |
+-----------+------+-----------------------+-------------------------------------------+
| 127.0.0.1 | wp | mysql_native_password | *6C69D17939B2C1D04E17A96F9B29B284832979B7 |
+-----------+------+-----------------------+-------------------------------------------+

We can now create the database and grant the privileges to our user:

mysql> create database wp;
Query OK, 1 row affected (0.00 sec)
mysql> grant all privileges on wp.* to 'wp'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

Restore the data

This process is also defined by the options chosen earlier.

Option 1

This option, is the most straight forward, one restore and our site is back online:

# mysql -u wp -pfred wp <~/wp.sql

Option 2

This operation is more complicated as it requires more steps.

First we will have to restore all the schema with no data:

# mysql -u wp -pfred wp <~/wp_nodata.sql

And now for every tables we need to perform the following operations:

mysql> alter table wp_posts discard tablespace;

# cp ~/wp_innodb/wp_posts.ibd /var/lib/mysql/wp/
# cp ~/wp_innodb/wp_posts.cfg /var/lib/mysql/wp/
# chown mysql. /var/lib/mysql/wp/wp_posts.*

mysql> alter table wp_posts import tablespace

Yes, this is required for all tables, this is why I encourage you to script it if you choose this option.

Conclusion

So as you could see, it’s still possible to migrate from MariaDB to MySQL but since 10.x, this is not a drop in replacement anymore and requires several steps including logical backup.

Subscribe to Blog via Email

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

20 Comments

  1. But it is still Oracle, and everything they touched got…. 🙂 nope, even with shiny Features not.

    • Hi Steve, then you should not use many things… Oracle contributes much more than you think, and most of the biggest companies like Oracle, IBM, Microsoft invest a lot in Open Source… And to conclude, Oracle has been a wonderful tutor for MySQL better than any other before. Also I won’t reply anymore to such arguments, all technical driven comments are welcome 😉

    • Hi Valerii,

      Curently on the small test I did, it seems so (unless you have more info).

      However, I believe that it won’t remain de case in the future as both InnoDB formats are also diverging.

      Regards,

      • Hi Valerii,

        I checked what I had in mind, and indeed if you have used `ADD INSTANT Column` feature that are different, you will need an extra step to rebuild the table (using ALTER FORCE).

  2. Transportable tablespaces is a nice feature and will be faster than the logical dump
    Some caveats:
    – You shouldn’t be using MariaDB/Percona only features (encryption or compression).
    – Import is also a slow process; all the pages need to be checksumed again.

  3. Hi Lefred,

    Nice blog. One minor comment. (typo)

    systemctl start mysq => systemctl start mysqld

    -Bala

  4. Would the instructions above be the same if I were to replace mariadb 10.2 by mysql 5.7? If not, would you consider writing an article of how to do so?

  5. Hi Lefred,

    trying to do from mariadb 10.4.12 but “flush tables xxxxx for export;” command does not result in any output files (xxxx.idb / xxxx.cfg). As we are running with partitions, is this the reason or blocker now for me?

    Thanks

  6. I try to move mariadb to mysql 8 but always get the ERROR 1064 (42000). After correcting the data, I get an error in another line. just like a never-ending error since I have around 200Mb of database. I think I will try what you suggest. Thank you!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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.