MySQL读写分离与高可用方案（CentOS 5.6.44环境）

一、架构设计方案

1. 目标架构

[应用层]
  │
  ├─ [主库] 10.109.10.122 (读写)
  │
  └─ [从库] 10.109.10.125 (只读)

2. 技术选型

• 数据同步：MySQL原生主从复制

• 故障转移：手动切换（5.6版本推荐方案）

• 读写分离：应用层实现（或使用ProxySQL）

二、主库(122)配置

1. 修改my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

2. 创建复制账号

CREATE USER 'repl'@'10.109.10.%' IDENTIFIED BY 'SecurePass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.109.10.%';
FLUSH PRIVILEGES;

3. 锁定数据库并获取位置点

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 记录File和Position值（例如：mysql-bin.000001, 107）

三、从库(125)配置

1. 安装相同版本MySQL

# 确保版本一致
yum install mysql-server-5.6.44

2. 修改my.cnf

[mysqld]
server-id = 2
read-only = 1
relay-log = mysql-relay-bin
log-slave-updates = 1

3. 配置主从复制

CHANGE MASTER TO
MASTER_HOST='10.109.10.122',
MASTER_USER='repl',
MASTER_PASSWORD='SecurePass123!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

START SLAVE;

4. 验证复制状态

SHOW SLAVE STATUS\G
-- 确保 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes

四、读写分离实现方案

方案A：应用层实现（推荐）

# Python示例
db_config = {
    'master': {
        'host': '10.109.10.122',
        'user': 'app_user',
        'password': 'AppPassword123!',
        'database': 'app_db'
    },
    'slave': {
        'host': '10.109.10.125',
        'user': 'app_user',
        'password': 'AppPassword123!',
        'database': 'app_db'
    }
}

def get_db_connection(is_write=False):
    return pymysql.connect(**db_config['master'] if is_write else db_config['slave'])

方案B：使用中间件（ProxySQL）

-- ProxySQL配置示例
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'10.109.10.122',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'10.109.10.125',3306);

-- 读写分离规则
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES
(1,1,'^SELECT.*FOR UPDATE',10,1),
(2,1,'^SELECT',20,1),
(3,1,'^INSERT',10,1),
(4,1,'^UPDATE',10,1),
(5,1,'^DELETE',10,1);

五、高可用保障措施

1. 主从监控脚本

#!/bin/bash
SLAVE_STATUS=$(mysql -h 10.109.10.125 -e "SHOW SLAVE STATUS\G" | grep "Running")
if [[ ! $SLAVE_STATUS =~ "Yes" ]]; then
    echo "主从复制异常！" | mail -s "MySQL复制告警" admin@example.com
fi

2. 手动故障切换流程

1. 原主库(122)故障时：

   -- 在从库(125)执行：
   STOP SLAVE;
   RESET MASTER;
   SET GLOBAL read_only = 0;

2. 修改应用配置：将所有请求指向125

3. 原主库恢复后：

   -- 在原主库(122)执行：
   CHANGE MASTER TO MASTER_HOST='10.109.10.125', ...;
   START SLAVE;

3. 数据一致性校验

# 使用pt-table-checksum工具
pt-table-checksum --replicate=test.checksums h=10.109.10.122,u=check_user,p=CheckPass123!

六、注意事项

1. 版本限制：

• MySQL 5.6不支持GTID自动故障转移

• 考虑升级到5.7+以获得更好的高可用特性

2. 备份策略：

   # 主库每日全备
   mysqldump --single-transaction --master-data=2 -h 122 -u backup_user -p > /backups/full_$(date +%F).sql

3. 性能优化：

   # 从库my.cnf追加
   innodb_read_only = 1
   skip-slave-start = 0