MySQL高可用(Galera Cluster)

落日映苍穹つ 2023-03-01 11:40 36阅读 0赞

### 前言： ###

> Galera Cluster是集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster和MariaDB Cluster，Galera本时是具有多主特性，即采用Multi-master的集群架构，是一个即稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案。

###  内部机制流程图： ###

![20200724163152255.png][]

Galera Cluster种每个MySQLServer之间相互为主主的关系，当客户端发送指令到一个数据库时，数据库将数据修改后返回一个OK，表示用户的请求已经被收到，但是事务并没有结束依旧可以撤销。当事务结束时用户发送一个commit(提交)，服务器收到后会将数据的更新发送给其他的MySQLServer，此时会开启一个全局性的事务ID给组内的其他服务器，其他服务器会查看是否有事务冲突，如果没有冲突就确认更改成功。

### 主要特点： ###

1.  多主架构：真正的多点读写的集群，在任何时候读写数据都是最新的
2.  同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失
3.  并发复制：从节点APPLY数据时，支持并行执行有更好的性能。
4.  故障切换：数据库故障时，因为支持多点写入，切换容易
5.  热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务的时间就会非常少。在节点故障期间，节点本身对集群的影响非常小
6.  自动节点克隆： 在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一直
7.  对应用透明：集群的维护，对应用程序是透明的

### 搭建准备： ###

虚拟环境平台：[Windows10自带的Hyper-V虚拟机平台搭建][Windows10_Hyper-V]

使用CentOS7镜像：[CentOS7精简版最小安装镜像（CentOS-7-x86\_64-Minimal-2003.iso）][CentOS7_CentOS-7-x86_64-Minimal-2003.iso]

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="width:198px;">主机名/IP</td> 
   <td style="width:114px;">CPU</td> 
   <td style="width:187px;">MEM</td> 
  </tr> 
  <tr> 
   <td style="width:198px;">galera-01/172.16.0.21</td> 
   <td style="width:114px;">2</td> 
   <td style="width:187px;">4096&nbsp;Mib</td> 
  </tr> 
  <tr> 
   <td style="width:198px;">galera-02/172.16.0.22</td> 
   <td style="width:114px;">2</td> 
   <td style="width:187px;">4096&nbsp;Mib</td> 
  </tr> 
  <tr> 
   <td style="width:198px;">galera-03/172.16.0.23</td> 
   <td style="width:114px;">2</td> 
   <td style="width:187px;">4096&nbsp;Mib</td> 
  </tr> 
 </tbody> 
</table>

### 关闭防火墙，配置yum源，安装所需服务。(三台机器) ###

systemctl stop firewalld && systemctl disable firewalld
    cd /etc/yum.repos.d/
    mkdir backup
    mv ./* backup
    vi galera.repo
    [galera]
    name = Galera
    baseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64/
    gpgcheck = 0
    
    [mysql-wsrep]
    name = MySQL-wsrep
    baseurl = http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86_64/
    gpgcheck = 0
    :wq
    yum clean all
    yum makecache
    yum -y install galera-3 mysql-wsrep-5.7 rsync lsof policycoreutils-python

### 启动服务(节点一)： ###

systemctl enable mysqld --now
    获取临时密码：cat /var/log/messages |grep password
    复制临时密码，直接输入命令回车：这里会有五六个步骤，按提示操作即可。我就不一一列举了。
    mysql_secure_installation

### 登录服务进行赋权(节点一) ###

mysql -uroot -p
    grant all privileges on *.* to root@'172.16.0.%' identified by '#EDC4rfv';
    flush privileges;

### 编辑/etc/my.cnf(节点一) ###

[mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    binlog_format=ROW
    bind-address=0.0.0.0
    default_storage_engine=innodb
    innodb_autoinc_lock_mode=2
    innodb_flush_log_at_trx_commit=0
    innodb_buffer_pool_size=122M
    wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
    wsrep_provider_options="gcache.size=300M;gcache.page_size=300M"
    wsrep_cluster_name="galera_cluster1"
    wsrep_cluster_address="gcomm://"
    wsrep_sst_auth=root:#EDC4rfv
    wsrep_sst_method=rsync
    server_id=1
    wsrep_node_address="172.16.0.21"
    wsrep_node_name="galera-01"
    validate_password=off
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    [mysql_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

### 重启mysql服务（节点一） ###

systemctl restart mysqld

### 节点二加入集群,没节点一那么麻烦，我们直接修改/etc/my.cnf,直接启动mysql服务。 ###

[mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    binlog_format=ROW
    bind-address=0.0.0.0
    default_storage_engine=innodb
    innodb_autoinc_lock_mode=2
    innodb_flush_log_at_trx_commit=0
    innodb_buffer_pool_size=122M
    wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
    wsrep_provider_options="gcache.size=300M;gcache.page_size=300M"
    wsrep_cluster_name="galera_cluster1"
    wsrep_cluster_address="gcomm://172.16.0.21"
    wsrep_sst_auth=root:#EDC4rfv
    wsrep_sst_method=rsync
    server_id=2
    wsrep_node_address="172.16.0.22"
    wsrep_node_name="galera-02"
    validate_password=off
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    [mysql_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

### 节点三加入集群,没节点一那么麻烦，我们直接修改/etc/my.cnf,直接启动mysql服务。 ###

[mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    binlog_format=ROW
    bind-address=0.0.0.0
    default_storage_engine=innodb
    innodb_autoinc_lock_mode=2
    innodb_flush_log_at_trx_commit=0
    innodb_buffer_pool_size=122M
    wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
    wsrep_provider_options="gcache.size=300M;gcache.page_size=300M"
    wsrep_cluster_name="galera_cluster1"
    wsrep_cluster_address="gcomm://172.16.0.21,172.16.0.22"
    wsrep_sst_auth=root:#EDC4rfv
    wsrep_sst_method=rsync
    server_id=3
    wsrep_node_address="172.16.0.23"
    wsrep_node_name="galera-03"
    validate_password=off
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    [mysql_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

###  集群初始化完毕，后续配置文件还需调整。节点三不用动！一个一个重启即可。 ###

<table> 
 <tbody> 
  <tr> 
   <td style="width:182px;">galera-01/172.16.0.21</td> 
   <td style="width:134px;">/etc/my.cnf</td> 
   <td style="width:423px;">wsrep_cluster_address="gcomm://172.16.0.22,172.16.0.23"</td> 
  </tr> 
  <tr> 
   <td style="width:182px;">galera-02/172.16.0.22</td> 
   <td style="width:134px;">/etc/my.cnf</td> 
   <td style="width:423px;">wsrep_cluster_address="gcomm://172.16.0.21,172.16.0.23"</td> 
  </tr> 
  <tr> 
   <td style="width:182px;">galera-03/172.16.0.23</td> 
   <td style="width:134px;">/etc/my.cnf</td> 
   <td style="width:423px;">wsrep_cluster_address="gcomm://172.16.0.21,172.16.0.22"</td> 
  </tr> 
 </tbody> 
</table>

### 测试集群：(节点一) ###

[root@galera-01 etc]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.7.30 MySQL Community Server - (GPL), wsrep_25.22
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> create database test;
    ERROR 1007 (HY000): Can't create database 'test'; database exists
    mysql> create database galera;
    Query OK, 1 row affected (0.04 sec)
    
    mysql> use galera;
    Database changed
    mysql> create table t_user(id int not null,name varchar(24) not null);
    Query OK, 0 rows affected (0.09 sec)
    
    mysql> show tables;
    +------------------+
    | Tables_in_galera |
    +------------------+
    | t_user           |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into t_user values(1,'jack');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from t_user;
    +----+------+
    | id | name |
    +----+------+
    |  1 | jack |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql>

### 测试集群：(节点二) ###

[root@galera-02 ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.30 MySQL Community Server - (GPL), wsrep_25.22
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> use galera;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +------------------+
    | Tables_in_galera |
    +------------------+
    | t_user           |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from t_user;
    +----+------+
    | id | name |
    +----+------+
    |  1 | jack |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql>

### 测试集群：(节点三) ###

[root@galera-03 ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.30 MySQL Community Server - (GPL), wsrep_25.22
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    
    mysql> use galera;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +------------------+
    | Tables_in_galera |
    +------------------+
    | t_user           |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from t_user;
    +----+------+
    | id | name |
    +----+------+
    |  1 | jack |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql>

### 大家可以看到数据是同步的。想测试高可用的话，可以手关闭任何一台mysql服务，然后在可用节点去尝试写入数据，之后再启动起来看是不是会立即同步，如果可以，证明集群基本上搭建完毕啦！！！ ###

[20200724163152255.png]: /images/20230208/8f676a0ab1ba421b888fb91dcae6a26d.png
[Windows10_Hyper-V]: http://www.cppcns.com/os/linux/241419.html
[CentOS7_CentOS-7-x86_64-Minimal-2003.iso]: http://mirrors.aliyun.com/centos/7/isos/x86_64/CentOS-7-x86_64-Minimal-2003.iso