Tuesday, April 10, 2012

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.

failover-2012-04-3-20-40.png

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

17 comments:

  1. Hi Chuck,

    Great info! And very welcome addition to MySQL utilities. BTW, the link to the launchpad site is incorrect. Should be "launchpad.net" instead of "launchpad/net".

    /Craig

    ReplyDelete
  2. Hi,

    Thanks for the article. Theses utilities look quite useful for handling automatic failover.

    Can you clarify some points for me?

    I am interested in using mysqlfailover to automatically manage a cluster of replicated databases.

    For the benefits of high-availability, am I right in thinking that I should run the mysqlfailover utility on a dedicated machine, separate from any of the DB machines?

    In case the machine running mysqlfailover also fails, I should also have a standby host that can take over to make sure that the process which runs the automatic failover actually keeps on running.

    Should I also avoid running two instances of mysqlfailover at the same time, as they might take conflicting decisions when it comes to promoting slaves following a master failure?

    Thanks.

    /Guillaume.

    ReplyDelete
  3. You do not need a dedicated machine for mysqlfailover. It can be run from any client. It is best to run it on a reliable machine, however.

    As for multiple instances, you cannot run multiple instances of the console connected to the same master (it will prevent you from doing so). However, you can run one instance of the console for each master in your topology.

    The utility manages failover for a single master and its slaves. For complex topologies, you can use the extension points built into the utility to execute application-specific cascades, notifications, etc. via scripts.

    ReplyDelete
  4. Thanks @Chuck, I will give it a try.

    ReplyDelete
  5. Nice info to look forward to in MySQL, this can add apps. I can learn more of the binary log files.



    kamagra

    ReplyDelete
  6. These facts are really interesting. Few of them were well known for me but many of them were brand new for me too!
    I will print this one out and show to my friends because they will be definitely interested in that. Thanks!
    MySQL

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi ,
    Is GTID functionality available through mysql replication listener ??

    Thanks

    ReplyDelete
    Replies
    1. No, sorry. The replication listener does not support GTIDs nor does it support MySQL v5.6. No word yet on when or if for either.

      Delete
  9. I will be really glad my spouse and I witnessed an excellent web site. I have to thank a lot a lot of information! I enjoy we have saved your website for brand new things to examine sand on the roads.
    Advertising agencies in Karachi | Advertising agencies in Pakistan

    ReplyDelete
  10. Is there any way to get the health status for all slaves when the master is down? I tried mysqlfailover & mysqlrpladmin but they give error "Cannot connect to the master server." & exits.

    Also is there any way to do failover (not switchover) for non-GTID based replication setup using mysqlrpladmin?

    ReplyDelete
    Replies
    1. 1) Those utilities are designed to contact the master and get the list of slaves from the master. However, that would be a good feature to add!

      2) If you are talking about automatic failover like what you can do with mysqlfailover, no. Automatic and failover on demand require GTIDs.




      Delete
    2. Thanks for the quick reply!

      For non-GTID based replication, can't it do automatic failover based on log position to detect the most updated slave & then promote it as master? Just a thought :)

      Delete
    3. In principle, yes, but there is more to it. Consider the possibility that none of your slaves have all of the transactions. You have to reconcile the transactions on each slave manually until you get all of the slaves caught up. Even if one of the slaves is behind, it means different binlog file and position will need to be used. Failover with GTIDs has none of these issues. See the following for descriptions of the challenges of non-GTID failover:

      http://code.google.com/p/mysql-master-ha/wiki/Other_HA_Solutions

      Delete