Migrating From MariaDB to Percona Server for MySQL 8: The Security Witchcraft

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.

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)

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.

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)

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:

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)

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:

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)

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:

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

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

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/

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:

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`@`%`;

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

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`@`%`;

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 `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!

Try Percona Distribution for MySQL today!

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Karl Levik

11 months ago

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

Fernando Mattera

Author

Reply to Karl Levik

11 months ago

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

$ mariadb-dump –system=users –compact | grep user_want_all | grep -i create
CREATE USER user_want_all@% IDENTIFIED BY PASSWORD
‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

$ mariadb-dump –system=users –compact | grep user_want_all | grep -i create

CREATE USER user_want_all@% IDENTIFIED BY PASSWORD

‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

The CREATE USER attempt on MySQL/Percona Server

mysql> CREATE USER user_want_all@% IDENTIFIED BY PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4” at line 1

mysql> CREATE USER user_want_all@% IDENTIFIED BY PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4” at line 1

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

MariaDB_10.2 > select @@version;
+———————+
| @@version      |
+———————+
| 10.2.44-MariaDB-log |
+———————+
1 row in set (0.03 sec)

MariaDB_10.2 > create role foo;
Query OK, 0 rows affected (0.03 sec)

MariaDB_10.2 > show grants for foo;
+—————————–+
| Grants for foo       |
+—————————–+
| GRANT USAGE ON *.* TO ‘foo’ |
+—————————–+
1 row in set (0.02 sec)

$ mysqldump –system=users | grep -i foo
/*!80001 CREATE ROLE ‘foo’ */;
/*M!100005 CREATE ROLE ‘foo’ WITH ADMIN mariadb_dump_import_role */;
/*M!100005 GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION */;
GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION;
GRANT USAGE ON *.* TO ‘foo’;

MariaDB_10.2 > select @@version;

+———————+

| @@version |

+———————+

| 10.2.44-MariaDB-log |

+———————+

1 row in set (0.03 sec)

MariaDB_10.2 > create role foo;

Query OK, 0 rows affected (0.03 sec)

MariaDB_10.2 > show grants for foo;

+—————————–+

| Grants for foo |

+—————————–+

| GRANT USAGE ON *.* TO ‘foo’ |

+—————————–+

1 row in set (0.02 sec)

$ mysqldump –system=users | grep -i foo

/*!80001 CREATE ROLE ‘foo’ */;

/*M!100005 CREATE ROLE ‘foo’ WITH ADMIN mariadb_dump_import_role */;

/*M!100005 GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION */;

GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION;

GRANT USAGE ON *.* TO ‘foo’;

Thanks for your suggestion, anyway. Best,

Fernando.

MySQL 5.7
End of Life

Compare Percona to Leading Database Solutions

Software
Downloads

Product
Documentation

Resource Hub

Financial Services

Driving Database Success

Percona Blog

Percona Community Hub

Percona Events Hub

About Percona

Percona in the News

Our Customers

Our Partners

Careers

Contact Us

Migrating From MariaDB to Percona Server for MySQL 8: The Security Witchcraft

Conclusion

Related

Related Blog Articles

RECOMMENDED ARTICLES

MySQL 8.4 First Peek

LDAP Authentication in PgBouncer Through PAM

Trying out the PostgreSQL pg_tde Tech Preview Release

MOST POPULAR ARTICLES

Auditing login attempts in MySQL

Deploy Django on Kubernetes With Percona Operator for PostgreSQL

MySQL “Got an error reading communication packet”

MySQL 5.7 End of Life

Compare Percona to Leading Database Solutions

Software Downloads

Product Documentation

Resource Hub

Financial Services

Driving Database Success

Percona Blog

Percona Community Hub

Percona Events Hub

About Percona

Percona in the News

Our Customers

Our Partners

Careers

Contact Us

Migrating From MariaDB to Percona Server for MySQL 8: The Security Witchcraft

Conclusion

Related

Share This Post!

Want to get weekly updates listing the latest blog posts?

Related Blog Articles

RECOMMENDED ARTICLES

MySQL 8.4 First Peek

LDAP Authentication in PgBouncer Through PAM

Trying out the PostgreSQL pg_tde Tech Preview Release

MOST POPULAR ARTICLES

Auditing login attempts in MySQL

Deploy Django on Kubernetes With Percona Operator for PostgreSQL

MySQL “Got an error reading communication packet”

MySQL 5.7
End of Life

Software
Downloads

Product
Documentation