MySQL Workbench Utilities – Clone MySQL users with mysqluserclone
August 19, 2012 3 Comments
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 launchpad.net. 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@% | +----------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%' IDENTIFIED BY PASSWORD '*xxxxxxxxxx' | +----------------------------------------------------------------------------------------------------------------------------------------------------+ 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) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS for WebUser; +----------------------------------------------------------------------------------+ | Grants for WebUser@% | +----------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLESPACE ON *.* TO 'WebUser'@'%' | +----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> |
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 (192.168.1.2) to my new replication slave server (192.168.1.5). I issued the following command, and this is what happened:
$ mysqluserclone --source=utility:tonyd765@192.168.1.2:3306 --destination=utility:tonyd959@192.168.1.5:3306 \ WebUser@localhost WebUser:secret1@localhost # Source on 192.168.1.2: ... connected. # Destination on 192.168.1.5: ... 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 (192.168.1.3), 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@192.168.1.2:3306 --destination=utility:tonyd32s@192.168.1.3:3306 \ WebUser@localhost WebUser1:secret1@localhost WebUser2:secret2@localhost # Source on 192.168.1.2: ... connected. # Destination on 192.168.1.3: ... 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. |
Source: http://dev.mysql.com/doc/workbench/en/mysqluserclone.html
 |
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] ScriptingMySQL.com and on LinkedIn. |
|
Tony is the author of Twenty Forty-Four: The League of Patriots
Visit http://2044thebook.com for more information. |
info [at] Scripting
Scripting MySQL 