Overview of replication

Replication is the technique to have more than one databases set up to serve single or multiple client applications. This means all databases participating in database replication are exactly the same as each other. Replication works by frequently copying data from one database to all other replica databases. These databases may be located on the same database server, different database servers, or different machines altogether.

MySQL replication is mostly set up for scaling reads. In any web application, the number of read operations is pretty higher compared to that of write database operations.

In a traditional MySQL replication, one of the databases acts as a master and the rest of the databases play the role of slaves. It is not always necessary that we have only one master database. We can have multiple master databases in a replication. This technique is called multi-master replication. The slaves copy data from master databases. The process of copying data is asynchronous in traditional MySQL replication. This means slave database servers are not permanently connected with master database servers. MySQL supports replication at different levels. We can replicate all master databases, selected databases, or selected tables from a master database in to slave databases.

MySQL 8 provides different database replication methods. MySQL 8 has a binary log file. The contents of the file are events describing database changes. The event can be of type statement based or row based. The changes include data definition changes and data manipulation changes or statements that can potentially modify the database such as DELETE statements. The binary log also contains information on how much time each SQL statement took to update the database. The traditional MySQL database replication method synchronizes databases from master to slaves based on the binary log file on the master database server. The slaves replicate or copy the contents of binary log file from the master database server based on the positions of log records in the file.

MySQL 8 also supports newer database replication methods based on GTID. A unique global transaction identifier (GTID) is associated with every committed transaction on the master database server. It greatly simplifies the replication process as it is not dependent on events from binary log files and their positions. GTID is represented as a pair of colon (:) separated coordinates, as shown in the following block:

GTID= source_id:transaction_id

The source_id is the identifier for database servers originated from the GTID. The transaction_id is the sequence number in which the transaction was committed on the database server. For example, the following example shows the GTID for the first committed transaction:

1A22AF74-17AC-E111-393E-80C49AB653A2:1

The sequence number for transactions committed starts with 1. It can never be 0.

A GTID-based MySQL replication method is transactional and so this is why it is more reliable than a binary log file-based replication method. GTID guarantees the replication accuracy and consistency between master and slave databases as long as all the transactions committed on master database servers have also been applied on all of the slave database servers.

MySQL 8 also supports semisynchronous synchronization methods. In semisynchronous methods of replication, any transaction committed on a master database server is blocked until the master database server receives acknowledgement from at least one of the slave database servers that it has received and logged the transaction event. Delayed replication is another replication method supported by MySQL 8. In delayed replication, slave database servers intentionally log the transaction event behind master database servers by some amount of time.

Advantages of MySQL replication

  • Scale out solutions
  • Data Security
  • Analytics
  • Long distance data distribution

results matching ""

    No results matching ""