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:
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:
Alright, let’s go to the example to see if it’s as easy as it seems.
|
1 |
CentOS7_2 192.168.0.71 (CentOS 7.9 + Percona Server 8.0.32-24)<br>CentOS7_3 192.168.0.72 (CentOS 7.9 + MariaDB 10.11.3) |
Let’s create the table and insert data into it.
|
1 |
MariaDB [test]> CREATE TABLE Persons (PersonID int primary key, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));<br>Query OK, 0 rows affected (0.116 sec)<br><br>mysql> insert into Persons values (1,'Joey','Koz','USA','N. Carolina');<br>Query OK, 1 row affected (0.00 sec)<br><br>mysql> insert into Persons values (2,'Wally','G','ARG','MZA');<br>Query OK, 1 row affected (0.02 sec)<br><br>mysql> insert into Persons values (3,'Fer','Matt','ARG','QUI');<br>Query OK, 1 row affected (0.00 sec)<br> |
Let’s start creating roles, and users, and assigning them:
|
1 |
MariaDB [(none)]> create role role_can_read;<br>Query OK, 0 rows affected (0.017 sec)<br><br>MariaDB [(none)]> create role role_can_write;<br>Query OK, 0 rows affected (0.012 sec)<br><br>MariaDB [(none)]> create role role_can_all;<br>Query OK, 0 rows affected (0.024 sec)<br><br>MariaDB [(none)]> grant select on test.* to role_can_read;<br>Query OK, 0 rows affected (0.015 sec)<br><br>MariaDB [(none)]> grant insert,delete,update on test.* to role_can_write;<br>Query OK, 0 rows affected (0.002 sec)<br><br>MariaDB [(none)]> create user user_want_read identified by 'wantread';<br>Query OK, 0 rows affected (0.002 sec)<br><br>MariaDB [(none)]> create user user_want_write identified by 'wantwrite';<br>Query OK, 0 rows affected (0.019 sec)<br><br>MariaDB [(none)]> create user user_want_all identified by 'wantnall';<br>Query OK, 0 rows affected (0.013 sec)<br><br>MariaDB [(none)]> grant role_can_read to user_want_read;<br>Query OK, 0 rows affected (0.013 sec)<br><br>MariaDB [(none)]> grant role_can_write to user_want_write;<br>Query OK, 0 rows affected (0.033 sec)<br><br>MariaDB [(none)]> grant role_can_all to user_want_all;<br>Query OK, 0 rows affected (0.013 sec)<br><br> |
We check everything:
|
1 |
MariaDB [(none)]> show grants for user_want_read;<br>+---------------------------------------------------------------------------------------------------------------+<br>| Grants for user_want_read@% |<br>+---------------------------------------------------------------------------------------------------------------+<br>| GRANT `role_can_read` TO `user_want_read`@`%` |<br>| GRANT USAGE ON *.* TO `user_want_read`@`%` IDENTIFIED BY PASSWORD '*9084B8A46A36BE084E6FB06E1E750D596B15CC5D' |<br>+---------------------------------------------------------------------------------------------------------------+<br>2 rows in set (0.000 sec)<br><br>MariaDB [(none)]> show grants for user_want_write;<br>+----------------------------------------------------------------------------------------------------------------+<br>| Grants for user_want_write@% |<br>+----------------------------------------------------------------------------------------------------------------+<br>| GRANT `role_can_write` TO `user_want_write`@`%` |<br>| GRANT USAGE ON *.* TO `user_want_write`@`%` IDENTIFIED BY PASSWORD '*5C7B1B946CC5F748678A0BA197B9122AD7EA5634' |<br>+----------------------------------------------------------------------------------------------------------------+<br>2 rows in set (0.000 sec)<br><br>MariaDB [(none)]> show grants for user_want_all;<br>+--------------------------------------------------------------------------------------------------------------+<br>| Grants for user_want_all@% |<br>+--------------------------------------------------------------------------------------------------------------+<br>| GRANT `role_can_all` TO `user_want_all`@`%` |<br>| GRANT USAGE ON *.* TO `user_want_all`@`%` IDENTIFIED BY PASSWORD '*210F0DA943585CF2372ADF07342C92824DAA9EE4' |<br>+--------------------------------------------------------------------------------------------------------------+<br>2 rows in set (0.000 sec) <br> |
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 |
somewhere $ mkdir -p /home/percona/MIGRATION_SECURITY<br>somewhere $ cd /home/percona/MIGRATION_SECURITY<br>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<br>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<br>somewhere $ pt-show-grants -u root -p root -h 192.168.0.72 | egrep -v "Grants|IDENTIFIED|mariadb.sys|PROXY" > step3.sql<br>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<br>somewhere $ for NUM in {1..4}; do mysql -h 192.168.0.71 -u root -proot -f -vve "source step$NUM.sql"; done<br> |
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 |
somewhere $ mkdir -p /home/percona/MIGRATION_DATA<br>somewhere $ cd /home/percona/MIGRATION_DATA<br>somewhere $ mydumper -h 192.168.0.72 -u root -p root -v 3 --database=test --outputdir /home/percona/MIGRATION_DATA/<br>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 |
somewhere $ pt-show-grants -u root -p root -h 192.168.0.72 | egrep -v "Grants|Dumped"<br>GRANT DELETE, SELECT ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`;<br>GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`;<br>GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B';<br>GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION;<br>GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;<br>GRANT `role_can_all` TO `root`@`localhost` WITH ADMIN OPTION;<br>GRANT `role_can_read` TO `root`@`localhost` WITH ADMIN OPTION;<br>GRANT `role_can_write` TO `root`@`localhost` WITH ADMIN OPTION;<br>GRANT USAGE ON *.* TO `user_want_all`@`%` IDENTIFIED BY PASSWORD '*210F0DA943585CF2372ADF07342C92824DAA9EE4';<br>GRANT `role_can_all` TO `user_want_all`@`%`;<br>GRANT USAGE ON *.* TO `user_want_read`@`%` IDENTIFIED BY PASSWORD '*9084B8A46A36BE084E6FB06E1E750D596B15CC5D';<br>GRANT `role_can_read` TO `user_want_read`@`%`;<br>GRANT USAGE ON *.* TO `user_want_write`@`%` IDENTIFIED BY PASSWORD '*5C7B1B946CC5F748678A0BA197B9122AD7EA5634';<br>GRANT `role_can_write` TO `user_want_write`@`%`; |
This is the output of pt-show-grants for the migrated instance (Percona Server for MySQL 8):
|
1 |
somewhere $ pt-show-grants -u root -p root -h 192.168.0.71 | egrep -v "Grants|IDENTIFIED|mariadb.sys|PROXY|Dumped|oles"<br>CREATE ROLE IF NOT EXISTS `role_can_all`;<br>CREATE ROLE IF NOT EXISTS `role_can_read`;<br>CREATE ROLE IF NOT EXISTS `role_can_write`;<br>CREATE USER IF NOT EXISTS `role_can_write`@`%`;<br>GRANT DELETE, INSERT, UPDATE ON `test`.* TO `role_can_write`@`%`;<br>GRANT USAGE ON *.* TO `role_can_write`@`%`;<br>CREATE USER IF NOT EXISTS `role_can_read`@`%`;<br>GRANT SELECT ON `test`.* TO `role_can_read`@`%`;<br>GRANT USAGE ON *.* TO `role_can_read`@`%`;<br>CREATE USER IF NOT EXISTS `role_can_all`@`%`;<br>GRANT USAGE ON *.* TO `role_can_all`@`%`;<br>GRANT `role_can_read`@`%`,`role_can_write`@`%` TO `role_can_all`@`%`;<br>CREATE USER IF NOT EXISTS `mysql.infoschema`@`localhost`;<br>GRANT AUDIT_ABORT_EXEMPT,FIREWALL_EXEMPT,SYSTEM_USER ON *.* TO `mysql.infoschema`@`localhost`;<br>GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;<br>CREATE USER IF NOT EXISTS `mysql.session`@`localhost`;<br>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`;<br>GRANT SELECT ON `mysql`.`user` TO `mysql.session`@`localhost`;<br>GRANT SELECT ON `performance_schema`.* TO `mysql.session`@`localhost`;<br>GRANT SHUTDOWN, SUPER ON *.* TO `mysql.session`@`localhost`;<br>CREATE USER IF NOT EXISTS `mysql.sys`@`localhost`;<br>GRANT AUDIT_ABORT_EXEMPT,FIREWALL_EXEMPT,SYSTEM_USER ON *.* TO `mysql.sys`@`localhost`;<br>GRANT SELECT ON `sys`.`sys_config` TO `mysql.sys`@`localhost`;<br>GRANT TRIGGER ON `sys`.* TO `mysql.sys`@`localhost`;<br>GRANT USAGE ON *.* TO `mysql.sys`@`localhost`;<br>CREATE USER IF NOT EXISTS `root`@`%`;<br>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;<br>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;<br>CREATE USER IF NOT EXISTS `root`@`localhost`;<br>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;<br>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;<br>GRANT `role_can_all`@`%`,`role_can_read`@`%`,`role_can_write`@`%` TO `root`@`localhost` WITH ADMIN OPTION;<br>CREATE USER IF NOT EXISTS `user_want_all`@`%`;<br>GRANT USAGE ON *.* TO `user_want_all`@`%`;<br>GRANT `role_can_all`@`%` TO `user_want_all`@`%`;<br>CREATE USER IF NOT EXISTS `user_want_read`@`%`;<br>GRANT USAGE ON *.* TO `user_want_read`@`%`;<br>GRANT `role_can_read`@`%` TO `user_want_read`@`%`;<br>CREATE USER IF NOT EXISTS `user_want_write`@`%`;<br>GRANT USAGE ON *.* TO `user_want_write`@`%`;<br>GRANT `role_can_write`@`%` TO `user_want_write`@`%`; |
It is what it should be.
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!
Resources
RELATED POSTS
