Replication is a powerful feature in-built in MySQL Database Server. In replication data from the primary server (Master) is copied to one or more secondary servers (Slaves). The shared data can be all databases, a subset of databases or even tables only. This data replication is asynchronous, means that, slaves need not maintain connection to master all the time to synchronize data and request the latest version of data anytime they are configured to.
Data Synchronization in Replication
Different types of synchronization can be implemented in MySQL 5.7
Asynchronous Replication
In this Master accepts all data updates/manipulations and maintains the most recent version of data, disregards of whether slaves have copied the update. Slaves independently request the latest version of data from master and update themselves.
Semi Synchronous Replication
Here the master waits after an update, until at least one server pulls the change and acknowledges this to the master.
Delayed Replication
Here slaves deliberately lag behind the master to update the data for a specified amount of time. Only after that the changes are requested and pulled from master.
Replication Formats
In Statement Based Replication (SBR), all the SQL statements that represent the entire DDL/DML operations are replicated from master to slaves. In Row Based Replication (RBR), only the changed rows are replicated.
Replication Implementation
Binary Log File Position Based Replication
In this replication, the master records all updates, deletes, etc on its binary log file, except the READs, since they do not change the database structure or data. These are called events recorded in the binary log. Slaves can request for these event data and pull them from the binary files. These events are then executed on the slave server such that the operation performed on master is exactly replayed on the slave, synchronizing the slave with the master. This synchronization is controlled by the slave and it can decide about the timing and pace of the synchronization. Thus any operation – table changes, inserts, deletes, updates, alters etc are synchronized. Each slave can pull and maintain its own local copy of binary file and marks the position upto which it has executed events. Thus it can start, stop or pause the replication execution, since the last executed event position is marked. Individual slaves connect and disconnect with master separately and master is unaffected by this binary file copying and event execution.
Replication Using Global Transaction Identifiers
In this type of transaction based replication, the replication status is controlled by Global Transaction Identifiers (GTIDs). The GTIDs are used to verify that all transactions that are committed on master are replicated as transactions on each slave. Here RBR format replication is best suitable for replicating all master transactions onto the slaves. Consistency between master and slaves are ensured through the GTIDs.
Advantages of MySQL Replication
Scale-Out
The database query requests can be distributed or scale-out to multiple servers allowing scalable database solutions. Since most web applications has more READ operations encountered when site visitors request data, compared to site users or admins periodically performs WRITEs, the replication slaves that provide response data to these READ requests make this a scale-out solution.
Performance
The critical database operation – WRITES (updates, deletes, alters, etc), always take place on master so that master’s data snapshot is the latest one. READS take place on the slaves, thus distributing the loads to slaves with a dedicated master for WRITES only. This improves performance of the system. Different databases under a master can be replicated to separate slaves to keep load on any database to the minimum and thus to improve performance.
Backup and Security
Backups, that may affect server up-time due to table locks, can be avoided by performing backups on any slave. Latest data snapshot is replicated to to a particular slave and it is backed up with needed locks. This allows full up-time because the master as well as other slaves is available for WRITES and READS respectively. Also, data corruption is also eliminated, since the master runs flawless to receive all the updates. Security is also high in this system, since data is distributed among multiple slave servers and an affected slave can be disconnected from the network to provide isolation and to enable repair without affecting master or other slaves.
Analytics and Benchmarking
These and other experiments can be performed on slaves, without affecting the up-time or data integrity of the master. Anytime, the master can re-synch a slave with the latest data snapshot.
Remote Access
A slave with copy of data can be provided as host for a remote application, without access to the master. Thus dedicated remote services can be offered with such a setup.
Conclusion
MySQL Server Replication is a great facility for building robust, scalable, distributed database systems that support low to high level and enterprise applications. When properly configured and deployed, it improves performance and efficiency multi-fold compared to standalone database servers. Thus replication is the foundation for high-end computing and grows vertical with data requirements.