Announcing: MySQL Utilities release-1.6.4 GA

The MySQL Utilities Team is pleased to announce a new GA release of MySQL Utilities. This release includes a number of improvements for usability, stability, and a few enhancements for better compatibility with MySQL 5.7. A complete list of all improvements can be found in our release notes.

Many Improvements!

This release represents a stable release of the product and is a significant improvement from the 1.5 release. Along with defect patches, we also include the following enhancements.

• Support for MySQL 5.7 
• New! Binlog Utilities:
• mysqlbinlogpurge - purge old binary logs
• mysqlbinlogrotate - rotate the binary log
• mysqlbinlogmove - relocate binary log files
• New! Replication Utilities: 
• mysqlslavetrx - skip transactions on one or more slaves to solve errant transactions and consistency error
• New! General Utilities: 
• mysqlgrants - show grants for users
• Improved functionality of --exclude option with SQL wildcards
• Improved packaging in RPM and Windows distributions
• Improved accuracy of calculated disk usage for mysqldiskusage
• Improved packaging in RPM and Windows distributions
• Improved output for mysqldbcompare and mysqldiff
• Improved SSL support
• The Replication utilities have undergone a number of improvements for new 5.7 features as well as quality and usability improvements.
• The documentation has been improved including more descriptions of SSL connections and clarification of replication commands.
• Finally, this release contains a host of minor improvements for quality and robustness

How Can I Download MySQL Utilities?

You can download MySQL Utilities 1.6.4 GA from the following link using one of the pre-built installation repositories including a source download.

http://dev.mysql.com/downloads/tools/utilities/

Where is the Documentation?

You can find online documentation for MySQL Utilities version 1.6 at:

http://dev.mysql.com/doc/index-utils-fabric.html

Announcing MySQL Utilities 1.6.3 Beta!

The MySQL Utilities Team is pleased to announce a new beta release of MySQL Utilities. This release includes a number of improvements for usability, stability, and a few enhancements. A complete list of all improvements can be found in our release notes.

New Enhancements!

This release represents a stable release of the product. Along with several defect patches, we also include the following enhancements.

Improved support for MySQL 5.7
Improved functionality of --exclude option with SQL wildcards
Improved packaging in RPM and Windows distributions
Improved accuracy of calculated disk usage for mysqldiskusage
...and a host of minor improvements for quality and robustness

How Can I Download MySQL Utilities?

You can download MySQL Utilities 1.6.3 Beta from the following link using one of the pre-built installation repositories including a source download. Click on "Development Releases" to see the 1.6.3 release downloads.

http://dev.mysql.com/downloads/tools/utilities/

Where is the Documentation?

You can find online documentation for MySQL Utilities version 1.6 at:

http://dev.mysql.com/doc/index-utils-fabric.html

Announcing MySQL Utilities release-1.5.2!

The MySQL Utilities Team is pleased to announce the general availability (GA) release of MySQL Utilities. This release includes a number of improvements for useabilty, stability, and a few enhancements. A complete list of all improvements can be found in our release_notes.

New Enhancements!

This release represents a stable release of the product. Along with several defect patches, we also include the following enhancements.

• The mysqlserverclone utility now checks diskspace as a prerequisite to cloning the server.
• The --force option was removed from the mysqlfailover utility to remove confusion on its purpose.
• A warning is issued if the mysqld --skip-innodb option is used because this option is ignored in MySQL 5.7 and later.
• Utilities can correctly parse and display the PROXY privilege for GRANT statements.

 

How Can I Download MySQL Utilities?

You can download MySQL Utilities 1.5.2 GA from the following link using one of the pre-built installation repositories including a source download. http://dev.mysql.com/downloads/tools/utilities/

Where is the Documentation?

You can find online documentation for MySQL Utilities version 1.5 at: http://dev.mysql.com/doc/index-utils-fabric.html

Announcing MySQL Utilities release-1.6.0-alpha

The MySQL Utilities Team is pleased to announce the alpha release of MySQL Utilities. This release represents a new evolution of the product including the following enhancements.

• The mysqlprocgrep utility now displays the processes and connections killed during a --kill operation. The displayed rows come from the appropriate SHOW PROCESSLIST entries.
• The mysqlbinlogmove utility was added. It relocates binary log files, and moves files based on their sequence number or modified date.
• The mysqlgrants utility was added. It displays grants per object, and produces reports by user, user with grants, and GRANT statement.
• Health reports can now be generated for a list of slaves without a master specified. In this case, "No master specified" appears for connection status for each slave, instead of an error.

 

How Can I Download MySQL Utilities?

You can download MySQL Utilities 1.6.0 Alpha from the following link using one of the pre-built installation repositories including a source download. Click on the Development Releases tab. http://dev.mysql.com/downloads/tools/utilities/

Do you want to learn more about DevOps and MySQL?

Are you attending Oracle Open World? Would you like to know more about Oracle DevOps products for managing your MySQL servers? If so, stop by Tuesday, September 30 at 10:15 in the Hotel Nikko - Bay View and join us for a hands on lab (HOL9234) for MySQL Utilities and Connector/Python.

We will present the basics for getting started using Python to manage your MySQL servers. MySQL Utilities includes utilities for a wide variety of operations from displaying GRANT statements to setting up automatic failover for replication.

Check out the abstract for the HOL here.

New! MySQL Utilities release-1.4.2-RC

The MySQL Utilities Team is pleased to announce the latest release candidate (RC) release of MySQL Utilities. This release includes a number of improvements for useabilty, stability, and a few enhancements. A complete list of all improvements can be found in our release_notes.

New Utilities!

We have also included two new utilities.

• The mysqlrplsync utility was added, which checks data consistency between servers in a replicated setup. 
• The mysqlrplms utility was added, which provides round-robin multi-source replication (a slave server continually cycles through multiple masters in order to store a consolidated data set).

How Can I Download MySQL Utilities?

You can download MySQL Utilities 1.4.2 from the following link using one of the pre-built installation repositories including a source download. Click on the Development Releases tab.

http://dev.mysql.com/downloads/tools/utilities/

Where is the Documentation?

You can find online documentation for MySQL Utilities version 1.4 at:

http://dev.mysql.com/doc/index-gui.html

New! MySQL Utilities release-1.3.6 GA

The MySQL Utilities Team is pleased to announce the latest GA release of MySQL Utilities. This release includes a number of improvements for usability, stability, and a few enhancements. We have also included a performance upgrade for exporting, importing, and copying databases.

Improvements

The following highlights a few of the more significant improvements.

* mysqldbexport, mysqldbimport, and mysqldbcopy have multiprocessing support that allows for much improved performance
* mysqlfrm can now generate a .frm file with storage engine substitution
* Mac OS X packages added!
* mysqlserverinfo now includes the log files (error, general, slow)
* mysqlprocgrep can now search and kill processes by id
* mysqlmetagrep can now search the body of routines with the new --body option
* all utilities report license type with --version and --help
* all utilities have the new --license option to view the license text
* the mysqluc now reports errors with clearer text and tags the message with the name of the utility that returned the error
* mysqlindexcheck now warns user if there is not enough information to calculate best/worst indexes
* rpm, debian, and msi packages will update/remove old versions automatically when installing a newer version
* the documentation is now a separate reference manual (see link below)

The following spotlight some of the more important enhancements.

Multiprocessing with mysqldbexport, mysqldbimport, and mysqldbcopy

The performance of the mysqldbcopy, mysqldbexport and mysqldbimport utilities has been significantly improved. Moreover, a new --multiprocess option was added to allow concurrent execution making the most of the available CPU resources (the number of CPU cores).

Note: the --thread option in mysqldbcopy was replaced by the --multiprocess option.

Multiprocessing is applied at different levels according to the operating system. The utilities mysqldbcopy and mysqldbexport allow multiprocessing at the table-level for non-Windows systems and at the database-level for Windows system. The mysqldbimport utility allows multiprocessing at the file-level independently from the OS.

Other more specific options were also added for performance reasons for the other utility. A new --output-file option was added to mysqldbexport to specify a file to store the generated output which allows faster output than sending messages to the terminal.

Two additional additional options are now available in mysqldbimport: 1) --autocommit to enable autocommit for each operation because now by default a single commit is performed at the end of importing each file which is much faster, and 2) --max-bulk-insert to adjust the maximum number of inserts in a bulk, following the improved bulk insert support that is now provided.

Create New .frm Files with New Storage Engine

The mysqlfrm utility allows you to use the --new-storage-engine and the new --frmdir option to provide a directory to store the new .frm files. This feature is useful for those who want to recover the CREATE statement from existing .frm files and change the storage engine without having to launch the server. Try it out!

Mac OS X Installer

Yes, we now have a Mac OS X package installer. If you install Utilities with this installer, you will need to either use Connector/Python version 1.1.4 or later (which has a Mac OS X installer too) or use the Connector/Python 1.0.8 or later source code package and install manually.

How Can I Download MySQL Utilities?

You can download MySQL Utilities 1.3.6 from the following link using one of the pre-built installation repositories including a source download.

http://dev.mysql.com/downloads/tools/utilities/

If you are a commercial customer, you can download MySQL Utilities from the following link:

https://edelivery.oracle.com/

MySQL Utilities is also available on Lauchpad as a source download at:

https://code.launchpad.net/mysql-utilities

Where is the Documentation?

You can find online documentation for MySQL Utilities version 1.3 at:

http://dev.mysql.com/doc/index-gui.html

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!