Master && Slaves

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. https://clusterengine.me/advantages-of-mysql-server-replication/ MySQL replication happens as close to real-time as possible, as limited by disk and network I/O. The slaves open a socket to the master, which is kept open. When a transaction occurs on the master, it gets recorded in the binlog, and is simply replayed on the slave(s). If the socket between master and slave is interrupted, the binlog is replayed for the slave upon the next successful connection. Multi-master replication does the same thing, but in both directions. Some basic calculations will assist you in making a better determination of your bandwidth needs. Average transaction size * number of slaves * updates/minute = bandwidth needed Hope this helps. https://serverfault.com/questions/30605/how-fast-is-mysql-replication