Linux上设置高可用数据库复制方案

主流数据库高可用方案概览

1. MySQL高可用方案

• 主从复制(Master-Slave)
• 组复制(Group Replication)
• InnoDB Cluster
• Galera Cluster
• MHA (Master High Availability)

2. PostgreSQL高可用方案

• 流复制(Streaming Replication)
• 逻辑复制(Logical Replication)
• Patroni + etcd/Consul/ZooKeeper
• Pgpool-II
• Repmgr

3. MongoDB高可用方案

• 副本集(Replica Set)
• 分片集群(Sharded Cluster)

MySQL主从复制高可用配置示例

环境准备

• 两台或多台Linux服务器(CentOS/Ubuntu等)
• MySQL 5.7+或MariaDB 10.2+

配置步骤

1. 主服务器配置(my.cnf)

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
expire_logs_days = 7
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10

2. 从服务器配置(my.cnf)

[mysqld]
server-id = 2
log_bin = mysql-bin
relay_log = mysql-relay-bin
read_only = ON
log_slave_updates = ON
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

3. 创建复制用户(主服务器)

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

4. 配置从服务器连接主服务器

CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='securepassword',
MASTER_AUTO_POSITION=1;
START SLAVE;

5. 验证复制状态

SHOW SLAVE STATUS\G

使用ProxySQL实现读写分离高可用

1. 安装ProxySQL

# Ubuntu/Debian
sudo apt-get install proxysql

# CentOS/RHEL
sudo yum install proxysql

2. 配置ProxySQL

-- 添加主从服务器
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'master_ip',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'slave1_ip',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);

-- 监控配置
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitorpassword' WHERE variable_name='mysql-monitor_password';

使用Keepalived实现VIP高可用

1. 安装Keepalived

# Ubuntu/Debian
sudo apt-get install keepalived

# CentOS/RHEL
sudo yum install keepalived

2. 主节点配置(/etc/keepalived/keepalived.conf)

vrrp_script chk_mysql {
    script "/usr/bin/mysql -uroot -ppassword -e 'SELECT 1'"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 101
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.100/24
    }
    track_script {
        chk_mysql
    }
}

3. 从节点配置

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.100/24
    }
    track_script {
        chk_mysql
    }
}

监控与自动故障转移

1. 使用Orchestrator进行MySQL拓扑管理

# 下载安装
wget https://github.com/openark/orchestrator/releases/latest/download/orchestrator-3.2.6-linux-amd64.tar.gz
tar xzvf orchestrator-*.tar.gz
sudo mv orchestrator /usr/local/bin/

2. 配置Orchestrator

{
  "Debug": true,
  "ListenAddress": ":3000",
  "MySQLTopologyUser": "orchestrator",
  "MySQLTopologyPassword": "orchestrator_password",
  "MySQLReplicaUser": "repl",
  "MySQLReplicaPassword": "securepassword",
  "RaftEnabled": true,
  "RaftDataDir": "/var/lib/orchestrator",
  "RaftBind": "server_ip",
  "DefaultRaftPort": 10008
}

最佳实践建议

1. 网络配置

   • 确保主从服务器间低延迟网络连接
   • 考虑专用复制网络接口

2. 监控指标

   • 复制延迟(Seconds_Behind_Master)
   • 复制线程状态(Slave_IO_Running, Slave_SQL_Running)
   • 二进制日志位置

3. 定期维护

   • 监控磁盘空间
   • 定期验证数据一致性(pt-table-checksum)
   • 测试故障转移流程

4. 安全考虑

   • 加密复制流量(SSL/TLS)
   • 限制复制用户权限
   • 定期轮换凭据

以上方案可根据实际业务需求进行调整和扩展，对于生产环境建议先在测试环境验证并制定详细的回滚计划。