MariaDB Galera Cluster部署實(shí)踐

官方文檔：

http://galeracluster.com/documentation-webpages/index.html

一、 Galera Cluster的工作原理

主要關(guān)注點(diǎn)是數(shù)據(jù)一致性。 事務(wù)既可以應(yīng)用于每個(gè)節(jié)點(diǎn)，也可以不全部應(yīng)用。 所以，只要它們配置正確，數(shù)據(jù)庫(kù)保持同步。

Galera復(fù)制插件不同于傳統(tǒng)的MySQL復(fù)制，可以解決多個(gè)問(wèn)題，包括多主寫入沖突，復(fù)制滯后和主從不同步。

在典型的Galera集群實(shí)例中，應(yīng)用程序可以寫入集群中的任何節(jié)點(diǎn)，然后通過(guò)基于認(rèn)證的復(fù)制將事務(wù)提交（RBR事件）應(yīng)用于所有服務(wù)器。

使用組通信和事務(wù)排序技術(shù)，基于認(rèn)證的復(fù)制是同步數(shù)據(jù)庫(kù)復(fù)制的另一種方法

二、Galera集群安裝

說(shuō)明：Galera集群至少需要三個(gè)節(jié)點(diǎn)的服務(wù)器硬件。

node-12:10.71.11.12
node-13:10.71.11.13
node-14:10.71.11.14

操作系統(tǒng):

centos7

內(nèi)核版本:

 3.10.0-693.21.1.el7.x86_64

開(kāi)始安裝

說(shuō)明：為集群中的每個(gè)節(jié)點(diǎn)執(zhí)行以下步驟。文檔以node-12配置為例
1.編輯/etc/hosts文件，配置節(jié)點(diǎn)互相解析

[root@node-12 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.71.11.12  node-12
10.71.11.13  node-13
10.71.11.14  node-14

2.關(guān)閉節(jié)點(diǎn)防火墻

[root@node-12 ~]# cat /etc/sysconfig/selinux |grep di
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
#     minimum - Modification of targeted policy. Only selected processes are protected.

3.配置Galera集群yum源

[root@node-12 ~]# cat /etc/yum.repos.d/galera.repo 
[galera]
name = Galera
baseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1
[mysql-wsrep]
name = MySQL-wsrep
baseurl =  http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64
gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com
gpgcheck = 1

4.更新yum源緩存

yum makecache

5.安裝服務(wù)

yum install mysql-wsrep-shared-5.6

說(shuō)明：如果節(jié)點(diǎn)上之前安裝過(guò)mysql，執(zhí)行上面命令可能會(huì)安裝包沖突報(bào)錯(cuò)
6.以node-12為例，編輯/etc/my.cnf,添加如下配置

[root@node-12 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=row
bind-address=10.71.11.12
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_sst_method=rsync
wsrep_cluster_name=MyCluster
wsrep_cluster_address="gcomm://10.71.11.12,10.71.11.13,10.71.11.14"
wsrep_node_name=node-12
wsrep_node_address="10.71.11.12"
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

說(shuō)明：其他另外node-13和node-14依據(jù)1-6步配置，需要修改/etc/my.cnf與每個(gè)節(jié)點(diǎn)對(duì)應(yīng)

系統(tǒng)內(nèi)存配置說(shuō)明

Galera Cluster的內(nèi)存要求很難準(zhǔn)確預(yù)測(cè)。 它使用的特定內(nèi)存量可能會(huì)有很大差異，具體取決于給定節(jié)點(diǎn)接收的負(fù)載。 如果Galera Cluster嘗試使用比節(jié)點(diǎn)更多的內(nèi)存，則mysqld實(shí)例會(huì)崩潰。
保護(hù)節(jié)點(diǎn)系統(tǒng)崩潰的方法是確保服務(wù)有足夠的swap 空間分區(qū)或者交換文件

檢查系統(tǒng)可用交換空間

如果系統(tǒng)swap分區(qū)不滿足環(huán)境需求，可以進(jìn)行如下操作配置swap分區(qū)
說(shuō)明：集群的中每個(gè)節(jié)點(diǎn)都要進(jìn)行相關(guān)swap分區(qū)設(shè)置

1.根據(jù)需求在根目錄下創(chuàng)建一個(gè)2G大小swap空文件

[root@node-11 ~]#fallocate -l 2048M /swapfile

或者執(zhí)行

[root@node-11 ~]#dd if=/dev/zero of=/swapfile bs=1M count=2048

設(shè)置swap交換分區(qū)大小
2.設(shè)置swap文件權(quán)限

[root@node-11 ~]#chown 600 /swapfile
ll /swapfile 
-rw-r--r-- 1 600 root 2147483648 Apr 17 09:13 /swapfile

3.格式化swapfile

[root@node-11 ~]# mkswap /swapfile
Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=6341a320-1804-4fe1-8c96-7c22fe270eeb

4 .激活swapfile

[root@node-11 ~]# swapon /swapfile
swapon: /swapfile: insecure permissions 0644, 0600 suggested.
swapon: /swapfile: insecure file owner 600, 0 (root) suggested.

5. 編輯/etc/fstab,在文件底部加入如下配置

   /swapfile none swap defaults 0 0

   
   6.驗(yàn)證swap交換分區(qū)配置文件是否成功

   [root@node-11 ~]# swapon --summary
   Filename                Type        Size    Used    Priority
   /swapfile                               file    2097148 0   -1

   說(shuō)明：由于現(xiàn)在部署Galera集群的節(jié)點(diǎn)環(huán)境部署過(guò)其他環(huán)境，這里說(shuō)下刪除swap分區(qū)的步驟

[root@node-11 ~]# swapon --summary
Filename                Type        Size    Used    Priority
/openstack/swap.img                     file    4194300 0   0
/swapfile                               file    2097148 0   -1
[root@node-11 ~]# swapoff /openstack/swap.img   ##首先停止swap分區(qū)
[root@node-11 ~]# rm -rf /openstack/swap.img  ##刪除swap分區(qū)文件

[root@node-11 ~]# swapon --summary
Filename                Type        Size    Used    Priority
/swapfile                               file    2097148 0   -1

三、啟動(dòng)Galera集群

說(shuō)明：完成安裝和配置Galera Cluster后，需要使用--wsrep-new-cluster選擇一個(gè)節(jié)點(diǎn)上啟動(dòng)mysqld。 這將會(huì)初始化集群的核心組件。 在此之后啟動(dòng)的每個(gè)節(jié)點(diǎn)都將連接到該組件并開(kāi)始復(fù)制
1.在node-12上使用參數(shù)--wsrep-new-cluster啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)

[root@node-12 mysql]# service mysql start --wsrep-new-cluster

數(shù)據(jù)庫(kù)初始化報(bào)錯(cuò)解決辦法，刪除/var/lib/mysq/目錄下的logfile

注意警告：只有在初始化主要組件時(shí)才使用--wsrep-new-cluster參數(shù)。 如果希望節(jié)點(diǎn)連接到現(xiàn)有群集時(shí)，不要執(zhí)行這條命令。
2.在node-12節(jié)點(diǎn)啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)后，登錄mysql數(shù)據(jù)庫(kù)庫(kù)，通過(guò)wsrep_cluster_size參數(shù)來(lái)判斷啟動(dòng)是否成功。

SHOW STATUS LIKE 'wsrep_cluster_size';

說(shuō)明：參數(shù)wsrep_cluster_size數(shù)值代表目前galera集群當(dāng)前節(jié)點(diǎn)在線數(shù)

3.向galera集群添加節(jié)點(diǎn)，分別在node-12和node-13上執(zhí)行下面命令啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)

[root@node-13 mysql]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/node-13.err'.
....... SUCCESS!
[root@node-14 yum.repos.d]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/node-14.err'.
...... SUCCESS!

然后node-13或者node-14上登錄mysql數(shù)據(jù)庫(kù)，查看wsrep_cluster_size的數(shù)值

[root@node-14 yum.repos.d]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22

Copyright (c) 2000, 2018, 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> SHOW STATUS LIKE 'wsrep_cluster_size';

到此,galera集群搭建成功

四、Galera集群復(fù)制測(cè)試

說(shuō)明：要測(cè)試Galera群集是否正常工作
1.在數(shù)據(jù)庫(kù)客戶端上，驗(yàn)證所有節(jié)點(diǎn)是否已相互連接

[root@node-12 ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22

Copyright (c) 2000, 2018, 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> show status like 'wsrep_%';

參數(shù)說(shuō)明：
wsrep_local_state_comment參數(shù)中Synced表示該節(jié)點(diǎn)已經(jīng)連接到集群并能正常運(yùn)行
wsrep_cluster_size的數(shù)值3表示galera集群中有3個(gè)節(jié)點(diǎn)都在線
wsrep_ready中on表示此節(jié)點(diǎn)連接到集群并能夠處理事務(wù)。

2.在node-12上創(chuàng)建一個(gè)表并插入數(shù)據(jù)

[root@node-12 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22

Copyright (c) 2000, 2018, 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 galeratest;
Query OK, 1 row affected (0.01 sec)

mysql> USE galeratest;
Database changed
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT,msg TEXT ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test_table (msg) VALUES ("Hello my dear cluster.");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (msg) VALUES ("Hello, again, cluster dear."); 
Query OK, 1 row affected (0.00 sec)

3. 在node-13或者ndoe-14查看三個(gè)節(jié)點(diǎn)能夠同步node-12上的數(shù)據(jù)操作

   
   [root@node-13 mysql]# mysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 4
   Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| galeratest |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use galeratest;
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> SELECT * FROM test_table;
+----+-----------------------------+
| id | msg |
+----+-----------------------------+
| 1 | Hello my dear cluster. |
| 4 | Hello, again, cluster dear. |
+----+-----------------------------+
2 rows in set (0.00 sec)