MariaDB GaleraCluster簡介及雙主HA實踐

http://blog.sina.com.cn/s/blog_6de3aa8a0102w00d.html

概述

      MariaDB Galera Cluster由于其優異的特性，被廣泛用于各類分布式系統中，例如OpenStack集群，freeradius集群等。本文將簡要介紹GaleraCluster，并實際設計部署雙主MariaDB GaleraCluster(基于freeradius認證的VPN集群系統)來驗證HA（HighAvailability）特性。

GaleraCluster要素

DatabaseReplication

分布式數據庫/數據庫集群系統中數據庫復制是十分重要的，常用的有以下兩種架構：Master/SlaveReplication架構（左）和Multi-masterReplication架構（右）。

What is GaleraCluster?

Galera Cluster is awrite-set replication service PRovider in the formof the dlopenable library. It provides synchronous replication andsupports multi-master replication. Galera Cluster is capable ofunconstrained parallel applying (that is,“parallel replication”),multicast replication and automatic node provisioning.

The primary focus ofGalera Cluster is data consistency. Transactions are either appliedto every node or not at all. Galera Cluster is not a clustermanager, a load balancer or a cluster monitor. What it does it keepdatabases synchronized provided that they were properly configuredand synchronized in the beginning.

而GaleraCluster提供了并行的復制機制，能實現同步的，多主的復制方式。因此具有很多實用特性：（1）可以在任意節點上進行讀寫（2）自動剔除故障節點（3）自動加入新節點（4）真正行級別的并發復制（5）客戶端連接跟操作單數據庫的體驗一致。

在數據復制方面有同步和異步兩種方式，而同步方式的優勢十分明顯：（1）高可用（當節點宕機時數據不會丟失，數據復制一致性高，實現不復雜）；（2）具有較高的性能；（3）跨集群。但是缺點是隨著節點數量的增加，其事務的響應時間，沖突的概率和死鎖率會呈指數增長。（因此像MySQL和PostgreSQL都采用異步復制方式）

      為克服同步復制的的缺點，現在主要的技術包括：GroupCommunication，Write-sets，Database StateMachine和TransactionReordering。GaleraCluster使用一種稱為Certification-basedReplication的策略實現集群同步制，Certification-basedReplication綜合使用了GroupCommunication和TransactionReordering技術。具體來說：事務可以在任意單節點上執行或者復制，但是在事務在提交時，會運行一個協調認證程序來保證事務的全局一致性，它借助廣播服務，建立一個全局的并發事務。

      Certification-based Replication的主要思想是數據庫中的任何事務在未到達提交點（commitpoint）時，會假設沒有任何沖突。在事務commit發生之前，這個事務將會引起的所有數據庫變化和所變行的主鍵都會被收集到一個write-set中。然后會把這個write-set發送到集群中的其它節點上。有了write-set，此時集群中所有的節點會使用主鍵執行一個certificationtest，如果失敗則丟棄write-set并且回滾事務，如果成功則事務會被提交并將write-set應用到集群中其余的節點。而GaleraCluster中使用Certification-basedReplication策略則依賴于一個全局的事務序列。

GaleraCluster的內部架構

?DatabaseManagement System (DBMS) The databaseserver that runs on the individual node. Galera Cluster can useMySQL, MariaDB or Percona XtraDB.

?wsrepAPI The interface andthe responsibilities for the database server and replicationprovider. It consists of:

?wsrephooks The integrationwith the database server engine for write-setreplication.

?dlopen()Thefunction that makes the wsrep provider available to the wsrephooks.

?Galera ReplicationPlugin The plugin thatenables write-set replication service functionality.

?GroupCommunication plugins(GCS plugins) The various groupcommunication systems available to Galera Cluster. Forinstance,gcommand Spread..

State SnapshotTransfer (SST)

SST是集群提供給節點用來從一個節點到其它節點對一個完整數據的拷貝傳輸功能。GaleraCluster可以使用邏輯方法（mysqldump）和物理方法（rsync，rsync_wan，xtrabackup等）。

常用的GaleraCluster方式

如下圖，對此本文設計了一個基于freeradius認證的VPN集群系統，其核心的數據庫采用MariaDB雙主GaleraCluster實現，同時借助keepalive和haproxy實現負載均衡。

基于freeradius認證的VPN集群系統

      主要在對數據庫mariadb做雙主GaleraCluster架構；使用keepalive配置虛擬ip地址；實現兩個freeradius服務器構成的集群（多機方式而非冗余方式）；使用haproxy做httpd服務（包括daloradius和其它的輔助服務）的負載均衡；vpn-l2tpserver其規模具有線性拓展特性。

設VIP地址為192.168.10.99

說明：由于是雙機配置，相同的操作或配置以#開頭，否則[root@radiusone/two~]# 開頭。

系統初始化和準備yum源

# cat/etc/hosts

127.0.0.1  localhost localhost.localdomain localhost4localhost4.localdomain4

::1        localhost localhost.localdomain localhost6localhost6.localdomain6

192.168.10.91  radiusone      radiusone.ctgu.cn

192.168.10.92  radiustwo      radiustwo.ctgu.cn

# yum install -yepel-release

為了安裝galeramariadb-galera-common mariadb-galera-server mariadb-libs mariadbmariadb-galera-common，方便起見，這里使用RDO的源。

#wgethttps://repos.fedorapeople.org/repos/openstack/openstack-kilo/rdo-release-kilo-1.noarch.rpm

# rpm -ivhrdo-release-kilo-1.noarch.rpm  

# yum update-y

# yum install -y wgetnet-tools  iptables-services

# vim/etc/sysconfig/selinux

SELINUX=disabled

# setenforce0

設置防火墻

# iptables -A INPUT-p tcp -m state --state NEW -m multiport --dports3306,3307,4567,4568,4444 -j ACCEPT

#iptables-save

# systemctl enableiptables

# cat/etc/sysconfig/iptables

# sampleconfiguration for iptables service

# you can edit thismanually or use system-config-firewall

# please do not askus to add additional ports/services to this defaultconfiguration

*filter

:INPUT ACCEPT[0:0]

:FORWARD ACCEPT[0:0]

:OUTPUT ACCEPT[0:0]

-A INPUT -m state--state RELATED,ESTABLISHED -j ACCEPT

-A INPUT -p icmp -jACCEPT

-A INPUT -i lo -jACCEPT

-A INPUT -p tcp -mstate --state NEW -m tcp --dport 22 -j ACCEPT

-A INPUT -p tcp--dport 3306 -j ACCEPT

-A INPUT -p tcp--dport 3307 -j ACCEPT

-A INPUT -p tcp--dport 4567 -j ACCEPT

-A INPUT -p tcp--dport 4568 -j ACCEPT

-A INPUT -p tcp--dport 4444 -j ACCEPT

-A INPUT -j REJECT--reject-with icmp-host-prohibited

-A FORWARD -j REJECT--reject-with icmp-host-prohibited

COMMIT

# systemctl restartiptables

# systemctl stopfirewalld

# systemctl disablefirewalld

安裝數據庫和相關軟件包

# yum install -ymariadb mariadb-libs mariadb-devel mariadb-galera-commonmariadb-galera-server  galera rsync

# systemctl enable mariadb

# systemctl enable rsyncd

單節點初始化mysql數據庫

[root@radiusone ~]#systemctl restart mariadb

[root@radiusone ~]#mysql_secure_installation

設置root用戶遠程使用密碼passWord訪問

[root@radiusone ~]#mysql -u root –p

MariaDB [(none)]>grant all privileges on *.* to 'root'@'%' identified by 'password'with grant option;

MariaDB[(none)]>  select host, user frommysql.user;

MariaDB Galera雙主集群設置

# vim/etc/my.cnf.d/galera.cnf +27

bind-address=0.0.0.0 --> bind-address=192.168.10.91/92

[root@radiusone ~]#vim /etc/my.cnf.d/server.cnf

[mysqld]

datadir=/var/lib/mysql

query_cache_size=0

binlog_format=ROW

default_storage_engine=innodb

innodb_autoinc_lock_mode=2

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

wsrep_provider_options="gcache.size=1G"

wsrep_cluster_address=gcomm://

#wsrep_cluster_address=gcomm://192.168.10.91,192.168.10.92

wsrep_cluster_name='dbcluster'

wsrep_node_address='192.168.10.91'

wsrep_node_name='radiusone'

#wsrep_sst_method=xtrabackup

wsrep_sst_method=rsync

wsrep_sst_auth=username:Password

字段說明

wsrep_provider – Ifyou are running on 32bit it will be in the /usr/lib otherwise on64bit systems it will be in /usr/lib64

wsrep_cluster_address– Include all theaddresses of the servers that will exist in the cluster. You canuse hostnames if you wish

wsrep_cluster_name –This is the name of the cluster. All servers in the cluster mustmatch names

wsrep_node_address– The IP address of thisserver

wsrep_node_name– The name of thisserver

wsrep_sst_auth – Thisis the username and password used to authenticate xtrabackup. Setthis the same on all machines

[root@radiustwo ~]#vim /etc/my.cnf.d/server.cnf

[mysqld]

datadir=/var/lib/mysql

query_cache_size=0

binlog_format=ROW

default_storage_engine=innodb

innodb_autoinc_lock_mode=2

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

wsrep_provider_options="gcache.size=1G"

wsrep_cluster_address=gcomm://192.168.10.91

#wsrep_cluster_address=gcomm://192.168.10.91,192.168.10.92

wsrep_cluster_name='dbcluster'

wsrep_node_address='192.168.10.92'

wsrep_node_name='radiustwo'

#wsrep_sst_method=xtrabackup

wsrep_sst_method=rsync

wsrep_sst_auth=username:Password

[root@radiusone ~]#systemctl restart mariadb

[root@radiustwo ~]#systemctl restart mariadb

【按照下文附錄中的Mariadb-galeraHA集群啟動順序操作】

安裝和配置keepalive

# yum install -ykeepalived

# vim/etc/sysctl.conf

net.ipv4.tcp_keepalive_intvl=1

net.ipv4.tcp_keepalive_time=5

net.ipv4.tcp_keepalive_probes=5

net.ipv4.ip_forward=1

net.ipv4.ip_nonlocal_bind=1

net.bridge.bridge-nf-call-ip6tables=1

net.bridge.bridge-nf-call-iptables=1

net.bridge.bridge-nf-call-arptables=1

# sysctl-p

配置keepalive forVIP 192.168.10.99

[root@radiusone ~]#cat /etc/keepalived/keepalived.conf

! Configuration Filefor keepalived

global_defs{

  notification_email {

    zhangguoqingas@Gmail.com

  }

  notification_email_from root@radiusone

  smtp_server 127.0.0.1

  smtp_connect_timeout 30

  router_id radiusone

}

vrrp_scriptcheck_haproxy {

   script "killall -0 haproxy"

   interval 2 # every 2 seconds

   weight 2 # add 2 points if OK

}

vrrp_instance VI_1{

   state MASTER

   interface eth0

   virtual_router_id 51

   priority 100

   advert_int 1

   authentication {

       auth_type PASS

       auth_pass password

   }

   virtual_ipaddress {

       192.168.10.99

   }

}

[root@radiustwo ~]#cat /etc/keepalived/keepalived.conf

! Configuration Filefor keepalived

global_defs{

  notification_email {

    zhangguoqingas@gmail.com

  }

  notification_email_from root@radiustwo

  smtp_server 127.0.0.1

  smtp_connect_timeout 30

  router_id radiustwo

}

vrrp_scriptcheck_haproxy {

   script "killall -0 haproxy"

   interval 2 # every 2 seconds

   weight 2 # add 2 points if OK

}

vrrp_instance VI_1{

   state BACKUP

   interface eth0

   virtual_router_id 51

   priority 80

   advert_int 1

   authentication {

       auth_type PASS

       auth_pass password

   }

   virtual_ipaddress {

       192.168.10.99

   }

}

# systemctl restartkeepalived

# systemctl enablekeepalived

可以查看當前VIP192.168.10.99在radiusone[MASTER]主機上

[root@radiusone ~]#ip a

2: eth0: mtu 1500qdisc pfifo_fast state UP qlen 1000

   link/ether 08:00:27:fb:94:ea brd ff:ff:ff:ff:ff:ff

   inet 192.168.10.91/24 brd 192.168.10.255 scope globaleth0

      valid_lft forever preferred_lft forever

   inet 192.168.10.99/32 scope globaleth0

      valid_lft forever preferred_lft forever

   inet6 fe80::a00:27ff:fefb:94ea/64 scope link

      valid_lft forever preferred_lft forever

[root@radiustwo ~]#ip a

2: eth0: mtu 1500qdisc pfifo_fast state UP qlen 1000

   link/ether 08:00:27:33:3d:f8 brd ff:ff:ff:ff:ff:ff

   inet 192.168.10.92/24 brd 192.168.10.255 scope globaleth0

      valid_lft forever preferred_lft forever

   inet6 fe80::a00:27ff:fe33:3df8/64 scope link

      valid_lft forever preferred_lft foreve

當停止radiusone上的keepalive時，VIP會自動漂移至radiustwo上

[root@radiusone ~]#systemctl stop  keepalived

[root@radiustwokeepalived]# ip a

2: eth0: mtu 1500qdisc pfifo_fast state UP qlen 1000

   link/ether 08:00:27:33:3d:f8 brd ff:ff:ff:ff:ff:ff

   inet 192.168.10.92/24 brd 192.168.10.255 scope globaleth0

      valid_lft forever preferred_lft forever

   inet 192.168.10.99/32 scope globaleth0

      valid_lft forever preferred_lft forever

   inet6 fe80::a00:27ff:fe33:3df8/64 scope link

      valid_lft forever preferred_lft forever

[root@radiusone ~]#mysql -h 192.168.10.99 -u root -p

安裝和配置haproxy

# yum install-y haproxy

# cat/etc/haproxy/haproxy.cfg

global

   log        192.168.10.92local2

   chroot     /var/lib/haproxy

   pidfile    /var/run/haproxy.pid

   maxconn    4000

   user       haproxy

   group      haproxy

   daemon

   stats socket /var/lib/haproxy/stats

defaults

   mode                   tcp

   log                    global

   option                 tcplog

   option                 dontlognull

   option                 redispatch

   retries                3

   timeouthttp-request   10s

   timeout queue          1m

   timeoutconnect        10s

   timeoutclient         1m

   timeoutserver         1m

   timeout http-keep-alive 10s

   timeoutcheck          10s

   maxconn                3000

# systemctl starthaproxy

添加數據庫服務

# vim/etc/haproxy/haproxy.cfg

listengalera_cluster

       bind 192.168.10.99:3307

       balance  source

       option tcpka

       server radiusone 192.168.10.91:3306 check port 4567 inter 2000 rise2 fall 5

       server radiustwo 192.168.10.92:3306 check port 4567 inter 2000 rise2 fall 5 backup#

# systemctl restarthaproxy

# systemctlenable haproxy

測試連接數據庫和檢查集群

重點要檢查的字段

SHOW GLOBAL STATUSLIKE’wsrep_%’;     

(1)SHOW GLOBAL STATUSLIKE ’wsrep_cluster_state_uuid’; 集群中每個節點的值必須一致。

(2)SHOW GLOBAL STATUSLIKE ’wsrep_cluster_size’; 顯示集群的規模，此例應該顯示”2”。

(3)SHOW GLOBAL STATUSLIKE ’wsrep_cluster_status’; 顯示”Primary”為正常。

(4)SHOW GLOBAL STATUSLIKE ’wsrep_ready’; 顯示”ON”為正常。

(5) SHOW GLOBALSTATUS LIKE ’wsrep_connected’; 顯示”ON”為正常。

(6) SHOW GLOBALSTATUS LIKE ’wsrep_local_state_comment’; 顯示”Joined”為正常。

至此，MariaDBGalera雙主Cluster構建完成，接下來即可參考官方文檔完成freeradius和daloradius以及vpn-l2tp的部署，實現基于freeradius認證的VPN集群系統。

附錄

Mariadb-galeraHA集群啟動順序

https://mariadb.com/kb/en/mariadb/starting-and-stopping-mariadb/

（1）先啟動radiusone節點mariadb，并且vim/etc/my.cnf.d/server.cnf配置文件中

wsrep_cluster_address=gcomm://

#wsrep_cluster_address=gcomm://192.168.10.91,192.168.10.92

（2）再啟動radiustwo節點mariadb，并且vim/etc/my.cnf.d/server.cnf配置文件中

#wsrep_cluster_address=gcomm://

wsrep_cluster_address=gcomm://192.168.10.91,192.168.10.92

（3）重啟radiusone節點mariadb，并且vim/etc/my.cnf.d/server.cnf配置文件中

#wsrep_cluster_address=gcomm://

wsrep_cluster_address=gcomm://192.168.10.91,192.168.10.92

（4）查看端口（VIP在radiusone上面）

[root@radiusone ~]#netstat -anp | grep 3306

tcp       0     0192.168.10.91:3306     0.0.0.0:*              LISTEN     3191/mysqld        

tcp       0     0192.168.10.91:3306     192.168.10.91:34365    ESTABLISHED3191/mysqld        

tcp       0     0192.168.10.91:34365    192.168.10.91:3306     ESTABLISHED1309/haproxy       

[root@radiusone ~]#netstat -anp | grep 3307

tcp       0     0192.168.10.99:3307     0.0.0.0:*              LISTEN     1309/haproxy       

tcp       0     0192.168.10.99:3307     192.168.10.1:62490     ESTABLISHED 1309/haproxy

[root@radiustwo ~]#netstat -anp | grep 3306

tcp       0     0192.168.10.92:3306     0.0.0.0:*              LISTEN     3156/mysqld        

[root@radiustwo ~]#netstat -anp | grep 3307

tcp       0     0192.168.10.99:3307     0.0.0.0:*              LISTEN     1843/haproxy       

檢查mariadb集群的狀態

# mysql -u root–p

MariaDB [(none)]>SHOW STATUS LIKE 'wsrep%';

https://mariadb.com/kb/en/mariadb/galera-cluster-status-variables/

HA測試

（1）關閉或者重啟slave節點radiustwo，檢查能否使用VIP：3307正常訪問數據庫；【通過】

（2）關閉或者重啟master節點radiusone，檢查能否使用VIP：3307正常訪問數據庫；【通過】

（3）將master(radiusone)和slave(radiustwo)節點全部關閉，啟動后，檢查能否使用VIP：3307正常訪問數據庫。【按照上面集群啟動的順序來操作】

集群排查錯誤

（1）查看日志如果有以下類似錯誤：

# tailf/var/log/mariadb/mariadb.log

160203 18:31:37[Warning] WSREP: no nodes coming from prim view, prim notpossible

160203 18:31:37[Note] WSREP:view(view_id(NON_PRIM,4c674ade-ca61-11e5-a9f8-7e298e07ba

33,1) memb{

       4c674ade-ca61-11e5-a9f8-7e298e07ba33,0

} joined {

} left {

} partitioned{

})

160203 18:31:38[Warning] WSREP: last inactive check more than PT1.5S ago(PT3.50687S)

, skippingcheck

160203 18:32:07[Note] WSREP: view((empty))

160203 18:32:07[ERROR] WSREP: failed to open gcomm backend connection: 110: failedto

 reach primaryview: 110 (Connection timed out)

        at gcomm/src/pc.cpp:connect():141

160203 18:32:07[ERROR] WSREP: gcs/src/gcs_core.c:gcs_core_open():202: Failed toopen

backend connection:-110 (Connection timed out)

160203 18:32:07[ERROR] WSREP: gcs/src/gcs.c:gcs_open():1291: Failed to openchannel '

dbcluster' at'gcomm://192.168.10.91': -110 (Connection timed out)

160203 18:32:07[ERROR] WSREP: gcs connect failed: Connection timed out

160203 18:32:07[ERROR] WSREP: wsrep::connect() failed: 7

160203 18:32:07[ERROR] Aborting

160203 18:32:07[Note] WSREP: Service disconnected.

160203 18:32:08[Note] WSREP: Some threads may fail to exit.

160203 18:32:08[Note] /usr/libexec/mysqld: Shutdown complete

多半是因為防火墻的問題，使用iptables-F關閉防火墻，再次重啟集群，檢查HA是否正常。

# sudo iptables-F

（2）Mariadb無法重啟，報如下錯誤

160203 21:03:26[Note] Plugin 'FEEDBACK' is disabled.

160203 21:03:26[Note] Server socket created on IP: '0.0.0.0'.

160203 21:03:26[ERROR] Can't start server: listen() on TCP/IP port: Addressalready in use

160203 21:03:26[ERROR] listen() on TCP/IP failed with error 98

160203 21:03:26[ERROR] Aborting

16020321:03:26  InnoDB: Starting shutdown...

16020321:03:27  InnoDB: Shutdown completed; log sequencenumber 1600051

160203 21:03:27[Note] /usr/libexec/mysqld: Shutdown complete'

原因是3306端口被占用，使用命令netstat-anp | grep 3306查看可知被haproxy監聽listengalera_cluster端口占用。

解決方法：

關閉haproxy，重啟mariadb集群成功后，再開啟haproxy。

# iptables-F

# systemctl stophaproxy

# systemctl restartmariadb

# systemctl restarthaproxy

參考：

http://serverfault.com/questions/623054/centos-7-freeradius-fails-to-start-on-boot-due-to-priority

或者

將haproxy監聽端口改為其它端口，比如3307。但是其它應用（比如freeradius和daloradius）在配置數據庫是也要將端口設為3307。再或者是將各節點的mariadb配置為其它端口，修改

listengalera_cluster

       bind 192.168.10.99:3307

       balance  source

       option tcpka

       server radiusone 192.168.10.91:3306 check port 4567 inter 2000 rise2 fall 5

       server radiustwo 192.168.10.92:3306 check port 4567 inter 2000 rise2 fall 5 backup

# vim/etc/my.cnf.d/galera.cnf +27

bind-address=0.0.0.0 --> bind-address=192.168.10.91/92

主要參考：http://galeracluster.com/documentation-webpages/galera-documentation.pdf

cp /usr/share/zoneinfo/Asia/Shanghai/etc/localtime