##
Galera Cluster for MySQL 学习笔记
1、意犹未尽的banner
打开Galera Cluster的官网你会发现
Galera Cluster for MySQL | The world’s most advanced open … —怎么说呢。引起你的兴趣，然后告诉你World’s Most。
World’s Most Advanced Features and Un-Seen Benefits
- True Multi-master, Active-Active Cluster Read and write to any node at any time. —多主方案
- Synchronous Replication No slave lag, no data is lost at node crash.—同步复制
- Tightly Coupled All nodes hold the same state. No diverged data between nodes allowed. —高一致性
- Multi-threaded Slave For better performance. For any workload. —多线程
- No Master-Slave Failover Operations or Use of VIP. --没有主从fa和vip
- Hot Standby No downtime during failover (since there is no failover). --不停机切换
- Automatic Node Provisioning No need to manually back up the database and copy it to the new node. --新增节点很方便
- Supports InnoDB. —支持innodb
- Transparent to Applications Required no (or minimal changes) to the application. --应用透明
- No Read and Write Splitting Needed. --没有读写分离，但是可以有
- Easy to Use and Deploy --部署简单
2、Galera Cluster分支概述
MySQL Galera Cluster
MariaDB Galera Cluster
XtraDB Galera Cluster
下面两个分别是MariaDB和Percona对应的方案，以前都接触过，对于MySQL Galera Cluster并没有实际接触过，最近一个客户服务中正好遇到，所以测试看看。
3、版本发布概述
Galera Cluster for MySQL 5.7.44 and MySQL 8.0.35 released
Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.7, consisting of MySQL-wsrep 5.7.44 (release notes, download) and MySQL-wsrep 8.0.35 (release notes, download), with Galera replication library 4.17 (release notes, download) implementing wsrep API version 26. This release incorporates all changes to MySQL 5.7.44 and MySQL 8.0.35 respectively, adding a synchronous option for your MySQL High Availability solutions.
发布适用于MySQL 5.7.44和MySQL 8.0.35的Galera集群
Codership很高兴地宣布MySQL 5.7的多主Galera集群正式发布，该集群由MySQL-wsrep 5.7.44（发行说明，下载）和MySQL-wsrep 8.0.35（发行说明，下载）组成，其中Galera复制库4.17（发行说明，下载）实现了wsrep API版本26。此版本分别纳入了MySQL 5.7.44和MySQL 8.0.35的所有更改，为您的MySQL高可用性解决方案添加了同步选项。
看起来版本非常新，都到8.0.35了
4、Galera Cluster for MySQL安装
这里采用的方法是Galera Cluster for MySQL—Binary Installation，去看了下官网的文档，不管是online还是PDF，都非常不详细，但是在blog中却有一些方法。另外查看在线或者PDF文档，需要企业邮箱，这对于这种无业游民来说，很不友好。
另外除了yum/RPM安装，就是编译安装了。这和PG很像。
4.1、安装规划
| ip | 系统 | galera node name |
|---|---|---|
| 192.168.5.130 | CentOS 7.9 | galera01 + Galera Manager |
| 192.168.5.140 | CentOS 7.9 | galera02 |
| 192.168.5.150 | CentOS 7.9 | galera03 |
4.2、安装过程
1.卸载系统自带的mariadb和mysql
rpm -qa|grep mysql |xargs rpm -e --nodeps
rpm -qa|grep mariadb |xargs rpm -e --nodeps
2.准备repo源
[root@centos ~]# cat /etc/yum.repos.d/galera.repo
[galera4]
name = Galera
baseurl = https://releases.galeracluster.com/galera-4/centos/7/x86_64
gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
[mysql-wsrep8]
name = MySQL-wsrep
baseurl = https://releases.galeracluster.com/mysql-wsrep-8.0/centos/7/x86_64
gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
由于众所周知的原因，下载安装包可能非常慢，所以快捷rpm下载吧
RPM包下载地址：https://releases.galeracluster.com/mysql-wsrep-8.0/centos/7/x86_64/
3.参数文件准备
第一个节点
##modify
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=4096M
bind-address=0.0.0.0
binlog_format=ROW
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_provider_options="gcache.size=128M; gcache.page_size=128M"
# Galera Node Configuration
wsrep_node_name="galera01"
wsrep_node_address="192.168.5.130"
# Galera Cluster Configuration
wsrep_cluster_name="galerademo"
wsrep_cluster_address="gcomm://192.168.5.130,192.168.5.140,192.168.5.150"
# Galera Synchronization Configuration
wsrep_slave_threads=4
wsrep_sst_method=rsync
第二第三个节点，只需要改wsrep_node_nam和wsrep_node_address，看起来和MGR的参数设置差不多。but…
4.启动节点
启动第一个节点
mysqld_bootstrap
然后根据日志找到临时密码，之后更改密码
然后再启动第二、三个节点
5.查看集群成员
mysql> select * from mysql.wsrep_cluster_members order by node_name;
+--------------------------------------+--------------------------------------+-----------+-----------------------+
| node_uuid | cluster_uuid | node_name | node_incoming_address |
+--------------------------------------+--------------------------------------+-----------+-----------------------+
| 109be04f-c020-11ee-94c3-1f081e25537f | dafd800b-c00c-11ee-86b3-c7bed75dc0b6 | galera01 | AUTO |
| 91efcc0a-c03a-11ee-a315-a30e8d947c99 | dafd800b-c00c-11ee-86b3-c7bed75dc0b6 | galera02 | AUTO |
| 26aa23dd-c020-11ee-b8f6-96b87393bc83 | dafd800b-c00c-11ee-86b3-c7bed75dc0b6 | galera03 | AUTO |
+--------------------------------------+--------------------------------------+-----------+-----------------------+
3 rows in set (0.00 sec)
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.01 sec)
三个节点就是3，这里，如果只初始化了主节点，那就是1
6.安装gm
https://galeracluster.com/2021/02/using-galera-manager-to-monitor-your-existing-galera-clusters/
[root@galera01 galerarpm]# wget https://galeracluster.com/galera-manager/gm-installer
[root@galera01 galerarpm]# chmod +x gm-installer
[root@galera01 galerarpm]# ./gm-installer install
INFO[0000] OS Detected: RedHat / CentOS Linux / Linux / Core / 7.9.2009
Do you accept the License Agreement? [a] to accept; [r] to read agreement; or [n] to reject.
Your choice: a
GMD Package Repository URL (blank for default):
GMD Admin User Login [admin]:
GMD Admin Password:
Repeat your entry:
By what domain name or an IP address this service will be reached?
(Note that an externally resolvable domain name is needed to use an external
Certificate Authority, otherwise we will have to resort to self-signed
certificates for SSL if encryption is required):
Enter your domain name or IP of the server: 192.168.5.130
同样非常慢，需要yum update以及安装一堆东西，虽然是自动化的，但是很慢。
另外gm也是GALERA CLUSTER ENTERPRISE EDITION的一员，看起来也是收费项目
4.3、炫酷的gm
安装的时候需要ssh认证，并且可以三个node一起安装，不需要等待，有后台进程运行，步骤就是添加集群，添加节点。
一言难尽，太简陋了，并且添加节点非常慢，也需要安装一堆东西。
5、基础测试数据
SQL statistics:
queries performed:
read: 1447628
write: 334661
other: 285749
total: 2068038
transactions: 103401 (344.54 per sec.)
queries: 2068038 (6890.89 per sec.)
ignored errors: 1 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.1098s
total number of events: 103401
Latency (ms):
min: 5.37
avg: 58.03
max: 734.26
95th percentile: 101.13
sum: 6000216.77
Threads fairness:
events (avg/stddev): 5170.0500/60.81
execution time (avg/stddev): 300.0108/0.03
仅参考，并无实际意义
6、HA测试
1.添加和删除节点—很丝滑，同第二/三节点方法。用airportdb测试了下，没有MGR中的克隆快。
2.关闭节点测试，剩余节点不影响，同步成功
7、Load Balancing方案
官网提到了三个方案，目前性价比最高的就是HAProxy 了，另外Galera Load Balancer还得收费。
HAProxy an open source TCP/HTTP load balancer.
Pen another open source TCP/HTTP load balancer. Pen performs better than HAProxy on SQL traffic.
Galera Load Balancer inspired by Pen, but is limited to balancing generic TCP connections only.
这里把balance 改成了roundrobin，测试结果如下(推荐的策略是source)。另外官网这里的文档也很不详细，并且有错。
[root@haproxy ~]# mysql -uroot --password=Sroot@1234 -h192.168.5.180 -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| galera01 |
+------------+
[root@haproxy ~]# mysql -uroot --password=Sroot@1234 -h192.168.5.180 -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| galera02 |
+------------+
[root@haproxy ~]# mysql -uroot --password=Sroot@1234 -h192.168.5.180 -e "select @@hostname"
+------------+
| @@hostname |
+------------+
| galera03 |
+------------+
8、总结
MySQL的多主方案有很多，Galera Cluster 算是一个不错的选择，另外8.0.35也是一个不错的版本。上生产还是可以的。
