MySQLUserclone or just Copy MySQL Users

I recently ran across a MySQL Forum post that was looking for how to migrate users onto a new system.

While this could be done a number of ways, I figured it gave me a chance to play and demo the mysqluserclone tool.

So I have two databases and only one wiki user:


root@localhost [(none)]> select VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.10-log |
+------------+
root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost                                                                                     |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480'                            |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+

 select VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.30-log |
+------------+
show grants for wikiuser@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'wikiuser' on host 'localhost'


So now use the mysqluserclone tool


Usage: mysqluserclone --source=user:pass@host:port:socket --destination=user:pass@host:port:socket joe@localhost sam:secret1@localhost

# mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning wikiuser@localhost to user wikiuser@localhost
# ...done.

root@localhost [(none)]>  select VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.30-log |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost                                                                                     |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost'                                                                      |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)



Notice that it is missing a password because I did not set a password for the user. Opps that is not good enough.

Granted it works great if you want to set up new user accounts with new passwords. I could of passed the new password as part of the clone command


mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser:<PASSWORD>@localhost


But what if I do not know the passwords? I want to ensure all the clients have all their same passwords.

I could also do the process below.

• Gather grants from DB 1 
• Edit the file for grants I want to move
• Edit the user file to add comments to make safe sql
• Load the file into DB 2

select CONCAT('SHOW GRANTS FOR `',USER,'`@',HOST,';') as showgrants FROM mysql.user INTO OUTFILE '/tmp/showgrants.sql';

vi /tmp/showgrants.sql

mysql < /tmp/showgrants.sql  > /tmp/user_grants.sql

vi /tmp/user_grants.sql
:%s/Grants for/#Grants for/g
:%s/\n/;\r/g
mysql --port=3307  -u root -p  < /tmp/user_grants.sql 
> show grants for wikiuser@localhost;
+----------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost                                                                            |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480'                   |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+----------------------------------------------------------------------------------------------------------+


Now I have the same User , Host and password.