Archive for August 14, 2013

New replication & HA white papers

MySQLReplication and High Availability logoWith the General Availability of the standalone MySQL Utilities it now makes sense to use these to simplify (and optionally automate) your MySQL Replication and High Availability solutions. In light of that, 4 of our MySQL white papers have been updated to reflect the new opportunities:

MySQL Guide to High Availability Solutions. Data is the currency of today’s web, mobile, social, enterprise and cloud applications. Ensuring data is always available is a top priority for any organization – minutes of downtime will result in significant loss of revenue and reputation.

This Guide is designed to assist Developers, Architects and DBAs in navigating the complex waters of HA. It presents:

  • A methodology for selecting the right HA solution to meet Service Level Agreements
  • A tour of the leading certified HA solutions for MySQL
  • Operational best practices to implement and support HA

MySQL Replication: High Availability – Building a Self-Healing Replication Topology. Download the whitepaper to learn how to improve user-experience, reduce cost and innovate faster using MySQL replication.

Global Transaction Identifiers (GTIDs) are one of the core new features of MySQL 5.6 replication, providing a foundation to building self-healing, highly available data clusters.

By reading this whitepaper, you will be able to:

  • Illustrate use-cases and implementation of MySQL replication
  • Learn how High Availability (HA) with MySQL replication is achieved using GTIDs
  • Gain an overview of MySQL replication utilities
  • Discover resources for achieving HA with MySQL replication

The paper concludes with an overview of operational best practices.

MySQL Replication: An Introduction. Download the whitepaper to learn how MySQL replication enables the largest web, cloud, mobile and social applications to scale-out on commodity hardware, while reducing the risks of downtime.

The whitepaper discusses:

  • Replication concepts
  • Replication enhancements in MySQL 5.6
  • Replication use-cases
  • Replication topologies
  • Replication monitoring and management

The paper concludes with resources to get started with MySQL replication in building next generation services.

MySQL Replication Tutorial: Configuration, Provisioning and Management. Download the whitepaper for practical examples and best practices in building highly available services using MySQL replication as well as MySQL Utilities.

By reading this paper, you will be able to:

  • Configure and provision MySQL replication (with or without MySQL Utilities)
  • Migrate to semi-synchronous replication
  • Administer and trouble-shoot MySQL replication (with or without MySQL Utilities)
  • Promote a slave to be the new master in the event of a failed Master (manual or automatic, with or without MySQL Utilities

The paper concludes with additional resources to tune and optimize MySQL replication for your environment.





Sessions now scheduled for MySQL Connect

MySQL Connect 2013 - I'm speaking logoThe sessions for this year’s MySQL Connect conference have now been scheduled – as you can see below, my 2 MySQL Cluster sessions will be on Saturday 21st September at 11:30 and 14:30 (Pacific).

The MySQL Connect conference is a great opportunity to listen to and chat with people from the MySQL community – including the engineers who work on or around MySQL as well people who are using it in production. The conference takes place from 21-23 September in San Francisco (runs up to Oracle OpenWorld). There are 84 sessions scheduled and the content catalog has now been published.

I’ll be presenting 2 sessions:

  • What’s New in MySQL Cluster 7.3 [CON2477] (Saturday 11:30 Hilton – Imperial Ballroom B). In this session, discover the latest developments and how MySQL Cluster 7.3 enables developers to focus on building robust, scalable applications faster. Get your database up and running in minutes by using the browser-based autoinstaller, combining autodiscovery with best practices to deliver the ideal configuration the first time. Migrate existing applications and frameworks to MySQL Cluster, and simplify new ones by exploiting cross-shard foreign keys. Access your data directly with the native driver for JavaScript/Node.js. At the same time, enjoy the benefits of 99.999 percent uptime and a distributed, autosharded database that scales to deliver higher loads than ever whether with SQL or NoSQL APIs—even when you’re working with queries and updates that span shards.
  • Deploy and Scale MySQL Cluster Like a Pro Without Opening the Manual [CON3763] (Saturday 14:30 Hilton – Union Square Room 5/6). This session aims to tackle a few myths head-on: “Installing a distributed database has to be complex,” “Only NoSQL data stores make life easy for developers,” “Years of painful experience and a PhD are needed to configure a distributed, real-time database.” In this session, see for yourself how to configure and deploy MySQL Cluster over several hosts in just minutes—right from your browser. Observe how the MySQL Cluster Installer applies best practices to produce a tailored configuration, using your hints about your application together with autodiscovery of the system resources. Also see how MySQL Cluster Manager simplifies the management of your cluster, performing sophisticated operations such as adding new nodes or performing online upgrades with ease.

Register now!





Standalone MySQL Utilities Now GA! Includes running mysqlfailover as a daemon

MySQL Utilities are now GA - logoWith the release of MySQL Utilities 1.3.4, the standalone (not bundled with the MySQL WorkBench GUI) package is now Generally Available and fully supported. This post will focus on a very important change (the ability to run as a daemon rather than in a terminal) to the mysqlfailover utility which allows you to build a light-weight HA database solution using MySQL Replication.

For a general overview of MySQL Utilities, take a look at this recent webinar or for a deeper dive into using them to setup replication and adding auto-failover of the master function to slaves watch this video and worked example.

When we first released the mysqlfailover utility, the reaction was very positive but the feedback also told us that to really use this to provide High Availability in a production system two enhancements were critical:

  1. The ability to not have the database password visible when someone queries the status of the process (for example, using the ps command). This was addressed by allowing the connection string to be specified using a login-path (referring to an entry in .mylogin.cnf – see https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html) in place of <user>[:<passwd>]@<host>. This is a vital security enhancement added in MySQL Utilities 1.3.1.
  2. Allowing mysqlfailover to be run as a daemon rather than being tied to the terminal from where it had to be manually launched. This option is key to enabling the user to build a reslient HA system that doesn’t rely on mysqlfailover being launched from a terminal and that terminal then never going away.

The rest of this post focuses on how to run mysqlfailover as a daemon.

By default, mysqlfailover runs as an interactive program within the terminal it was run from; it constantly refreshes, providing a summary of the status of the replication topology as shown below.

mysql@mini servers]$ mysqlfailover --master=root@192.168.1.101:5001 
  --discover-slaves-login=root --rediscover

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Thu Aug  1 23:34:56 2013

Master Information
------------------
Binary Log File   Position  Binlog_Do_DB  Binlog_Ignore_DB
mini1-bin.000001  581

GTID Executed Set
1aca3d80-faf9-11e2-a214-0800272b8804:1-2

WARNING: Errant transaction(s) found on slave(s).
Replication Health Status
+----------------+-------+---------+--------+------------+---------+
| host           | port  | role    | state  | gtid_mode  | health  |
+----------------+-------+---------+--------+------------+---------+
| 192.168.1.101  | 5001  | MASTER  | UP     | ON         | OK      |
| 192.168.1.101  | 5002  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.101  | 5003  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.101  | 5004  | SLAVE   | UP     | ON         | OK      |
+----------------+-------+---------+--------+------------+---------+

Q-quit R-refresh H-health G-GTID Lists U-UUIDs

To run mysqlfailover as a daemon, the first new command-line option you must provide is –daemon=start; as you’d expect this runs the process as a daemon. In this mode, you won’t get to see the output from mysqlfailover at your terminal and so you should make sure that you know what log file is being used – so it’s best to specify it with –log=<path-to-log-file>. You can also control what information is periodically written to the log file using –report-values=<list-of-attributes from health,gtid,uuid>.  As you’ll likely to want to be able to manage the daemon without having to be in the same directory (and also likely to manage it from scripts that are automatically run when the server starts and stops) it makes sense to specify where the process ID file should be stored using –pid=<path-to-pid-file>.

The final incantation might look something like the following:

mysqlfailover --master=root@192.168.1.101:5001 
  --discover-slaves-login=root 
  --rediscover 
  --log=/home/mysql/servers/mysqlfailover.log 
  --pidfile=/home/mysql/servers/mysqlfailover.pid 
  --daemon=start 
  --report-values=health,gtid,uuid

and the resulting log file could then contain information such as this:

2013-08-02 01:10:34 AM INFO Getting health for master: 192.168.1.101:5001.
2013-08-02 01:10:35 AM INFO Health Status:
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5001, role: MASTER, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2013-08-02 01:10:35 AM INFO GTID Status - Transactions executed on the servers:
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5001, role: MASTER, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, gtid: 1db19050-faf9-11e2-a214-0800272b8804:1
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, gtid: 200f8139-faf9-11e2-a214-0800272b8804:1
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, gtid: 1aca3d80-faf9-11e2-a214-0800272b8804:1-2
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, gtid: 22842441-faf9-11e2-a214-0800272b8804:1
2013-08-02 01:10:35 AM INFO UUID Status:
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5001, role: MASTER, uuid: 1aca3d80-faf9-11e2-a214-0800272b8804
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5002, role: SLAVE, uuid: 1db19050-faf9-11e2-a214-0800272b8804
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5003, role: SLAVE, uuid: 200f8139-faf9-11e2-a214-0800272b8804
2013-08-02 01:10:35 AM INFO host: 192.168.1.101, port: 5004, role: SLAVE, uuid: 22842441-faf9-11e2-a214-0800272b8804

As you’d expect, you can also stop the daemon:

mysqlfailover 
    --log=/home/mysql/servers/mysqlfailover.log 
    --pidfile=/home/mysql/servers/mysqlfailover.pid 
    --daemon=stop

and restart it:

mysqlfailover 
    --log=/home/mysql/servers/mysqlfailover.log 
    --pidfile=/home/mysql/servers/mysqlfailover.pid 
    --daemon=restart

The final option for daemon= is daemon=nodetach which is like start but the terminal that it’s run from will continue to view the output from the daemon.

One thing to note is that when the server is restarted, mysqlfailover needs to be started again and this is not something that is automatically configured when you run it as a daemon – rather, it’s your responsibility to ensure that it’s restarted (for example, including it in an init script.

Please try it out and (as always) let us know how you get on – the addition of these extra options is a direct result of the user feedback received for the earlier versions.