MySQL Replication 面试题

# 主库 my.cnf
[mysqld]
server_id              = 1
log_bin                = /var/log/mysql/mysql-bin.log
binlog_format          = ROW
gtid_mode              = ON
enforce_gtid_consistency = ON
sync_binlog            = 1
innodb_flush_log_at_trx_commit = 1

# 从库 my.cnf
[mysqld]
server_id              = 2
relay_log              = /var/log/mysql/relay-bin.log
read_only              = ON
log_slave_updates      = ON
gtid_mode              = ON
enforce_gtid_consistency = ON

-- 全局修改（需所有会话断开才能生效）
SET GLOBAL binlog_format = 'ROW';

-- 会话级修改（推荐测试场景）
SET SESSION binlog_format = 'ROW';

-- 验证当前格式
SHOW VARIABLES LIKE 'binlog_format';
SHOW MASTER STATUS;

-- 主库查看位点
SHOW MASTER STATUS;
-- File: mysql-bin.000003  Position: 154  Binlog_Do_DB: mydb

-- 从库配置复制
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.10',
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'repl_pass',
  MASTER_LOG_FILE = 'mysql-bin.000003',
  MASTER_LOG_POS = 154;

START SLAVE;

# 主从均需配置
[mysqld]
gtid_mode                = ON
enforce_gtid_consistency = ON
log_bin                   = /var/log/mysql/mysql-bin.log
log_slave_updates         = ON

-- 从库使用 GTID 自动定位
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.10',
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'repl_pass',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

-- 查看实例已执行 GTID 集合
SHOW MASTER STATUS;
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100

-- 从库恢复时需设置 gtid_purged
SET GLOBAL gtid_purged = '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100';

-- 实时查看从库状态
SHOW SLAVE STATUS\G
-- Retrieved_Gtid_Set / Executed_Gtid_Set

-- 1. 主库安装半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;  -- 10秒超时
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;  -- 至少1个ACK

-- 2. 从库安装半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 3. 重启从库 IO 线程使插件生效
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

[mysqld]
# 主库
plugin-load                    = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled   = 1
rpl_semi_sync_master_timeout   = 30000           # 30秒超时降级为异步
rpl_semi_sync_master_wait_for_slave_count = 2    # 至少2个从库ACK（5.7+）
rpl_semi_sync_master_wait_point = AFTER_SYNC     # 增强半同步

# 从库
plugin-load                    = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled    = 1

SHOW STATUS LIKE 'Rpl_semi_sync_master_status';     -- ON/OFF
SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';     -- 成功等待ACK次数
SHOW STATUS LIKE 'Rpl_semi_sync_master_no_tx';      -- 超时降级次数
SHOW STATUS LIKE 'Rpl_semi_sync_master_clients';    -- 半同步从库数

[mysqld]
slave_parallel_type    = LOGICAL_CLOCK
slave_parallel_workers = 16                # 建议 CPU 核数 50%~100%
slave_preserve_commit_order = ON           # 8.0 强制 ON，保证 binlog 顺序

[mysqld]
slave_parallel_type        = WRITESET
slave_parallel_workers     = 16
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = ON
transaction_write_set_extraction = XXHASH64

SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0
-- Slave_SQL_Running_State: Reading event from the relay log
-- (Seconds_Behind_Master 不准确时检查)
SELECT RECEIVED_TRANSACTION_SET, APPLIED_TRANSACTION_SET
  FROM performance_schema.replication_applier_status_by_worker;

[mysqld]
plugin_load_add                = 'group_replication.so'
group_replication_group_name   = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "192.168.1.10:33061"
group_replication_group_seeds  = "192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061"
group_replication_bootstrap_group = OFF

# 单主模式
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF

-- 1. GTID 集合差异
SELECT
  (SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status) AS received,
  (SELECT APPLIED_TRANSACTION_SET FROM performance_schema.replication_applier_status) AS applied;

-- 2. 复制性能表（8.0+）
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- 3. 监控 worker 延迟
SELECT WORKER_ID, LAST_APPLIED_TRANSACTION,
       LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,
       APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,
       (APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP -
        LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) AS lag_sec
FROM performance_schema.replication_applier_status_by_worker;

[mysqld]
# 主库
binlog_group_commit_sync_delay      = 1000   # 1ms 组提交窗口，提升从库并行机会
binlog_group_commit_sync_no_delay_count = 10
sync_binlog                          = 1

# 从库
slave_parallel_workers               = 16
slave_parallel_type                  = LOGICAL_CLOCK
slave_preserve_commit_order          = ON
read_only                            = ON
super_read_only                      = ON        # 8.0 防止误写
log_slave_updates                    = ON
innodb_flush_log_at_trx_commit       = 2        # 从库可放宽
sync_binlog                          = 0
slave_skip_errors                    = 1062,1032  # 谨慎使用

# 硬件：SSD/NVMe、万兆网络

STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 3600;   -- 滞后 1 小时
START SLAVE;

-- 查看延迟
SHOW SLAVE STATUS\G
-- SQL_Delay: 3600
-- SQL_Remaining_Delay: 2453

[mysqld]
# 延迟从库独立配置
read_only                 = ON
super_read_only           = ON
log_slave_updates         = ON
relay_log_info_repository = TABLE   # 8.0 默认 FILE，更可靠
master_info_repository    = TABLE

# 误操作后回滚流程
# 1. 主库执行 DROP TABLE
# 2. 立即 STOP SLAVE 延迟从库，暂停中继日志应用
# 3. 确认延迟从库的 SQL_Remaining_Delay 跑完已删除 event 之前
# 4. mysqldump 导出被误删对象
# 5. 恢复到主库

-- 添加复制通道
CHANGE MASTER TO
  MASTER_HOST='192.168.1.10', MASTER_USER='repl',
  MASTER_PORT=3306, MASTER_AUTO_POSITION=1
  FOR CHANNEL 'channel_north';

CHANGE MASTER TO
  MASTER_HOST='192.168.1.11', MASTER_USER='repl',
  MASTER_PORT=3306, MASTER_AUTO_POSITION=1
  FOR CHANNEL 'channel_east';

START SLAVE FOR CHANNEL 'channel_north';
START SLAVE FOR CHANNEL 'channel_east';

SHOW SLAVE STATUS FOR CHANNEL 'channel_east'\G

# Master-1
auto_increment_increment = 2
auto_increment_offset    = 1

# Master-2
auto_increment_increment = 2
auto_increment_offset    = 2

# 优雅主从切换（计划内）
orchestrator -c graceful-master-takeover -alias mycluster \
  -designated-master replica-2.example.com \
  -reason "patching primary"

# 发现拓扑
orchestrator -c discover -i mycluster

# 强制故障切换
orchestrator -c force-master-failover -alias mycluster \
  -reason "master unreachable"

# 3 节点 Raft 集群
node1: orchestrator -c raft -raft-leader
node2: orchestrator -c raft
node3: orchestrator -c raft

# 后端 MySQL 存储元数据
OrchestratorDB: orchestrator_meta (clusters, topology, recovery)

# 全量校验所有从库
pt-table-checksum \
  --host=master.example.com \
  --user=checksum_user \
  --password='xxx' \
  --replicate=percona.checksums \
  --replicate-check-only      # 仅检查，不写入

# 仅校验指定表
pt-table-checksum \
  --host=master.example.com \
  --user=checksum_user \
  --password='xxx' \
  --databases=mydb \
  --tables=orders,users \
  --chunk-size=1000 \
  --threads=4

# 报告差异
pt-table-checksum --replicate-check-only \
  --replicate=percona.checksums \
  --host=master.example.com

# 1. 打印修复 SQL（不执行）
pt-table-sync \
  --execute \
  --print \
  --sync-to-master \
  h=replica.example.com,P=3306,u=checksum_user,p='xxx' \
  --databases=mydb \
  --tables=orders

# 2. 同步方向：主库 -> 从库
pt-table-sync \
  --execute \
  --sync-to-master \
  h=replica.example.com,u=sync_user,p='xxx' \
  --databases=mydb \
  --tables=orders

# 3. 仅打印 SQL 供审核
pt-table-sync \
  --print \
  --sync-to-master \
  h=replica.example.com,u=sync_user,p='xxx' \
  --databases=mydb \
  --tables=orders > fix.sql

-- 排查步骤
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 7200
-- Slave_SQL_Running_State: Waiting for dependent transaction to be committed
-- (常见提示)

-- 1. 查大事务
SELECT * FROM mysql.slow_log
WHERE sql_text LIKE '%UPDATE%' AND rows_examined > 1000000
ORDER BY start_time DESC LIMIT 5;

-- 2. 查 worker 状态（8.0）
SELECT * FROM performance_schema.replication_applier_status_by_worker;
-- LAST_ERROR_MESSAGE / LAST_ERROR_TIMESTAMP

-- 3. 查 relay log 积压
SELECT * FROM performance_schema.replication_applier_status
WHERE REMAINING_DELAY IS NOT NULL;

-- Slave_IO_Running: Yes
-- Slave_SQL_Running: No
-- Last_SQL_Error: Could not execute Write_rows event;
--   Duplicate entry '12345' for key 'PRIMARY'

-- 1. 跳过单条错误（GTID 模式）
STOP SLAVE;
SET GTID_NEXT = '3E11FA47-71CA-11E1-9E33-C80AA9429562:12345';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

-- 2. File-Position 模式
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 3. 彻底修复（推荐）
pt-table-sync --execute --sync-to-master h=replica,u=sync_user,p='xxx' \
  --databases=mydb --tables=orders

-- 主库 error log
[ERROR] Binlog dump thread is killed because it is not able to send binlog
  events to the slave. Error: Read timeout

[mysqld]
# 主从都需配置
slave_net_timeout        = 30           # 缩短超时
# 主库
binlog_cache_size         = 4M
max_binlog_size           = 1G
# 从库
master_heartbeat_period   = 10          # 心跳周期
master_retry_count        = 86400       # 重试次数

-- 从库 Executed_Gtid_Set 包含主库不存在的 GTID
-- 常见于：主从切换后从库"跨代"恢复

-- 1. 备份主库现有数据
mysqldump --single-transaction --master-data=2 --triggers --routines \
  --events --all-databases > full_backup.sql

-- 2. 从库重置 GTID 集合
RESET MASTER;
SET GLOBAL gtid_purged = '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-1000';

-- 3. 重新建立复制
CHANGE MASTER TO MASTER_AUTO_POSITION=1, ...;
START SLAVE;

-- 从集群状态查
SELECT * FROM performance_schema.replication_group_members\G
-- MEMBER_STATE: ERROR / UNREACHABLE

# 查看网络端口 33061 是否可达
nc -zv node2 33061

# 查看 group_replication_local_address 配置
SHOW VARIABLES LIKE 'group_replication_local_address';

# 查看本节点 GR 日志
tail -f error.log | grep -i "group_replication"

-- 失联节点重加入
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_recovery_get_public_key = 1;
START GROUP_REPLICATION;

-- 或重置后重新加入集群
RESET MASTER;
SET GLOBAL group_replication_allow_local_disjoint_gtids_join = 1;
START GROUP_REPLICATION;