It is almost time for another big Google patch. This one has global transaction IDs, which is suddenly a hot topic. Continuent supports something similar to this with a very different implementation and no changes to the MySQL source. There are now two methods to make slave failover easy. Failover with master-master can be automated. But once you need more than two servers for read scaleout, your options are master-master-master-master-master-master-master-... or 1 master with many slaves. The former is a bad idea as each master in the chain adds replication delay. The latter makes slave failover hard.
Slave failover is the process of changing the master used by the slave when a new server becomes the master. If this is a planned change, then this can be done with a short downtime. When this is an unplanned change, it is not fun. The problem is that a slave uses the filename and offset of the master's binlog. When a new server becomes the master, the new server may use different filenames. But even if it doesn't the filename/offset the slave used on the old master cannot be used on the new master.
There are a few solutions to this:
- switch and hope - run CHANGE MASTER to switch the slave to the new master and pretend that lost or extra transactions don't matter. Transactions are lost on the slave when it was behind the new master relative to the replication stream of the old master. The slave will have extra transactions when it was ahead of the new master relative to the replication stream of the old master.
- check and lose - don't run CHANGE MASTER on slaves that did not stop replication on the old master at the same point as the new master. These slaves must be restored from a backup taken on the new master and the backup/restore must complete before another failover is done.
- check and fix - promote the slave with the most transactions from the old master to be the new master. Manually apply transactions from it to other slaves when the other slaves were behind.
With global transaction IDs, every binlog event has a global ID. The ID is a 64-bit integer that is incremented for each group of binlog events and is maintained on failover. Slaves store both the filename/offset of their current master and the global ID. On failover, a slave can connect to the new master using the global ID. This requires no manual intervention. That solves one problem.
Slaves that may become new masters should be run with log-slave-updates to record binlog events received from the master in their binlog. These events are stored with the global ID from the master on which the event originated. If there is a master failover, the new master can serve binlog events for global IDs that originated on the old master. Thus, a slave that is behind can connect to the new master and get the events it is missing. That solves the other problem.
Finally, a backup taken from the old master (using InnoDB hot backup) can be restored using the new master as long as the backup includes the global ID from the old master. I think SHOW MASTER STATUS was augmented to include the global ID.
As an added bonus, checksums were added to binlog events.