內容目錄
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
下載設定檔蓋掉原本的config.inc.php
登入phpmyadmin http://192.168.1.12/phpmyadmin/
隨機關閉後端的mysql,測試資料庫新增刪除查詢是否正常
如果集群中的機器全部重啟,如機房斷電,第一台啟動的伺服器必須以空地址啟動:
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/