温馨提示×

Ubuntu中MySQL如何实现高可用性

小樊
36
2025-10-11 22:04:41
栏目: 云计算

在Ubuntu中实现MySQL的高可用性,可以采用多种策略和技术。以下是一些常见的方法:

1. 使用MySQL复制(Replication)

MySQL复制是实现高可用性的基础。通过复制,一个MySQL服务器(主服务器)的数据可以复制到一个或多个MySQL服务器(从服务器)。

主从复制(Master-Slave Replication)

  • 设置主服务器

    1. 编辑/etc/mysql/mysql.conf.d/mysqld.cnf文件,添加以下配置:
      [mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = your_database_name 
    2. 重启MySQL服务:
      sudo systemctl restart mysql 
    3. 创建一个用于复制的用户:
      CREATE USER 'replicator'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%'; FLUSH PRIVILEGES; 
    4. 查看主服务器的状态:
      SHOW MASTER STATUS; 
  • 设置从服务器

    1. 编辑/etc/mysql/mysql.conf.d/mysqld.cnf文件,添加以下配置:
      [mysqld] server-id = 2 relay_log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log replicate_do_db = your_database_name 
    2. 重启MySQL服务:
      sudo systemctl restart mysql 
    3. 配置从服务器连接到主服务器:
      CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; START SLAVE; 

2. 使用MySQL Group Replication

MySQL Group Replication是MySQL 5.7及以上版本提供的一种高可用性解决方案,它支持多主模式和单主模式。

设置Group Replication

  1. 安装MySQL Group Replication插件:
    sudo apt-get install mysql-group-replication-plugin 
  2. 配置/etc/mysql/mysql.conf.d/mysqld.cnf文件:
    [mysqld] server-id = 1 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeeeee" loose-group_replication_start_on_boot = OFF loose-group_replication_ssl_mode = REQUIRED loose-group_replication_recovery_use_ssl = 1 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" loose-group_replication_bootstrap_group = OFF 
  3. 重启MySQL服务:
    sudo systemctl restart mysql 
  4. 启动Group Replication:
    SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; 

3. 使用Keepalived和LVS

Keepalived和LVS(Linux Virtual Server)可以用来实现MySQL的负载均衡和高可用性。

设置Keepalived和LVS

  1. 安装Keepalived:
    sudo apt-get install keepalived 
  2. 配置Keepalived:
    sudo nano /etc/keepalived/keepalived.conf 
    vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1234 } virtual_ipaddress { 192.168.1.100 } } 
  3. 启动Keepalived:
    sudo systemctl start keepalived 

4. 使用MariaDB Galera Cluster

MariaDB Galera Cluster是MariaDB提供的一种高可用性解决方案,支持多主模式。

设置Galera Cluster

  1. 安装MariaDB Galera Cluster:
    sudo apt-get install mariadb-galera-cluster 
  2. 配置/etc/mysql/conf.d/galera.cnf文件:
    [mysqld] wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 wsrep_cluster_name=galera_cluster wsrep_node_address=192.168.1.1 wsrep_node_name=node1 wsrep_sst_method=xtrabackup-v2 
  3. 启动MariaDB服务:
    sudo systemctl start mariadb 

通过以上方法,可以在Ubuntu中实现MySQL的高可用性。选择哪种方法取决于具体的需求和环境。

0