Cloning MySQL Users With Common_schema
Today I needed to clone a MySQL user on one of my databases. More precisely, I needed to clone a MySQL grantee in order to allow an existing user to access the database from a different IP address. The process was very easy with the
duplicate_grantee() procedure in common_schema. (As an aside: When I first saw this procedure in the docs I thought it was meant to find grantees that are duplicates in order to consolidate them, but it’s actually meant to copy an existing grantee and its privileges. I guess the name reminded me of pt-duplicate-key-checker. In this case “duplicate” is a verb, not an adjective. Now that I know what it does I find it quite useful).
Here’s the command I ran:
Oops, what’s up with those warnings?
1 2 3 4 5 6 7 8 9 10 11 12
Ah yes, this DB is running with
group_concat_max_len set to the default of 1024 (I’ve never understood why that default is so low). This was a good reminder to increase
group_concat_max_len in /etc/my.cnf on this host, but for now I can just increase it dynamically and re-run the proc:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Out of curiousity I did a quick search for other tools to do this. I found a somewhat similar procedure in securich called
clone_user(), but it seems targetted towards creating an entirely new user, not just a new grantee. So it was not ideal for my use case today, but I may find it useful in the future.
From the docs:
If you have a particular user in a team already set up and you just want to create other users likewise, why not just clone them? The new user will of course have a different password which is supplied to the creator upon creation.
From the tutorial:
1 2 3 4 5 6 7 8 9
mysqluserclone utility also provides similar functionality to
clone_user() in securich.