New Replication Features in MySQL 5.6

I’ve just been reading an article on replication in MySQL 5.6 and there are quite a few new cool features that will vastly improve replication environments with MySQL. Some of these include:

  • Optimised row based replication (documentation here) – replication used to mean execute the exact same queries on the slave server(s) as the master. This, as you can imagine, was incredibly inefficient. With row based replication, the resultant changed row of an INSERT, UPDATE or DELETE operation was replicated saving significant resources on slave servers. This was a big win. It now looks like it’s been further optimised in that only the changed elements (and a primary key) need to be replicated.
  • Introduction of Global Transaction Identifiers (GTIDs) allowing the source and sequence of a replication statement to be globally unique within a replication cluster. This with some cool new utilities (mysqlfailover and mysqlrpladmin) create a lot of native options for failover for maintenance or failure (see more about GTIDs and the utilities).
  • Time delayed replication allowing a slave to purposefully remain behind the master for any given delay. This may be a life saver for that table you accidently dropped!

There’s a lot more too and you can read about it all here.

PostgreSQL 9.0 Released with Mission Critical Features

At work, we’ve always used and advocated MySQL for high availability mission critical applications thanks to it’s built in binary replication features.

This was a feature PostgreSQL sorely lacked but it is some thing that has also been rectified in the 9.0 release which, among many other features, includes:

  • Hot standby;
  • Streaming replication; and
  • In-place upgrades.

We’ll be studying and testing this new release carefully as, despite the previous lack of the above features, PostgreSQL had a lot of other features over MySQL and was also ahead of MySQL with such taken for granted features as views, triggers and stored procedures.