MySQL 5.1 introduces row based binary logging.  In fact, the default binary logging format in GA versions of MySQL 5.1 is 'MIXED' STATEMENT*;   The binlog_format  variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the actual statement which changed data, even when the global setting on the master is to write binary logs in statement mode.   The row-based format does offer advantages particularly if triggers or stored procedures are used, or if non deterministic functions like RAND() are used in DML statements.

A statement based replication slave can get out of sync with the master fairly easily, especially if data is changed on the slave.   It is possible for a statement to execute successfully on a slave even if the data is not 100% in sync, so MySQL doesn't know anything is wrong.  This isn't the case …

MySQL is an easy database to get running initially but it can be tricky to run in demanding environments. High Availability MySQL Cookbook is a relatively thin book packed with information.

The first four chapters are on the care and feeding of a MySQL 7.x Cluster. It starts simply with an initial configuration, covers backups & replication, covers user defined partitioning, and covers troubleshooting node failures. The examples are short, to the point, and devoid of any unneeded filler.

Next come chapters on replication including the very tricky Multi Master Replication. Used shared storage is covered in a separate chapter from DRBD. The book finishes strongly with performance tuning.

The good points -- the book is concise and not padded with filler. The bad is that it is too short. Considering how well the …

A lot of systems are relatively easy to make HA (highly available). You just slap them into a well-known HA framework such as Linux-HA and you’re done. But databases are different, especially replicated databases, especially replicated MySQL.

The reason has to do with some properties that hold for many systems, but not for most databases. Most systems that you want to make HA are relatively lightweight and interchangeable, with little to zero statefulness, easy to start, easy to stop, don’t care a lot about storage (or at least don’t write a lot of data; that’s usually delegated to the database), and there’s little or no harm done if you ruthlessly behead them. The classic example is a web server or even most application servers. Most of the time these things are all about CPU power and network bandwidth. If I were to compare them to a car, I’d say they are like matchbox cars: there are many of them, and they are cheap …

I recently presented a webcast hosted by O’Reilly and Webex.  In it I take you on a step-by-step installation of DRBD and MySQL.  I start by using Sun’s Virtualbox to create to virtual machines running CentOS.  I then explain how to configure them with virtual external drives to use for DRBD.  I next configure the network interfaces to support routed packets into and out of the boxes.  Then I install various packages with yum, configure drbd and finally install MySQL as the last step.  You can follow along at the command line and do it yourself on a Windows, Mac or Linux box.

In the last of our three part series on MySQL high availability we discuss the Linux Heartbeat project, and how it can be used to automate failover between two MySQL databases.

Heartbeat exposes a virtual IP address for use by the database, and manages it as well.  In the event that one server becomes unavailable, Heartbeat will  revoke primary control of DRBD from that node, hand over the IP address to the alternate node, mount the DRBD device, and start MySQL.  MySQL’s InnoDB engine will then perform crash recovery, rollback uncommitted transactions, and startup.

Read the full article at Database Journal – DRBD & MySQL, Heartbeat Setup

The next presentation by Piotr Biel from Percona was on Multi-Master Manager for MySQL.

The introduction included a discussion of the popular MySQL HA solutions including:

• MySQL Master-slave replication with failover
• MMM managed bi-directional replication
• Heartbeat/SAN
• Heartbeat/DRBD
• NDB Cluster

A key problem that was clarified in the talk is the discussion of Multi-Master and this IS NOT master-master. You only write to a single node. With MySQL is this critical because MySQL replication does not manage collision detection.

The MMM Cluster Elements are:

• monitoring node
• database nodes

And the Application Components are:

• mon
• agent
• angel

MMM works with 3 layers.

• Network Layer – uses a virtual IP address, related to servers, not …

In part two of our article on DRBD and High Availability, we take you step-by-step through setting up Sun’s Virtualbox software, creating a couple of VMs, and then installing CentOS on those.  These two virtual Linux boxes then serve as two nodes in our DRBD mirrored disk setup which we use as a platform to install MySQL.

DRBD, MySQL and the Virtualbox Setup – Database Journal

Keep on the lookout for our third part in the series next month.  In that issue we’ll explain how the Linux Heartbeat project can be used to control the whole setup, and provide automatic failover in the event that one node goes down.

With all the trouble keeping MySQLs built-in replication running, some folks are looking for alternatives.  DRBD provides a distributed block level device, which can provide the sort of database mirroring we need, below the filesystem.  That makes it transparent to MySQL, but nevertheless a great complimentary solution.  In this article we’ll discuss the pros and cons, and then part two will take you step by step through a basic setup.

Read the article on Database Journal – DRBD and MySQL – Excellent Low-cost HA Solution

I will be presenting a free Webinar on Geographic Replication for MySQL Cluster at 9:00 am (UK time) on Tuesday 24 November.

Multi-Master Replication for HA with MySQL Cluster

MySQL Cluster has been deployed into some of the most demanding web, telecoms and enterprise /
government workloads, supporting 99.999% availability with real time performance and linear write scalability.

You can register on-line here.

Tune into this webinar where you can hear from the MySQL Cluster product management team provide a detailed “deep dive” into one of MySQL Cluster’s key capabilities – Geographic Replication.

In this session, you will learn how using Geographic Replication enables your …

Fig 1. Typical management configuration

MySQL Cluster is designed to be a High Availability, Fault Tolerant database where no single failure results in any loss of service.

This is however dependent on how the user chooses to architect the configuration – in terms of which nodes are placed on which physical hosts, and which physical resources each physical host is dependent on (for example if the two blades containing the data nodes making up a particular node group are cooled by the same fan then the failure of that fan could result in the loss of the whole database).

Of course, there’s always the possibility of an entire data center being lost due to earthquake, sabotage etc. and so for a fully available system, you should consider using asynchronous replication to a geographically remote Cluster.

Fig 1. …