High performance MySQL consulting

The mysqldump program, which ships with MySQL, takes data from existing tables and turns it into SQL statements which can then be used to re-create the data later. It has several different options which dictate its behavior for collecting data. Some of the most important change its locking behaviour; depending on the options passed to the mysqldump command, it may need to lock tables for the entire duration of the dumping process, briefly at the beginning, or not at all.

When using mysqldump for backups with a MySQL replication slave, the master-data option is often required in order to save the slave replication log file name and position in the backup. By default, the master-data option will execute a FLUSH TABLES WITH READ LOCK command—which disallows writes to any table—and will keep the read lock for the entire duration of the dump to ensure that the replication position won’t change during the dump. This can be problematic, because any clients trying to write to tables during the dump process are locked out. With MyISAM, this is necessary, but with InnoDB it isn’t.

With a little help from InnoDB’s transaction and multi-versioning support, it’s possible to keep the read lock for a very short period of time—just long enough to quiesce the system and check the replication status. InnoDB guarantees that a transaction will see a consistent view of the database after a transaction has started. If all of the tables being dumped are InnoDB, using the single-transaction flag to mysqldump will instruct it to only keep a read lock long enough to gather the replication state and start a transaction. After the transaction has been started, the read lock is released so that the dump and other clients can continue without locking each other out.

On the topic of consistency for a dump, there is an important distinction between the lock-all-tables and lock-tables options: With the lock-all-tables option, mysqldump will use the same type of global read lock it uses when gathering the replication position. This can be used to ensure a consistent dump when not using replication. The lock-tables option, however, issues a LOCK TABLES query for each database it’s dumping. If you choose multiple databases to dump, each database will be locked and unlocked in turn. This introduces a race condition: modifications can be made to database A while database B is locked and being dumped. If you’re using InnoDB, single-transaction is a much better option.

Most people worry about the total time it takes to perform a backup, but the most important timing aspect of any backup is not the time it takes to create the backup, but the time it takes to restore it. Nonetheless, Maatkit includes mk-parallel-dump and mk-parallel-restore which offer a nice speed boost over the traditional MySQL tools.

At Proven Scaling, we’re great fans of using pairs of MySQL servers replicating to each other (commonly known as master-master replication or dual-master replication) as a way of ensuring high availability for MySQL databases.

Deploying servers in this way enables one half of the pair to be taken offline for maintenance work while the other half carries on dealing with queries from clients — meaning that, for instance, lengthy ALTER TABLE operations can be done with no impact on service. This strategy has been in use at many sites for years, and has been very successful at minimizing downtime.

The usual way of implementing this model is to have IP addresses floating between the two MySQL servers. Rather than having the clients use the actual IP addresses or hostnames of the servers themselves, these “floating IPs” (or “virtual IPs”, “VIPs”, “IP aliases”) are used by clients to access MySQL based on a role (typically “writable” and “read-only”). The floating IP addresses can be moved between the servers as required to ensure that each role is always available.

There are some tools already available to manage pairs of MySQL servers, most notably mysql-master-master (MMM) and the High Availability Linux project.

Today, we’re announcing the release of Flipper, a tool for managing access to MySQL servers using master-master replication.

Reinventing the wheel?

Although the existing tools have worked well for some people in some situations, we (and our customers) have been frustrated by the number of situations where they’re not suitable.

Most of the existing tools are specific to Linux, and therefore no good to users of Solaris, FreeBSD and other operating systems. Most are heavy-weight implementations, with monitoring daemons running all the time. Configuration is rarely simple, sometimes because some of the available solutions try to be all things to all men, doing things that would be better handled elsewhere.

A lot of the effort that’s been put into other tools has been aimed at implementing automatic failover. Sometimes this can be very useful (for instance in stateless applications, restartable services, etc.), but very often this is implemented with little consideration for the possible consequences in a stateful, database environment.

Bringing failed servers back into service prematurely (as often happens with hardware load-balancing solutions) can be disastrous, with bad data being returned to clients, or data received from clients and theoretically committed being lost. Likewise, servers may be incorrectly diagnosed as having failed, causing a painful, lengthy, and potentially irreversible failover process to take place for what should have been a barely noticeable event. In some cases, an automatic failover system may change its mind back and forth, causing repeated failover events (known as “flapping”). All in all, we’re not convinced that completely automatic failover is always a good idea¹.

Automated, but manually triggered

Flipper’s design comes from a very pragmatic perspective. It’s a standalone tool that doesn’t require constantly running monitoring daemons — it evaluates the current situation at the moment that it’s executed, and does only what it’s told. It doesn’t attempt to do anything fancy right now; it just manages moving IP addresses between MySQL nodes and reconfiguring a typical master-master setup, in a safe, controlled manner. If one of the MySQL masters fails, it will allow you to move services away from the failed master, enabling you to fix the failure.

Flipper has been designed to be as portable as possible. It’s capable of running on almost any UNIX-like operating system, as it’s written in Perl and uses DBD::mysql to communicate with MySQL servers. Flipper itself doesn’t necessarily require any special privileges, user accounts, or daemons; it uses ssh and sudo to run system commands (and you’d typically want to set up SSH keys, and use ssh-agent to avoid typing your passphrase so many times).

We will add additional features in the future, but the system will always remain modular — you’ll be able to use whichever parts of it you want.

Where can I find out more?

We’ve set up a new (and currently rather minimal) micro-site for Flipper at provenscaling.com/software/flipper with documentation and links to various resources.

We will also post on this blog when there’s a new release, or some other important Flipper-related news.

¹ Of course, we’d be delighted to hear from anyone who wants to try and convince us that any of the current MySQL automatic failover/HA strategies are error-proof, or anyone who’s got new ideas about how this can be achieved.