-- 查看复制格式
SHOW VARIABLES LIKE 'binlog_format';

-- 推荐使用 ROW 格式
SET GLOBAL binlog_format = 'ROW';

# my.cnf - 主库
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

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

-- 查看主库状态
SHOW MASTER STATUS;
-- +------------------+----------+--------------+
-- | File             | Position | Binlog_Do_DB |
-- +------------------+----------+--------------+
-- | mysql-bin.000003 | 785      |              |
-- +------------------+----------+--------------+

# my.cnf - 从库
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = ON
super-read-only = ON

-- 配置主库连接信息（MySQL 8.0.23+）
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.100',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'Repl!Str0ngPass',
    SOURCE_LOG_FILE = 'mysql-bin.000003',
    SOURCE_LOG_POS = 785;

-- 启动复制
START REPLICA;

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

SHOW REPLICA STATUS\G

-- 重点关注：
-- Replica_IO_Running: Yes     ← I/O 线程是否运行
-- Replica_SQL_Running: Yes    ← SQL 线程是否运行
-- Seconds_Behind_Source: 0    ← 复制延迟（秒）
-- Last_Error:                 ← 最后错误信息
-- Retrieved_Source_Log_File   ← 已获取的主库 Binlog 文件
-- Executed_Source_Log_File    ← 已执行的主库 Binlog 文件

-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = ON;
SET GLOBAL rpl_semi_sync_source_timeout = 5000;  -- 超时5秒降级为异步

-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = ON;

-- 重启复制
STOP REPLICA;
START REPLICA;

# my.cnf - 主库和从库都需要
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log-bin = mysql-bin
server-id = 1  # 每个实例不同

-- 从库配置 GTID 复制
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.100',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'Repl!Str0ngPass',
    SOURCE_AUTO_POSITION = 1;  -- 使用 GTID 自动定位

START REPLICA;

-- 查看延迟
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source

-- 更精确的延迟监控（基于心跳）
-- 主库设置
SET GLOBAL rpl_semi_sync_source_timeout = 5000;

-- 查看延迟
SELECT * FROM performance_schema.replication_connection_status;
SELECT * FROM performance_schema.replication_applier_status_by_worker;

-- 1. 使用多线程复制（MySQL 5.7+）
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';  -- 5.7+
SET GLOBAL replica_parallel_workers = 4;  -- 8.0+
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';

-- 2. 从库配置
[mysqld]
replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON

-- 设置从库延迟 1 小时执行（用于数据误操作恢复）
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;

-- 如果主库误删数据，可以在延迟从库上找到数据
STOP REPLICA;
START REPLICA UNTIL SQL_BEFORE_GTIDS = 'gtid_to_stop_before';

-- 1. 确认从库已追上主库
SHOW REPLICA STATUS\G  -- Seconds_Behind_Source = 0

-- 2. 主库设为只读
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

-- 3. 从库停止复制，设为可写
STOP REPLICA;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

-- 4. 其他从库指向新主库
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'new_master_ip',
    SOURCE_AUTO_POSITION = 1;
START REPLICA;

-- ProxySQL 配置（简化示例）
-- 定义后端 MySQL 实例
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
(10, '192.168.1.100', 3306),  -- 写组
(20, '192.168.1.101', 3306),  -- 读组
(20, '192.168.1.102', 3306);  -- 读组

-- 读写分离规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 10),  -- SELECT FOR UPDATE 走主库
(2, 1, '^SELECT', 20);              -- 普通 SELECT 走从库