MySQL高可用(Galera Cluster)

最新推荐文章于 2024-08-05 15:34:36 发布

chengli.zou

最新推荐文章于 2024-08-05 15:34:36 发布

阅读量597

点赞数 1

分类专栏： galera mysql 文章标签：数据库 linux centos mysql

本文链接：https://blog.csdn.net/ZouChengli/article/details/107564041

版权

mysql 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

galera

1 篇文章 0 订阅

订阅专栏

前言：

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

内部机制流程图：

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

主要特点：

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

搭建准备：

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

使用CentOS7镜像：CentOS7精简版最小安装镜像（CentOS-7-x86_64-Minimal-2003.iso）

主机名/IP	CPU	MEM
galera-01/172.16.0.21	2	4096 Mib
galera-02/172.16.0.22	2	4096 Mib
galera-03/172.16.0.23	2	4096 Mib

关闭防火墙，配置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

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

galera-01/172.16.0.21	/etc/my.cnf	wsrep_cluster_address="gcomm://172.16.0.22,172.16.0.23"
galera-02/172.16.0.22	/etc/my.cnf	wsrep_cluster_address="gcomm://172.16.0.21,172.16.0.23"
galera-03/172.16.0.23	/etc/my.cnf	wsrep_cluster_address="gcomm://172.16.0.21,172.16.0.22"

测试集群：(节点一)

[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>