第6章：高可用与复制原理

binlog_format = ROW

rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 5000

gtid_mode = ON
enforce_gtid_consistency = ON

CHANGE MASTER TO MASTER_DELAY = 600;  # 延迟 10 分钟

-- 查看 Binlog 相关配置
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

-- 查看当前 Binlog 文件
SHOW BINARY LOGS;

-- 查看当前 Binlog 位置
SHOW MASTER STATUS;

-- 1. 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 2. 查看主库状态
SHOW MASTER STATUS;
-- 记录 File 和 Position 值

-- 1. 配置主库信息
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- 2. 启动复制
START SLAVE;

-- 3. 查看复制状态
SHOW SLAVE STATUS\G

-- 主库配置
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- 从库配置
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION = 1;

START SLAVE;
SHOW SLAVE STATUS\G

-- 主库配置
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 = 5000;

-- 从库配置
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE;
START SLAVE;

-- 查看半同步状态
SHOW STATUS LIKE 'Rpl_semi_sync%';

-- 配置延迟复制
CHANGE MASTER TO MASTER_DELAY = 600;  # 延迟 10 分钟

-- 查看延迟状态
SHOW SLAVE STATUS\G
-- 查看 Seconds_Behind_Master 字段

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 关注字段：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0
-- Last_IO_Error: 
-- Last_SQL_Error: 

-- 查看复制延迟
SELECT 
    MASTER_LOG_FILE,
    READ_MASTER_LOG_POS,
    RELAY_LOG_FILE,
    RELAY_LOG_POS,
    SLAVE_IO_RUNNING,
    SLAVE_SQL_RUNNING,
    SECONDS_BEHIND_MASTER
FROM performance_schema.replication_connection_status;

-- 1. 停止从库复制
STOP SLAVE;

-- 2. 确保从库数据最新
-- 等待 Seconds_Behind_Master = 0

-- 3. 从库提升为主库
RESET MASTER;
SET GLOBAL read_only = 0;

-- 4. 原主库配置为从库
CHANGE MASTER TO
    MASTER_HOST='192.168.1.101',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION = 1;

START SLAVE;

-- 使用 pt-table-checksum 工具校验数据一致性
-- 需要安装 Percona Toolkit

-- 命令行执行：
pt-table-checksum --host=192.168.1.100 --user=root --password=password --databases=mysql_learning

-- 查看校验结果
pt-table-sync --host=192.168.1.100 --user=root --password=password --databases=mysql_learning --print

-- 主库配置（写操作）
-- my.cnf 配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
rpl_semi_sync_master_enabled = 1

-- 从库配置（读操作）
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
rpl_semi_sync_slave_enabled = 1
read_only = 1

-- 写操作路由到主库
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);

-- 读操作路由到从库
SELECT * FROM orders WHERE user_id = 1;

-- 使用中间件（如 ProxySQL）自动路由
-- 配置读写分离规则

-- 1. 主库配置
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- 2. 同机房从库配置
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION = 1;

-- 3. 跨机房从库配置
CHANGE MASTER TO
    MASTER_HOST='10.0.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_password',
    MASTER_AUTO_POSITION = 1;

-- 4. 配置延迟复制（防误操作）
CHANGE MASTER TO MASTER_DELAY = 3600;  # 延迟1小时

-- 节点1配置
[mysqld]
server-id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "192.168.1.101:33061"
loose-group_replication_group_seeds = "192.168.1.101:33061,192.168.1.102:33061,192.168.1.103:33061"
loose-group_replication_bootstrap_group = off

-- 初始化集群
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- 其他节点加入集群
START GROUP_REPLICATION;

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 关键指标说明
-- Seconds_Behind_Master: 复制延迟秒数
-- Last_IO_Error / Last_SQL_Error: 错误信息
-- Relay_Log_Space: 中继日志累计大小
-- Master_Log_File: 主库当前日志文件
-- Read_Master_Log_Pos: 已读取的主库位置
-- Exec_Master_Log_Pos: 已执行的主库位置

-- 查看复制延迟详情
SELECT 
    CHANNEL_NAME,
    SOURCE_HOST,
    SOURCE_PORT,
    SLAVE_IO_RUNNING,
    SLAVE_SQL_RUNNING,
    SLAVE_LAG,
    SLAVE_DELAY
FROM performance_schema.replication_connection_status;

-- 查看复制性能统计
SELECT 
    CHANNEL_NAME,
    COUNT_TRANSACTIONS_RETRIES,
    COUNT_DUPLICATE_KEYS,
    COUNT_SLAVE_DELAY
FROM performance_schema.replication_applier_status;

-- 启用 GTID
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- 配置 GTID 复制
CHANGE MASTER TO
    MASTER_HOST = 'master.example.com',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_AUTO_POSITION = 1;

-- 查看 GTID 状态
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G

-- GTID 恢复实验
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;

-- 配置一致性等级
SET GLOBAL group_replication_consistency = 'BEFORE_AND_AFTER';

-- 一致性等级说明
-- EVENTUAL: 最终一致性（默认）
-- BEFORE_ON_PRIMARY_FAILOVER: 主节点故障前一致性
-- AFTER: 主节点故障后一致性
-- BEFORE_AND_AFTER: 主节点故障前后一致性

-- 查看组成员状态
SELECT 
    CHANNEL_NAME,
    MEMBER_ID,
    MEMBER_HOST,
    MEMBER_PORT,
    MEMBER_STATE,
    MEMBER_ROLE
FROM performance_schema.replication_group_members;

-- 查看组成员统计
SELECT 
    CHANNEL_NAME,
    MEMBER_ID,
    COUNT_TRANSACTIONS_IN_QUEUE,
    COUNT_TRANSACTIONS_CHECKED,
    COUNT_CONFLICTS_DETECTED,
    COUNT_TRANSACTIONS_ROWS_VALIDATING
FROM performance_schema.replication_group_member_stats;

-- 配置网络延迟容忍度
SET GLOBAL group_replication_flow_control_mode = 'QUOTA';
SET GLOBAL group_replication_flow_control_certifier_threshold = 25000;
SET GLOBAL group_replication_flow_control_applier_threshold = 25000;

-- 配置超时参数
SET GLOBAL group_replication_communication_debug_options = 'GCS_DEBUG_BASIC';
SET GLOBAL group_replication_communication_max_message_size = 10485760;

-- 自动故障转移配置
SET GLOBAL group_replication_autorejoin_tries = 3;
SET GLOBAL group_replication_autorejoin_interval = 60;

-- 手动故障转移
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;

-- 检查故障转移状态
SELECT * FROM performance_schema.replication_group_members 
WHERE MEMBER_STATE != 'ONLINE';

-- 启用多线程复制
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

-- 配置并行复制参数
SET GLOBAL slave_preserve_commit_order = ON;
SET GLOBAL slave_transaction_retries = 128;

-- 配置复制过滤
# 主库配置
[mysqld]
binlog-do-db = mysql_learning
binlog-ignore-db = test

# 从库配置
[mysqld]
replicate-do-db = mysql_learning
replicate-ignore-db = test
replicate-wild-do-table = mysql_learning.orders_%

-- 创建复制延迟监控视图
CREATE VIEW replication_lag_monitor AS
SELECT 
    CHANNEL_NAME,
    SOURCE_HOST,
    SLAVE_LAG,
    CASE 
        WHEN SLAVE_LAG > 300 THEN 'CRITICAL'
        WHEN SLAVE_LAG > 60 THEN 'WARNING'
        ELSE 'OK'
    END AS status
FROM performance_schema.replication_connection_status;

-- 查询延迟状态
SELECT * FROM replication_lag_monitor;