Talk 9: The Future of MySQL Replication

This talk is by Dr. Lars Thalmann regarding where MySQL replication is going.

First bit is about why and how replication works as it stands.  People do it for high availibility (failover), load-balancing, and having an online backup.  It's done with the standard snapshot and binlog method that everybody uses.  Hurrah, nothing new here.

New features in 5.0... auto-increment works in bi-directional replication (master-master), character set and timezone replication, stored procedure and triggers and views replication, and other advanced things such as FOREIGN_KEY_CHECKS, UNIQUE_KEY_CHECKS, SQL_AUTO_IS_NULL, and SQL_MODE.

New in 5.1... row-based logging and replication, dynamic switching of log format (between statement, default, and row), MySQL Cluster replication, etc.  More detail coming up.

Fairly simple, but now MySQL allows you to change the auto_increment offset and increment on a server, so that one will generate all the odd numbers, one all the event.  For more complex setups, you can make them do every third number, or every hundredth, or whatever.

The bigger change is that MySQL 5.1 allows row-based replication (RBR).  Usually, entire statements are written to the binlog on the master, then the slave reads the statements, and executes them.  Row-based goes into the binlog straight from the storge engine.  Using RBR, it is possible to replicate when you're using such features as LOAD_FILE(), UUID(), USER(), FOUND_ROWS(), and UDFs.  Statement-based replication (SBR) is the default, is proven technology, and lets you audit the queries being executed.  This also allows you to have different table definitions on the master and slave.

Also in 5.1 are four new binlog events.  A table map event, stating, "this table id maps this table definition".  Then there are three row change commands: binwrite, binupdate, and bindelete.  Oh neat!  Using this RBR and if you have primary keys on your tables, the binlogs are idempotent.  You can run the same log against the slave multiple times, and it won't hurt it.  (Useful in some recovery situations - just replay all the logs! It won't hurt! No more calculating where we left off, etc etc.   I like that.)

The format of the binlog doesn't seem to have changed.  Same 19 byte common header.  Then the new binlog event types.  Moving on.  RBR also provides lots of potential optimizations.  If the master had to create a temporary table and do an hour of sorting to get the data to insert, the slave won't have to do that work.  It just replicates the final results of the query from the master.  This also means that if you do something like "INSERT INTO t1 SELECT FROM t2" then the table t2 doesn't have to exist on the slave.

Another advantage of RBR is that now MySQL Cluster (the in-memory HA setup) support replication.  You can now replicate from one cluster to another.  This enables you to have your machines in lock-step, synchronously replicating the data, so every machine is guaranteed up to date.  Then you can asynchronously replicate the data to another setup, such as having one cluster in the US, one in Japan, providing HA at each location, and yet keep them relatively up to date with eachother.

MySQL Cluster is looking very interesting now.  The target for the replication doesn't even have to be Cluster, you can have a HA MySQL Cluster system, and replicate it to a InnoDB/MyISAM system for backup purposes or whatever else you want.

Now a huge slide with no less than 30 arrows describing how MySQL Cluster works.  Yikes.  Pardon me for not getting into detail - the slide's gone now so I don't really have a chance to type it up.

Another new thing, the binlog now has an "injector interface" which enables you to inject events into the binlog.  He didn't provide much information, but this seems interesting for people who are writing plugins for MySQL.

A neat trick you can use is to have the master write SBRs and then set the slave to write RBRs, thereby converting them.  Not sure what you'd want to do that for, but he suggested it.  Heh.

Next steps is multi-source replication, online backup, conflict detection and resolution, and automatic failover.  That is where MySQL is going next.