MySQL Workbench Utilities – Clone MySQL users with mysqluserclone

This post is one in a series that I will be doing on MySQL Workbench Utilities – Administer MySQL with Python Scripts.

MySQL Utilities are a part MySQL Workbench. The utilities are written in Python, available under the GPLv2 license, and are extendable using the supplied library. They are designed to work with Python 2.x greater than 2.6. If you don’t have Workbench, you may download the MySQL Utility scripts from You will also need to install Python and to make sure that your execution $PATH’s are set correctly.


I recently created a new MySQL replication slave instance on a new server, and I needed a way to copy a few of the users from the master database over to the slave database. With the mysqluserclone script, it was fairly easy. The user that I wanted to copy from the master to the slave was named “WebUser”.

Normally, to duplicate a user, I start by taking a look at the privileges that this user has by issuing a “SHOW GRANTS” statement, like this:

mysql> SHOW GRANTS FOR WebUser;
| Grants for WebUser@%                                                                                                                               |
1 row in set (0.04 sec)

I would then go to the new instance, copy the above SQL statement, and issue the CREATE USER command using the HASH value of the password, and then issue the same “GRANT SELECT,…” statement – but without the IDENTIFIED BY PASSWORD part of the command.

mysql> CREATE USER 'WebUser'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS for WebUser;
| Grants for WebUser@%                                                            |
1 row in set (0.00 sec)


This doesn’t take that much time, but what if you wanted to create the same user on several remote machines? You would have to connect to each machine, login to mysql, and then issue the commands. With mysqluserclone, the process is much easier and faster.

From the mysqluserclone man page: “This utility [mysqluserclone] uses an existing MySQL user account on one server as a template, and clones it to create one or more new user accounts with the same privileges as the original user. The new users can be created on the original server or a different server.”

As the man page states, you may clone an existing user on the local machine, or on any remote machine. In my case, I wanted to clone a user to a remote machine. You need to make sure that the MySQL user on each machine that you are using to perform the user creation has the proper MySQL permissions on that machine. I have created a user named “utility” on all of my servers, which I use instead of the root user for creating users and executing scripts.

I needed to clone the user named “WebUser” from my master server ( to my new replication slave server ( I issued the following command, and this is what happened:

$ mysqluserclone --source=utility:tonyd765@ --destination=utility:tonyd959@ \
  WebUser@localhost WebUser:secret1@localhost
# Source on ... connected.
# Destination on ... connected.
ERROR: User WebUser:secret1@localhost already exists. Use --force to drop and recreate user.

At first, it appeared that somehow during the creation of the new replication slave, I had already created the user named WebUser, or that user already existed. I then realized that I had put in the wrong IP address of my new server. In this situation, it was nice that mysqluserclone checked to make sure that the user did not exist before attempting to clone that user. After getting the correct IP address (, I decided that I wanted to clone the original WebUser (from the master server) to two new users on the new slave server – named WebUser1 and WebUser2.

$ mysqluserclone --source=utility:tonyd32s@ --destination=utility:tonyd32s@ \
 WebUser@localhost WebUser1:secret1@localhost WebUser2:secret2@localhost
# Source on ... connected.
# Destination on ... connected.
# Cloning 2 users...
# Cloning WebUser@localhost to user WebUser1:secret1@localhost 
# Cloning WebUser@localhost to user WebUser2:secret2@localhost 
# ...done.

In the above example, the first user “WebUser@localhost” was the user to be cloned, while WebUser1 and WebUser2 are the names of the new users on the remote machine.

I saved the syntax in a shell script, so that the next time I need to clone a user (or users), I can just quickly edit the script and execute it. This makes it a lot easier than having to do everything manually. There are a few more options that you may use with mysqluserclone, and you should reference the official mysqluserclone man page for more information.

Option Description
–help Display a help message and exit.
–destination= Connection information for the destination server in [:]@[:][:] format.
–dump, -d Display the GRANT statements to create the account rather than executing them. In this case, the utility does not connect to the destination server and no –destination option is needed.
–format=, -f Specify the user display format. Permitted format values are grid, csv, tab, and vertical. The default is grid. This option is valid only if –list is given.
–force Drop the new user account if it exists before creating the new account. Without this option, it is an error to try to create an account that already exists.
–include-global-privileges Include privileges that match base_user@% as well as base_user@host.
–list List all users on the source server. With this option, a destination server need not be specified.
–quiet, -q Turn off all messages for quiet execution.
–source= Connection information for the source server in [:]@[:][:] format.
–verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.
–version Display version information and exit.



Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots


Visit for more information.

3 Responses to MySQL Workbench Utilities – Clone MySQL users with mysqluserclone

  1. Claudeir says:

    Hello friend , very, very good his post, but how to migrate all users at once? assuming you have 500 users or more(for example)? would be very difficult to do this one by one , I have a job here in my work and I’m looking for the best way to migrate all users and their respective permissions of a server mysql 5.1 to a new server Mysql 5.7.
    From already thank you very much for your attention and dedication to their posts .

    • Tony Darnell says:

      You can use mysqldump to export the mysql.user table, but the columns from mysql.user in 5.1 won’t match the columns in 5.7. So, you could simply do a select on the user name and password, and then insert that data into 5.7. To dump the entire table – you may use: /usr/local/mysql/bin/mysqldump -uroot -pYobuddy! –skip-extended-insert –no-create-info mysql user > MySQL-Users.sql

      In 5.7, the password column is called the authentication_string, so when you re-import the dump file, you will have to remember to change that column name.

      You will want to also export the grants for each user. To export the grants, you can run this from the command line:

      MYSQL_CONN=”-uroot -pPassword”
      mysql ${MYSQL_CONN} –skip-column-names -A -e”SELECT CONCAT(‘SHOW GRANTS FOR ”’,user,”’@”’,host,”’;’) FROM mysql.user WHERE user”” | mysql ${MYSQL_CONN} –skip-column-names -A | sed ‘s/$/;/g’ > MySQLUserGrants.sql

      Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them is removed in MySQL 5.7.5. So you will need to upgrade these older pre-4.1 passwords (if you have any) prior to dumping and reloading the mysql.user table. (From:

      Script provide by a post answer from Rolando Edwards: LinkedIn –

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: