"Dark Horse MySQL" Four, Master-Slave Replication

May 21, 202545

AI Translation

This post is translated from Chinese into English through AI.View Original

AI-generated summary

Master-slave replication involves transferring DDL and DML operations from the master database to slave servers via binary logs (binlog), allowing the slave to execute these logs to maintain data synchronization. **Advantages:** - Failover to the slave if the master crashes. - Separation of read and write operations. - The slave can perform backups while still allowing reads, even with global locks. **Process:** 1. The master records data changes (row or statement) in the binlog upon transaction commit. 2. The slave reads the binlog and writes it to its relay log. 3. The slave replays the relay log events to achieve consistency with the master. **Configuration:** - The document includes server configuration details for both the master and slave databases. - The slave can be set to read-only for regular users, but super users can still modify data unless restricted by specific settings.

Overview#

Master-slave replication refers to the process of transferring DDL and DML operations from the master database to the slave server through binary logs (binlog), and then re-executing these logs on the slave, also known as redo, to keep the data in sync between the master and slave.

Advantages:

In case of master failure, it can switch to the slave.
Read-write separation.
The slave can perform backups, and can still read even with a global lock.

image.png|500

Principle#

Process:

When the master processes a transaction commit, it records data changes (row or statement - DDL, DML) to the binary log file (binlog).
The slave reads the master’s binary log file (binlog) and writes it to the slave's relay log.
The slave redoes the events in the relay log to achieve consistency between master and slave.

image.png|500

Setup#

Server Configuration#

image.png|500

Master Configuration#

image.png|500

Slave Configuration#

Read-only applies only to ordinary users; users with super privileges can still perform insert, delete, and update operations. Super privileges can be disabled by setting super-read-only = 1 or super_read_only = 1.

image.png|500

This article was synchronized and updated to xLog by Mix Space. The original link is https://blog.0xling.cyou/posts/mysql/mysql-4