MySQL主从架构原理、优化与实践指南全解析

-- 架构示意图
master (主库) → binary log → relay log → slave (从库)
    ↓
write operations          read operations

-- 查看当前binlog格式
show variables like 'binlog_format';

-- 三种格式对比
-- 1. statement: 记录sql语句
-- 2. row: 记录行数据变更(推荐)
-- 3. mixed: 混合模式

-- master端配置示例
-- my.cnf配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = row
expire_logs_days = 7
max_binlog_size = 100m
sync_binlog = 1

-- 创建复制用户
create user 'repl'@'%' identified by 'repl123456';
grant replication slave on *.* to 'repl'@'%';

-- 查看master状态
show master status;
/*
+------------------+----------+--------------+------------------+
| file             | position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
*/

-- my.cnf配置
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read_only = 1
log_slave_updates = 1

-- 配置复制链路
change master to
    master_host = 'master_host',
    master_user = 'repl',
    master_password = 'repl123456',
    master_port = 3306,
    master_log_file = 'mysql-bin.000001',
    master_log_pos = 107,
    master_connect_retry = 60,
    master_heartbeat_period = 10;

-- 启动复制
start slave;

-- 查看复制状态
show slave status\g
/*
slave_io_running: yes      # i/o线程状态
slave_sql_running: yes     # sql线程状态
seconds_behind_master: 0   # 复制延迟秒数
last_io_error:             # 最后io错误
last_sql_error:            # 最后sql错误
*/

-- 监控指标查询
-- 磁盘io性能
show global status like 'innodb_data_%';

-- 网络延迟检测
-- master执行
select now();
-- slave执行对比时间差

-- cpu负载
show processlist;

-- 关键参数检查
show variables like '%sync_binlog%';  -- 建议=1
show variables like '%innodb_flush_log_at_trx_commit%'; -- 建议=1
show variables like '%slave_parallel_workers%'; -- 并行复制
show variables like '%slave_parallel_type%';    -- logical_clock

-- 创建延迟监控表
create table replication_monitor (
    id bigint auto_increment primary key,
    check_time datetime default current_timestamp,
    slave_host varchar(50),
    seconds_behind_master int,
    slave_io_running varchar(3),
    slave_sql_running varchar(3),
    last_io_error text,
    last_sql_error text,
    key idx_check_time (check_time)
);

-- 监控存储过程
delimiter $$
create procedure monitor_replication_lag()
begin
    declare v_seconds_behind_master int;
    declare v_slave_io_running varchar(3);
    declare v_slave_sql_running varchar(3);
    declare v_last_io_error text;
    declare v_last_sql_error text;
    
    select 
        seconds_behind_master,
        slave_io_running,
        slave_sql_running,
        last_io_error,
        last_sql_error
    into 
        v_seconds_behind_master,
        v_slave_io_running,
        v_slave_sql_running,
        v_last_io_error,
        v_last_sql_error
    from performance_schema.replication_applier_status_by_worker
    where channel_name = '';
    
    insert into replication_monitor 
    (slave_host, seconds_behind_master, slave_io_running, 
     slave_sql_running, last_io_error, last_sql_error)
    values 
    (@@hostname, v_seconds_behind_master, v_slave_io_running,
     v_slave_sql_running, v_last_io_error, v_last_sql_error);
end$$
delimiter ;

-- 创建定时事件
create event if not exists monitor_replication_event
on schedule every 30 second
do call monitor_replication_lag();

-- mysql 5.7+ 并行复制配置
stop slave;
set global slave_parallel_type = 'logical_clock';
set global slave_parallel_workers = 8;  -- 根据cpu核心数调整
start slave;

-- 查看并行复制状态
show variables like 'slave_parallel%';
select * from performance_schema.replication_applier_status_by_worker;

-- 1. 拆分大事务
-- 不推荐
start transaction;
insert into large_table select * from huge_source; -- 百万行
commit;

-- 推荐：分批提交
set autocommit = 0;
insert into large_table select * from huge_source limit 10000;
commit;
-- 循环处理...

-- 2. 使用pt-online-schema-change避免ddl锁表
-- 示例命令
pt-online-schema-change \
  --alter="add index idx_name (name)" \
  d=test,t=large_table \
  --execute

-- 增大复制缓冲区
set global slave_net_timeout = 60;  -- 默认60秒
set global slave_compressed_protocol = on;  -- 开启压缩

-- master端增大binlog缓存
set global binlog_cache_size = 4m;
set global max_binlog_size = 512m;

// spring boot + mybatis 多数据源配置示例
@configuration
public class datasourceconfig {
    
    @primary
    @bean(name = "masterdatasource")
    @configurationproperties(prefix = "spring.datasource.master")
    public datasource masterdatasource() {
        return datasourcebuilder.create().build();
    }
    
    @bean(name = "slavedatasource")
    @configurationproperties(prefix = "spring.datasource.slave")
    public datasource slavedatasource() {
        return datasourcebuilder.create().build();
    }
    
    @bean(name = "dynamicdatasource")
    public datasource dynamicdatasource() {
        map<object, object> targetdatasources = new hashmap<>();
        targetdatasources.put("master", masterdatasource());
        targetdatasources.put("slave", slavedatasource());
        
        dynamicdatasource dynamicdatasource = new dynamicdatasource();
        dynamicdatasource.settargetdatasources(targetdatasources);
        dynamicdatasource.setdefaulttargetdatasource(masterdatasource());
        
        return dynamicdatasource;
    }
}

// 自定义路由数据源
public class dynamicdatasource extends abstractroutingdatasource {
    
    @override
    protected object determinecurrentlookupkey() {
        // 根据注解选择数据源
        return dynamicdatasourcecontextholder.getdatasourcetype();
    }
}

// 自定义注解
@target({elementtype.method, elementtype.type})
@retention(retentionpolicy.runtime)
@documented
public @interface datasource {
    string value() default "master";
}

// 使用示例
@service
public class userservice {
    
    @datasource("master")
    public void createuser(user user) {
        // 写操作，使用主库
        usermapper.insert(user);
    }
    
    @datasource("slave")
    public user getuserbyid(long id) {
        // 读操作，使用从库
        return usermapper.selectbyid(id);
    }
}

-- 1. 安装后配置后端服务器
insert into mysql_servers(hostgroup_id, hostname, port) 
values 
(10, 'master_host', 3306),   -- 写组
(20, 'slave1_host', 3306),   -- 读组
(20, 'slave2_host', 3306);

-- 2. 配置监控用户
update global_variables 
set variable_value='monitor' 
where variable_name='mysql-monitor_username';

update global_variables 
set variable_value='monitor_password' 
where variable_name='mysql-monitor_password';

-- 3. 配置路由规则
-- 写操作路由到hostgroup 10
insert into mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) 
values 
(1, 1, '^insert', 10, 1),
(2, 1, '^update', 10, 1),
(3, 1, '^delete', 10, 1),
(4, 1, '^select.*for update', 10, 1);

-- 读操作路由到hostgroup 20
insert into mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) 
values 
(5, 1, '^select', 20, 1);

-- 4. 加载配置
load mysql servers to runtime;
save mysql servers to disk;
load mysql query rules to runtime;
save mysql query rules to disk;

-- 5. 配置读写分离用户
insert into mysql_users(username, password, default_hostgroup) 
values ('app_user', 'password', 10);

// 延迟感知的负载均衡策略
public class lagawareloadbalance {
    
    private list<slaveinfo> slaves = new arraylist<>();
    private static final int max_lag_threshold = 5; // 最大允许延迟5秒
    
    public datasource selectslave() {
        list<slaveinfo> availableslaves = slaves.stream()
            .filter(s -> s.getlagseconds() <= max_lag_threshold)
            .collect(collectors.tolist());
            
        if (availableslaves.isempty()) {
            // 所有从库延迟过高，降级到主库
            return masterdatasource;
        }
        
        // 加权随机选择（延迟越低权重越高）
        return selectbyweight(availableslaves);
    }
    
    private datasource selectbyweight(list<slaveinfo> slaves) {
        int totalweight = slaves.stream()
            .maptoint(s -> calculateweight(s.getlagseconds()))
            .sum();
        
        int random = new random().nextint(totalweight);
        int current = 0;
        
        for (slaveinfo slave : slaves) {
            current += calculateweight(slave.getlagseconds());
            if (random < current) {
                return slave.getdatasource();
            }
        }
        
        return slaves.get(0).getdatasource();
    }
    
    private int calculateweight(int lagseconds) {
        // 延迟越低，权重越高
        return math.max(10 - lagseconds, 1);
    }
}

-- 1. 主从复制中断
-- 查看错误信息
show slave status\g

-- 常见错误1：主键冲突
-- 解决方案：跳过错误
stop slave;
set global sql_slave_skip_counter = 1;
start slave;

-- 或指定gtid跳过
stop slave;
set gtid_next='aaa-bbb-ccc-ddd:n';
begin; commit;
set gtid_next='automatic';
start slave;

-- 2. 数据不一致修复
-- 使用pt-table-checksum检查
pt-table-checksum \
  --replicate=test.checksums \
  --databases=your_db \
  h=master_host,u=user,p=password

-- 使用pt-table-sync修复
pt-table-sync \
  --execute \
  --replicate test.checksums \
  h=master_host,u=user,p=password \
  h=slave_host,u=user,p=password

-- 3. 重新同步数据
-- 方法1：使用mysqldump
-- master端
mysqldump --master-data=2 --single-transaction -uroot -p dbname > dbname.sql

-- slave端
stop slave;
source dbname.sql;
start slave;

-- 方法2：使用xtrabackup
# master备份
xtrabackup --backup --target-dir=/backup/master/

# slave恢复
xtrabackup --prepare --target-dir=/backup/master/
xtrabackup --copy-back --target-dir=/backup/master/

-- mha (master high availability) 配置示例
-- 1. 配置ssh免密登录
-- 2. 编辑配置文件 /etc/mha/app1.cnf
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
ssh_user=mysql
user=repl
password=repl123456
repl_user=repl
repl_password=repl123456

[server1]
hostname=master_host
candidate_master=1

[server2]
hostname=slave1_host
candidate_master=1

[server3]
hostname=slave2_host
no_master=1

-- 3. 启动mha监控
masterha_manager --conf=/etc/mha/app1.cnf

# prometheus.yml 配置
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['master_host:9104', 'slave1_host:9104']
    params:
      collect[]:
        - global_status
        - slave_status
        - info_schema.innodb_metrics

# mysqld_exporter启动
./mysqld_exporter \
  --collect.slave_status \
  --collect.info_schema.innodb_metrics \
  --collect.global_status \
  --web.listen-address=":9104"

// 关键监控指标
{
  "panels": [
    {
      "title": "复制延迟",
      "targets": [{
        "expr": "mysql_slave_status_seconds_behind_master",
        "legendformat": "{{instance}}"
      }],
      "thresholds": [
        {"color": "green", "value": 0},
        {"color": "yellow", "value": 5},
        {"color": "red", "value": 30}
      ]
    },
    {
      "title": "复制线程状态",
      "targets": [{
        "expr": "mysql_slave_status_slave_io_running",
        "legendformat": "io线程 {{instance}}"
      }]
    }
  ]
}

# alertmanager配置
groups:
  - name: mysql_alerts
    rules:
      - alert: highreplicationlag
        expr: mysql_slave_status_seconds_behind_master > 30
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "mysql复制延迟过高"
          description: "实例 {{ $labels.instance }} 复制延迟已达 {{ $value }} 秒"
      
      - alert: slavenotrunning
        expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "mysql从库复制停止"

-- 主库配置要点
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = row
expire_logs_days = 7
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

-- 从库配置要点
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read_only = 1
log_slave_updates = 1
slave_parallel_type = logical_clock
slave_parallel_workers = 4

#!/bin/bash
# 复制状态检查脚本

check_replication() {
    local host=$1
    local user=$2
    local password=$3
    
    result=$(mysql -h$host -u$user -p$password -e "show slave status\g")
    
    io_running=$(echo "$result" | grep "slave_io_running" | awk '{print $2}')
    sql_running=$(echo "$result" | grep "slave_sql_running" | awk '{print $2}')
    lag=$(echo "$result" | grep "seconds_behind_master" | awk '{print $2}')
    
    if [ "$io_running" = "yes" ] && [ "$sql_running" = "yes" ]; then
        echo "replication is running. lag: ${lag} seconds"
        if [ $lag -gt 30 ]; then
            echo "warning: high replication lag detected!"
            return 2
        fi
        return 0
    else
        echo "error: replication is broken!"
        return 1
    fi
}

# 定期检查
while true; do
    check_replication "slave_host" "monitor" "password"
    sleep 60
done