Replication enables data from one MySQL server to be replicated on one or more other MySQL servers. Replication is mostly used as scale-out solution. In such a solution, all writes and updates take place on the master server, while reads take place on one or more slaves. This model is actually known as master-slave replication and this is the kind of replication that I will be setting up in this post.
One of the most asked questions is how to setup MySQL replication. The MySQL Reference Manual provides a good Replication How To as a starting guide on MySQL Replication 101.
MySQL replication has many uses including read scalability, backups, failover, online maintenance, upgrade testing and verification, software upgrades, alternative data or structure for performance queries and even benchmarking to name the popular uses.
When reviewing an installation of MySQL replication I use the following as part of my checklist of steps used for confirming your replication environment is operational.
- server-id – Replication will not work without this correctly set and unique
- log-bin – Pre-requisite for working replication
- max_binlog_size …
wrote recently about why
SHOW SLAVE STATUS is really not a
good way to monitor how far behind your slave servers are,
and how slave network timeouts can mess up the slave
lag. I'd like to chime in and say this is exactly why I
Cole's MySQL Heartbeat script was such a natural fit for
the MySQL Toolkit. It measures slave lag in a "show me the money"
way: it looks for the effects of up-to-date replication,
rather than asking the slave how far behind it thinks it is.
The slave doesn't even need to be running. In …[Read more]