Fedora Server上安装配置MariaDb galera集群

下载与安装 MariaDB Galera Cluster

10.1之前的版本安装,输入以下命令进行安装:

sudo dnf install mariadb-galera-server

  
如果电脑上还没安装Mariadb Server,会顺便安装上,如果已经安装了,则会更新相关的包。

10.1之后的版本,Mariadb Server把群集功能打包在一起了,所以直接安装数据库,不用单独安装:

sudo dnf install mariadb-server

配置

基本步骤,使用–wsrep-new-cluster创建一个集群(只在第一个节点上使用),用–wsrep_cluster_address=gcomm://192.168.0.1加入节点。

由于文本使用的是Fedora,服务管理是systemd,所以需要修改mariadb.service,将参数加进去。

修改后的启动文件:

[Unit]
Description=MariaDB 10.1 database server
After=network.target

[Service]
Type=notify
User=mysql
Group=mysql

ExecStartPre=/usr/libexec/mysql-check-socket
# '%n' expands to 'Full unit name'; man systemd.unit
ExecStartPre=/usr/libexec/mysql-prepare-db-dir %n
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb@.service.d/MY_SPECIAL.conf
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/libexec/mysqld --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_CLUSTER_ADDRESS
ExecStartPost=/usr/libexec/mysql-check-upgrade
ExecStopPost=/usr/libexec/mysql-wait-stop

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false

KillMode=process
KillSignal=SIGTERM

# Don't want to see an automated SIGKILL ever
SendSIGKILL=no

# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s

UMask=007

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

# Place temp files in a secure directory, not /tmp
PrivateTmp=true

[Install]
WantedBy=multi-user.target

  
第一个节点的环境设置:

systemctl show-environment
LANG=en_US.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
_WSREP_CLUSTER_ADDRESS=--wsrep_cluster_address=gcomm://192.168.30.184
_WSREP_NEW_CLUSTER=--wsrep-new-cluster

第二个节点的环境配置:

systemctl show-environment
LANG=en_US.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
_WSREP_CLUSTER_ADDRESS=--wsrep_cluster_address=gcomm://192.168.30.184

  

测试

为测试方便,先将防火墙关闭,还有selinux。先启动第一个节点,再启动第二个节点。在其中一个节点上创建一个数据库,切换到另一个节点上就可以看到这个数据库。

通过数据命令可以查看集群的状态:

SHOW STATUS LIKE 'wsrep_%';
+------------------------------+-----------------------------------------+
| Variable_name                | Value                                   |
+------------------------------+-----------------------------------------+
| wsrep_apply_oooe             | 0.000000                                |
| wsrep_apply_oool             | 0.000000                                |
| wsrep_apply_window           | 1.000000                                |
| wsrep_causal_reads           | 0                                       |
| wsrep_cert_deps_distance     | 1.000000                                |
| wsrep_cert_index_size        | 2                                       |
| wsrep_cert_interval          | 0.000000                                |
| wsrep_cluster_conf_id        | 189                                     |
| wsrep_cluster_size           | 2                                       |
| wsrep_cluster_state_uuid     | e5b100e2-7f7e-11e7-9266-de309a905227    |
| wsrep_cluster_status         | Primary                                 |
| wsrep_commit_oooe            | 0.000000                                |
| wsrep_commit_oool            | 0.000000                                |
| wsrep_commit_window          | 1.000000                                |
| wsrep_connected              | ON                                      |
| wsrep_desync_count           | 0                                       |
| wsrep_evs_delayed            |                                         |
| wsrep_evs_evict_list         |                                         |
| wsrep_evs_repl_latency       | 0/0/0/0/0                               |
| wsrep_evs_state              | OPERATIONAL                             |
| wsrep_flow_control_paused    | 0.000000                                |
| wsrep_flow_control_paused_ns | 0                                       |
| wsrep_flow_control_recv      | 0                                       |
| wsrep_flow_control_sent      | 0                                       |
| wsrep_gcomm_uuid             | f023c418-7f81-11e7-bfbb-86741b99a879    |
| wsrep_incoming_addresses     | 192.168.30.184:3306,192.168.30.186:3306 |
| wsrep_last_committed         | 3                                       |
| wsrep_local_bf_aborts        | 0                                       |
| wsrep_local_cached_downto    | 1                                       |
| wsrep_local_cert_failures    | 0                                       |
| wsrep_local_commits          | 0                                       |
| wsrep_local_index            | 1                                       |
| wsrep_local_recv_queue       | 0                                       |
| wsrep_local_recv_queue_avg   | 0.125000                                |
| wsrep_local_recv_queue_max   | 2                                       |
| wsrep_local_recv_queue_min   | 0                                       |
| wsrep_local_replays          | 0                                       |
| wsrep_local_send_queue       | 0                                       |
| wsrep_local_send_queue_avg   | 0.000000                                |
| wsrep_local_send_queue_max   | 1                                       |
| wsrep_local_send_queue_min   | 0                                       |
| wsrep_local_state            | 4                                       |
| wsrep_local_state_comment    | Synced                                  |
| wsrep_local_state_uuid       | e5b100e2-7f7e-11e7-9266-de309a905227    |
| wsrep_protocol_version       | 7                                       |
| wsrep_provider_name          | Galera                                  |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>       |
| wsrep_provider_version       | 3.16(r5c765eb)                          |
| wsrep_ready                  | ON                                      |
| wsrep_received               | 8                                       |
| wsrep_received_bytes         | 2148                                    |
| wsrep_repl_data_bytes        | 0                                       |
| wsrep_repl_keys              | 0                                       |
| wsrep_repl_keys_bytes        | 0                                       |
| wsrep_repl_other_bytes       | 0                                       |
| wsrep_replicated             | 0                                       |
| wsrep_replicated_bytes       | 0                                       |
| wsrep_thread_count           | 2                                       |
+------------------------------+-----------------------------------------+

CentOS 7.2安装配置MariaDB Galera Cluster(10.1.21-MariaDB) 三主集群环境

MariaDB Galera Cluster 介绍

Galera Cluster是由第三方公司Codership所研发的一套免费开源的集群高可用方案,实现了数据零丢失,官网地址为http://galeracluster.com/。其在MySQLInnoDB存储引擎基础上打了wrep(虚拟全同步复制),Percona/MariaDB已捆绑在各自的发行版本中。

MariaDB Galera Cluster是MariaDB同步多主机集群。它仅支持XtraDB/InnoDB存储引擎(虽然有对MyISAM实验支持,具体看wsrep_replicate_myisam系统变量)。

MariaDB Galera Cluster主要功能:

  • 同步复制

  • 真正的multi-master,即所有节点可以同时读写数据库

  • 自动的节点成员控制,失效节点自动被清除

  • 新节点加入数据自动复制

  • 真正的并行复制,行级

  • 用户可以直接连接集群,使用感受上与MySQL完全一致

优势:

  • 因为是多主,所以不存在Slavelag(延迟)

  • 不存在丢失事务的情况

  • 同时具有读和写的扩展能力

  • 更小的客户端延迟

  • 节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的

缺点:

  • 加入新节点时开销大,需要复制完整的数据

  • 不能有效地解决写扩展的问题,所有的写操作都发生在所有的节点

  • 有多少个节点,就有多少份重复的数据

  • 由于事务提交需要跨节点通信,即涉及分布式事务操作,因此写入会比主从复制慢很多,节点越多,写入越慢,死锁和回滚也会更加频繁

  • 对网络要求比较高,如果网络出现波动不稳定,则可能会造成两个节点失联,Galera Cluster集群会发生脑裂,服务将不可用

还有一些地方存在局限:

  • 仅支持InnoDB/XtraDB存储引擎,任何写入其他引擎的表,包括mysql.*表都不会被复制。但是DDL语句可以复制,但是insert into mysql.user(MyISAM存储引擎)之类的插入数据不会被复制

  • Delete操作不支持没有主键的表,因为没有主键的表在不同的节点上的顺序不同,如果执行select … limit …将出现不同的结果集

  • LOCK/UNLOCK TABLES/FLUSH TABLES WITH READ LOCKS不支持单表所锁,以及锁函数GET_LOCK()、RELEASE_LOCK(),但FLUSH TABLES WITH READ LOCK支持全局表锁

  • General Query Log日志不能保存在表中,如果开始查询日志,则只能保存到文件中

  • 不能有大事务写入,不能操作wsrep_max_ws_rows=131072(行),且写入集不能超过wsrep_max_ws_size=1073741824(1GB),否则客户端直接报错

  • 由于集群是乐观锁并发控制,因此,在commit阶段会有事务冲突发生。如果两个事务在集群中的不同节点上对同一行写入并提交,则失败的节点将回滚,客户端返回死锁报错

  • XA分布式事务不支持Codership Galera Cluster,在提交时可能会回滚

  • 整个集群的写入吞吐量取决于最弱的节点限制,集群要使用同一的配置

技术:

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

未分类

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

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

未分类

关于新节点的加入,流程如下:

未分类

新加入的节点叫做Joiner,给Joiner提供复制的节点叫Donor。在该过程中首先会检查本地grastate.dat文件的seqno事务号是否在远端donor节点galera.cache文件里,如果存在,那么进行Incremental State Transfer(IST)增量同步复制,将剩余的事务发送过去;如果不存在那么进行State Snapshot Transfer(SST)全量同步复制。SST有三种全量拷贝方式:mysqldump、rsync和xtrabackup。SST的方法可以通过wsrep_sst_method这个参数来设置。

未分类

备注:

SST是指从donor到joiner的数据全量拷贝,它通常使用在一个新的节点加入时,为了与集群同步,新的节点不得不去一个已经在集群中的节点上拷贝数据,在PXC(Percona Xtradb Cluster)中,有三种SST的方法,mysqldump,rsync,Xtrabackup。

建议使用XtraBackup,另外对XtraBackup补充说明:

在XtraBackup 2.1.x版本里,使用innobackupex备份时,备份流程如下:

  1. 备份InnoDB表数据

  2. 执行全局表读锁FLUSH TABLES WITH READ LOCKS

  3. 拷贝.frm和MyISAM表数据

  4. 得到当前的binlog文件名和position点

  5. 完成redo log事务日志的后台复制

  6. 解锁UNLOCK TABLES

由上面可以看出如果备份好几张MyISAM存储的大表时,将会进行锁表。

环境信息

  • MariaDB Server:10.1.21-MariaDB

  • CentOS:CentOS Linux release7.2.1511 (Core)

MariaDB Galera Cluster 三个集群节点主机名和IP地址信息:

  • 192.168.1.104 mariadb-a03

  • 192.168.1.105 mariadb-a04

  • 192.168.1.106 mariadb-a05

环境准备

1、配置hosts文件

# cat /etc/hosts  
127.0.0.1 localhost.localdomain localhost  
192.168.1.104 mariadb-a03  
192.168.1.105 mariadb-a04  
192.168.1.106 mariadb-a05  

2、 /etc/security/limits.conf

* soft nofile 65536  
* hard nofile 65536  

3、 /etc/sysctl.conf

fs.file-max=655350  
net.ipv4.ip_local_port_range = 1025 65000  
net.ipv4.tcp_tw_recycle = 1  

最后执行:

# sysctl -p

4、 安装Percona XtraBackup热备份工具

下载地址:

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.6/binary/tarball/percona-xtrabackup-2.4.6-Linux-x86_64.tar.gz

解压缩:

# tar -zxvf percona-xtrabackup-2.4.6-Linux-x86_64.tar.gz

拷贝脚本到指定位置:

# cd percona-xtrabackup-2.4.6-Linux-x86_64/bin/  
# cp -a * /usr/bin/  

安装依赖的一些包,比如lsof,socat,openssl,tar等

创建XtraBackup备份时用的用户名和密码:

MariaDB [(none)]> grant all on *.* to 'galera'@'localhost' identified by '123456';  

部署MariaDB

从MariaDB 10.1版本开始,Galera Cluster就已经包含在MariaDB包里面了,不需要单独部署MariaDB-Galera-server 和galera 包。

这里演示使用YUM方式部署MariaDB Galera Cluster。

步骤一:配置Yum源(192.168.1.104,192.168.1.105,192.168.1.106)

# touch /etc/yum.repos.d/MariaDB-IDC.repo  
添加如下内容:  
[mariadb]  
name = MariaDB  
baseurl =http://yum.mariadb.org/10.1/centos7-amd64  
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB  
gpgcheck=1  

由于我们的环境无法访问外网,单独部署一个MariaDB的Yum源:

# cat /etc/yum.repos.d/MariaDB-IDC.repo  
[MariaDB-10.1-IDC]  
name=MariaDB-10.1-IDC  
baseurl=http://192.168.1.100/repo/yum.mariadb.org/10.1/centos7-amd64  
gpgcheck=0  
enabled=1  

步骤二:安装MariaDB(192.168.1.104,192.168.1.105,192.168.1.106)

# yum install MariaDB-server MariaDB-clientgalera  -y  

配置 MariaDB Galera Cluster

下面我们开始配置MariaDB Galera Cluster,分别修改MariaDB Galera集群的每个节点上的/etc/my.cnf.d/server.cnf文件,具体每个节点的内容如下:

1、192.168.1.104节点的/etc/my.cnf.d/server.cnf文件内容:

[root@mariadb-a03 ~]# cat /etc/my.cnf.d/server.cnf | grep -v "#" | grep -v "^$"  
[server]  
[mysqld]  
server_id=128  
datadir=/app/galera  
user=mysql  
skip-external-locking  
skip-name-resolve  
character-set-server=utf8  

[galera]  
wsrep_causal_reads=ON  #节点应用完事务才返回查询请求  
wsrep_provider_options="gcache.size=4G"#同步复制缓冲池  
wsrep_certify_nonPK=ON   #为没有显式申明主键的表生成一个用于certificationtest的主键,默认为ON  
#log-bin=/app/galera/mysql-bin  #如果不接从库,注释掉  
#log_slave_updates=1         #如果不接从库,注释掉  
query_cache_size=0           #关闭查询缓存  
wsrep_on=ON   #开启全同步复制模式  
wsrep_provider=/usr/lib64/galera/libgalera_smm.so#galera library  
wsrep_cluster_name=MariaDB-Galera-Cluster  
    wsrep_cluster_address="gcomm://192.168.1.104,192.168.1.105,192.168.1.106"  #galera cluster URL  
wsrep_node_name=mariadb-a03  
wsrep_node_address=192.168.1.104  
binlog_format=row  
default_storage_engine=InnoDB  
innodb_autoinc_lock_mode=2   #主键自增模式修改为交叉模式  
wsrep_slave_threads=8  #开启并行复制线程,根据CPU核数设置  
innodb_flush_log_at_trx_commit=0   #事务提交每隔1秒刷盘  
innodb_buffer_pool_size=2G  
wsrep_sst_method=rsync  
[embedded]  
[mariadb]  
[mariadb-10.1]  

上面配置使用的是rsync方式同步数据,如果要使用xtrabackup方式(建议使用),需要设置:

wsrep_sst_auth=galera:123456

wsrep_sst_method=xtrabackup-v2 #默认是rsync全量拷贝,但是需要在donor节点上执行全局读锁(flushtables with read lock),建议采用xtrabackup热备份方式,只有在备份.frm表结构文件才会锁表

2、 192.168.1.105节点的/etc/my.cnf.d/server.cnf文件内容:

[root@mariadb-a04 ~]# cat /etc/my.cnf.d/server.cnf | grep -v "#" | grep -v "^$"  
[server]  
[mysqld]  
server_id=129  
datadir=/app/galera  
user=mysql  
skip-external-locking  
skip-name-resolve  
character-set-server=utf8  

[galera]  
wsrep_causal_reads=ON  
wsrep_provider_options="gcache.size=4G"  
wsrep_certify_nonPK=ON  
query_cache_size=0  
wsrep_on=ON  
wsrep_provider=/usr/lib64/galera/libgalera_smm.so  
wsrep_cluster_name=MariaDB-Galera-Cluster  
    wsrep_cluster_address="gcomm://192.168.1.104,192.168.1.105,192.168.1.106"  
wsrep_node_name=mariadb-a04  
wsrep_node_address=192.168.1.105  
binlog_format=row  
default_storage_engine=InnoDB  
innodb_autoinc_lock_mode=2  
wsrep_slave_threads=8  
innodb_flush_log_at_trx_commit=0  
innodb_buffer_pool_size=2G  
wsrep_sst_method=rsync  
[embedded]  
[mariadb]  
[mariadb-10.1]  

3、 192.168.1.106节点的/etc/my.cnf.d/server.cnf文件内容:

[root@mariadb-a05 yum.repos.d]# cat /etc/my.cnf.d/server.cnf | grep -v "#" | grep -v "^$"  
[server]  
[mysqld]  
server_id=130  
datadir=/app/galera  
user=mysql  
skip-external-locking  
skip-name-resolve  
character-set-server=utf8  

[galera]  
wsrep_causal_reads=ON  
wsrep_provider_options="gcache.size=4G"  
wsrep_certify_nonPK=ON  
query_cache_size=0  
wsrep_on=ON  
wsrep_provider=/usr/lib64/galera/libgalera_smm.so  
wsrep_cluster_name=MariaDB-Galera-Cluster  
    wsrep_cluster_address="gcomm://192.168.1.104,192.168.1.105,192.168.1.106"  
wsrep_node_name=mariadb-a05  
wsrep_node_address=192.168.1.106  
binlog_format=row  
default_storage_engine=InnoDB  
innodb_autoinc_lock_mode=2  
wsrep_slave_threads=8  
innodb_flush_log_at_trx_commit=0  
innodb_buffer_pool_size=2G  
wsrep_sst_method=rsync  
[embedded]  
[mariadb]  
[mariadb-10.1]  

MariaDB一个节点初始化安装(192.168.1.104):

# mysql_install_db --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql  

在192.168.1.104节点上通过bootstrap启动(第一次启动一定要使用–wsrep-new-cluster,再次启动就不需要)

mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql  --wsrep-new-cluster &  

在192.168.1.104节点上设置root密码以及安全设置(192.168.1.104,192.168.1.105,192.168.1.106)

/usr/bin/mysql_secure_installation  
或  
mysql_secure_installation  

在192.168.1.105,192.168.1.106节点启动MariaDB:

mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql  &  

验证操作

登录三个节点查看

192.168.1.104节点:  
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';  
+--------------------+-------+  
| Variable_name      | Value |  
+--------------------+-------+  
| wsrep_cluster_size | 3     |  
+--------------------+-------+  
1 row in set (0.00 sec)  

MariaDB [(none)]> show global status like 'ws%';  
+------------------------------+-------------------------------------------------------------+  
| Variable_name                | Value                                                      |  
+------------------------------+-------------------------------------------------------------+  
| wsrep_apply_oooe             | 0.000000                                                   |  
| wsrep_apply_oool             | 0.000000                                                   |  
| wsrep_apply_window           | 1.000000                                                    |  
| wsrep_causal_reads           | 11                                                         |  
| wsrep_cert_deps_distance     | 1.000000                                                   |  
| wsrep_cert_index_size        | 2                                                           |  
| wsrep_cert_interval          | 0.000000                                                   |  
| wsrep_cluster_conf_id        | 3                                                          |  
| wsrep_cluster_size           | 3                                                          |  
| wsrep_cluster_state_uuid     |3108c722-ff29-11e6-a31f-bb500598d033                        |  
| wsrep_cluster_status         | Primary                                                     |  
| wsrep_commit_oooe            | 0.000000                                                   |  
| wsrep_commit_oool            | 0.000000                                                   |  
| wsrep_commit_window          | 1.000000                                                    |  
| wsrep_connected              | ON                                                         |  
| wsrep_desync_count           | 0                                                          |  
| wsrep_evs_delayed            |                                                            |  
| wsrep_evs_evict_list         |                                                            |  
| wsrep_evs_repl_latency       | 0/0/0/0/0                                                   |  
| wsrep_evs_state              | OPERATIONAL                                                |  
| wsrep_flow_control_paused    | 0.000000                                                   |  
| wsrep_flow_control_paused_ns | 0                                                           |  
| wsrep_flow_control_recv      | 0                                                          |  
| wsrep_flow_control_sent      | 0                                                          |  
| wsrep_gcomm_uuid             |3107a278-ff29-11e6-96d3-374133af7e21                        |  
| wsrep_incoming_addresses     | 192.168.1.105:3306,192.168.1.106:3306,192.168.1.104:3306|  
| wsrep_last_committed         | 3                                                           |  
| wsrep_local_bf_aborts        | 0                                                          |  
| wsrep_local_cached_downto    | 1                                                          |  
| wsrep_local_cert_failures    | 0                                                           |  
| wsrep_local_commits          | 0                                                          |  
| wsrep_local_index            | 2                                                          |  
| wsrep_local_recv_queue       | 0                                                          |  
| wsrep_local_recv_queue_avg   | 0.000000                                                   |  
| wsrep_local_recv_queue_max   | 1                                                          |  
| wsrep_local_recv_queue_min   | 0                                                          |  
| wsrep_local_replays          | 0                                                          |  
| wsrep_local_send_queue       | 0                                                           |  
| wsrep_local_send_queue_avg   | 0.000000                                                   |  
| wsrep_local_send_queue_max   | 1                                                          |  
| wsrep_local_send_queue_min   | 0                                                          |  
| wsrep_local_state            | 4                                                          |  
| wsrep_local_state_comment    | Synced                                                     |  
| wsrep_local_state_uuid       |3108c722-ff29-11e6-a31f-bb500598d033                        |  
| wsrep_protocol_version       | 7                                                          |  
| wsrep_provider_name          | Galera                                                      |  
| wsrep_provider_vendor        | Codership Oy<info@codership.com>                           |  
| wsrep_provider_version       | 25.3.19(r3667)                                              |  
| wsrep_ready                  | ON                                                         |  
| wsrep_received               | 10                                                         |  
| wsrep_received_bytes         | 806                                                        |  
| wsrep_repl_data_bytes        | 1044                                                       |  
| wsrep_repl_keys              | 3                                                          |  
| wsrep_repl_keys_bytes        | 93                                                          |  
| wsrep_repl_other_bytes       | 0                                                          |  
| wsrep_replicated             | 3                                                          |  
| wsrep_replicated_bytes       | 1329                                                        |  
| wsrep_thread_count           | 9                                                          |  
+------------------------------+-------------------------------------------------------------+  
58 rows in set (0.00 sec)

注释:

  • wsrep_cluster_status为Primary,表示节点为主节点,正常读写。

  • wsrep_ready为ON,表示集群正常运行。

  • wsrep_cluster_size为3,表示集群有三个节点。

创建数据库测试

192.168.1.104节点:  
[root@mariadb-a03 my.cnf.d]# mysql -uroot –pxxxxxx  
MariaDB [(none)]> create databasetest_db;  
Query OK, 1 row affected (0.01 sec)  
192.168.1.105节点查看:  
[root@mariadb-a04 my.cnf.d]# mysql -uroot -pxxxxxx  
MariaDB [(none)]> show databases;  
+--------------------+  
| Database           |  
+--------------------+  
| information_schema |  
| mysql              |  
| performance_schema |  
| test_db            |  
+--------------------+  
192.168.1.106节点查看:  
[root@mariadb-a05 my.cnf.d]# mysql -uroot -pxxxxxx  
MariaDB [(none)]> show databases;  
+--------------------+  
| Database           |  
+--------------------+  
| information_schema |  
| mysql              |  
| performance_schema |  
| test_db            |  
+--------------------+  
4 rows in set (0.00 sec)  

可以看到集群正常使用。

创建MyISAM表测试

[root@mariadb-a03 my.cnf.d]# mysql -uroot –pxxxxxx  
MariaDB [(none)]> use test_db;  
Database changed  
MariaDB [test_db]> create table myisam_tbl (id int,name text) ENGINE MyISAM;  
Query OK, 0 rows affected (0.01 sec)  

MariaDB [test_db]> insert into myisam_tbl values(1,'hive');  
Query OK, 1 row affected (0.00 sec)  

MariaDB [test_db]> insert into myisam_tbl values(2,'hbase');  
Query OK, 1 row affected (0.00 sec)  

其他节点查看:

[root@mariadb-a04 my.cnf.d]# mysql -uroot -pxxxxxx  
MariaDB [(none)]> use test_db;  
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  
MariaDB [test_db]> select * from myisam_tbl;  
Empty set (0.00 sec)  

[root@mariadb-a05 my.cnf.d]# mysql -uroot –pxxxxxx  
MariaDB [(none)]> use test_db;  
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  
MariaDB [test_db]> select * from myisam_tbl;  
Empty set (0.00 sec)  

可以看到MyISAM存储的表,Galera不支持同步。它仅支持XtraDB/ InnoDB存储引擎(虽然有对MyISAM实验支持,具体看wsrep_replicate_myisam系统变量)。

验证InnoDB存储的表

[root@mariadb-a03 my.cnf.d]# mysql -uroot  –pxxxxxx  
MariaDB [test_db]> create table innodb_tbl(id int,name text) ENGINE InnoDB;  
Query OK, 0 rows affected (0.04 sec)  

MariaDB [test_db]> insert into innodb_tbl values(1,'hive');  
Query OK, 1 row affected (0.00 sec)  

MariaDB [test_db]> insert into innodb_tbl values(2,'hbase');  
Query OK, 1 row affected (0.00 sec)  

MariaDB [test_db]>  

其他节点查看:

[root@mariadb-a04 my.cnf.d]# mysql -uroot -pxxxxxx  
MariaDB [(none)]> use test_db;  
Reading table information for completion oftable and column names  
You can turn off this feature to get aquicker startup with -A  

Database changed  
MariaDB [test_db]> select * from innodb_tbl;  
+------+-------+  
| id  | name  |  
+------+-------+  
|   1 | hive  |  
|   2 | hbase |  
+------+-------+  
2 rows in set (0.00 sec)  

[root@mariadb-a05 my.cnf.d]# mysql -uroot –pxxxxxx  
MariaDB [(none)]> use test_db;  
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  
MariaDB [test_db]> select * from innodb_tbl;  
+------+-------+  
| id  | name  |  
+------+-------+  
|   1 | hive  |  
|   2 | hbase |  
+------+-------+  
2 rows in set (0.00 sec)  

模拟节点故障

将192.168.1.104数据库停止掉:

[root@mariadb-a03 system]# mysqladmin -uroot -p "shutdown"  

然后在其他节点192.168.1.105执行:

MariaDB [test_db]> show global status like 'wsrep%';  
+------------------------------+-----------------------------------------------+  
| Variable_name                | Value                                         |  
+------------------------------+-----------------------------------------------+  
| wsrep_apply_oooe             | 0.000000                                      |  
| wsrep_apply_oool             | 0.000000                                      |  
| wsrep_apply_window           | 1.000000                                      |  
| wsrep_causal_reads           | 26                                            |  
| wsrep_cert_deps_distance     | 1.142857                                      |  
| wsrep_cert_index_size        |6                                            |  
| wsrep_cert_interval          | 0.000000                                      |  
| wsrep_cluster_conf_id        | 6                                             |  
| wsrep_cluster_size          | 2                                             |  
| wsrep_cluster_state_uuid     |3108c722-ff29-11e6-a31f-bb500598d033         |  
| wsrep_cluster_status         | Primary                                       |  
| wsrep_commit_oooe            | 0.000000                                      |  
| wsrep_commit_oool            | 0.000000                                      |  
| wsrep_commit_window          | 1.000000                                      |  
| wsrep_connected              | ON                                            |  
| wsrep_desync_count           | 0                                             |  
| wsrep_evs_delayed            |                                              |  
| wsrep_evs_evict_list         |                                              |  
| wsrep_evs_repl_latency       |0.000403989/0.000656768/0.0012094/0.0003239/4 |  
| wsrep_evs_state              | OPERATIONAL                                   |  
| wsrep_flow_control_paused    | 0.000000                                      |  
| wsrep_flow_control_paused_ns | 0                                             |  
| wsrep_flow_control_recv      | 0                                             |  
| wsrep_flow_control_sent      | 0                                             |  
| wsrep_gcomm_uuid             | 0ce8537e-ff2a-11e6-b037-8a383b6a8db5          |  
| wsrep_incoming_addresses    | 192.168.1.105:3306,192.168.1.106:3306       |  
| wsrep_last_committed         | 10                                            |  
| wsrep_local_bf_aborts        | 0                                            |  
| wsrep_local_cached_downto    | 4                                             |  
| wsrep_local_cert_failures    | 0                                             |  
| wsrep_local_commits          | 0                                             |  
| wsrep_local_index            | 0                                             |  
| wsrep_local_recv_queue       | 0                                             |  
| wsrep_local_recv_queue_avg   | 0.000000                                      |  
| wsrep_local_recv_queue_max   | 1                                             |  
| wsrep_local_recv_queue_min   | 0                                             |  
| wsrep_local_replays          | 0                                             |  
| wsrep_local_send_queue       | 0                                             |  
| wsrep_local_send_queue_avg   | 0.000000                                      |  
| wsrep_local_send_queue_max   | 1                                             |  
| wsrep_local_send_queue_min   | 0                                             |  
| wsrep_local_state            | 4                                             |  
| wsrep_local_state_comment    | Synced                                        |  
| wsrep_local_state_uuid       |3108c722-ff29-11e6-a31f-bb500598d033         |  
| wsrep_protocol_version       | 7                                             |  
| wsrep_provider_name          | Galera                                        |  
| wsrep_provider_vendor        | Codership Oy<info@codership.com>             |  
| wsrep_provider_version       | 25.3.19(r3667)                                |  
| wsrep_ready                  | ON                                            |  
| wsrep_received               | 14                                            |  
| wsrep_received_bytes         | 3908                                          |  
| wsrep_repl_data_bytes        | 0                                             |  
| wsrep_repl_keys              | 0                                             |  
| wsrep_repl_keys_bytes        | 0                                             |  
| wsrep_repl_other_bytes       | 0                                             |  
| wsrep_replicated             | 0                                             |  
| wsrep_replicated_bytes       | 0                                             |  
| wsrep_thread_count           | 9                                             |  
+------------------------------+-----------------------------------------------+

此时集群为自动将192.168.1.104故障节点剔除掉,并且正常提供服务。

最后我们恢复失败的节点:

[root@mariadb-a03 system]# mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql &  

再次查看集群环境:

MariaDB [test_db]> SHOW STATUS LIKE 'wsrep_cluster_size';  
+--------------------+-------+  
| Variable_name      | Value |  
+--------------------+-------+  
| wsrep_cluster_size | 3     |  
+--------------------+-------+  
1 row in set (0.00 sec  

模拟脑裂后的处理

下面模拟在网络抖动发生丢包的情况下,两个节点失联导致脑裂。首先,在192.168.1.105和192.168.1.106两个节点上分别执行:

iptables -A INPUT -p tcp --sport 4567 -j DROP

iptables -A INPUT -p tcp --dport 4567 -j DROP

以上命令用来禁止wsrep全同步复制4567端口通信。

然后我们在192.168.1.104节点查看:

MariaDB [(none)]> show global statuslike 'ws%';  
可以看到下面的几个值:  
wsrep_cluster_size    1  
wsrep_cluster_status  non-Primary  
wsrep_ready         OFF  

MariaDB [(none)]> use test_db;  
ERROR 1047 (08S01): WSREP has not yetprepared node for application use  

MariaDB [(none)]> select@@wsrep_node_name;  
ERROR 1205 (HY000): Lock wait timeoutexceeded; try restarting transaction  

现在已经出现脑裂的情况,并且集群无法执行任何命令。

为了解决这个问题,可以执行:

set global wsrep_provider_options="pc.bootstrap=true";

通过这个命令来强制恢复出现脑裂的节点。

下面我们来验证一下:

MariaDB [(none)]> select @@wsrep_node_name;  
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  
MariaDB [(none)]> set global wsrep_provider_options="pc.bootstrap=true";  
Query OK, 0 rows affected (0.00 sec)  

MariaDB [(none)]> select @@wsrep_node_name;  
+-------------------+  
| @@wsrep_node_name |  
+-------------------+  
| mariadb-a03     |  
+-------------------+  
1 row in set (0.27 sec)  

MariaDB [(none)]> use test_db;  
Reading table information for completion oft able and column names  
You can turn off this feature to get a quicker startup with -A  

Database changed  
MariaDB [test_db]> show tables;  
+-------------------+  
| Tables_in_test_db |  
+-------------------+  
| innodb_tbl        |  
| myisam_tbl        |  
+-------------------+  

最后我们将节点192.168.1.105和192.168.1.106恢复一下,只要清理一下iptables表即可(因为我的是测试环境,生产环境需要删除上面的规则即可):

iptables –F

各个节点验证一下:

192.168.1.104:  
MariaDB [test_db]> SHOW STATUS LIKE  'wsrep_cluster_size';  
+--------------------+-------+  
| Variable_name      | Value |  
+--------------------+-------+  
| wsrep_cluster_size | 3     |  
+--------------------+-------+  
1 row in set (0.00 sec)  


192.168.1.105:  
MariaDB [(none)]> select @@wsrep_node_name;  
+-------------------+  
| @@wsrep_node_name |  
+-------------------+  
| mariadb-a04     |  
+-------------------+  

避免脏读

Galera Cluster不是真正意义上的全同步复制,存在延迟。我们可以在一个节点上面执行FLUSH TABLES WITH READ LOCK;全局读锁。

然后在其他节点执行写操作,观察延迟情况。

比如我们在192.168.1.106节点执行全局读锁设置:

MariaDB [test_db]> flush tables with read lock;  
Query OK, 0 rows affected (0.00 sec)  

MariaDB [test_db]> select * from innodb_tbl;  
+------+------+  
| id  | name |  
+------+------+  
|   1 | hive |  
+------+------+  
1 row in set (0.00 sec)  


然后在192.168.1.104节点插入操作:  
MariaDB [test_db]> select @@wsrep_node_name;  
+-------------------+  
| @@wsrep_node_name |  
+-------------------+  
| mariadb-a03     |  
+-------------------+  
1 row in set (0.00 sec)  

MariaDB [test_db]> insert into innodb_tbl values(2,'hbase');  
Query OK, 1 row affected (0.00 sec)  

MariaDB [test_db]> select * from innodb_tbl;  
+------+-------+  
| id  | name  |  
+------+-------+  
|   1 | hive  |  
|   2 | hbase |  
+------+-------+  
2 rows in set (0.00 sec)  

在节点192.168.1.106上测试查询操作:  
MariaDB [test_db]> select * from innodb_tbl;  
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  

这里之所以没有读取到脏数据,是因为我在MariaDB配置文件中设置了wsrep_causal_reads=ON;

我们将wsrep_causal_reads修改为0或OFF来看一下效果:

MariaDB [test_db]> set wsrep_causal_reads=0;  
Query OK, 0 rows affected, 1 warning (0.00sec)  

MariaDB [test_db]> select * from innodb_tbl;  
+------+------+  
| id  | name |  
+------+------+  
|   1 | hive |  
+------+------+  
1 row in set (0.00 sec)  

MariaDB [test_db]>  

总结

通过上面的一系列测试,最后总结一下:

1、 在生产环境下应该避免使用大事务,不建议在高并发写入场景下使用Galera Cluster架构,会导致集群限流,从而引起整个集群hang住,出现生产故障。针对这种情况可以考虑主从,实现读写分离等手段。

2、对数据一致性要求较高,并且数据写入不频繁,数据库容量也不大(50GB左右),网络状况良好的情况下,可以考虑使用Galera方案。

centos7.3 系统MariaDB Galera Cluster多主集群搭建

1. 环境

CentOS Linux release 7.3
MariaDB 10.1.25

2. 安装MariaDB

配置mariadb10.1的yum源

[root@centos7-compute1 ~]# cat /etc/yum.repos.d/MariaDB.repo
[root@centos7-compute2 ~]# cat /etc/yum.repos.d/MariaDB.repo
[root@centos7-compute3 ~]# cat /etc/yum.repos.d/MariaDB.repo
MariaDB.repo:
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

3. 使用yum安装

[root@centos7-compute1 ~]# sudo yum install MariaDB-server MariaDB-client galera
[root@centos7-compute2 ~]# sudo yum install MariaDB-server MariaDB-client galera
[root@centos7-compute3 ~]# sudo yum install MariaDB-server MariaDB-client galera

4. 安全配置

[root@centos7-compute1 ~]# /usr/bin/mysql_secure_installation
[root@centos7-compute2 ~]# /usr/bin/mysql_secure_installation
[root@centos7-compute3 ~]# /usr/bin/mysql_secure_installation
compute1,compute2,compute3 三节点 启动MariaDB并赋权:
grant all privileges on *.* to root@"%" identified by "123456";
flush privileges;
然后全部节点关闭数据库
[root@centos7-compute1 ~]# systemctl stop mariadb
[root@centos7-compute2 ~]# systemctl stop mariadb
[root@centos7-compute3 ~]# systemctl stop mariadb
注意:此时需要全部节点关闭selinx,防火墙。防止接下来的影响集群通讯
 systemctl stop firewalld
systemctl disable firewalld

5. 配置MariaDB Galera Cluster

修改三台节点上的/etc/my.cnf.d/server.cnf 文件
compute1 配置如下:

[mysqld]

[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='my_wsrep_cluster'
wsrep_cluster_address = "gcomm://192.168.140.197,192.168.140.141,192.168.140.192"
wsrep_node_name = centos7-compute1
wsrep_node_address=192.168.140.197
wsrep_on=ON
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
wsrep_sst_method=rsync
wsrep_sst_auth=root:123456

[embedded]

[mariadb]

[mariadb-10.1]

compute2 配置如下:

[mysqld]

[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='my_wsrep_cluster'
wsrep_cluster_address = "gcomm://192.168.140.197,192.168.140.141,192.168.140.192"
wsrep_node_name = centos7-compute2
wsrep_node_address=192.168.140.141
wsrep_on=ON
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
wsrep_sst_method=rsync
wsrep_sst_auth=root:123456

[embedded]

[mariadb]

[mariadb-10.1]

compute3 配置如下:

[mysqld]

[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='my_wsrep_cluster'
wsrep_cluster_address = "gcomm://192.168.140.197,192.168.140.141,192.168.140.192"
wsrep_node_name = centos7-compute3
wsrep_node_address=192.168.140.192
wsrep_on=ON
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
wsrep_sst_method=rsync
wsrep_sst_auth=root:123456

[embedded]

[mariadb]

[mariadb-10.1]

6. 启动集群

引导集群创建
只在compute1操作:

[root@centos7-compute1 ~]# /usr/sbin/mysqld --wsrep-new-cluster --user=root &amp;

查看集群状态

MariaDB [(none)]> show  status like "wsrep_cluster_size";  
+--------------------+-------------+  
| Variable_name      | Value |  
+--------------------+-------------+  
| wsrep_cluster_size | 1     |  
+--------------------+-------------+  


MariaDB [(none)]> show  status like "wsrep%";  
+------------------------------+------------------------------------------------+  
| Variable_name                | Value                                          |  
+------------------------------+------------------------------------------------+  
| wsrep_apply_oooe             | 0.000000                                       |  
| wsrep_apply_oool             | 0.000000                                       |  
| wsrep_apply_window           | 0.000000                                       |  
| wsrep_causal_reads           | 0                                              |  
| wsrep_cert_deps_distance     | 0.000000                                       |  
| wsrep_cert_index_size        | 0                                              |  
| wsrep_cert_interval          | 0.000000                                       |  
| wsrep_cluster_conf_id        | 1                                              |  
| wsrep_cluster_size           | 1                                              |  
| wsrep_cluster_state_uuid     | 1e434901-71b5-11e7-b190-7bb2f4bbed7a           |  
| wsrep_cluster_status         | Primary                                        |  
| wsrep_commit_oooe            | 0.000000                                       |  
| wsrep_commit_oool            | 0.000000                                       |  
| wsrep_commit_window          | 0.000000                                       |  
| wsrep_connected              | ON                                             |  
| wsrep_desync_count           | 0                                              |  
| wsrep_evs_delayed            |                                                |  
| wsrep_evs_evict_list         |                                                |  
| wsrep_evs_repl_latency       | 5.592e-06/1.25208e-05/2.5685e-05/8.62896e-06/5 |  
| wsrep_evs_state              | OPERATIONAL                                    |  
| wsrep_flow_control_paused    | 0.000000                                       |  
| wsrep_flow_control_paused_ns | 0                                              |  
| wsrep_flow_control_recv      | 0                                              |  
| wsrep_flow_control_sent      | 0                                              |  
| wsrep_gcomm_uuid             | 35623b8e-71ad-11e7-af9f-52f25b42ebcf           |  
| wsrep_incoming_addresses     | 192.168.140.197:3306                           |  
| wsrep_last_committed         | 0                                              |  
| wsrep_local_bf_aborts        | 0                                              |  
| wsrep_local_cached_downto    | 18446744073709551615                           |  
| wsrep_local_cert_failures    | 0                                              |  
| wsrep_local_commits          | 0                                              |  
| wsrep_local_index            | 0                                              |  
| wsrep_local_recv_queue       | 0                                              |  
| wsrep_local_recv_queue_avg   | 0.500000                                       |  
| wsrep_local_recv_queue_max   | 2                                              |  
| wsrep_local_recv_queue_min   | 0                                              |  
| wsrep_local_replays          | 0                                              |  
| wsrep_local_send_queue       | 0                                              |  
| wsrep_local_send_queue_avg   | 0.000000                                       |  
| wsrep_local_send_queue_max   | 1                                              |  
| wsrep_local_send_queue_min   | 0                                              |  
| wsrep_local_state            | 4                                              |  
| wsrep_local_state_comment    | Synced                                         |  
| wsrep_local_state_uuid       | 1e434901-71b5-11e7-b190-7bb2f4bbed7a           |  
| wsrep_protocol_version       | 7                                              |  
| wsrep_provider_name          | Galera                                         |  
| wsrep_provider_vendor        | Codership Oy <info@codership.com>              |  
| wsrep_provider_version       | 25.3.20(r3703)                                 |  
| wsrep_ready                  | ON                                             |  
| wsrep_received               | 2                                              |  
| wsrep_received_bytes         | 155                                            |  
| wsrep_repl_data_bytes        | 0                                              |  
| wsrep_repl_keys              | 0                                              |  
| wsrep_repl_keys_bytes        | 0                                              |  
| wsrep_repl_other_bytes       | 0                                              |  
| wsrep_replicated             | 0                                              |  
| wsrep_replicated_bytes       | 0                                              |  
| wsrep_thread_count           | 2                                              |  
+------------------------------+------------------------------------------------+

向集群中添加其他节点:

[root@centos7-compute2 ~]# systemctl start mariadb
[root@centos7-compute3 ~]# systemctl start mariadb

查看集群状态:

MariaDB [(none)]> show status like "wsrep_cluster_size";  
+--------------------+-------+  
| Variable_name      | Value |  
+--------------------+-------+  
| wsrep_cluster_size | 3     |  
+--------------------+-------+  
1 row in set (0.00 sec)  

7. 测试数据同步

在 compute1上创建数据库,表并插入数据,观察compute2,compute3 数据情况

MariaDB [(none)]> create database galera;
MariaDB [galera]> create table t (id int primary key);
insert into t value(1);
insert into t value(2);
insert into t value(3);

查看compute2,compute3数据库

MariaDB [galera]> show tables;  
+------------------+  
| Tables_in_galera |  
+------------------+  
| t                |  
+------------------+  
1 row in set (0.00 sec)  
MariaDB [galera]> select * from t;  
+----+  
| id |  
+----+  
|  1 |  
|  2 |  
|  3 |  
+----+

经过测试,删除数据库,增删查改表都可以实时同步。

8. 故障测试

[root@centos7-compute3 ~]# systemctl stop mariadb

然后在compute1,compute2 分别插入数据时,等待compute3启动后自己会同步

并且此时集群节点为两个。

MariaDB [galera]> SHOW STATUS LIKE 'wsrep_cluster_size';  
+--------------------+-------+  
| Variable_name      | Value |  
+--------------------+-------+  
| wsrep_cluster_size | 2     |  
+--------------------+-------+  

CentOS安装配置MariaDB Galera Cluster集群

MariaDB Galera Cluster是MariaDB的同步多主集群。它仅在Linux上可用,并且仅支持xtraDB/InnoDB存储引擎,对MyISAM也有一定的支持(https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_replicate_myisam),

特征:

  • 同步复制
  • 多主可同时读写 Active-active multi-master
  • 任何节点成员可读写
  • 自动成员控制,故障节点从群集中删除
  • 节点可自动加入(基于配置)
  • 真正的并行复制,在行级复制
  • 使用者在客户端连接,在使用和感官上和mysql一样

优点:

  • 没有从库延迟
  • 不会丢失事物
  • 读写扩展高(后续文章会加入读写分离)
  • 多主,不存在slave 延迟,也不以来binlog
  • 具有同步复制,故障切换和重新同步的高可用性解决方案
  • 所有服务器都具有最新数据(无滞后)
  • 跨数据中心的高可用性

它依赖于wsrep API(https://launchpad.net/wsrep):

wsrep API定义了一组应用程序回调和复制库调用,以实现事务数据库和类似应用程序的同步写入复制。在从应用程序详细信息中抽象和分离复制实现。虽然此接口的主要目标是基于认证的多主机复制,但同样适用于异步和同步主/从复制。

它的复制过程也是基于认证的,基于WSREP API,大量的配置参考:

https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_replicate_myisam

Galera可以利用四个端口:

  • 3306 数据库端口
  • 4567 对于Galera Cluster复制,组播复制在此端口上同时使用UDP传输和TCP。
  • 4568 增量数据同步IST,节点下线、重启后使用该端口,增量同步数据,增量状态转移。
  • 4444 镜像数据传输SST,集群数据同步端口,全量同步,新节点加入时起作用

三台机器:10.0.1.49,10.10.240.113,10.0.1.61
其实用起来可以是这样的:

MySQL

前面使用LVS等进行调度,当然在中间可以使用中间件进行读写分离

I. yum安装选择

https://downloads.mariadb.org/mariadb/repositories/
centos7 yum如下:

[root@LinuxEA ~]# cat > /etc/yum.repos.d/mariadb.repo << EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
[root@LinuxEA ~]# yum install socat MariaDB-Galera-server MariaDB-client rsync galera

其他版本tar包安装下载地址:
https://downloads.mariadb.org/mariadb-galera/+releases/

本次使用二进制安装:
mariadb-galera安装:http://download.nus.edu.sg/mirror/mariadb//mariadb-galera-10.0.30/bintar-linux-x86_64/mariadb-galera-10.0.30-linux-x86_64.tar.gz

galera安装:http://releases.galeracluster.com/centos/7/x86_64/galera-3-25.3.20-2.el7.x86_64.rpm

防火墙添加:

iptables -I INPUT 4 -p tcp -m tcp -m state --state NEW -m multiport --dports 3306,4444,4567,4568 -m comment --comment &quot;mariadb-galera&quot; -j ACCEPT

其他依赖包:

yum install socat MariaDB-client rsync galera lsof

文档参考

  • 编译参考:https://mariadb.com/kb/en/mariadb/installating-galera-from-source/
  • 理解参考:https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/
  • 其他教程:https://severalnines.com/resources/tutorials/galera-cluster-mysql-tutorial
  • http://galeracluster.com/products/technology/
  • Galera Cluster 局限:https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

II. node1

解压

[root@LinuxEA /data]# tar xf mariadb-galera-10.0.30-linux-x86_64.tar.gz -C /usr/local/
[root@LinuxEA /data]# cd /usr/local/
[root@LinuxEA /usr/local]# ln -s mariadb-galera-10.0.30-linux-x86_64/ mysql

安装

[root@LinuxEA /usr/local]# useradd mysql -s /sbin/nologin -M
[root@LinuxEA /usr/local]# mkdir -p /data/mysql 
[root@LinuxEA /usr/local]# chown -R mysql.mysql  /data/mysql 
[root@LinuxEA /usr/local]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql

复制启动脚本

[root@LinuxEA /usr/local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@LinuxEA /usr/local]# systemctl enable mysqld
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on
[root@LinuxEA /usr/local]# 

安装jemalloc内存分配器

[root@LinuxEA /]# wget https://github.com/jemalloc/jemalloc/releases/download/4.2.1/jemalloc-4.2.1.tar.bz2
[root@LinuxEA /]# yum install -y gcc lsof
[root@LinuxEA /]# tar xf jemalloc-4.2.1.tar.bz2
[root@LinuxEA /]# cd jemalloc-4.2.1/
[root@LinuxEA /jemalloc-4.2.1]# ./configure
[root@LinuxEA /jemalloc-4.2.1]# make 
[root@LinuxEA /jemalloc-4.2.1]# make install

授权用户:

grant all privileges on *.* to 'tb'@'%' identified by 'password';
flush privileges;

III. 配置文件部分说明:

  1. wsrep_cluster_address=gcomm:// 如下:
    这条命令,gcomm://是一个特殊的参数,在启动第一台数据库时需要使用这个参数来启动,否则会启动失败

  2. wsrep_cluster_address=”gcomm://10.0.1.49,10.10.240.113,10.0.1.61″如下:
    gcomm://后的是集群成员的ip地址

  3. wsrep_sst_auth=tb:password如下:这里的sst需要安装lsof依赖包
    这个参数就是我们之前设定的用来同步的用户名和密码

  4. wsrep_node_name=’node1′ 如下:
    当前node

  5. wsrep_node_address=’10.0.1.49′ 如下:
    当前node ip地址

IV. 部分配置文件如下

galera

[galera]
##https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_cluster_address=&quot;gcomm://&quot;
wsrep_cluster_address=&quot;gcomm://10.0.1.49,10.10.240.113,10.0.1.61&quot;
wsrep_cluster_name='cluster'
wsrep_node_address='10.0.1.49'
wsrep_node_name='node1'
#wsrep_replicate_myisam=1
####wsrep_slave_threads = 8
#wsrep_slave_threads = 16
##wsrep_provider_options=&quot;gcs.fc_limit=512&quot;
#wsrep_provider_options=&quot;gcache.page_size=128M;gcache.size=2G;gcs.fc_limit=512;gcs.fc_factor=0.9;evs.send_window=256;evs.user_send_window=128;cert.log_conflicts=yes&quot;
##wsrep_notify_cmd='/data/galeranotify/galeranotify.py'
#wsrep_log_conflicts=1
#wsrep_forced_binlog_format=ROW
#wsrep_drupal_282555_workaround=1
#wsrep_max_ws_size=2147483647
##wsrep_dirty_reads=0 #当值是1的时候这个节点是只读节点
##wsrep_sst_method=[mysqldump|xtrabackup|xtrabackup-v2|rsync|rsync_wan]
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep_sst_auth=tb:password

V. 启动第一台:

这里需要注意的是,第一次启动使用–wsrep-new-cluster,如果此时集群中这台故障,需要修改配置文件打开:wsrep_cluster_address=”gcomm://10.0.1.49,10.10.240.113,10.0.1.61″这一项,如过其他两天数据已经和这台不同步,这需要删除目录数据后进行同步恢复并加入集群

[root@LinuxEA /data/mysql]# /etc/init.d/mysqld start --wsrep-new-cluster

做一些简单的优化:

DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db LIKE 'test%';
DROP DATABASE test;
UPDATE mysql.user SET password = password('abc123') WHERE user = 'root';

VI. 启动第二台:

第二台的mariadb安装完成后,直接删掉/data/mysql下的文件,从10.0.1.49同步

[root@LinuxEA-2 /data/mysql]# rm -rf *
[root@LinuxEA-2 /data/mysql]# /etc/init.d/mysqld start
Starting MySQL.170524 17:43:41 mysqld_safe Adding '/usr/local/lib/libjemalloc.so' to LD_PRELOAD for mysqld
170524 17:43:41 mysqld_safe Logging to '/data/mysql/mysql-error.log'.
170524 17:43:41 mysqld_safe Starting mysqld daemon with databases from /data/mysql
..........SST in progress, setting sleep higher.... SUCCESS! 

部分配置文件:

[galera]
##https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html
wsrep_on=ON
wsrep_cluster_address=&quot;gcomm://10.0.1.49,10.10.240.113,10.0.1.61&quot;
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='cluster'
wsrep_node_address='10.0.1.61'
wsrep_node_name='node2'
#wsrep_replicate_myisam=1
#wsrep_node_name='node1'
#wsrep_replicate_myisam=1
####wsrep_slave_threads = 8
#wsrep_slave_threads = 16
##wsrep_provider_options=&quot;gcs.fc_limit=512&quot;
#wsrep_provider_options=&quot;gcache.page_size=128M;gcache.size=2G;gcs.fc_limit=512;gcs.fc_factor=0.9;evs.send_window=256;evs.user_send_window=128;cert.log_conflicts=yes&quot;
##wsrep_notify_cmd='/data/galeranotify/galeranotify.py'
#wsrep_log_conflicts=1
#wsrep_forced_binlog_format=ROW
#wsrep_drupal_282555_workaround=1
#wsrep_max_ws_size=2147483647
##wsrep_dirty_reads=0 #当值是1的时候这个节点是只读节点
##wsrep_sst_method=[mysqldump|xtrabackup|xtrabackup-v2|rsync|rsync_wan]
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep_sst_auth=tb:password

VII. 启动第三台

同样删除/data/mysql下的文件,从10.0.1.49同步

[root@LinuxEA-3 /data/mysql]# rm -rf *
[root@LinuxEA-3 /data/mysql]# /etc/init.d/mysqld start
Starting MySQL.170524 18:58:34 mysqld_safe Adding '/usr/local/lib/libjemalloc.so' to LD_PRELOAD for mysqld
170524 18:58:34 mysqld_safe Logging to '/data/mysql/mysql-error.log'.
170524 18:58:34 mysqld_safe Starting mysqld daemon with databases from /data/mysql
.....SST in progress, setting sleep higher.. SUCCESS! 
[root@LinuxEA-3 /data/mysql]# 

部分配置文件:

[galera]
##https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=&quot;gcomm://10.0.1.49,10.10.240.113,10.0.1.49&quot;
#wsrep_cluster_address=&quot;gcomm://&quot;
wsrep_cluster_name='cluster'
wsrep_node_address='10.10.240.113'
wsrep_node_name='node3'
#wsrep_replicate_myisam=1
####wsrep_slave_threads = 8
#wsrep_slave_threads = 16
##wsrep_provider_options=&quot;gcs.fc_limit=512&quot;
#wsrep_provider_options=&quot;gcache.page_size=128M;gcache.size=2G;gcs.fc_limit=512;gcs.fc_factor=0.9;evs.send_window=256;evs.user_send_window=128;cert.log_conflicts=yes&quot;
##wsrep_notify_cmd='/data/galeranotify/galeranotify.py'
#wsrep_log_conflicts=1
#wsrep_forced_binlog_format=ROW
#wsrep_drupal_282555_workaround=1
#wsrep_max_ws_size=2147483647
##wsrep_dirty_reads=0 #当值是1的时候这个节点是只读节点
##wsrep_sst_method=[mysqldump|xtrabackup|xtrabackup-v2|rsync|rsync_wan]
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync
wsrep_sst_auth=tb:password

三台启动完成状态:

[root@LinuxEA /data/mysql]# mysql -e &quot;SHOW STATUS LIKE 'wsrep_cluster_size';&quot;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+