Galera Cluster 介紹

  • 同步複製資料
  • 可讀取和寫入叢集系統內任一節點
  • 自動偵測節點錯誤,如果有節點當機,則叢集系統自動移除該節點
  • 可任意擴充節點
  • 採用 row level 方式來平行複製資料

安裝 Galera Cluster Server

本文教學安裝版本為MariaDB,原始的MySQL安裝CentOS 和 Ubuntu 安裝方式雷同,前者是用 yum 後者則是 aptitude,在安裝前請先下載對應的repository 設定檔

CentOS  依照需求5.5或10.1之一安裝即可

MariaDB galera cluster 5.5版(如果是5.5或10.0則需要安裝galera cluster版)

yum -y update && yum -y upgrade
yum -y install MariaDB-Galera-server MariaDB-client galera

MariaDB Galera Cluster 10.1版 是有包含galera cluster

vi /etc/yum.repos.d/MariaDB.repo

# MariaDB 10.1 CentOS repository list - created 2016-02-28 13:16 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum install MariaDB-server MairaDB-client MariaDB-devel*

Ubuntu

aptitude -y update
aptitude -y install mariadb-galera-server-5.5 galera

如要安裝MySQL的版本請到這邊下載MySQL servers 5.5.49 and 5.6.30

下載最新版的xtrbackup版本

https://www.percona.com/downloads/XtraBackup/LATEST/

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm
yum install ppercona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm

 

設定 Galera Cluster Server

這邊會用到三台的Galera Server,IP為

mysql-cluster-node1 192.168.1.170

mysql-cluster-node2 192.168.1.171

mysql-cluster-node3 192.168.1.172

node1第一台節點起動

mysqld_safe --wsrep-new-cluster &
或是
service mysql start --wsrep-new-cluster

先建立MySQL User,用來認證使用 先入MySQL Console.

[root@cluster-node1 my.cnf.d]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.49-MariaDB-wsrep MariaDB Server, wsrep_25.14.r9949137

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

建立 cluster 使用者,密碼為 1234,針對 % 跟 localhost 同時建立。

MariaDB [(none)]> GRANT USAGE ON *.* to cluster@'%' IDENTIFIED BY '1234';
MariaDB [(none)]> GRANT ALL PRIVILEGES on *.* to cluster@'%';
MariaDB [(none)]> GRANT USAGE ON *.* to cluster@'localhost' IDENTIFIED BY '1234';
MariaDB [(none)]> GRANT ALL PRIVILEGES on *.* to cluster@'localhost';

在 192.168.1.170 設定 Galera 設定檔 /etc/my.cnf.d/server.cnf

[galera]
# Mandatory settings
wsrep_on=ON                ### 這個參數一定要加入 
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
binlog_format=row
wsrep_sst_auth=cluster:1234		## sst備份設定的帳號密碼
wsrep_sst_method=xtrabackup-v2   ##rsync會鎖表改用xtrabackup
wsrep_sst_method            = xtrabackup-v2
#default_storage_engine=InnoDB #innodb_autoinc_lock_mode=2 bind-address=0.0.0.0

在 192.168.1.171 設定 Galera 設定檔 /etc/my.cnf.d/server.cnf

[galera]
# Mandatory settings
wsrep_on=ON		    ## 這個參數一定要加入 
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.170:4567"
binlog_format=row
wsrep_sst_auth=cluster:1234   ## sst備份設定的帳號密碼 
wsrep_sst_method=xtrabackup-v2 ##rsync會鎖表改用xtrabackup
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

在 192.168.1.172 設定 Galera 設定檔 /etc/my.cnf.d/server.cnf

[galera]
# Mandatory settings
wsrep_on=ON			## 這個參數一定要加入 
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.171:4567"
binlog_format=row
wsrep_sst_auth=cluster:1234	## sst備份設定的帳號密碼 
wsrep_sst_method=xtrabackup-v2 ##rsync會鎖表改用xtrabackup
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

#### 每台資料庫重起服務 #### (Cluster 預設port 是4567)

[root@cluster-node1 my.cnf.d]# /etc/init.d/mysql restart
Shutting down MySQL....                                    [  OK  ]
Starting MySQL.....                                        [  OK  ]
[root@cluster-node1 my.cnf.d]# netstat -tlnup | grep mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      76821/mysqld        
tcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LISTEN      76821/mysqld

如何檢查是否有設定成功,可以進入MySQL Console來看

#### 檢查Cluster服務狀態 ####

mysql -e "SHOW STATUS LIKE 'wsrep%';"
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep%';
+------------------------------+----------------------------------------------------------+
| Variable_name                | Value                                                    |
+------------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid       | 31b2fc48-268a-11e6-846e-6ffc78ff3253                     |
| wsrep_protocol_version       | 7                                                        |
| wsrep_last_committed         | 52                                                       |
| wsrep_replicated             | 0                                                        |
| wsrep_replicated_bytes       | 0                                                        |
| wsrep_repl_keys              | 0                                                        |
| wsrep_repl_keys_bytes        | 0                                                        |
| wsrep_repl_data_bytes        | 0                                                        |
| wsrep_repl_other_bytes       | 0                                                        |
| wsrep_received               | 4                                                        |
| wsrep_received_bytes         | 668                                                      |
| wsrep_local_commits          | 0                                                        |
| wsrep_local_cert_failures    | 0                                                        |
| wsrep_local_replays          | 0                                                        |
| wsrep_local_send_queue       | 0                                                        |
| wsrep_local_send_queue_max   | 1                                                        |
| wsrep_local_send_queue_min   | 0                                                        |
| wsrep_local_send_queue_avg   | 0.000000                                                 |
| wsrep_local_recv_queue       | 0                                                        |
| wsrep_local_recv_queue_max   | 1                                                        |
| wsrep_local_recv_queue_min   | 0                                                        |
| wsrep_local_recv_queue_avg   | 0.000000                                                 |
| wsrep_local_cached_downto    | 18446744073709551615                                     |
| wsrep_flow_control_paused_ns | 0                                                        |
| wsrep_flow_control_paused    | 0.000000                                                 |
| wsrep_flow_control_sent      | 0                                                        |
| wsrep_flow_control_recv      | 0                                                        |
| wsrep_cert_deps_distance     | 0.000000                                                 |
| wsrep_apply_oooe             | 0.000000                                                 |
| wsrep_apply_oool             | 0.000000                                                 |
| wsrep_apply_window           | 0.000000                                                 |
| wsrep_commit_oooe            | 0.000000                                                 |
| wsrep_commit_oool            | 0.000000                                                 |
| wsrep_commit_window          | 0.000000                                                 |
| wsrep_local_state            | 4                                                        |
| wsrep_local_state_comment    | Synced                                                   |
| wsrep_cert_index_size        | 0                                                        |
| wsrep_causal_reads           | 0                                                        |
| wsrep_cert_interval          | 0.000000                                                 |
| wsrep_incoming_addresses     | 192.168.1.171:3306,192.168.1.172:3306,192.168.1.170:3306 |
| wsrep_evs_delayed            |                                                          |
| wsrep_evs_evict_list         |                                                          |
| wsrep_evs_repl_latency       | 0/0/0/0/0                                                |
| wsrep_evs_state              | OPERATIONAL                                              |
| wsrep_gcomm_uuid             | ff428578-299b-11e6-abc0-0b6aa2df1578                     |
| wsrep_cluster_conf_id        | 3                                                        |
| wsrep_cluster_size           | 3                                                        |
| wsrep_cluster_state_uuid     | 31b2fc48-268a-11e6-846e-6ffc78ff3253                     |
| wsrep_cluster_status         | Primary                                                  |
| wsrep_connected              | ON                                                       |
| wsrep_local_bf_aborts        | 0                                                        |
| wsrep_local_index            | 2                                                        |
| wsrep_provider_name          | Galera                                                   |
| wsrep_provider_vendor        | Codership Oy <[email protected]>                        |
| wsrep_provider_version       | 25.3.15(r3578)                                           |
| wsrep_ready                  | ON                                                       |
| wsrep_thread_count           | 2                                                        |
+------------------------------+----------------------------------------------------------+
57 rows in set (0.00 sec)

wsrep_ready,正確值是 ON,另外看看 wsrep_cluster_size 是否跟您設置 Node 的數量相同,這兩個如果都正確,那就表示設定成功了

動態設定 gcomm://

[root@cluster-node3 my.cnf.d]# mysql -u root -p -e "SHOW VARIABLES LIKE 'wsrep_cluster_address';"
Enter password: 
+-----------------------+----------------------------+
| Variable_name         | Value                      |
+-----------------------+----------------------------+
| wsrep_cluster_address | gcomm://192.168.1.171:4567 |
+-----------------------+----------------------------+

進入MySQL console 請設定要同步的server 設定完畢後不用重啟也可以跟上其它台的node資料

MariaDB [(none)]> SET GLOBAL wsrep_cluster_address='gcomm://192.168.1.171:4567';
Query OK, 0 rows affected (4.21 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_cluster_address';
+-----------------------+----------------------------+
| Variable_name         | Value                      |
+-----------------------+----------------------------+
| wsrep_cluster_address | gcomm://192.168.1.171:4567 |
+-----------------------+----------------------------+
1 row in set (0.00 sec)

設定MySQL clustercheck-iptables 三台Node節點都要配置

安裝iptables

yum install -y iptables-services
#CentOS6
chkconfig --level 234 iptables on
service iptables start
#CentOS7
systemctl enable iptables
systemctl start iptables

新增iptables規則

iptables -t nat -A PREROUTING -s $0.0.0.0/0 -p tcp --dport 3308 -j REDIRECT --to-ports 3306
iptables -I INPUT -m tcp -p tcp --dport 3306 -j ACCEPT
iptables -I INPUT -m tcp -p tcp --dport 3308 -j ACCEPT
iptables -I INPUT -m tcp -p tcp --dport 4444 -j ACCEPT
iptables -I INPUT -m tcp -p tcp --dport 4567:4568 -j ACCEPT
service iptables save
service iptables restart
iptables -L -n  #檢查iptables設定值
iptables -L -n -t nat #檢查nat設定

進入MySQL console設定mysqlchk_user帳號及密碼

MariaDB [mysql]> GRANT PROCESS ON *.* TO 'mysqlchk_user'@'localhost' IDENTIFIED BY 'mysqlchk_password';
Query OK, 0 rows affected (0.05 sec)

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

下載clustercheck-iptables

git clone https://github.com/ashraf-s9s/clustercheck-iptables
cp clustercheck-iptables/mysqlchk_iptables /usr/local/sbin

測試mysqlchk_iptables

[root@cluster-node3 my.cnf.d]# cd
[root@cluster-node3 ~]# mysqlchk_iptables -t
Detected variables/status:
wsrep_local_state: 4
wsrep_sst_method: rsync
read_only: OFF

[03-06-16 23:22:03.774669828] [INFO] Galera Cluster Node is synced.

檢查mysqlchk_iptables程序pid是否在背景正常執行

[root@cluster-node1 my.cnf.d]# mysqlchk_iptables -d
/usr/local/sbin/mysqlchk_iptables started with PID 76920.

加入開機啟動

echo '/usr/local/sbin/mysqlchk_iptables -d' >> /etc/rc.local
chmod +x /etc/rc.local

設定nginx as MySQL load Balancer

Nginx機器的ip為192.168.1.169

安裝必需套件

yum -y install gcc gcc-c++ pcre-devel zlib-devel libxml2-devel curl-devel libjpeg-devel libpng-devel libXpm-devel freetype-devel openldap-devel

nginx下載及編譯

wget http://nginx.org/download/nginx-1.9.6.tar.gz
tar -xzf nginx-1.9.6.tar.gz
./configure --prefix=/usr/local/web/nginx --with-http_stub_status_module --with-http_ssl_module --with-stream
make && make install

設定nginx

vi /usr/local/web/nginx/conf/nginx.conf    #最下方加入此段  後端ip請設置mariadb-galera-cluster

stream {
      upstream stream_backend {
        zone tcp_servers 64k;
        server 192.168.1.170:3308;
        server 192.168.1.171:3308;
        server 192.168.1.172:3308;
    }
 
    server {
        listen 3307;
        proxy_pass stream_backend;
        proxy_connect_timeout 1s;
    }
}

重啟nginx

/usr/local/web/nginx/sbin/nginx -s stop
/usr/local/web/nginx/sbin/nginx

檢查是否有Listen 3307 port

netstat -tulpn | grep 3307

測試本機登入3307是否可以登入後端mysql

[root@loadbalancer conf]# mysql -u root -P 3307 -h192.168.1.169 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4801
Server version: 5.5.49-MariaDB-wsrep MariaDB Server, wsrep_25.14.r9949137

Copyright (c) 2000, 2013, 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測試套件phpmyadmin

測試web ip 192.168.1.12

前面已經建立好環境後 測試的web請先安裝好LAMP 然後下載phpmyadmin

wget https://files.phpmyadmin.net/phpMyAdmin/4.4.15.6/phpMyAdmin-4.4.15.6-all-languages.tar.gz
tar zxvf phpMyAdmin-4.4.15.6-all-languages.tar.gz
mv phpMyAdmin-4.4.15.6-all-languages phpmyadmin

開啟phpmyadmin設定頁 概要→建立伺服器  http://192.168.1.12/phpmyadmin/setup

phpmyadmin1

phpmyadmin2

下載設定檔蓋掉原本的config.inc.php

phpmyadmin3

登入phpmyadmin      http://192.168.1.12/phpmyadmin/

隨機關閉後端的mysql,測試資料庫新增刪除查詢是否正常

phpmyadmin4

如果集群中的機器全部重啟,如機房斷電,第一台啟動的伺服器必須以空地址啟動:

mysqld_safe --wsrep_cluster_address=gcomm:// >/dev/null &

加入仲裁節點

garbd -a gcomm://172.16.5.156:4567 -g my_wsrep_cluster -d

參考資料:

http://it2record.blogspot.tw/2014/07/centos65-mariadb-55-galera-server.html

https://blog.wu-boy.com/2013/03/galera-cluster-for-mysql-multi-master-replication/

http://severalnines.com/blog/nginx-database-load-balancer-mysql-or-mariadb-galera-cluster/

 

By tony

自由軟體愛好者~喜歡不斷的思考各種問題,有新的事物都會想去學習嘗試 做實驗並熱衷研究 沒有所謂頂天的技術 只有謙虛及不斷的學習 精進專業,本站主要以分享系統及網路相關知識、資源而建立。 Github http://stnet253.github.io

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料