技术：

Galera集群的复制功能是基于认证的复制，其流程如下：

当客户端发出一个commit的指令，在事务被提交之前，所有对数据库的更改都会被write-set收集起来，并且将write-set 记录的内容发送给其他节点。

write-set 将在每个节点上使用搜索到的主键进行确认性认证测试，测试结果决定着节点是否应用write-set更改数据。如果认证测试失败，节点将丢弃 write-set ；如果认证测试成功，则事务提交，工作原理如下图：

Galera集群环境

硬件规划

系统版本

[root@ecs-f1ee-0001 ~]# cat /etc/redhat-release

CentOS Linux release 7.4.1708 (Core)

查看防火墙

[root@ecs-f1ee-0001 ~]# systemctl status firewalld

● firewalld.service - firewalld - dynamic firewall daemon

Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)

Active: inactive (dead)

Docs: man:firewalld(1)

加大文件描述符

[root@ecs-f1ee-0001 ~]# cat /etc/security/limits.conf

* soft stack 10240

* soft nproc 2047

* hard nproc 16384

* soft nofile 2048

* hard nofile 65536

* soft memlock 3145728

* hard memlock 3145728

最后执行：

# sysctl -p

初始安装

        我们从最简单的场景开始，假设在没有任何应用数据和访问的情况下，从头开始安装Galera集群。

安装galera-3、mysql-wsrep-5.7、Percona-XtraBackup-2.4.15

        以下步骤以root用户在二台主机执行。

安装依赖包

[root@ecs-f1ee-0001 ~]# yum install perl-Time-HiRes

[root@ecs-f1ee-0001 ~]# yum -y install perl-DBD-MySQL.x86_64

[root@ecs-f1ee-0001 ~]# yum -y install libaio*

创建yum源文件

[root@ecs-f1ee-0001 ~]# cat > /etc/yum.repos.d/galera.repo <<-END

[galera]

name = Galera

baseurl = https://releases.galeracluster.com/galera-3.28/centos/7/x86_64

gpgkey = https://releases.galeracluster.com/galera-3.28/GPG-KEY-galeracluster.com

gpgcheck = 1

[mysql-wsrep]

name = MySQL-wsrep

baseurl =  https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/centos/7/x86_64

gpgkey = https://releases.galeracluster.com/mysql-wsrep-5.7.27-25.19/GPG-KEY-galeracluster.com

gpgcheck = 1

END

安装galera-3与mysql-wsrep-5.7

[root@ecs-f1ee-0001 ~]# yum install -y galera-3 mysql-wsrep-5.7

Loaded plugins: fastestmirror, langpacks

Loading mirror speeds from cached hostfile

* base: mirrors.163.com

* extras: mirrors.ustc.edu.cn

* updates: mirrors.ustc.edu.cn

Resolving Dependencies

--> Running transaction check

---> Package galera-3.x86_64 0:25.3.31-1.el7 will be installed

---> Package mysql-wsrep-5.7.x86_64 0:5.7.31-25.23.el7 will be installed

--> Processing Dependency: mysql-wsrep-server-5.7(x86-64) = 5.7.31-25.23.el7 for package: mysql-wsrep-5.7-5.7.31-25.23.el7.x86_64

--> Processing Dependency: mysql-wsrep-client-5.7(x86-64) = 5.7.31-25.23.el7 for package: mysql-wsrep-5.7-5.7.31-25.23.el7.x86_64

--> Processing Dependency: mysql-wsrep-libs-compat-5.7(x86-64) = 5.7.31-25.23.el7 for package: mysql-wsrep-5.7-5.7.31-25.23.el7.x86_64

--> Processing Dependency: mysql-wsrep-libs-5.7(x86-64) = 5.7.31-25.23.el7 for package: mysql-wsrep-5.7-5.7.31-25.23.el7.x86_64

---> Package rsync.x86_64 0:3.1.2-10.el7 will be installed

--> Running transaction check

---> Package mysql-wsrep-client-5.7.x86_64 0:5.7.31-25.23.el7 will be installed

---> Package mysql-wsrep-libs-5.7.x86_64 0:5.7.31-25.23.el7 will be installed

--> Processing Dependency: mysql-wsrep-common-5.7(x86-64) >= 5.7.9 for package: mysql-wsrep-libs-5.7-5.7.31-25.23.el7.x86_64

---> Package mysql-wsrep-libs-compat-5.7.x86_64 0:5.7.31-25.23.el7 will be installed

---> Package mysql-wsrep-server-5.7.x86_64 0:5.7.31-25.23.el7 will be installed

--> Running transaction check

---> Package mysql-wsrep-common-5.7.x86_64 0:5.7.31-25.23.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================

Package Arch Version Repository Size

===============================================================================

Installing:

galera-3 x86_64 25.3.31-1.el7 galera 13 M

mysql-wsrep-5.7 x86_64 5.7.31-25.23.el7 mysql-wsrep 80 k

rsync x86_64 3.1.2-10.el7 base 404 k

Installing for dependencies:

mysql-wsrep-client-5.7 x86_64 5.7.31-25.23.el7 mysql-wsrep 17 M

mysql-wsrep-common-5.7 x86_64 5.7.31-25.23.el7 mysql-wsrep 314 k

mysql-wsrep-libs-5.7 x86_64 5.7.31-25.23.el7 mysql-wsrep 1.5 M

mysql-wsrep-libs-compat-5.7 x86_64 5.7.31-25.23.el7 mysql-wsrep 1.2 M

mysql-wsrep-server-5.7 x86_64 5.7.31-25.23.el7 mysql-wsrep 132 M

Transaction Summary

==============================================================================

Install 3 Packages (+5 Dependent packages)

Total size: 165 M

Installed size: 739 M

Is this ok [y/d/N]: y

Downloading packages:

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : mysql-wsrep-common-5.7-5.7.31-25.23.el7.x86_64 1/8

Installing : mysql-wsrep-libs-5.7-5.7.31-25.23.el7.x86_64 2/8

Installing : mysql-wsrep-client-5.7-5.7.31-25.23.el7.x86_64 3/8

Installing : mysql-wsrep-server-5.7-5.7.31-25.23.el7.x86_64 4/8

Installing : mysql-wsrep-libs-compat-5.7-5.7.31-25.23.el7.x86_64 5/8

Installing : mysql-wsrep-5.7-5.7.31-25.23.el7.x86_64 6/8

Installing : galera-3-25.3.31-1.el7.x86_64 7/8

Installing : rsync-3.1.2-10.el7.x86_64 8/8

Verifying : mysql-wsrep-5.7-5.7.31-25.23.el7.x86_64 1/8

Verifying : mysql-wsrep-server-5.7-5.7.31-25.23.el7.x86_64 2/8

Verifying : mysql-wsrep-libs-5.7-5.7.31-25.23.el7.x86_64 3/8

Verifying : rsync-3.1.2-10.el7.x86_64 4/8

Verifying : mysql-wsrep-common-5.7-5.7.31-25.23.el7.x86_64 5/8

Verifying : galera-3-25.3.31-1.el7.x86_64 6/8

Verifying : mysql-wsrep-libs-compat-5.7-5.7.31-25.23.el7.x86_64 7/8

Verifying : mysql-wsrep-client-5.7-5.7.31-25.23.el7.x86_64 8/8

Installed:

galera-3 0:25.3.31-1.el7 mysql-wsrep-5.7 0:5.7.31-25.23.el7 rsync.x86_64 0:3.1.2-10.el7

Dependency Installed:

mysql-wsrep-client-5.7.x86_64 0:5.7.31-25.23.el7

mysql-wsrep-common-5.7.x86_64 0:5.7.31-25.23.el7

mysql-wsrep-libs-5.7.x86_64 0:5.7.31-25.23.el7

mysql-wsrep-libs-compat-5.7.x86_64 0:5.7.31-25.23.el7

mysql-wsrep-server-5.7.x86_64 0:5.7.31-25.23.el7

Complete!

确认相关的rpm包

[root@ecs-f1ee-0001 ~]# rpm -qa | grep -E 'galera|wsrep'

mysql-wsrep-client-5.7-5.7.31-25.23.el7.x86_64

mysql-wsrep-5.7-5.7.31-25.23.el7.x86_64

mysql-wsrep-server-5.7-5.7.31-25.23.el7.x86_64

galera-3-25.3.31-1.el7.x86_64

mysql-wsrep-libs-5.7-5.7.31-25.23.el7.x86_64

mysql-wsrep-libs-compat-5.7-5.7.31-25.23.el7.x86_64

mysql-wsrep-common-5.7-5.7.31-25.23.el7.x86_64

修改配置文件

编辑/etc/my.cnf文件，增加以下内容：

[mysqld]

datadir=/mnt/mysql_data

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=234 #每个节点一个唯一的ID

#skip-grant-tables #初次需要修改root密码

# 当前版本galera cluster只支持innodb

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

query_cache_size=0

query_cache_type=0

bind-address=0.0.0.0 #开启远程访问

wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so

wsrep_provider_options='gmcast.listen_addr=tcp://40.40.40.139:4567' #修改为本节点地址

wsrep_cluster_name="my_wsrep_cluster" #集群名称，所有节点配置为同一个

wsrep_cluster_address=gcomm://40.40.40.139,40.40.40.148 #节点中所有节点地址

wsrep_node_name=node1 #node名称，每个节点名称唯一

wsrep_node_address='40.40.40.139' #本节点地址

wsrep_node_incoming_address='40.40.40.139' #本节点地址

wsrep_slave_threads=1

wsrep_certify_nonPK=1

wsrep_max_ws_rows=131072

wsrep_max_ws_size=1073741824

wsrep_debug=0

wsrep_convert_LOCK_to_trx=0

wsrep_retry_autocommit=1

wsrep_auto_increment_control=1

wsrep_drupal_282555_workaround=0

wsrep_causal_reads=0

wsrep_notify_cmd=

wsrep_sst_method=rsync　　　　　　　　　　　#使用系统rsync数据同步

#wsrep_sst_method=xtrabackup #如果使用xtrabackup数据同步

wsrep_sst_auth=root:Ebt-2012 #数据库用户名密码(必须与数据库对应)

wsrep_sst_donor='node1,node2' #节点中所有节点的node名称

[client]

socket=/mysql_data/mysql.sock       

系统变量说明：

• log-error：MySQL错误日志文件，集群初始化后从该文件中查找初始密码。
• wsrep_provider：galera库文件。
• wsrep_cluster_name：集群名称。
• wsrep_cluster_address：集群节点IP地址。
• wsrep_sst_method：SST方法。
• wsrep_sst_auth：SST认证信息，xtrabackup使用此用户名和口令连接数据库实例。
• wsrep_node_name：当前节点名称。
• wsrep_node_address：当前节点地址。

初始化集群

下面步骤以root用户在任一主机执行。

启动第一个节点

[root@ecs-f1ee-0001 ~]# mysqld_bootstrap

        该命令会启动本机的 mysqld 服务，MySQL缺省安装目录为/var/lib/mysql。注意，/usr/bin/mysqld_bootstrap 命令只在集群第一个节点启动时使用，因为该脚本中带有一个参数：–wsrep-new-cluster，代表新建集群。

查看mysqld服务状态

查找并修改初始密码

# 查找初始密码

[root@ecs-f1ee-0001 ~]# grep -i 'temporary password' /var/log/mysqld.log

# 修改mysql root用户密码，需要根据提示输入上一步输出的初始密码

[root@ecs-f1ee-0001 ~]# mysqladmin -uroot -p password 'Ebt-2012'

启动集群其它节点的mysqld服务

# 在其它两个主机上以root用户执行

[root@ecs-f1ee-0001 ~]# systemctl start mysqld

验证安装

（1）查看集群节点数量

mysql> show status like 'wsrep_cluster_size';

mysql> show status like 'wsrep_c%';

mysql> show status like 'wsrep_%';

（2）在三个节点分别建表插入数据，查看复制情况

-- node1

create database test;

use test;

create table t1(a int);

insert into t1 values(1);

-- node2

use test;

create table t2(a int);

insert into t2 values(2);

-- node2

use test;

create table t3(a int);

insert into t3 values(3);

        在三个节点查询数据，结果一致：

mysql> select t1.a,t2.a,t3.a from test.t1,test.t2,test.t3;

+------+------+------+

| a    | a    | a    |

+------+------+------+

|    1 |    2 |    3 |

+------+------+------+

1 row in set (0.00 sec)