How Replication Works in MySQL?

In this article, we will discuss how Replication works in MySQL. But before discussing how it works, we must know about replication and its types.

What is Replication?

The process of copying data from one MySQL database (the source or master) to one or more MySQL databases (the replicas or slaves) is known as replication in MySQL. We can make redundant copies of our data for several uses, including data backup, failover, and load balancing. Replication in MySQL is an essential feature for increasing database availability, dependability, and performance.

Types of Replication

There are several types of Replication in MySQL. Some main types of Replication are as follows:

• One-Way Replication: One or more slaves and one master are involved in one-way replication. The data moves from the master to the slaves in a single path. The most used kind of replication, serves read scalability, data redundancy, and disaster recovery functions.
• Master-Master Replication: This type of replication involves the simultaneous use of two or more MySQL servers as masters and slaves. It implies that read and write operations can be accepted by any server. It is frequently utilized for load balancing read and write operations and databases that are spread geographically.
• Group Replication: It has been added to MySQL more recently. The group replication is intended for high availability. When several servers cooperate in a group replication configuration, data consistency is preserved even in the event of a server failure. A consensus mechanism is used in group replication to control server responsibilities and synchronize data.

Replication Setup

Setting up the Master Server: We initially set up the MySQL server on the master server to allow binary logging. Additionally, we start the MySQL server with replication enabled and permit the master server(s) replication abilities.

• Setting Up Slave Server: Here, we need to set up the slave server(s) to replicate records from the master server by connecting to it. We can set other parameters, such as the replication credentials and the hostname or IP address of the master.

A key factor of replication is binary logs. Every modification made to the data at the master server is documented in them. Conversely, relay logs are utilized via slave servers. They are used to apply modifications to the slave and save the binary log events that are acquired from the master. It needs to be watched after and maintained to maintain the integrity and dependability of replication. We may also monitor the replication process and become aware of problems with the aid of tools like third-party solutions and MySQL's integrated replication tracking.

Master-Slave Relationship

In MySQL replication, the master-slave relationship is a basic concept. In a replication setup, it outlines the duties and obligations of the database servers.

Master Server

In a replication configuration, the master server is the main database server. It is essential to the procedure. The following are the main features of the master server:

• Data Source: The master server is the server where the real data is found. Every data alteration, including schema changes and INSERT, UPDATE, and DELETE operations, is done on the master.
• Binary Log: The master server keeps a chronological record of all database updates in a binary log. The replication process depends on this binary log. The modifications are represented by low-level binary data or SQL expressions.
• Replication Server ID: In a replication configuration, a unique server ID is assigned to each MySQL server. With its unique server ID, the master server is utilized to identify it to the slave servers.
• Configuration for Replication: The master server's configuration must be changed to enable replication. Configuring the server ID, replication user credentials, and binary logging are important setup variables.
• Replication Privileges: The slave servers need to be granted particular replication rights by the master server. It includes having access to the database, the ability to replicate the data, and the ability to read the binary log.

Slave Servers

The secondary servers in a replication configuration are called slave servers or duplicate servers. They acquire statistics modifications from the master and replicate them. The important functions of slave servers are as follows:

• Data Replication: The master's statistics are replicated on slave servers. They reproduce the information to make sure it is steady with the records on the master.
• Binary Log and Relay Log: Binary logs and relay logs are the 2 log codes utilized by slave servers. The events are recorded within the relay go browsing the slave, and the I/O thread of the slave reads the binary go online the master. The relay log's modifications are ultimately applied to the nearby database via the SQL thread.

Replication Process

Data Modification on the Master Server

The modifications made to the data on the master server initiate the replication process. Any data-changing operations, INSERT, UPDATE, and DELETE, can be included in these activities.

Recording Changes in Binary Log

A data modification is documented in the binary log as soon as it is carried out on the master server. A chronological record of all database modifications can be found in the binary log. These modifications are represented by a series of SQL statements or low-level binary data.

It is essential for replication because the binary log is the source of verification for data changes. An event is recorded in the binary log for every update to the data.

Slave Requests Data

• The slave server(s) periodically establish a connection with the master server to obtain a copy of the binary log. The credentials of the MySQL replication user are used to establish this connection.
• The most recent binary log location processed by the slave server is communicated to the master. This location, which is sometimes called the "position" or the "log position", serves as a unique identifier for each event that occurs in the binary log that shows these modifications.
• It is essential for replication because the binary log is the source of truth for data changes. An event is recorded in the binary log for every update to the data.

Sending Binary Log Events to the Slave

• The master server starts delivering binary log events to the slave when it receives a connection request from the slave. It begins sending events from the slave-specified position.
• The data modification statements or binary data produced by the master's modifications are contained in the binary log events. The slave receives these experiences one after the other.

Applying Binary Log Events on the Slave

• After receiving the binary log activities, the slave server logs them locally in a relay log. The events are stored in an intermediate layout on this relay log. The series wherein the activities had been received from the master is preserved.

To apply those activities to the local database, the slave's two primary threads collaborate:

• I/O Thread (Replication I/O Thread): This thread is responsible for studying events from the master's binary log and writing them to the slave's relay log.
• Replication SQL Thread (SQL Thread): This thread reads events from the relay log and applies binary records to the local database or runs SQL operations. By doing this, the slave server's records are stored in sync with the master.

Replication Filters and Delay

Replication filters can be set up on slave servers to selectively reflect precise databases or tables. It permits us to manipulate which records are replicated to the slave.

One way to add a temporal delay to the replication process is to enable delayed replication on the slave. It can help stop unintentional or malicious data modifications from the master from spreading right away.

Setting up Replication Filters

• Enabling Delayed Replication

Monitoring and Troubleshooting

It must be continuously monitored to ensure the replication process is reliable and healthy. A number of MySQL utilities and functions, like SHOW SLAVE STATUS, offer helpful metrics and information about the replication status, including the slave log position and error messages.

Troubleshooting is required to find and fix problems that could cause the replication process to malfunction in the event of replication faults. Schema inconsistencies, network difficulties, and data conflicts are typical challenges.

• Checking Replication Status:

• Troubleshooting Replication:

Security Considerations

Using SSL for data encryption between the master and slave servers is essential for safe replication. Furthermore, set up network security and firewalls to allow authorized hosts to be the only hosts with access to the replication ports.

Advantages of Replication

There are several advantages of the Replication. Some main advantages of the Replication are as follows:

• High Availability

A major advantage of MySQL replication is increased high availability. We may ensure that our data is backed up by keeping one or more slave servers. One of the slaves might be elevated to the position of master in the event that the master server malfunctions, reducing downtime and guaranteeing ongoing database operation.

• Load Balancing

We can spread out read traffic over several slave servers by using replication. It is very helpful for applications with heavy reading workloads. The master server can handle write tasks by shifting read operations to the slaves, which improves overall database performance and response times.

• Data Redundancy

Redundancy in data is provided by replication. Every slave server keeps a synchronized copy of the master's data. This redundancy protects against unintentional data deletion, software bugs, and device malfunctions that might cause data loss. It ensures that we have a current, dependable duplicate of our data.

• Scalability

Horizontal scaling is supported by replication. We can add more slave servers to manage additional read traffic or to support more users. This scalability is affordable and aids in meeting the application's expanding requirements.

• Consistency and Data Integrity

Conflicts may arise among master-master replications systems when two servers try to update the same data at the same moment. MySQL contains functions for recognizing this problem, e.g., auto-increment, offsets, row-based replication, etc.