MySQL Replication from 1 Master to Multiple Slaves
A new white paper has been published that covers MySQL Replication – background information, how it works, how to use it and what’s new in MySQL 5.5. Simply register for the white paper at mysql.com and you’ll be sent your free copy.
The paper starts by covering the fundamental concepts behind replication such as the difference between synchronous and asynchronous replication and the idea behind semisynchronous replication.
It goes on to describe the common use-cases for replication – scaling out, high availability, geographic redundancy and offloading backups or analytics.
Various replication topologies are discussed from simple master-slave to multi-master rings.
As the title of the paper suggests, the paper covers the significant replication changes introduced in MySQL 5.5:
- Semisynchronous replication: Improved resilience by having master wait for slave to persist events.
- Slave fsync tuning & Automatic relay log recovery: Option to dictate when relay logs are written to disk rather than relying on default operating system behavior; set sync_relay_log=1 to ensure that no more than 1 statement or transaction is missing from the relay log after a crash. The slave can now recover from corrupted relay logs by requesting corrupt entries to be resent from the master. Three new options are introduced (sync-master-info, sync-relay-log and sync-relay-log-info)
- Replication Heartbeat: Automatically checks the status of the connection between the master and the slave(s), allowing a more precise failure detection mechanism. Can detect loss of connection within milliseconds (configurable). Avoid unnecessary relay log rotation when the master is idle.
- Per server replication filtering: When a server is removed from a replication ring, a surviving server can be selected to remove its outstanding replication messages once they’ve been applied by all servers.
- Precise Slave Type Conversions: Allows different types to be used on the master and slave, with automatic type promotion and demotion when using row-based replication (already possible with statement-based replication)
- Individual Log Flushing: Selectively flush server logs when using ‘FLUSH LOGS’ for greater control
- Safe logging of mixed transactions: Replicate transactions containing both InnoDB and MyISAM changes
To be better able to use replication, it helps to understand the basics about how MySQL Replication has been implemented – for example the roles of the binary and relay logs and so this is covered.
Perhaps the most useful sections are those that actually walk through using MySQL Replication – specifically these activities:
- Configuring, running and testing replication
- Migrating from traditional asynchronous replication to semisynchronous replication
- Administering & monitoring replication (including MySQL Enterprise Monitor)
- Failover and recovery
Replication is a little different when using MySQL Cluster; most significantly because you can have multiple MySQL Servers and other application nodes modifying the same database but also because the domains where MySQL Cluster has been used have required extreme High Availability and so there are Cluster-unique features such as active-active replication with conflict detection and resolution. This paper isn’t focussed on MySQL Cluster but a summary of these differences is included.
This would allow different types to be used on the master and slave, with automatic type promotion and demotion when using row-based replication (already possible with statement-based replication)