Hello friends, on certain occasions, some clients, for whatever reason, ask us to migrate a supposedly “equivalent” brand of an engine in terms of belonging to the MySQL family or ecosystem.
In this case, we will be analyzing the migration of MariaDB to Percona Server for MySQL 8 in particular. A comment worth clarifying is that, although Percona Server for MySQL is a drop-in replacement for MySQL since it offers compatibility continuity (Percona Server for MySQL even offers certain features that the community version does not, for free!), the same does not happen with MariaDB, which from a specific version, we could say in some way, distanced itself from MySQL and Percona Server for MySQL. Much has been said about the subject, and several interesting links on the ‘net talk about it.
In this opportunity, we will focus strictly on how the migration should be done, and in particular, I will “zoom” (if you allow me the analogy) in on everything related to security.
Based on our experience, the safest way to migrate MariaDB to Percona Server for MySQL is logical. For this, we recommend using mydumper since it offers several advantages over mysqldump, such as parallelism, compression, and other features that make it really interesting.
With all this said, what would be the steps to follow at a general plan level? Basically, it would be something like:
- Make a logical backup from MariaDB using mydymper.
- Upload that dump to the Percona Server for MySQL using myloader.
Simple, right? No, my friend, it’s not that simple. Leaving aside certain complexities regarding the compatible data types, and the code created in the instance (Store Procedures, Functions, etc.), which we will not talk about in this blog, there is another fence that many underestimate and is essential: Security.
Why do I bother talking about this? Because in MariaDB (also in Percona Server for MySQL 8), there are ROLES and such, they may have been used. Roles are very convenient for grouping permissions and assigning them to users. That has already been discussed, and here we can see one example.
So what would the steps be like now? We would have to:
- Make a security backup of the “source” instance (MariaDB) for this. The most practical/usual is to use pt-show-grants.
- Once the users, roles, etc., have been created, execute the two steps mentioned above but with the caveat that ONLY the application schemas will be exported/imported AND NOT those of the internal data dictionary (such as sys, mysql, information_schema, and so on).
Alright, let’s go to the example to see if it’s as easy as it seems.
1 2 | CentOS7_2 192.168.0.71 (CentOS 7.9 + Percona Server 8.0.32-24) CentOS7_3 192.168.0.72 (CentOS 7.9 + MariaDB 10.11.3) |
Let’s create the table and insert data into it.
1 2 3 4 5 6 7 8 9 10 11 | MariaDB [test]> CREATE TABLE Persons (PersonID int primary key, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)); Query OK, 0 rows affected (0.116 sec) mysql> insert into Persons values (1,'Joey','Koz','USA','N. Carolina'); Query OK, 1 row affected (0.00 sec) mysql> insert into Persons values (2,'Wally','G','ARG','MZA'); Query OK, 1 row affected (0.02 sec) mysql> insert into Persons values (3,'Fer','Matt','ARG','QUI'); Query OK, 1 row affected (0.00 sec) |
Let’s start creating roles, and users, and assigning them:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | MariaDB [(none)]> create role role_can_read; Query OK, 0 rows affected (0.017 sec) MariaDB [(none)]> create role role_can_write; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> create role role_can_all; Query OK, 0 rows affected (0.024 sec) MariaDB [(none)]> grant select on test.* to role_can_read; Query OK, 0 rows affected (0.015 sec) MariaDB [(none)]> grant insert,delete,update on test.* to role_can_write; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> create user user_want_read identified by 'wantread'; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> create user user_want_write identified by 'wantwrite'; Query OK, 0 rows affected (0.019 sec) MariaDB [(none)]> create user user_want_all identified by 'wantnall'; Query OK, 0 rows affected (0.013 sec) MariaDB [(none)]> grant role_can_read to user_want_read; Query OK, 0 rows affected (0.013 sec) MariaDB [(none)]> grant role_can_write to user_want_write; Query OK, 0 rows affected (0.033 sec) MariaDB [(none)]> grant role_can_all to user_want_all; Query OK, 0 rows affected (0.013 sec) |
We check everything:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | MariaDB [(none)]> show grants for user_want_read; +---------------------------------------------------------------------------------------------------------------+ | Grants for user_want_read@% | +---------------------------------------------------------------------------------------------------------------+ | GRANT `role_can_read` TO `user_want_read`@`%` | | GRANT USAGE ON *.* TO `user_want_read`@`%` IDENTIFIED BY PASSWORD '*9084B8A46A36BE084E6FB06E1E750D596B15CC5D' | +---------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [(none)]> show grants for user_want_write; +----------------------------------------------------------------------------------------------------------------+ | Grants for user_want_write@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT `role_can_write` TO `user_want_write`@`%` | | GRANT USAGE ON *.* TO `user_want_write`@`%` IDENTIFIED BY PASSWORD '*5C7B1B946CC5F748678A0BA197B9122AD7EA5634' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [(none)]> show grants for user_want_all; +--------------------------------------------------------------------------------------------------------------+ | Grants for user_want_all@% | +--------------------------------------------------------------------------------------------------------------+ | GRANT `role_can_all` TO `user_want_all`@`%` | | GRANT USAGE ON *.* TO `user_want_all`@`%` IDENTIFIED BY PASSWORD '*210F0DA943585CF2372ADF07342C92824DAA9EE4' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) |
And this is where all the magic begins.
On a third server, to avoid file transfers, etc., we execute the following commands:
a) The security stuff:
1 2 3 4 5 6 7 | somewhere $ mkdir -p /home/percona/MIGRATION_SECURITY somewhere $ cd /home/percona/MIGRATION_SECURITY somewhere $ mysql -h 192.168.0.72 -u root -proot -Bsne "select concat('CREATE USER IF NOT EXISTS ',user,'@''',host,'''',' IDENTIFIED WITH mysql_native_password AS ''',authentication_string,''';') from mysql.user where is_role='N' and user <> 'mariadb.sys' union all select concat('CREATE ROLE IF NOT EXISTS ',user,';') from mysql.user where is_role='Y' order by 1 ;" 2>/dev/null > step1.sql somewhere $ mysql -h 192.168.0.72 -u root -proot -Bsne "select concat('ALTER USER IF EXISTS ',user,'@''',host,'''',' IDENTIFIED WITH mysql_native_password AS ''',authentication_string,''';') from mysql.user where is_role='N' and user <> 'mariadb.sys'" 2>/dev/null > step2.sql somewhere $ pt-show-grants -u root -p root -h 192.168.0.72 | egrep -v "Grants|IDENTIFIED|mariadb.sys|PROXY" > step3.sql somewhere $ mysql -h 192.168.0.72 -u root -proot -Bsne "select concat('SHOW GRANTS FOR ',user,';') from mysql.user where is_role='Y' order by 1 ;" 2>/dev/null| grep -vi PUBLIC | mysql -Bsn -h 192.168.0.72 -u root -proot 2>/dev/null | sed 's/$/;/g' > step4.sql somewhere $ for NUM in {1..4}; do mysql -h 192.168.0.71 -u root -proot -f -vve "source step$NUM.sql"; done |
b) The data migration stuff (clarification: in this “somewhere” there should be enough space to be able to hold the data that we want to migrate).
1 2 3 4 | somewhere $ mkdir -p /home/percona/MIGRATION_DATA somewhere $ cd /home/percona/MIGRATION_DATA somewhere $ mydumper -h 192.168.0.72 -u root -p root -v 3 --database=test --outputdir /home/percona/MIGRATION_DATA/ somewhere $ myloader -h 192.168.0.71 -u root -p root -v 3 --database=test --overwrite-tables -d /home/percona/MIGRATION_DATA/ |
And that’s it.
Now you will say, why didn’t you directly use the output of MariaDB’s pt-show-grants and perform all these series of witchcraft or cheap tricks? I’ll tell you, friend: unfortunately, the tool is not very friendly to MariaDB. Although it decently generates “something” as output, it is insufficient, and the project will undoubtedly fail.
See the output generated by pt-show-grants (I’ve filtered headers and comments, not much) for the MariaDB instance:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | somewhere $ pt-show-grants -u root -p root -h 192.168.0.72 | egrep -v "Grants|Dumped" GRANT DELETE, SELECT ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`; GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`; GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'; GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION; GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; GRANT `role_can_all` TO `root`@`localhost` WITH ADMIN OPTION; GRANT `role_can_read` TO `root`@`localhost` WITH ADMIN OPTION; GRANT `role_can_write` TO `root`@`localhost` WITH ADMIN OPTION; GRANT USAGE ON *.* TO `user_want_all`@`%` IDENTIFIED BY PASSWORD '*210F0DA943585CF2372ADF07342C92824DAA9EE4'; GRANT `role_can_all` TO `user_want_all`@`%`; GRANT USAGE ON *.* TO `user_want_read`@`%` IDENTIFIED BY PASSWORD '*9084B8A46A36BE084E6FB06E1E750D596B15CC5D'; GRANT `role_can_read` TO `user_want_read`@`%`; GRANT USAGE ON *.* TO `user_want_write`@`%` IDENTIFIED BY PASSWORD '*5C7B1B946CC5F748678A0BA197B9122AD7EA5634'; GRANT `role_can_write` TO `user_want_write`@`%`; |
- Do you see the CREATE USER command and password authentication method needed in Percona Server for MySQL 8? I don’t (hence the step1.sql).
- Do you see any password manipulation that might work using MariaDB commands but on Percona Server for MySQL 8? I don’t think so (hence the step2.sql).
- The only step to get the most out of the utility is in the role assignment (step3.sql).
- Do you see the GRANTS assigned to the schemas? I don’t (that’s why the step4.sql).
This is the output of pt-show-grants for the migrated instance (Percona Server for MySQL 8):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | somewhere $ pt-show-grants -u root -p root -h 192.168.0.71 | egrep -v "Grants|IDENTIFIED|mariadb.sys|PROXY|Dumped|oles" CREATE ROLE IF NOT EXISTS `role_can_all`; CREATE ROLE IF NOT EXISTS `role_can_read`; CREATE ROLE IF NOT EXISTS `role_can_write`; CREATE USER IF NOT EXISTS `role_can_write`@`%`; GRANT DELETE, INSERT, UPDATE ON `test`.* TO `role_can_write`@`%`; GRANT USAGE ON *.* TO `role_can_write`@`%`; CREATE USER IF NOT EXISTS `role_can_read`@`%`; GRANT SELECT ON `test`.* TO `role_can_read`@`%`; GRANT USAGE ON *.* TO `role_can_read`@`%`; CREATE USER IF NOT EXISTS `role_can_all`@`%`; GRANT USAGE ON *.* TO `role_can_all`@`%`; GRANT `role_can_read`@`%`,`role_can_write`@`%` TO `role_can_all`@`%`; CREATE USER IF NOT EXISTS `mysql.infoschema`@`localhost`; GRANT AUDIT_ABORT_EXEMPT,FIREWALL_EXEMPT,SYSTEM_USER ON *.* TO `mysql.infoschema`@`localhost`; GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`; CREATE USER IF NOT EXISTS `mysql.session`@`localhost`; GRANT AUDIT_ABORT_EXEMPT,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,FIREWALL_EXEMPT,PERSIST_RO_VARIABLES_ADMIN,SESSION_VARIABLES_ADMIN,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN ON *.* TO `mysql.session`@`localhost`; GRANT SELECT ON `mysql`.`user` TO `mysql.session`@`localhost`; GRANT SELECT ON `performance_schema`.* TO `mysql.session`@`localhost`; GRANT SHUTDOWN, SUPER ON *.* TO `mysql.session`@`localhost`; CREATE USER IF NOT EXISTS `mysql.sys`@`localhost`; GRANT AUDIT_ABORT_EXEMPT,FIREWALL_EXEMPT,SYSTEM_USER ON *.* TO `mysql.sys`@`localhost`; GRANT SELECT ON `sys`.`sys_config` TO `mysql.sys`@`localhost`; GRANT TRIGGER ON `sys`.* TO `mysql.sys`@`localhost`; GRANT USAGE ON *.* TO `mysql.sys`@`localhost`; CREATE USER IF NOT EXISTS `root`@`%`; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `root`@`%` WITH GRANT OPTION; GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION; CREATE USER IF NOT EXISTS `root`@`localhost`; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `root`@`localhost` WITH GRANT OPTION; GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION; GRANT `role_can_all`@`%`,`role_can_read`@`%`,`role_can_write`@`%` TO `root`@`localhost` WITH ADMIN OPTION; CREATE USER IF NOT EXISTS `user_want_all`@`%`; GRANT USAGE ON *.* TO `user_want_all`@`%`; GRANT `role_can_all`@`%` TO `user_want_all`@`%`; CREATE USER IF NOT EXISTS `user_want_read`@`%`; GRANT USAGE ON *.* TO `user_want_read`@`%`; GRANT `role_can_read`@`%` TO `user_want_read`@`%`; CREATE USER IF NOT EXISTS `user_want_write`@`%`; GRANT USAGE ON *.* TO `user_want_write`@`%`; GRANT `role_can_write`@`%` TO `user_want_write`@`%`; |
It is what it should be.
Conclusion
Since MariaDB has gone farther away from MySQL, migrating back to the MySQL ecosystem and hence to Percona Server for MySQL is not as straightforward as it could be. Due to the same reasons, Percona Toolkit will not be able to assist in the migration task. This blog post will give you the tricks needed for a successful migration.
Of course, you always have the chance to contact us and ask for assistance with any migration. You can also learn how Percona experts can help you migrate to Percona Server for MySQL seamlessly here.
I hope you enjoyed the blog, and see you in the next one!
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Did you consider the special
--system=users
option to mariadb-dump?From
man mariadb-dump
:• users – the users, roles and their grants outputed as CREATE USER,
CREATE ROLE, GRANT, and SET DEFAULT ROLE (ALTER USER for MySQL-8.0+).
Hey Karl,
Thank you so much for reading and making this suggestion. I have a few things to say:
Not only do you need a server version equal to or higher than 10.3, but you also need at least the corresponding version of the MariaDB client tools, including mysqldump. Although my test was made using the latest version of MariaDB, my intention was to cover all possible versions such as 10.0 onwards. I didn’t write such clarification in order to avoid making this blog so long.Regardless you have version 10.3 or higher, the syntax of the CREATE USER differs and doesn’t work in MySQL/Percona Server. Below I show you an example
The dump on MariaDB
The CREATE USER attempt on MySQL/Percona Server
Also, you may ask, “Why are you talking about ROLES above all?” Again, ROLES were introduced in MariaDB 10.2, and the dump doesn’t look so good
Thanks for your suggestion, anyway. Best,
Fernando.