migrate users from mariadb10.1 to mysql5.7

Question

My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7).

Dumping / restoring our application schemas looks straightforward. However I'm trying to determine how I can migrate my user accounts given the following:

• mysql.user table structure differs between the two; I can't dump and import mysql.user
• pt-show-grants, for some users, does not output statements to create the user. For instance, for some users, pt-show-grants only outputs something like GRANT INSERT, SELECT, UPDATE ON `schema`.`table` TO 'username'@'x', which fails because the user 'username'@'x' doesn't exist yet on the MySQL5.7 instance.
• mariadb10.1 does not have a 'show create user' or equivalent statement.

Has anyone made this kind of move before and encountered this issue? I am hoping to avoid a wild-goose-chase for dozens of passwords in order to recreate the users manually; some of these users predate me and I have no record of the assigned passwords.

Answer 1

Use pt-show-grants utility to generate a "logical dump" of your accounts and grants. You can do the same thing manually if you just run show grants for for each user, but this percona-toolkit utility will make it even easier.

Unless you have special configuration or authentication plugins, this should mostly work (unlike trying to dump and restore all user and priv tables physically). However, in some cases (in compatible permissions, roles, plugins), you may want to review after sending the sql for errors or missed grants. Also check for configurations that may make the loading failing, like temporarily disabling NO_AUTO_USER_CREATE and the default authentication plugin (native, not sha...).

I recently ported MariaDB 10.1 grants to MySQL 8 and it required some iterations, such as different names for the unix socket authentication plugin, the default authentication plugin (vs native) and the usage of VIA vs WITH. Mostly due to the default configurations, something you will have to work in anyway for the migration.

• Mark S: Thanks for your reply jynus - In point two I mentioned pt-show-grants is failing for some users (on import) because the user doesn't exist in the target database, and pt-show-grants doesn't generate a create user statement (or a grant ... identified by password statement, which would create the user). Did you find any way to retrieve the password for those users?
• Mark S: It looks like the password still exists in the mysql.user table in the same format as pt-show-grants has been emitting them. So I can parse the output of pt-show-grants and update the statements for the users that are failing.
• jynus: I told you above how to overcome the CREATE thing- "temporarily disabling NO_AUTO_CREATE_USER sql mode".
• Mark S: If I understand correctly, disabling NO_AUTO_USER_CREATE would cause a statement like GRANT INSERT, SELECT, UPDATE ON `schema`.`table` TO 'username'@'x' to succeed, and the resulting user would have no password. The fact that there's no password is the issue in that case.
• jynus: But the passwords should be generated by pt-show-grants, is it not the case? Did it work in the end?