Monday, October 10, 2011

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!