# Single source / multiple replicas (most common read-scaling pattern)
#
#        [Source]
#        /      \
#   [Replica1] [Replica2]
#
# Writes go to Source only; reads distributed across replicas
# Each replica has server_id = 2, 3, etc.

-- GTID format: source_uuid:transaction_id_range
-- Example: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100
-- Meaning: transactions 1 through 100 from source with UUID 3E11FA47...

-- Check the GTIDs executed on this server
SHOW VARIABLES LIKE 'gtid_executed';

-- Check the GTID mode values
SHOW VARIABLES LIKE 'gtid_mode';
-- OFF          = GTID disabled
-- OFF_PERMISSIVE = Accepts both GTID and non-GTID transactions (migration step 1)
-- ON_PERMISSIVE  = Generates GTIDs, accepts both (migration step 2)
-- ON          = Full GTID mode (required for GTID replication)

-- Switching from position-based to GTID replication (online migration, MySQL 5.7.6+)
-- Step 1: source and all replicas
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
-- Step 2:
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
-- Step 3: wait for Ongoing_anonymous_transaction_count = 0
SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
-- Step 4:
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.GTID_MODE = ON;

[mysqld]
server_id                 = 1
log_bin                   = /var/log/mysql/mysql-bin.log
binlog_format             = ROW
binlog_row_image          = MINIMAL   # reduce log size while staying safe
gtid_mode                 = ON
enforce_gtid_consistency  = ON

[mysqld]
server_id                 = 2
gtid_mode                 = ON
enforce_gtid_consistency  = ON
read_only                 = ON
super_read_only           = ON   # blocks even SUPER users from writing

CREATE USER 'replicator'@'%'
  IDENTIFIED WITH caching_sha2_password BY 'str0ngP@ss!';

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

# On source — consistent dump with GTID info
mysqldump --single-transaction --set-gtid-purged=ON   --all-databases -u root -p > full_backup.sql

# Transfer to replica, then restore
mysql -u root -p < full_backup.sql

-- MySQL 8.0.23+ syntax
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST          = '192.168.1.10',
  SOURCE_PORT          = 3306,
  SOURCE_USER          = 'replicator',
  SOURCE_PASSWORD      = 'str0ngP@ss!',
  SOURCE_AUTO_POSITION = 1;    -- GTID auto-positioning (no manual file/offset needed)

START REPLICA;

SHOW REPLICA STATUSG

# my.cnf — enable multi-threaded parallel replication (MySQL 5.7+)
[mysqld]
replica_parallel_workers      = 8       # number of parallel SQL applier threads
replica_parallel_type         = LOGICAL_CLOCK  # better parallelism than DATABASE mode
replica_preserve_commit_order = ON      # maintain commit order for GTID consistency

-- Install plugin on BOTH source and replica
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

-- Enable on source
SET GLOBAL rpl_semi_sync_source_enabled = 1;
-- Fall back to async after 1 second if no replica acknowledges (prevents blocking forever)
SET GLOBAL rpl_semi_sync_source_timeout = 1000;
-- Require at least 1 replica to ack (increase for stricter durability)
SET GLOBAL rpl_semi_sync_source_wait_for_replica_count = 1;

-- Enable on replica
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

-- Restart the I/O thread to activate semi-sync
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

-- Monitor semi-sync status
SHOW STATUS LIKE 'Rpl_semi_sync%';

-- Skip a single erring event (position-based replication)
STOP REPLICA;
SET GLOBAL sql_replica_skip_counter = 1;
START REPLICA;

-- Skip a specific GTID transaction (GTID replication)
-- Find the problematic GTID from SHOW REPLICA STATUS: Last_SQL_Error_Timestamp
STOP REPLICA;
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:101';  -- the failing GTID
BEGIN; COMMIT;     -- inject an empty transaction to mark this GTID as executed
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

-- Verify replication resumed
SHOW REPLICA STATUSG

# LOGICAL_CLOCK mode: transactions that committed in overlapping binary log groups
# on the source can be applied in parallel on the replica.
# This matches the source's concurrency more accurately than DATABASE mode.
[mysqld]
replica_parallel_workers      = 8
replica_parallel_type         = LOGICAL_CLOCK
replica_preserve_commit_order = ON

-- Verify parallel workers are active
SHOW PROCESSLIST;
-- Should show multiple 'replica sql' threads

-- Monitor parallel replication efficiency
SHOW GLOBAL STATUS LIKE 'Replica_transactions_multi_threaded_retries';
-- High retries = conflicts between parallel workers; reduce workers or switch to DATABASE mode

# Application config example (Node.js / mysql2 pool cluster)
const pool = mysql.createPoolCluster();

pool.add('WRITE', {
  host: 'db-source.internal',
  user: 'app', password: 'secret', database: 'myapp'
});
pool.add('READ1', {
  host: 'db-replica-1.internal',
  user: 'app', password: 'secret', database: 'myapp'
});
pool.add('READ2', {
  host: 'db-replica-2.internal',
  user: 'app', password: 'secret', database: 'myapp'
});

# my.cnf on replica — replicate only specific databases or tables
replicate-do-db        = myapp        # Only replicate this database
replicate-ignore-db    = test         # Skip this database entirely
replicate-do-table     = myapp.orders # Only replicate this table
replicate-ignore-table = myapp.sessions  # Skip this table

-- Add a second source to an existing replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST          = '192.168.1.20',
  SOURCE_PORT          = 3306,
  SOURCE_USER          = 'replicator',
  SOURCE_PASSWORD      = 'pass',
  SOURCE_AUTO_POSITION = 1
FOR CHANNEL 'shard2';

START REPLICA FOR CHANNEL 'shard2';

-- Monitor each channel separately
SHOW REPLICA STATUS FOR CHANNEL 'shard2'G

-- Stop a specific channel
STOP REPLICA FOR CHANNEL 'shard2';

-- Most important replication health checks
SHOW REPLICA STATUSG

-- Key fields and their healthy values:
-- Replica_IO_Running:     Yes
-- Replica_SQL_Running:    Yes
-- Seconds_Behind_Source:  0 (or close to 0)
-- Last_IO_Error:          (empty)
-- Last_SQL_Error:         (empty)

-- Monitor via performance_schema (MySQL 8.0)
SELECT
  channel_name,
  service_state,
  LAST_ERROR_MESSAGE,
  LAST_ERROR_TIMESTAMP
FROM performance_schema.replication_applier_status_by_worker;

-- Compare GTID sets: what has the replica executed vs what source has committed?
-- On source:
SELECT @@GLOBAL.GTID_EXECUTED AS source_gtids;

-- On replica:
SELECT @@GLOBAL.GTID_EXECUTED AS replica_gtids;

-- Find which transactions replica is missing:
SELECT GTID_SUBTRACT(
  '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-1000',   -- source
  '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-990'     -- replica
) AS missing_gtids;
-- Returns: 3E11FA47-71CA-11E1-9E33-C80AA9429562:991-1000

-- List current binary log files and their sizes
SHOW BINARY LOGS;

-- Set automatic expiry (MySQL 8.0: binlog_expire_logs_seconds)
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 days in seconds
-- my.cnf: binlog_expire_logs_seconds = 604800

-- Manual purge: delete logs older than a specific date
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Manual purge: delete logs up to a specific file
PURGE BINARY LOGS TO 'mysql-bin.000050';

-- Check how much disk the binary logs are using
SELECT
  SUM(file_size) / 1024 / 1024 AS total_binlog_mb
FROM information_schema.FILES
WHERE file_type = 'BINARY LOG';

-- IMPORTANT: before purging, verify all replicas have consumed those logs
-- Check each replica's relay log position to ensure it is past the logs you will delete
SHOW REPLICA STATUSG  -- check Executed_Gtid_Set or Read_Master_Log_Pos