MySQL Replication Primer
High Availability & Scalability with Replication in MySQL
--
Caveat — Notes on replication taken in 2017. This content is updated till MySQL 5.7 GA release.
What is Replication
Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master
MySQL
Replication is one of the most powerful features of MySQL and plays a very important part in achieving High Availability and Scalability in any database system. Besides, it also plays a very important role in disaster recovery and data safety.
Replication comes built-in with MySQL. Native replication is not the only type of replication that one can use. One can use third-party replicators, stream replicators and so on.
Setting up replication on MySQL is simple. Let’s say you have two MySQL instances running, the following snippet is what you need to set up replication given that both machines have the appropriate network access and the relevant ports are open for communication.
Snippet: Set up Basic Master-Slave Replication on MySQL
Why is Replication Needed
High Availability through Hot Standby
If a server goes down, everything stops. It is of utmost importance that at a minimum amount of resources are always available to write new data, retrieve old data and make changes to old data in the database. The easiest solution for this is to configure an extra server with the sole purpose of acting as a hot standby, ready to take over the job of the main server if that fails. If that extra server is a slave, then slave shall be promoted to master. If it’s a multi-master setup, then the story is different.
