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. 

…

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 …

One of the many new things that is being introduced this week are some great new external tools for managing MySQL servers. These are available in MySQL Workbench under the name MySQL Workbench Utilities.

It is a package of easy-to-use utilities for maintenance and administration of MySQL servers. These utilities encapsulate a set of primitive commands bundling them so that you can perform macro operations with a single command.

Some of the key features of MySQL Workbench Utilities are:

• 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 …

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
…

Platform: Any UNIX*

If you are working on a small set of data or with fewer tables and if you notice that the DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE operation has generated few warnings then you can just execute the “SHOW WARNINGS” SQL command to display the exact warning messages generated during the last DML operation.
But “SHOW WARINGS” shows nothing if the last statement used a table and generated no messages.
SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session.
“SHOW WARINGS” can certainly help you in grabbing the warnings but what if you are loading a huge dump file or performing a batch operation and at the middle of load operation MySQL reports that the last DML completed with …

Last week some one asked how to Copy the tables (Only structure and NO DATA..also no other DB objects) from one schema to another on EE. This can be easily done from command line but user wanted to do this thru stored procedure.
http://www.experts-exchange.com/Database/MySQL/Q_25024073.html
So I came with a very small MySQL procedure which was doing as needed by the user. I'm not sure whether this is the best way to do this but "There is always room for improvement."
 
DELIMITER $$

DROP PROCEDURE IF EXISTS `CopySchema`$$
CREATE PROCEDURE `CopySchema`(sourceSchema VARCHAR(64),targetSchema VARCHAR(64))


BEGIN

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE …

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare.


The options can be viewed by executing the following command:
shell>mysqlhotcopy --help


Backup one/many database at once

shell>mysqlhotcopy [options] db_name1...db_nameN /path/to/backup_directory

Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:

shell>mysqlhotcopy [options] db_name./regex/

The regular expression for the table name can be negated by prefixing it with a tilde (“~”):


…

Have you ever tried to start a server like MySQL and been amazed to see an error that the port is already in use? You rack your brain and try to figure out what it would be to no avail. Sometimes you do a "ps" in Linux and don't even see anything that you think would be using the port. Well, forutantely, there are some tricks to help you find out without doing a reboot. If it is a production server, a reboot may not be an option anyway!

Below are some methods to help. We will start by looking at the "fuser" utility provided with many Linux distros:

fuser -n tcp 80
80/tcp:               1029  1030  1824  1838  1839  1840  1841 13972 14136 14137 14712

This example shows a simple check of everything using port 80. What you see above is a list of PIDs that are using that port. Now we could probably just do a simple "ps" to figure out what it is. You might also want to get more info by doing something like the …

Ever been handed a new server only to wonder whether it is 32-bit or 64-bit? Well, it is rather simple to tell without having to ask! On a Linux server one of the following commands could just give you the answer very easily!

We remember being given a supposed "64-bit" server once and being assured by a manager that it was "definitely" 64-bit. After trying a few times to install a MySQL binary, it became obvious something was wrong. We checked and sure enough the "64-bit" server was actually "32-bit!"

If you want to be sure, the easiest thing to do is run the "gentconf" utility in Linux:

getconf LONG_BIT

The above will display either 32 or 64. It doesn't get any easier than that!

Another method is below though this output is less obvious:

uname -m

You might also want to check all of the output of the "uname" command which can give you some great …