What is MySql Replication?
Replication is a basic feature of MySQL server. Its allow us to create replica of any database from one Mysql node to other MySql node or multiple MySql nodes.
Why we need Replications?
In order to create redundant environment on any server architecture we require to create runtime replica of database server. Mysql server is treated as backbone of most of website and web services and is most dynamic part in any live system. Mysql Replication means creating live image of any running mysql server and restoring it on another slave mysql server after any change in master database server that can be any insert, update, delete or any other operation. In other words it can be treated as maintaining state of one mysql server on another slave mysql server this can be useful in mission critical applications where data mirroring is required for backup purpose of for load balancing on primary or secondary mysql server.
Different kind of replication in Mysql Server.
1) Master To Slave Replication
In master to slave replication we need two MySql nodes, one will become master node and second will be slave node. All write operations are performed by master and read operations can be split between the master and slave. Master to slave most popular replication in Mysql server.
2) Master To Master Replication
In master to Master replication we need two MySql nodes and both will be master and slave to each other. Both nodes will perform read/write operation. Master to master replications provides high availability in the case of one node goes down then you can use second node as backup.
3) Master To Multiple Slaves Replication
In master to multiple salves replications multiple salves attached to one master server. All write operations are performed by master and read operations can be split between the master and slaves nodes.
4) Master To Slave To Slave Replication
In master to slave to slave replication, multiple slaves are attached in hierarchical order. Here one slave node act as master and slave for other slave nodes. But all writes operations are made to the primary master node.
5) Multi-Master Ring Replication
In multi-master ring replication, multiple Mysql nodes are attached in ring order and all nodes are master and slave to others. All nodes have grant access to reads and writes operation.
Replication work flow in multiple masters:
Three nodes in multi-master ring replication.
e.g—… –> server1 –> server2 –> server3 –> server1 –> …
Four nodes in multi-master ring replication
e.g—… –> server1 –> server2 –> server3 –> server4 –> server1 –> …