ubuntu上如何实现mysql的高可用性

在 Ubuntu 上实现 MySQL 高可用的主流方案与落地步骤

一、方案总览与选型建议

• 选型建议：读多写少优先“主从+读写分离”；需要自动故障切换且接受最终一致可用“MM+Keepalived”；追求强一致与多节点原生高可用优先“MGR/PXC”。

二、方案一 主从复制 + 读写分离（入门与通用）

• 主库配置（Ubuntu 常见路径为 /etc/mysql/mysql.conf.d/mysqld.cnf）

  • 开启 binlog 与唯一 server-id，建议启用 GTID 便于恢复与切换：

    [mysqld]
    server-id=1
    log-bin=mysql-bin
    binlog_format=ROW
    gtid-mode=ON
    enforce-gtid-consistency=ON
    

  • 重启：sudo systemctl restart mysql
  • 创建复制用户并授权：

    CREATE USER 'replica'@'%' IDENTIFIED BY 'StrongPass!';
    GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
    FLUSH PRIVILEGES;
    

  • 记录位点：SHOW MASTER STATUS;（记下 File 与 Position）。

• 从库配置

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

  • 重启：sudo systemctl restart mysql
  • 建立复制链路（使用上一步的 File/Position 或 MASTER_AUTO_POSITION=1 配合 GTID）：

    CHANGE MASTER TO
      MASTER_HOST='master_ip',
      MASTER_USER='replica',
      MASTER_PASSWORD='StrongPass!',
      MASTER_LOG_FILE='mysql-bin.000001',
      MASTER_LOG_POS=154;
    START SLAVE;
    

  • 检查：SHOW SLAVE STATUS\G，确保 Slave_IO_Running/Slave_SQL_Running=Yes，关注 Seconds_Behind_Master。

• 读写分离与流量治理

  • 中间件：ProxySQL/HAProxy 路由写主读从；或 MyCAT 做逻辑库与读写分离规则配置。

三、方案二 双主（MM）+ Keepalived（快速自动切换）

• MySQL 双主要点

  • 两节点互为主从，建议开启 auto-increment-increment 与 auto-increment-offset 避免主键冲突：

    # node1
    auto-increment-increment=2
    auto-increment-offset=1
    
    # node2
    auto-increment-increment=2
    auto-increment-offset=2
    

  • 其他复制参数与主从一致（server-id 唯一、开启 binlog/relay-log 等）。

• Keepalived 实现 VIP 漂移

  • 在两台节点部署 Keepalived，配置 虚拟 IP（VIP）；健康检查脚本探测 3306 与复制状态，失败则降低本机权重或停止 Keepalived，使 VIP 漂移到对端。
  • 优点：部署快、切换及时；注意防范“脑裂”、双 VIP 与复制冲突，必要时引入仲裁或增强半同步降低丢数风险。

四、方案三 原生高可用集群 MGR 或 PXC（强一致与多节点）

• MySQL Group Replication（MGR）

  • 适用 MySQL 5.7+ / 8.0+，多节点组成复制组，基于 多数派 提交与自动故障切换。
  • 关键配置（各节点一致，仅 server-id 不同）：

    [mysqld]
    server-id=1
    gtid-mode=ON
    enforce-gtid-consistency=ON
    binlog_checksum=NONE
    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
    loose-group_replication_start_on_boot=OFF
    loose-group_replication_local_address="192.168.1.1:33061"
    loose-group_replication_group_seeds="192.168.1.1:33061,192.168.1.2:33061,192.168.1.3: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;，通过 performance_schema.replication_group_members 查看成员状态。

• Percona XtraDB Cluster（PXC/Galera）

  • 多主多写、同步复制、强一致，适合高并发与强一致场景。
  • 关键配置（示例）：

    [mysqld]
    wsrep_on=ON
    wsrep_provider=/usr/lib/galera/libgalera_smm.so
    wsrep_cluster_address="gcomm://node1_ip,node2_ip,node3_ip"
    wsrep_cluster_name="my_cluster"
    wsrep_node_address="node1_ip"
    wsrep_node_name="node1"
    binlog_format=ROW
    default-storage-engine=innodb
    innodb_autoinc_lock_mode=2
    

  • 初始化与扩容：首个节点以 wsrep_new_cluster 启动，其余节点加入；可通过 rsync 或 SST 同步数据目录。

五、生产落地要点与运维清单

• 复制与一致性
  • 优先启用 GTID，便于恢复与切换；读负载高时启用 多线程复制（slave-parallel-workers）；对一致性敏感的业务考虑 增强半同步 或 组复制/ Galera 的强一致。
• 流量治理与中间件
  • 读写分离用 ProxySQL/HAProxy；复杂路由与分库分表可用 MyCAT；引入 Orchestrator/MHA 做主库自动切换与可视化运维。
• 监控与备份
  • 持续监控复制延迟（如 Seconds_Behind_Master）、节点存活、连接数、慢查询；定期全量+增量备份（如 mysqldump/Percona XtraBackup），并演练恢复流程。
• 网络与安全
  • 开放复制与管理端口（如 3306/33061），限制来源 IP；开启 SSL 加密复制通道；保证 NTP 时间同步 与稳定的存储/网络。