MySQL Master‑Slave Replication: Core Architecture, GTID Setup, and Common Troubleshooting

Background and Overview

MySQL replication is the backbone of high‑availability architectures. Production incidents such as data inconsistency, replication lag, connection loss, and GTID conflicts often stem from an incomplete understanding of the replication mechanism. MySQL 9.0 (2026) adds enhanced GTID support, more efficient parallel replication, and richer monitoring interfaces.

Prerequisites

Linux command line and shell scripting

Basic MySQL operation experience

Understanding of ACID transaction properties

TCP/IP networking basics

Experimental Environment

MySQL 9.0.2 Community Edition on Rocky Linux 9 or Ubuntu 24.04 LTS

GTID replication mode

Semi‑synchronous replication using plugin mode

1. Core Replication Architecture

1.1 Architecture Overview

Replication follows a log‑based model: the master writes changes to the binary log (Binlog); the slave’s IO thread pulls the Binlog, stores it in a Relay Log, and the SQL thread replays the events.

┌─────────────────┐      ┌─────────────────┐
│   Master        │      │   Slave         │
│                 │      │                 │
│  ┌───────────┐  │      │  ┌───────────┐  │
│  │  Client   │  │      │  │  SQL      │  │
│  │  Write    │  │      │  │  Thread   │  │
│  └─────┬─────┘  │      │  └─────┬─────┘  │
│        │        │      │        │        │
│        ▼        │      │        ▼        │
│  ┌───────────┐  │      │  ┌───────────┐  │
│  │   Binlog   │──┼──────┼──│ Relaylog   │  │
│  │ (write)   │  │      │  │ (read)    │  │
│  └───────────┘  │      │  └─────┬─────┘  │
│                 │      │        │        │
│                 │      │        ▼        │
│                 │      │   ┌───────────┐│
│                 │      │   │   Data    ││
│                 │      │   │ (execute) ││
│                 │      │   └───────────┘│
└─────────────────┘      └─────────────────┘

Core Components

Binary Log (Binlog) : records all changes on the master.

IO Thread : pulls Binlog events from the master.

Relay Log : local copy of received events.

SQL Thread : replays events from the Relay Log.

1.2 Replication Types

Asynchronous (default): master commits without waiting for slaves – highest performance, possible data loss.

Semi‑synchronous : master waits for at least one slave to acknowledge receipt – balances safety and latency.

Fully synchronous (MySQL Group Replication): all slaves must acknowledge – zero data loss, higher latency.

1.3 Binlog Formats

STATEMENT : logs SQL statements; small log size but nondeterministic functions can cause inconsistencies.

ROW : logs actual row changes; highest consistency, larger log size.

MIXED (default): uses STATEMENT and switches to ROW when needed; balances performance and consistency.

For production workloads that use triggers, stored procedures, or nondeterministic functions, ROW format is recommended.

2. GTID Replication

2.1 GTID Concept

GTID (Global Transaction Identifier) uniquely identifies each transaction as source_id:transaction_id, e.g. 3E11FA47-71CA-11E1-9E33-C80AA9429562:23.

Advantages:

Automatic position tracking – no need for file/position specifications.

Simplifies master‑to‑slave switch and failover.

Facilitates consistency checks.

Supports automatic skipping of erroneous transactions.

2.2 GTID Configuration

# my.cnf
[mysqld]
# Enable GTID
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
slave_parallel_type = 'LOGICAL_CLOCK'
slave_parallel_workers = 8
slave_preserve_commit_order = ON
binlog_format = ROW
sync_binlog = 1

Key settings: enforce_gtid_consistency = ON: only GTID‑compatible statements are allowed. log_slave_updates = ON: slaves also write applied events to their own Binlog (required for chained replication). slave_parallel_workers: number of parallel apply threads – typically 50‑80 % of CPU cores.

2.3 GTID Commands

# View GTID status
SHOW MASTER STATUS\G;
SHOW SLAVE STATUS\G;
# Skip a failing transaction (GTID mode)
STOP SLAVE;
SET GTID_NEXT='source_id:transaction_id';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;
# Reset slave for re‑initialisation
RESET SLAVE ALL;
RESET MASTER;

3. Replication Threads

3.1 IO Thread

Workflow:

1. Slave connects to master
2. Slave requests Binlog position (MASTER_LOG_FILE, MASTER_LOG_POS)
3. Master streams Binlog data
4. Slave writes to Relay Log
5. Slave acknowledges received position

Important status fields from SHOW SLAVE STATUS\G include Slave_IO_Running, Master_Host, Master_Log_File, Read_Master_Log_Pos, and Last_IO_Error.

3.2 SQL Thread

1. Read next event from Relay Log
2. Parse event
3. Execute corresponding SQL on slave
4. Update Exec_Master_Log_Pos
5. Repeat until Relay Log is exhausted

3.3 Parallel Replication (MySQL 9.0)

# View current parallel settings
SHOW VARIABLES LIKE 'slave_parallel%';
# Enable LOGICAL_CLOCK parallelism
SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers=16;
SET GLOBAL slave_preserve_commit_order=ON;

3.4 Thread Monitoring

# List all replication‑related threads
SHOW PROCESSLIST;
# Example output (truncated)
-- Id | User | Host | db | Command | Time | State | Info
-- 10 | system user |  | NULL | Connect | 12345 | Waiting for source to send event | NULL
-- 11 | system user |  | NULL | Connect | 12345 | Slave has read all relay log; waiting for more updates | NULL

4. Replication Topologies

4.1 Single‑Master Single‑Slave

# Master my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

# Slave my.cnf
[mysqld]
server-id = 2
log-bin = mysql-bin
relay-log = relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON

# Create replication user on master
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

# Take a GTID‑aware backup
mysqldump -u root -p \
  --single-transaction \
  --source-data=2 \
  --routines --triggers --events --all-databases > full_backup.sql;

# Restore on slave
mysql -u root -p < full_backup.sql;

# Point slave to master (GTID auto‑position)
CHANGE MASTER TO MASTER_HOST='master.example.com',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;
START SLAVE;

4.2 One‑Master Multiple‑Slaves (Read/Write Splitting)

# ProxySQL configuration (example)
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0,'master.example.com',3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1,'slave1.example.com',3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1,'slave2.example.com',3306);

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app_user','app_pass',0);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, comment) VALUES
(1,1,'^SELECT.*FOR UPDATE',0,'Write‑preferring SELECT'),
(2,1,'^SELECT',1,'Read queries go to slaves');

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

4.3 Cascading Replication

# Master -> Slave A -> Slave B & C
# Slave A must enable log_slave_updates
[mysqld]
server-id = 2
log-bin = mysql-bin
log_slave_updates = ON
gtid_mode = ON
enforce_gtid_consistency = ON

4.4 Dual‑Master (Active‑Active)

# Server A my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
auto_increment_offset = 1
auto_increment_increment = 2

# Server B my.cnf
[mysqld]
server-id = 2
log-bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
auto_increment_offset = 2
auto_increment_increment = 2

Key considerations:

Use auto_increment_offset and auto_increment_increment to avoid primary‑key collisions.

Application‑level conflict resolution is required; prefer writing to a single master and using the other for failover.

5. Semi‑Synchronous Replication

5.1 Principle

# Master commit flow (semi‑sync)
1. Transaction committed on master
2. Binlog written
3. Master waits for at least one slave to acknowledge receipt (configurable timeout)
4. Client receives success response

Key variables: rpl_semi_sync_master_timeout (default 10000 ms) – wait timeout. rpl_semi_sync_master_wait_for_slave_count (default 1) – number of slaves required for acknowledgment.

5.2 Configuration

# Install plugins (run on both master and slave)
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

# Master settings
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;   # 10 s
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;

# Slave settings
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

# Restart IO thread to apply changes
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

# Persist in my.cnf
[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_slave_enabled = 1

5.3 Monitoring

# Master status
SHOW GLOBAL STATUS LIKE 'rpl_semi_sync%';
# Important counters
# Rpl_semi_sync_master_clients – connected semi‑sync slaves
# Rpl_semi_sync_master_no_tx – transactions not yet confirmed
# Rpl_semi_sync_master_yes_tx – confirmed transactions
# Rpl_semi_sync_master_no_times – times switched to async
# Rpl_semi_sync_master_status – ON/OFF

6. Common Fault Diagnosis

6.1 Diagnosis Workflow

1. Detect replication anomaly (monitoring alert, application write failure, SHOW SLAVE STATUS).
2. Collect diagnostic information (SHOW SLAVE STATUS\G, SHOW PROCESSLIST, SHOW GLOBAL VARIABLES, error logs).
3. Analyse error type (IO thread, SQL thread, lag, GTID conflict).
4. Apply remediation steps specific to the error, verify, and update monitoring.

6.2 Scenario 1 – IO Thread Connection Failure

Symptom

Slave_IO_Running: Connecting
Last_IO_Error: error connecting to master 'repl@master:3306' - retry-time: 60 retries: 2

Investigation Steps

# 1. Test network connectivity
ping master.example.com
nc -zv master.example.com 3306

# 2. Verify replication user privileges on master
SHOW GRANTS FOR 'repl'@'%';

Resolution

# Fix user permissions (example)
CREATE USER 'repl'@'slave.example.com' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'slave.example.com';
FLUSH PRIVILEGES;

# Update master address on slave
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='master.example.com',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;
START SLAVE;

# Check max_connections on master if connections are exhausted
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

6.3 Scenario 2 – SQL Thread Error

Symptom

Last_SQL_Error: Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: MY-001033

Investigation Steps

# View detailed error
SHOW SLAVE STATUS\G;

# Skip the failing transaction (GTID mode)
STOP SLAVE;
SET GTID_NEXT='source_id:transaction_id';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

Resolution Options

Manually reconcile data differences (e.g., pt-table-checksum + pt-table-sync).

Re‑initialize the slave from a fresh backup if inconsistency is severe.

As a last resort, temporarily disable binary logging on the slave, skip the transaction, then re‑enable logging.

6.4 Scenario 3 – Excessive Replication Lag

Symptom

Seconds_Behind_Master: 3600  # 1 hour lag

Investigation Steps

# Check slave load
SHOW PROCESSLIST;

# Examine Binlog write performance on slave
SHOW GLOBAL STATUS LIKE 'binlog%';
SHOW GLOBAL STATUS LIKE 'relay%';

# Network latency (if semi‑sync)
SELECT @@Rpl_semi_sync_master_avg_trans_packet_size / @@Rpl_semi_sync_master_transactions_waited AS avg_packet_size;

# Parallel replication status
SHOW VARIABLES LIKE 'slave_parallel%';
SELECT * FROM performance_schema.replication_applier_status_by_worker\G;

# InnoDB log wait stats
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written%';

Resolution

# Increase parallel workers
STOP SLAVE;
SET GLOBAL slave_parallel_workers = 16;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_preserve_commit_order = ON;
START SLAVE;

# Optimize slave query performance (slow‑query log)
SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

# Reduce master binlog sync frequency (if appropriate)
SET GLOBAL sync_binlog = 1000;  # fewer fsync calls

# Consider multi‑source replication to spread load
CHANGE MASTER TO MASTER_HOST='master1.example.com', ... FOR CHANNEL 'master1';

6.5 Scenario 4 – GTID Purge Error

Symptom

Got fatal error 1236 from master IO thread:
  The slave is connecting using GTID position (source_id:12345, interval_start:100),
  but the master has purged binary logs containing transactions that the slave needs.

Resolution

# Obtain current Executed_Gtid_Set from master
SHOW MASTER STATUS;

# Re‑point slave with fresh GTID position
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO MASTER_HOST='master.example.com',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_AUTO_POSITION=1;
START SLAVE;

# If the required Binlog has been purged, take a fresh dump that includes GTID info
mysqldump -u root -p \
  --single-transaction \
  --source-data=2 \
  --all-databases > backup_with_gtid.sql;
mysql -u root -p < backup_with_gtid.sql;

For GTID conflicts, manually adjust GTID_EXECUTED or use pt-table-sync after reconciling data.

7. Data Consistency Verification

7.1 pt‑table‑checksum

#!/bin/bash
PT_TABLE_CHECKSUM="/usr/bin/pt-table-checksum"
MYSQL_OPTS="--user=root --password=your_password"
# 1. Create checksum table on all databases
$PT_TABLE_CHECKSUM $MYSQL_OPTS \
  --replicate=test.checksums \
  --create-replicate-table
# 2. Run checksum on master (example tables)
$PT_TABLE_CHECKSUM $MYSQL_OPTS \
  --replicate=test.checksums \
  --databases=test \
  --tables='orders,customers,products' \
  --no-check-binlog-format
# 3. Show differences
mysql -u root -p -e "SELECT db,tbl,this_crc,master_crc,this_cnt,master_cnt,DIFFS \
FROM test.checksums WHERE DIFFS>0 OR master_crc IS NULL;"

7.2 Manual Data Comparison Script

#!/bin/bash
MASTER_HOST="master.example.com"
SLAVE_HOST="slave.example.com"
USER="root"
PASS="your_password"
DB="test"
TABLE="orders"
MASTER_DATA=$(mysql -h $MASTER_HOST -u $USER -p$PASS -N -e "SELECT COUNT(*), SUM(id), SUM(amount) FROM $DB.$TABLE;")
SLAVE_DATA=$(mysql -h $SLAVE_HOST -u $USER -p$PASS -N -e "SELECT COUNT(*), SUM(id), SUM(amount) FROM $DB.$TABLE;")
echo "Master: $MASTER_DATA"
echo "Slave:  $SLAVE_DATA"
if [ "$MASTER_DATA" == "$SLAVE_DATA" ]; then
  echo "[Consistent] Data matches"
else
  echo "[Inconsistent] Data differs"
fi

8. Failover and Recovery

8.1 Master Failover Procedure

#!/bin/bash
NEW_MASTER="slave1.example.com"
OLD_MASTER="master.example.com"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"

# 1. Verify all slaves have caught up (Seconds_Behind_Master=0)
# (implementation omitted for brevity)

# 2. Stop replication on all slaves
for SLAVE in slave1 slave2 slave3; do
  mysql -h $SLAVE -u $MYSQL_USER -p$MYSQL_PASSWORD -e "STOP SLAVE;"
done

# 3. Promote new master
mysql -h $NEW_MASTER -u $MYSQL_USER -p$MYSQL_PASSWORD -e "STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only=OFF; SET GLOBAL super_read_only=OFF;"

# 4. Re‑configure remaining slaves to point to the new master
mysql -h slave2 -u $MYSQL_USER -p$MYSQL_PASSWORD -e "CHANGE MASTER TO MASTER_HOST='$NEW_MASTER', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1; START SLAVE;"

# 5. Update application connection strings to use $NEW_MASTER

8.2 Slave Recovery Procedure

#!/bin/bash
SLAVE_HOST="slave2.example.com"
MASTER_HOST="master.example.com"
BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"

# 1. Stop replication on the faulty slave
mysql -h $SLAVE_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "STOP SLAVE;"

# 2. Take a fresh dump from master (includes GTID)
mysqldump -h $MASTER_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD \
  --single-transaction --source-data=2 --all-databases > $BACKUP_DIR/full_backup.sql

# 3. Restore dump on the slave
mysql -h $SLAVE_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD < $BACKUP_DIR/full_backup.sql

# 4. Re‑configure replication
mysql -h $SLAVE_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST='$MASTER_HOST', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1; START SLAVE;"

# 5. Verify status
sleep 5
mysql -h $SLAVE_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"

9. Monitoring Scripts Collection

9.1 Comprehensive Monitoring Script

#!/bin/bash
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
ALERT_EMAIL="[email protected]"
LOG_FILE="/var/log/mysql/replication_monitor.log"

echo "$(date '+%Y-%m-%d %H:%M:%S') - Replication check" >> $LOG_FILE

SLAVE_STATUS=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G" 2>/dev/null)
if [ -z "$SLAVE_STATUS" ]; then
  echo "This server is not a slave" >> $LOG_FILE
  exit 0
fi

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')
LAST_ERROR=$(echo "$SLAVE_STATUS" | grep "Last_Error:" | cut -d':' -f2-)
LAST_IO_ERROR=$(echo "$SLAVE_STATUS" | grep "Last_IO_Error:" | cut -d':' -f2-)

echo "IO thread: $IO_RUNNING" >> $LOG_FILE
echo "SQL thread: $SQL_RUNNING" >> $LOG_FILE
echo "Lag: ${SECONDS_BEHIND}s" >> $LOG_FILE

ALERT_MSG=""
if [ "$IO_RUNNING" != "Yes" ]; then
  ALERT_MSG+="[CRITICAL] IO thread not running: $LAST_IO_ERROR
"
fi
if [ "$SQL_RUNNING" != "Yes" ]; then
  ALERT_MSG+="[CRITICAL] SQL thread not running: $LAST_ERROR
"
fi
if [ -n "$SECONDS_BEHIND" ] && [ "$SECONDS_BEHIND" != "NULL" ]; then
  if [ $SECONDS_BEHIND -gt 300 ]; then
    ALERT_MSG+="[CRITICAL] Replication lag >5 min ($SECONDS_BEHIND s)
"
  elif [ $SECONDS_BEHIND -gt 60 ]; then
    ALERT_MSG+="[WARNING] Replication lag >1 min ($SECONDS_BEHIND s)
"
  fi
fi

if [ -n "$ALERT_MSG" ]; then
  echo -e "$ALERT_MSG" >> $LOG_FILE
  # Uncomment to send email alerts
  # echo -e "$ALERT_MSG" | mail -s "MySQL Replication Alert" $ALERT_EMAIL
fi

echo "Check completed" >> $LOG_FILE

9.2 Lag‑Only Monitoring Script

#!/bin/bash
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
LAG=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -N -e "SHOW SLAVE STATUS" 2>/dev/null | awk '{print $40}')
if [ -z "$LAG" ] || [ "$LAG" == "NULL" ]; then
  echo "[ERROR] Unable to obtain lag or this instance is not a slave"
  exit 2
fi

echo "Current lag: ${LAG}s"
if [ $LAG -gt 300 ]; then
  echo "[CRITICAL] Lag >5 min"
  exit 2
elif [ $LAG -gt 60 ]; then
  echo "[WARNING] Lag >1 min"
  exit 1
else
  echo "[OK] Lag within acceptable range"
  exit 0
fi

10. Best‑Practice Summary

10.1 Recommended Configuration

# Master my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
max_connections = 2000
innodb_flush_log_at_trx_commit = 1

# Slave my.cnf
[mysqld]
server-id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON
read_only = ON
super_read_only = ON
slave_skip_errors = ddl_exist_errors

10.2 Operational Checklist

# Daily checks
SHOW SLAVE STATUS\G;
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'rpl%';

# Weekly checks
SELECT * FROM performance_schema.replication_applier_status_by_worker\G;
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits%';

# Monthly checks
pt-table-checksum --replicate=test.checksums ...
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(),INTERVAL 7 DAY);
SHOW MASTER STATUS;

10.3 Common Issues Quick Reference

IO thread stuck in Connecting – check network connectivity, firewall, and replication user privileges.

SQL thread error – examine Last_SQL_Error, use pt-table-sync or re‑initialize the slave.

High replication lag – analyze slave load, increase slave_parallel_workers, tune queries, or adjust sync_binlog on master.

GTID purge error – obtain current GTID set from master, reset slave, and re‑configure with MASTER_AUTO_POSITION=1 or restore from a fresh dump.

Dual‑master conflict – use auto_increment_offset / auto_increment_increment, limit writes to a single master, and resolve conflicts at the application layer.

10.4 Important Parameters

sync_binlog = 1 – forces Binlog flush on each transaction (data safety).

gtid_mode = ON – enables GTID replication.

enforce_gtid_consistency = ON – ensures only GTID‑compatible statements are used.

slave_parallel_workers – set to 50‑80 % of CPU cores for optimal parallel apply.

slave_preserve_commit_order = ON – maintains transaction commit order.

read_only / super_read_only = ON – enforce read‑only mode on slaves.

relay_log_purge = ON – automatically delete old Relay Logs.

11. Final Takeaways

11.1 Core Points Recap

Replication copies Binlog events from master to slave via dedicated IO and SQL threads.

GTID simplifies position tracking and enables automatic failover.

Select replication mode (asynchronous, semi‑synchronous, GTID) based on latency tolerance and data‑loss risk.

IO thread failures usually stem from network or authentication issues; SQL thread errors often indicate data inconsistency.

Continuously monitor Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master, and error fields to detect problems early.

11.2 Further Learning

MySQL 9.0 official documentation – Replication chapter.

“High Performance MySQL” (4th ed.) – deep dive into replication internals.

MySQL Server Blog – latest performance and feature articles.

Practice by simulating failure scenarios, running the provided scripts, and using pt-table-checksum / pt-table-sync for ongoing data‑consistency verification.