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:
- Use logical dump for schemes and data
- 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 oldmysqldump. 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 mysq
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
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.