MySQL Utilities Frequently Asked Questions

Momentum for MySQL Utilities continues to build.  I hosted a webinar recently about MySQL Utilities (available on-demand from the link below), which generated a lot of interest and some good questions.

http://event.on24.com/eventRegistration/EventLobbyServlet?target=lobby.jsp&eventid=448952&sessionid=1&key=7E741ED049DFBF49D10C90A2B62E410F&eventuserid=63530507

With so many questions and ideas coming in I decided to create a blog of FAQs. I plan to add these to the MySQL documentation as well.  Keep your ideas and questions coming!

I hope you find these questions enlightening. I have grouped them for easier reading. You can find the MySQL Utilities documentation using the link below. 

http://dev.mysql.com/doc/workbench/en/mysql-utilities.html

General

Are these utilities present in the community version of MySQL?

They are included in the community edition of the MySQL Workbench product, which can be downloaded from the following link.

http://www.mysql.com/downloads/workbench/

Should/can we run this on live data?

Yes. Naturally, you would want to test some operations before jumping directly into a production environment. For example, you may want to test any database migration, transformation, or similar massive change in a test environment.

Can we use the utilities in a production environment under the GPL license?

Yes. MySQL Utilities is part of MySQL Workbench so all such licenses apply accordingly.

Storage Engines

Can the utilities be used with MyISAM or CSV?

Yes. There are no storage engine specific limitations in using the utilities. There are some features written specifically for InnoDB so those may not apply but in general no utility is storage engine specific. For example, the mysqldiskusage utility shows exact sizes for MyISAM and InnoDB files but uses estimated sizes for any other storage engine based on number of rows and row size.

Platforms

Can I use MySQL Utilities on Linux?

Yes. MySQL Utilities runs on all platforms supported by MySQL Workbench.

Can the utilities be used on Windows?

Yes!

Do the utilities work both for window-based and linux-based servers?

Yes! They work for any server hosting MySQL.

Installation

Do we have to install the utilities with rpm or can we use the tar ball extract and run from there?

MySQL Utilities is installed as part of MySQL Workbench. You can download and install Workbench using several platform-specific installers.

You can also branch and download MySQL Utilities from Launchpad. You can also build and install it from the source code you’ve downloaded using typical Python install steps (python ./setup.py install).

https://launchpad.net/mysql-utilities

What's the link to download these utilities?

MySQL Utilities is part of MySQL Workbench. You can download MySQL Workbench from the following link.

http://www.mysql.com/downloads/workbench/

Locking

Do the utilities lock tables while running?

Yes, but only for situations that require locks. The mysqldbexport utility also allows you to specify what type of lock to use:

no-locks = do not use any table locks

lock-all = use table locks but no transaction and no consistent read

snaphot (default) = consistent read using a single transaction.

Are there any utilities that can show DB locks (like which query is blocking which one)?

No, not currently but that is an excellent suggestion!

mysqldbcompare

How fast is mysqldbcompare? Say a table with 10 million rows?

It is difficult to predict a precise estimate of run time based on number of rows. However, it is generally such that the more rows there are the longer the utility will run. The mysqldbcompare utility is used to produce a difference of two databases. It creates a difference between objects of the same name for either object definitions, data, or both. When comparing object definitions, the performance is very fast because there isn’t a lot of processing involved. When comparing data, the utility uses an algorithm to create checksums for each row in the table. During this phase, the tables are locked. Once that stage is done, the tables are unlocked and the algorithm begins to compact the checksums into chunks, which are later compared between the servers. If the checksums differ, the chunks are expanded and the differences calculated. Thus, for tables containing millions of rows the utility will take some time to complete. The best time to run this utility is during low usage periods such as times reserved for upgrades, backups, and similar operations.

How will running mysqldbcompare effect a production database?

If generating a difference for data, the utility will lock the tables long enough to calculate a checksum for each row. Depending on the number of rows this could be for a long time and in those cases you should run mysqldbcompare during low usage periods. The utility will use a consistent read to lock InnoDB tables but will issue table locks for non-InnoDB tables.

Will mysqldbcompare cause table locking on MyISAM table?

Yes. A table lock is issued during checksum creation.

What is the load going to be on the servers when mysqldbcompare runs?

The load on the server itself is minimal. There is moderate CPU usage during checksum creation but nothing that should cause a problem. The longest period of activity is when the table scans are executed for creating a checksum for each row.

mysqldbexport

Is mysqldbexport similar to mysqldump?

Yes, the mysqldbexport is designed to export data in a row-by-row or logical fashion. However, you can export data in CSV, TAB, Vertical formats as well as SQL statements using CREATE TABLE, INSERT, etc. making mysqldbexport more versatile than mysqldump. You would use mysqldbexport in situations where you need special machine or human readable output for operations like transforming the data or examining the structures in more detail – especially if you need a format other than SQL statements.

Replication Utilities

Are the high availability features available for version 5.5 or 5.6?

The general replication utilities such as mysqlrplcheck and mysqlreplicate will work with servers version 5.0 and later. The newest high availability feature, failover, in mysqlrpladmin and mysqlfailover work only for servers that support global transaction identifiers (GTIDs) which were added in version 5.6.5.

You can discover more about GTIDs from the following blog by Luis Soares.

http://d2-systems.blogspot.co.uk/2012/04/global-transaction-identifiers-are-in.html

Where do I get more info about mysqlrpladmin?

The online MySQL Workbench Manual has information about each utility. You can also use the --help option to show all options and their descriptions.

http://dev.mysql.com/doc/workbench/en/mysql-utilities.html

How can I use the utilities to test replication on a single host?

You can use mysqlserverclone to clone an existing, running instance of MySQL or clone from an installation (basedir), then mysqlreplicate to create the replication topology.

Is the replication failover feature only for version 5.6?

Yes. It requires support for global transaction identifiers, which were added in version 5.6.5. A developer milestone release of 5.6 is available for download.

http://dev.mysql.com/downloads/mysql/#downloads

(Select the Development Releases tab)

What features of mysqlrpladmin will work on version 5.5?

All of the features except slave election and failover.

Do you need to create a replication user on the slave site other than the master?

The mysqlreplicate utility provides an option to use a specific user on the master for replication or it will create a user by default. You can also request that a new user be created during the operation.

Compare and Synchronize Databases with MySQL Utilities

The mysqldiff and mysqldbcompare utilities were designed to produce a difference report for objects and in the case of mysqldbcompare the data. Thus, you can compare two databases and produce a report of the differences in both object definitions and data rows. 

While that may be very useful, would it not be much more useful to have the ability to produce SQL commands to transform databases? Wait no longer! The latest release of MySQL Utilities has added the ability to generate SQL transformation statements by both the mysqldiff and mysqldbcompare utilities. 

To generate SQL transformations in either utility, simply use the --sql option to tell the utility to produce the statements.

Object Transformations with mysqldiff

If you would like to compare the schema of two databases (the objects and their definitions), mysqldiff can do that for you and produce a difference report in a number of formats including CSV, TAB, GRID, and Vertical (like the mysql client’s \G option).

However, its greatest feature is the ability to generate transformation statements to alter the objects so that they conform. Best of all, mysqldiff works on all object types including the ability to recognize renames so you can get a true transformation path for all objects. For even greater flexibility, you can generate the difference in both directions. This means you can generate transformations for db1-to-db2 as well as db2-to-db1 in the same pass. Cool.

The following shows an example of running mysqldiff on two servers where some of the objects have diverged. It also shows how you can generate the reverse transformation statements.


$ mysqldiff --server1=root@localhost --server2=root@otherhost \
--changes-for=server1 --show-reverse util_test:util_test \
--force --difftype=SQL
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# WARNING: Objects in server1.util_test but not in server2.util_test:
# EVENT: e1
# Comparing util_test to util_test [PASS]
# Comparing util_test.f1 to util_test.f1 [PASS]
# Comparing util_test.p1 to util_test.p1 [PASS]
# Comparing util_test.t1 to util_test.t1 [PASS]
# Comparing util_test.t2 to util_test.t2 [PASS]
# Comparing util_test.t3 to util_test.t3 [FAIL]
# Transformation for --changes-for=server1:
#
ALTER TABLE util_test.t3
DROP COLUMN b,
ADD COLUMN d char(30) NULL AFTER a
ENGINE=MyISAM;
#
# Transformation for reverse changes (--changes-for=server2):
#
# ALTER TABLE util_test.t3
# DROP COLUMN d,
# ADD COLUMN b char(30) NULL AFTER a,
# ENGINE=InnoDB;
#
# Comparing util_test.trg to util_test.trg [FAIL]
# Transformation for --changes-for=server1:
#
DROP TRIGGER IF EXISTS `util_test`.`trg`;
CREATE DEFINER=root@localhost TRIGGER util_test.trg BEFORE UPDATE ON util_test.t1
FOR EACH ROW INSERT INTO util_test.t1 VALUES('Wax on, wax off');
#
# Transformation for reverse changes (--changes-for=server2):
#
# DROP TRIGGER IF EXISTS `util_test`.`trg`;
# CREATE DEFINER=root@localhost TRIGGER util_test.trg AFTER INSERT ON util_test.t1
# FOR EACH ROW INSERT INTO util_test.t2 VALUES('Test objects count');
#
# Comparing util_test.v1 to util_test.v1 [FAIL]
# Transformation for --changes-for=server1:
#
ALTER VIEW util_test.v1 AS
select `util_test`.`t2`.`a` AS `a` from `util_test`.`t2`;
#
# Transformation for reverse changes (--changes-for=server2):
#
# ALTER VIEW util_test.v1 AS
# select `util_test`.`t1`.`a` AS `a` from `util_test`.`t1`;
#
Compare failed. One or more differences found.

Generating Data Transformation with mysqldbcompare

The mysqldbcompare utility provides all of the object difference functionality included in mysqldiff along with the ability to generate transformation SQL statements for data. This means you can make sure your test or development databases are similar to your production databases or perhaps even your offline, read only databases match your online databases. Like mysqldiff, you can also get the reverse transformations at the same time. Very cool, eh?

The following shows an example of running mysqldbcompare to generate differences in data.

$ mysqldbcompare --server1=root@localhost --server2=root@otherhost \

inventory:inventory -a --difftype=sql --changes-for=server1 \

--show-reverse

# server1 on localhost: ... connected.

# server2 on localhost: ... connected.

# Checking databases inventory on server1 and inventory on server2

#

# WARNING: Objects in server1.inventory but not in server2.inventory:

# VIEW: finishing_up

# VIEW: cleaning

#

[...]

# TABLE supplier pass FAIL FAIL

#

# Row counts are not the same among inventory.supplier and inventory.supplier.

#

# Transformation for --changes-for=server1:

#

# Data differences found among rows:

UPDATE inventory.supplier SET name = 'Wesayso Corporation' WHERE code = '2';

INSERT INTO inventory.supplier (code, name) VALUES('3', 'Never Enough Inc.');

#

# Transformation for reverse changes (--changes-for=server2):

#

# # Data differences found among rows:

# UPDATE inventory.supplier SET name = 'Never Enough Inc.' WHERE code = '2';

# DELETE FROM inventory.supplier WHERE code = '3';

#

# Database consistency check failed.

#

# ...done

MySQL Utilities and Global Transaction Identifiers

The new MySQL 5.6 Development Milestone Release (DMR) includes many new enhancements. One of the most impressive is the use of Global Transaction Identifiers (GTIDs) for replication. With GTIDs enabled, administrators no longer need to keep track of binary log files and positions. In a nutshell, GTIDs simplify the setup and maintenance of replication.

MySQL Utilities has taken this a step further by providing two new utilities that automate two of the most complex replication administration tasks - switchover and failover. Switchover is changing the role of an active, healthy master to one of its slaves whereas failover is the act of promoting a candidate slave to become the new master. Clearly, switchover is an elective operation and failover is performed when there are issues with the master.

The GTID utilities are included in release-1.0.5 of MySQL Utilities. They are included as a plugin for MySQL Workbench or via source download from launchpad (see below). The new GTID utilities are included in Workbench version 5.2.39.

Automatic Failover Utility
The most impressive utility is mysqlfailover, It is an interactive tool used to report replication health, report GTIDs in use, and perform automatic failover. Yes, that’s right - you can setup mysqlfailover to automatically failover to one of a specific set of slaves whenever the master goes offline. It is designed to work with the MySQL 5.6.5 and later versions of the server.



As you can see in the screenshot, a list is presented including the host, port, role, state, and replication health for each server in the topology. The utility connects to a single master and its slaves. When used in multiple tier environments, users can run an instance for each master. The utility provides the ability to run a failover check and report health at specific intervals in seconds from five seconds and up

To start the utility, users can specify a list of slaves or provide a default user and password to be used in discovering the slaves connected to the master. Discovery of slaves requires the slaves to report the correct host and port when connecting to the master. Along with the list of slaves, the user can specify a list of servers to be used as candidates for selecting a new master when a failover event is detected.

Finally, the user can control how failover occurs with the failover mode. The auto mode tells the utility to failover to the list of candidates first and if none are viable, search the list of slaves for a candidate. The elect mode limits election to the candidate slave list and if none are viable, failover does not occur. The fail mode tells the utility to not perform failover and instead stop execution.

Along with these options are four extension points permitting users to interact with the utility during failover. These extension points permit users to specify a script to run at each of the following events.

• exec-fail-check - execute a script to determine if failover is needed. This replaces the default downed master detection and allows users to perform application-specific detection for failover.
• exec-before - execute a script before failover is performed. This can be used to tell the application to cease write attempts while a new master is setup.
• exec-after - execute a script immediately after failover to a new master. This permits users to inform the application that the new master is ready.
• exec-post-fail - execute a script after failover is complete and all slaves have been attached to the new master. This can be used to inform applications that use read-level scale out that it is safe to resume reads from the slaves.

The combination of options to control failover, the option to perform automatic failover, and the ability to inform applications of the failover event are powerful features that enable unattended automatic failover for critical replication-based applications.

Replication Administration Utility
The other utility, mysqlrpladmin, is used to perform switchover and failover operations and more on-demand permitting administrators to execute these tasks with a single command. You can use the command to perform one of the following commands.

• elect - This command is available to only those servers supporting global transaction identifiers (GTIDs), perform best slave election and report best slave to use in the event a switchover or failover is required. Best slave election is simply the first slave to meet the prerequisites.
• failover - This command is available to only those servers supporting GTIDs. Conduct failover to the best slave. The command will test each candidate slave listed for the prerequisites. Once a candidate slave is elected, it is made a slave of each of the other slaves thereby collecting any transactions executed on other slaves but not the candidate. In this way, the candidate becomes the most up-to-date slave.
• gtid - This command is available to only those servers supporting GTIDs. It displays the contents of the GTID variables used to report GTIDs in replication. The command also displays universally unique identifiers (UUIDs) for all servers.
• health - Display the replication health of the topology.
• reset - Execute the STOP SLAVE and RESET SLAVE commands on all slaves.
• start - Execute the START SLAVE command on all slaves.
• stop - Execute the STOP SLAVE command on all slaves.
• switchover - Perform slave promotion to a specified candidate slave as designated by the --new-master option. This command is available for both gtid-enabled servers and non-gtid-enabled scenarios.

These two utilities raise the bar for replication ease of use making the administrator’s job easier.

How Can I Get MySQL Utilities?
You can check out these new utilities and the entire suite of utilities by either downloading the source code from launchpad or by downloading and installing MySQL Workbench.

You can download MySQL Workbench from:

http://www.mysql.com/downloads/workbench/

You can also download the latest development source code tree for the MySQL Workbench Utilities from:

https://launchpad.net/mysql-utilities

To learn more about all of the great new replication features in MySQL 5.6, check out the developer zone article at:

http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html

MySQL Utilities Release 1.0.3

The MySQL Utilities project continues to evolve with key new features for
replication and export. The latest release, 1.0.3, is no exception.

MySQL Utilities is included in the MySQL Workbench product which can be
downloaded from HTTP://dev.mysql.com/downloads/workbench/5.2.html

If you want the latest developments for MySQL Utilities, you can create a
bazaar branch using the following command:

bzr branch lp:~mysql/mysql-utilities/trunk

New Utility - mysqlrplshow

You can now view a list of the slaves attached to your master with
mysqlrplshow. The utility displays a graph of the master and its slaves y
default but you can also get a list of the slaves in GRID, CSV, TAB, or
VERTICAL format as follows.

• GRID - Displays output formatted like that of the mysql monitor in a grid or table layout. 
• CSV - Displays the output in a comma-separated list. 
• TAB - Displays the output in a tab-separated list.
• VERTICAL - Displays the output in a single column similar to the '\G' option for the mysql monitor commands.

You can use the GRID format to output the data and store it away for a later
run then compare or diff against the recent output to see what has changed. In
this case, what new slaves have been changed, added or removed from your
topology.

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3310 - (SLAVE)
   |
   +--- localhost:3312 - (SLAVE + MASTER)
       |
       +--- localhost:3313 - (SLAVE)

If you want to see your replication topology, you can use the --recurse option
to instruct mysqlrplshow to traverse each slave found to locate downstream
slaves. Circular topologies are detected and displayed using a special
notation. You can even limit the depth of the recursive search if you want to
see only a portion of your topology.

The utility currently works by reading slaves with the SHOW SLAVE HOSTS
command. For most versions of MySQL, this requires the use of --report-host and
--report-port options when setting up your slaves. For example, version 5.5.3
and later always show the slaves regardless if --report-host is specified.
Versions older than 5.5.3 will not show the slaves unless --report-host is
specified.

You should specify the --report-host option when starting your slaves but take
care to ensure the correct host or IP is specified. If the slave is using a NAT
derived IP, be sure to use the IP address that can reach the slave from the
master.

You do not normally need to specify --report-port unless the slave is using a
custom port (something other than the default of 3306).

Improvements

There have been a number of minor enhancements and there are two major feature
additions that will improve usage in many scenarios.

Enhanced binlog control for mysqlreplicate - you can now start replication in
one of four ways:

1. Start from the first event recorded in the binary log on the master. This is typically used to setup a new master and slave replication topology.
2. Start from the first event in a specific binary log file This is used to restart replication from a specific point or to start replication after a slave has had data loaded (for example via a restore or file copy from the master).
3. Start from a specific event (position) in a specific binary log file. This is a common operation for point-in-time recovery.
4. Start from the current position of the master. This is typically used to start a slave following a recovery of the slave, fail-back to a recovered master, or restarting replication after an error event has been resolved.

We added --all option to mysqldbcopy, mysqldbexport. These utilities allow you
to include all of the databases on your server in the operation except the
system databases (mysql, information_schema, and performance_schema).

Try it out and let us know what you think!

Comparing Databases with mysqldbcompare

If you have two or more database servers containing the same data, how do you know if the objects are identical. Furthermore, how can you be sure the data is the same on all of the servers? What is needed is a way to determine if the databases are in synch - all objects are present, the object definitions are the same, and the tables contain the same data. Synchronizing data can become a nightmare without the proper tools to quickly identify differences among objects and data in two databases. Perhaps a worst case (and more daunting) is trying find data that you suspect may be different but you don’t have any way of finding out.

This is where the new 'mysqldbcompare' utility comes in handy. The mysqldbcompare utility uses the mysqldiff functionality (mysqldiff allows you to find the differences in object definitions for two objects or a list of objects in two databases) and permits you to compare the object definitions and the data among two databases. Not only will it find the differences among database objects and their definitions, it will also find differences in the data!

The databases can reside on the same server or different servers. The utility performs a consistency check to ensure two databases are the same defined as having the same list of objects, identical object definitions (including object names), and for tables the same row counts and the same data.

Some scenarios where mysqldbcompare can be employed include:

• checking master and slave for consistency
• checking production and development databases for consistency
• generating a difference report for expected differences among new and old data
• comparing backups for differences

Running the Utility

Let us take a look at the utility in action. Below are two examples of the utility comparing what should be the same database on two servers. I am using a simple detail shop inventory database used to manage supplies. It consists of two tables (supplier, supplies) and three views (cleaning, finishing_up, and tools).

In the first example, we see an example where the databases are consistent. When you examine the output, you will see each object is inspected in three passes. First, the object definitions are compared. Any discrepancies would be displayed as a difference in their CREATE statements. If the object is a table, a row count test is performed followed by a comparison of the data. Surely, if the row count test fails we know the data check will fail.

Note: This is the same output (and indeed the same code) that is used for mysqldiff. The mysqldbcompare utility has all of the same features with respect to difference type presented (unified, differ, and context) which you can select with the same option named ‘--difftype’.

mysqldbcompare --server1=root@localhost --server2=root@backup_host:3310 inventory:inventory
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases inventory on server1 and inventory on server2


Defn Row Data
Type Object Name Diff Count Check
---------------------------------------------------------------------------
TABLE supplier pass pass pass
TABLE supplies pass pass pass
VIEW cleaning pass - -
VIEW finishing_up pass - -
VIEW tools pass - -


Databases are consistent.


# ...done

Normally, the mysqldbcompare utility will stop on the first failed test. This default means you can run the utility as a safeguard on data that you expect to be consistent. However, if you suspect or know there will be differences in the database objects or data and want to run all of the checks, you can use the ‘--run-all-tests’ option. This option will run the tests on all objects even if some tests fail. Note that this does not include system or access errors such as a down server or incorrect login - those errors will cause the utility to fail with an appropriate error message.

In the second example, we expect the databases to be different and we want to know which data is different. As you can see, the utility found differences in the object definitions as well as differences in the data. Both are reported.

mysqldbcompare --server1=root@localhost --server2=root@backup_host:3310 inventory:inventory --run-all-tests
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases inventory on server1 and inventory on server2


WARNING: Objects in server1:inventory but not in server2:inventory:
VIEW: finishing_up
VIEW: cleaning


Defn Row Data
Type Object Name Diff Count Check
---------------------------------------------------------------------------
TABLE supplier pass FAIL FAIL


Row counts are not the same among inventory.supplier and inventory.supplier.


Data differences found among rows:
--- inventory.supplier
+++ inventory.supplier
@@ -1,2 +1,2 @@
code,name
-2,Never Enough Inc.
+2,Wesayso Corporation


Rows in inventory.supplier not in inventory.supplier
code,name
3,Never Enough Inc.


TABLE supplies pass FAIL FAIL


Row counts are not the same among inventory.supplies and inventory.supplies.


Data differences found among rows:
--- inventory.supplies
+++ inventory.supplies
@@ -1,4 +1,4 @@
stock_number,description,qty,cost,type,notes,supplier
-11040,Leather care,1,9.99,other,,1
-11186,Plastic polish,1,9.99,polishing,,1
-11146,Speed shine,1,9.99,repair,,1
+11040,Leather care,1,10.00,other,,1
+11186,Plastic polish,1,10.00,polishing,,1
+11146,Speed shine,1,10.00,repair,,1


Rows in inventory.supplies not in inventory.supplies
stock_number,description,qty,cost,type,notes,supplier
11104,Interior cleaner,1,9.99,cleaning,,1
11056,Microfiber and foam pad cleaner,1,9.99,cleaning,,1
11136,Rubber cleaner,1,9.99,cleaning,,1
11173,Vinyl and rubber dressing,1,9.99,cleaning,,1
11106,Wheel cleaner,1,9.99,cleaning,,1
11270,Carpet cleaner,1,9.99,cleaning,,1


Rows in inventory.supplies not in inventory.supplies
stock_number,description,qty,cost,type,notes,supplier
11269,Microfiber spray on car wash towel,3,16.99,cleaning,,1
11116,Microfiber wax removal towel,3,16.99,waxing,,1
10665,Glass polish pad,3,10.00,polishing,,1


VIEW tools FAIL - -


--- inventory.tools
+++ inventory.tools
@@ -1,1 +1,1 @@
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory`.`tools` AS select `inventory`.`supplies`.`stock_number` AS `stock_number`,`inventory`.`supplies`.`description` AS `description`,`inventory`.`supplies`.`qty` AS `qty`,`inventory`.`supplies`.`cost` AS `cost`,`inventory`.`supplies`.`type` AS `type`,`inventory`.`supplies`.`notes` AS `notes`,`inventory`.`supplies`.`supplier` AS `supplier` from `inventory`.`supplies` where (`inventory`.`supplies`.`type` = 'tool')
+CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory`.`tools` AS select `inventory`.`supplies`.`stock_number` AS `stock_number`,`inventory`.`supplies`.`description` AS `description`,`inventory`.`supplies`.`qty` AS `qty`,`inventory`.`supplies`.`cost` AS `cost`,`inventory`.`supplies`.`type` AS `type`,`inventory`.`supplies`.`notes` AS `notes`,`inventory`.`supplies`.`supplier` AS `supplier` from `inventory`.`supplies` where (`inventory`.`supplies`.`type` in ('tool','other'))


Database consistency check failed.

Take a moment to read through the report above. At the top of the report (the first object tested), we see a critical error in the suppliers table. Here we can see that there are two different names for the same supplier_id. All relational database theory aside, that could spell trouble when it comes time to reorder supplies.

Notice the report for the supplies table. In this example, the utility identified three rows that were different among the two databases. It also identified rows that were missing from either table. Clearly, that could help you diagnose what went wrong where in your application (or your data entry).

Lastly, we see a possible issue with the tools view. Here the view definition differs slightly. Depending the use of the view this may be acceptable but it is nice to know nonetheless.

What Does This All Mean?

If data consistency is important to you or if you need a way to quickly determine the differences among data in two databases, the mysqldbcompare utility is a great addition to your toolset. But don’t take my word for it - try it out yourself.

Download and Try It Out!

The MySQL Utilities project is written in Python and is a component of the MySQL Workbench tool. You can download the latest release of the MySQL Workbench here:

http://dev.mysql.com/downloads/workbench/

There are some limitations in this first release. Currently, if the storage engines differ among the tables in the compare, the object definitions will show this difference – which is exactly what you would expect. However, the utility will stop and report the object definition test as a failure. You can still run the data consistency check by using the --force option which instructs the mysqldbcompare utility to run all tests unless they fail from an internal exception (for example, the table files are corrupt).

Got Some Ideas or Want to See New Features?

One really cool feature would be if the utility generate SQL statements for synchronizing the data and objects. Would this be something you would want to see incorporated?

If you’re intrigued by this new utility and in your course of use find new features or new uses that you would like to see incorporated in future revisions, please email me and let me know!

Related Material

Th latest MySQL Utilities development tree (including mysqldbcompare) can be found here:

https://launchpad.net/mysql-utilities

Wand Selection at Ollivander’s

Fans of J. K. Rowling’s Harry Potter books should plan a visit to Universal Studio’s Wizarding World of Harry Potter. There you will encounter scenes from the books and movies, a castle ride that may result in battle damage from a dragon (I got sprayed by its hot, fetid breath), butter beer by the barrel, a dragon ride not for the faint of heart, a tour of one of the most famous classrooms, and several of the wizardly shops open to muggles (they will even let you buy stuff).

Perhaps the most exciting opportunity for Harry Potter fans is the wand ceremony at Ollivander’s. There a lucky few can have a wand selected for them by Ollivander himself. Having witnessed the ceremony, I can say it was one of those times I wish I were a child again. The ceremony I saw involved two boys about the ages of 8 and 10. Ollivander had them come forward and go through a series of tests to select a wand. The looks on their faces when presented with wands chosen specifically for them was well worth the time spent waiting in line to see the ceremony. I can imagine that event will be a lasting memory for those boys and their parents.

If the lines are too long or you were not fortunate enough to be selected by Ollivander, you can still have your wand selected for you by inquiring among Ollivander’s helpful wizardly staff.

The process is very simple. Just select a date that is most important to you - one that holds special meaning. It is best if this date is tied directly to your core being (so sayeth Ollivander). There are thirteen wands that are available for selection and each is tuned to a specific date range. The following lists the wands and the dates that apply. As to what these wands look like, you’ll have to visit Ollivander’s to find out.

Wand	Date Range
Birch	December 24 - January 20
Rowan	January 21 - February 17
Ash	February 18 - March 17
Alder	March 18 - April 14
Willow	April 15 - May 12
Hawthorn	May 13 - June 9
Oak	June 10 - July 7
Holly	July 8 - August 4
Hazel	August 5 - September 1
Vine	September 2 - September 29
Ivy	September 30 - October 27
Reed	October 28 - November 24
Elder	November 25 - December 23

There are, of course, all of the major character wands available for you to choose or collect. All of the wands are made of resin with very nice detail.

If you cannot make the trip to Orlando, you were there but did not buy a wand, or just want to add to your collection, you can go to the URL below  and order one from one of Ollivander’s helpful house elves. These are the very same wands sold at the theme park and the gift shops at the Orlando airport but are contained in a more travel friendly box (it’s a bit bigger).

http://www.universalorlando.com/Merchandise/Shop/Ollivanders_Wand_Shop.html

MySQL Workbench Utilities

Introducing MySQL Workbench Utilities

One of the many new things that is being introduced during the Collaborate 2011 and 2011 MySQL Users’ Conference are some great new additions to some of the external tools for managing MySQL servers. One of those tools receiving updates is the MySQL Workbench.

One of the jewels in a long list of new features is the addition of new command-line utilities to help you administer your servers. The new feature is called MySQL Workbench Utilities. It is a package of easy-to-use utilities for maintenance and administration of MySQL servers. These utilities incapsulate a set of primitive commands bundling them so that you can perform macro operations with a single command. Some of the key features in MySQL Workbench Utilities include:

• Plugin for MySQL Workbench 5.2.31
• Available under the GPLv2 license
• Written in Python
• Easily to extend using the supplied library

How Does It Work?

There are two ways to access the utilities from within the MySQL Workbench.

You can click on the drop down arrow icon to the right of the MySQL Workbench main window. This will display a list of the plugins available. You can scroll through the screens and find the MySQL Utilities icon. The image below shows what the window looks like.

Once you hover on the MySQL Utilities icon, you will see an Action button appear. Click Action then Start Plugin to launch the MySQL Utilities shell in a new window. The image below shows what the window would look like.

You can launch any of the utilities listed by typing the name of the command. To find out what options are available, use the --help option.

Note: you can also add the MySQL Utilities icon to your home screen. Highlight Add to Home Screen and then choose a position from the menu.

You can also launch the MySQL Utilities command window by clicking on the Plugins menu item and selecting Start Shell for MySQL Utilities.

What Utilities are Available?

The MySQL Workbench and MySQL Workbench Utilities developers are adding new features and utilities with every release. If you don’t see a utility you want, check back with each release of Workbench or send the developers an email and let them know what you want!

You should try the --help option for each tool to see what options it supports. Some utilities have lots of options for controlling the operation (for example the mysqldbexport can produce one of several formats with object definitions, data, or both).

There are several options that are common to the utilities available (for instance --version, --help, --verbose) but the one that is most important is the server specification option used to connect to a specific server. This manifests as --server, --source, --destination, or --server1, etc. and all require the same format as follows:

--server=user_name:password@hostname:port_num:socket_file.

The parameter includes the user name followed by the user’s password (if available) separated by a colon (colon is omitted if no password) followed by the @ symbol and the host machine name. Optionally, you can provide the port number and the socket file each preceded by a colon. That sounds like a lot of crazy typing but it is really simple. The following are some examples using this format.

--server=root@localhost:3306
--server=joe:pass@192.168.1.101
--server=dolly:sassyshoes@localhost:3310:/tmp/mysql.sock

The following list describes the utilities available as well as some examples of use. For more details, please see the manuals for each utility.

mysqldbcopy - Permits a database administrator to copy a database from one server either to another server as the same name or a different name or to the same server as the same or as a different name.

Example 1: Copy a database named ‘util_test’ to a new name ‘util_test_copy’ on the same server.

mysqldbcopy util_test:util_test_copy
--source=root:pass@host1:3306
--destination=root:pass@host1:3306

Example 2: Copy a database named ‘util_test’ to another server.

mysqldbcopy util_test:util_test
--source=root:pass@host1:3306
--destination=root:pwd@host2:33010

mysqldbexport - Permits a database administrator to export the metadata (object definitions, hence definitions) or data or both from one or more databases. By default, the utility will export only definitions.

Example 1: Export the definitions of the database ‘dev’ from a MySQL server on localhost via port 3306 producing CREATE statements.

mysqldbexport --skip=GRANTS
--server=root:pass@localhost
--export=DEFINITIONS util_test

Example 2: Export the data of the database ‘util_test’ producing bulk insert statements.

mysqldbexport --export=DATA
--bulk-insert util_test
--server=root:pass@localhost

mysqldbimport - Permits a database administrator to import the metadata (objects) or data for one or more databases from one or more files in either SQL or a text format such as CSV, TAB, GRID, or VERTICAL.

Example 1: Import the metadata of the database ‘util_test’ to server1 on port 3306 using a file in CSV format.

mysqldbimport --import=definitions
--server=root@localhost --format=csv
data.csv

Example 2: Import both the data and definitions of the database ‘util_test’ to server1 on port 3306 producing bulk insert statements from a file that contains SQL statements.

mysqldbimport data.sql --import=both
--bulk-insert --format=sql
--server=root@localhost

mysqldiff - Reads the definitions of objects and compares them using a diff-like method to determine if two objects are the same based on the definition of the object.

Example 1: Find the differences among objects in database employees on one server and emp1 on the same server.

mysqldiff --server1=root@localhost
employees:emp1

Example 2: Find differences between the salaries table on one server and the same table on another server.

mysqldiff --server1=root@localhost
--server2=root@host2:3306
employees.salaries:emp1.salaries
--differ

mysqldiskusage - Permits a database administrator to see the disk space usage for one or more databases in either CSV, TAB, GRID, or VERTICAL text formats. The utility will also allow the user to examine the disk usage for the binary logs, slow, error, and general log; and InnoDB tablespace usage.

Example 1: Show only the disk space usage for the employees and test databases in grid format.

mysqldiskusage --server=root@localhost
employees test

Example 2: Show all disk usage for the server in CSV format.

mysqldiskusage --server=root@localhost
--format=csv -a

mysqlindexcheck - Eeads the indexes for one or more tables and identifies duplicate and potentially redundant indexes.

Example 1: Scan all of the tables in the employees database to see the possible redundant and duplicate indexes as well as the DROP statements for the indexes.

mysqlindexcheck --server=root@localhost 
 --show-drops employees

Example 2: Scan all of the tables in the employees database and display the indexes. Display the output in a tabular format.

mysqlindexcheck --server=root@localhost
--show-indexes --format=TAB employees

mysqlmetagrep - Searches for objects matching a given pattern and shows a table of the objects that match the pattern `’t\_’.

mysqlmetagrep --pattern=“t_”
--server=mats@localhost

Example 2: Find all objects that contain ‘t2’ in the name or the body (for routines, triggers, and events).

mysqlmetagrep -b --pattern=“%t2%”
--server=mats@localhost:3306

mysqlprocgrep - Scans the process lists for processes that match the search criteria specified and will either print the result (the default) or execute certain actions on it.

Example 1: Kill all connections created by user “mats” that are younger than 1 minute.

mysqlprocgrep --server=root@localhost
--match-user=mats --age=1m
--kill-query

Example 2: Kill all connections with queries that have been idle for more than 1 hour.

mysqlprocgrep --server=root@localhost
--match-command=sleep --age=1h
--kill

mysqlreplicate - Permits an administrator to start replication among two servers. The user provides login information to the slave and provides connection information for connecting to the master.

Example 1: Setup replication between a MySQL instance on two different hosts using the default settings.

mysqlreplicate --rpl-user=rpl:rpl
--master=root@localhost:3306
--slave=root@localhost:3307

Example 2: Ensure the replication between the master and slave is successful if and only if the InnoDB storage engines are the same and both servers have the same storage engines with the same default specified.

mysqlreplicate --pedantic
--master=root@localhost:3306
--slave=root@localhost:3307
--rpl-user=rpl:rpl -vv

mysqlserverclone - Permits an administrator to start a new instance of a running server.

Example 1: Create a new instance of a running server.

mysqlserverclone --new-port=3310
--server=root:pass@localhost
--new-data=/source/test123

Example 2: Create a new instance of a running server set the root password and turn binary logging on.

mysqlserverclone --new-port=3310
--server=root:pass@localhost
--new-data=/source/test123
--root-password=pass
--mysqld=--log-bin=mysql-bin

mysqluserclone - Permits a database administrator to use an existing user account on one server as a template and clone a MySQL user such that one or more new user accounts are created on another (or the same) server with the same privileges as the original user.

Example 1: Clone ‘joe’ as ‘sam’ and ‘sally’ with passwords and logging in as root on the local machine.

mysqluserclone --source=root@localhost
--destination=root@localhost
joe@localhost sam:secret1@localhost
sally:secret2@localhost

Example 2: Show all of the users on the localhost server in the most verbose output in CSV format.

mysqluserclone --source=root@localhost
--list --format=CSV -vvv

mut - Designed to execute predefined tests to test the MySQL Utilities.

Example 1: Run all tests.

mut --server=root@localhost

Example 2: Run all tests that start with ‘clone_user’ and set the width of the display to 70 characters.

mut --server=root@localhost
--do-tests=clone_user –width=70

How Can I Get It?

You can download MySQL Workbench from:

http://www.mysql.com/downloads/workbench/

You can also download the latest development source code tree for the MySQL Workbench Utilities from:

https://launchpad/net/mysql-utilities