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 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.

MySQL Workbench Utilities – Administer MySQL with Python Scripts

Over the next few months, I am going to be writing about the MySQL Utilities, and I will be posting links to each individual blog on this page.

If you haven’t heard of the MySQL Utilities (from the introduction to MySQL Utilities page):

“MySQL Utilities is a package of utilities that are used for maintenance and administration of MySQL servers. These utilities encapsulate a set of primitive commands, and bundles them so they can be used to perform macro operations with a single command. MySQL Utilities may be installed via MySQL Workbench, or as a standalone package. 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.

The MySQL Utilities are maintained by Chuck Bell. You may find more information about MySQL Utilities on his web site..

To start the MySQL Utilities, from within MySQL Workbench, simply click on the “MySQL Utilities” icon located in the top right area of the home window.

Or, from the MySQL Workbench Plugins menu, select “Start Shell for MySQL Utilities”.

When you open MySQL Utilities, you should be taken to a terminal window, with a list of all of the utilities that are available.

As long as you have your $PATH set correctly, you can just run the scripts from any terminal window and in cron jobs.

Here is a list of the available utilities, with a link to the manual page and a link to my blog about that page (if I have written a post about that utility).

Blog Post Link Script Name & Man Page Description
MySQL Utilities Overview Brief overview of command-line utilities
mut MySQL Utilities Testing
mysqldbcompare Compare Two Databases and Identify Differences
mysqldbcopy Copy Database Objects Between Servers
mysqldbexport Export Object Definitions or Data from a Database
mysqldbimport Import Object Definitions or Data into a Database
mysqldiff Identify Differences Among Database Objects
mysqldiskusage Show Database Disk Usage
Blog mysqlfailover Automatic replication health monitoring and failover
mysqlindexcheck Identify Potentially Redundant Table Indexes
mysqlmetagrep Search Database Object Definitions
mysqlprocgrep Search Server Process Lists
mysqlreplicate Set Up and Start Replication Between Two Servers
mysqlrpladmin Administration utility for MySQL replication
mysqlrplcheck Check Replication Prerequisites
mysqlrplshow Show Slaves for Master Server
mysqlserverclone Clone Existing Server to Create New Server
mysqlserverinfo Display Common Diagnostic Information from a Server
Blog mysqluserclone Clone Existing User to Create New User

 


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.
Follow

Get every new post delivered to your Inbox.

Join 34 other followers