Flite Careers

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:

1
2

mysql> call common_schema.duplicate_grantee('scott@1.2.3.4', 'scott@5.6.7.8');
Query OK, 0 rows affected, 6 warnings (0.27 sec)

Oops, what’s up with those warnings?

1
2
3
4
5
6
7
8
9
10
11
12

mysql> show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 44 was cut by GROUP_CONCAT()  |
| Warning | 1260 | Row 73 was cut by GROUP_CONCAT()  |
| Warning | 1260 | Row 88 was cut by GROUP_CONCAT()  |
| Warning | 1260 | Row 96 was cut by GROUP_CONCAT()  |
| Warning | 1260 | Row 189 was cut by GROUP_CONCAT() |
| Warning | 1260 | Row 197 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
6 rows in set (0.00 sec)

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

mysql> select @@group_concat_max_len;
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                   1024 |
+------------------------+
1 row in set (0.00 sec)

mysql> set global group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> set session group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> call common_schema.duplicate_grantee('scott@1.2.3.4', 'scott@5.6.7.8');
Query OK, 0 rows affected (0.12 sec)

Success!

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:

  • clone_user(‘sourceusernanme’,’sourcehostname’,’destusername’,’desthostname’,’destemailaddress’);

    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

mysql> call clone_user('paul' , '10.0.0.2' , 'judas' , '10.0.0.2' , 'judas@domain.com');
+----------------------------------------------------------------------------------------------+
| USER_PASSWORD                                                                                |
+----------------------------------------------------------------------------------------------+
| Password for user -- judas -- contactable at -- judas@domain.com -- is -- eb5186cfa4b1c21 -- | 
+----------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

Query OK, 1 row affected, 3 warnings (0.14 sec)

The mysqluserclone utility also provides similar functionality to clone_user() in securich.

MySQL, common_schema
Comments
Back to Top

Posted by Ike Walker

Comments