1

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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
7
  • 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
    Nov 13, 2019 at 20:30
  • 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.
    – Mark S
    Nov 13, 2019 at 20:33
  • 1
    I told you above how to overcome the CREATE thing- "temporarily disabling NO_AUTO_CREATE_USER sql mode".
    – jynus
    Nov 13, 2019 at 21:11
  • 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.
    – Mark S
    Nov 13, 2019 at 21:43
  • But the passwords should be generated by pt-show-grants, is it not the case? Did it work in the end?
    – jynus
    Nov 14, 2019 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.