The futures of replication in MySQL

By Mark Callaghan on Friday, August 21, 2009 at 9:37am

Our DBA team wants replication state on a MySQL slave to be more robust against machine crashes. There are some things I can do, but now is a good time to review the work in progress by the community that will eventually make replication much easier to support in production.

Replication in MySQL has many possible futures. I use MySQL out of convenience to mean official MySQL, MariaDB, Drizzle, XtraDB, the Facebook patch and the Google patch.

Drizzle has by far (leaps and bounds far) the cleanest interface for replication. This will enable rapid innovation and hopefully production deployment of new features. Jay Pipes has begun to describe the interface in blog posts here and here.

Official MySQL has a preview of semi-sync replication. This is derived from the implementation in the Google patch. But they did a lot of work to reimplement it the right way. Those of us on the outside who maintain large patches for MySQL must compromise between the best implementation and something that is easier to maintain as a patch.

Official MySQL also has a worklog to make slave state crash proof. The code used on a master to keep the binlog and InnoDB in sync after a crash can be reused on a slave.

I have yet to see a worklog for using an InnoDB table instead of the file for slave replication state. Such a change will reduce the fsync overhead from using XA. Assuming dictionary tables were converted from MyISAM to InnoDB, then the system would be much more robust.

Recent releases of MySQL 5.0 contain fixes for a few bugs that corrupt slave replication state on a mysqld crash. These were found and fixed by Justin in the Google patch and quickly incorporated into official MySQL.

Finally, official MySQL has made progress on one of my favorite bugs. Several patches are listed for bug 31664 that add features to reduce the chance of corruption. Unaware of this, I reimplemented one of the patches myself this week and added the my.cnf variable sync_relay_info to specify the frequency at which writes to should be forced to disk. I think the the final patch uses fsync for and adds code to refetch relay log data from the master when and/or the relay log might be corrupt. This feature might be available in the next branch.

Global transaction IDs from the Google patch are the best feature you could use now were it in a supported release. The code is also available for MySQL 5.0.67 as a standalone patch on launchpad. I have yet to hear whether Percona or MariaDB plan to use it. When the feature is enabled, you can kill the slave at any point in time (excluding some DDL) and it will recover on restart without duplicating or losing transactions (assuming you use InnoDB). It also enables hierarchical replication and provides automatic slave failover to a new master after a crash.

I know that MariaDB has plans to do interesting things for MySQL replication. But I don't know whether I am at liberty to discuss them.

Last but not least there is Tungsten Replicator. They are doing clever things for replication without the burden of changing MySQL replication source code. I am not sure that is fair.