====== CentOS 7 安裝 Percona XtraDB Cluster ======
* 預計安裝在 CentOS Linux release 7.7.1908 (Core) (PVE6 Template upgrade) 環境下
* 配置 node 如下:
* ct-percona1 192.168.11.252 (Master)
* ct-percona2 192.168.11.202
===== 安裝程序 =====
yum install epel-release
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum install percona-xtrabackup Percona-XtraDB-Cluster-server-56
* 要開啟 firewalld 允許 3306, 4444, 4567, 4568
* 要關閉 selinux
* 第一台 mysql 要建立 sstuser 讓 my.cnf 內的 wsrep_sst_auth 可以正確設定
* 調整 firewalld
firewall-cmd --add-service=mysql
firewall-cmd --add-port=4444/tcp
firewall-cmd --add-port=4567/tcp
firewall-cmd --add-port=4568/tcp
firewall-cmd --permanent --add-service=mysql
firewall-cmd --permanent --add-port=4444/tcp
firewall-cmd --permanent --add-port=4567/tcp
firewall-cmd --permanent --add-port=4568/tcp
* 關閉 SELinux
vi /etc/selinux/config
:
SELINUX=disabled
:
更改後要重新開機才會生效
==== 設定程序 =====
* /etc/my.cnf@ct-percona1
[mysqld]
server_id=1
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=sstuser:**password**
wsrep_provider_options="gcache.size=4G"
wsrep_cluster_name=Percona
wsrep_sst_method=xtrabackup-v2
wsrep_node_name=db_01
wsrep_slave_threads=4
log_slave_updates
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
expire-logs-days = 30
#max_binlog_files = 20
:
:
* /etc/my.cnf@ct-percona2
[mysqld]
server_id=2
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.11.252"
wsrep_sst_auth=sstuser:**password**
wsrep_provider_options="gcache.size=4G"
wsrep_cluster_name=Percona
wsrep_sst_method=xtrabackup-v2
wsrep_node_name=db_02
wsrep_slave_threads=4
log_slave_updates
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
expire-logs-days = 30
#max_binlog_files = 20
:
:
* 更改 mysql 內 root 密碼與建立 sstuser @ct-percona1
/usr/bin/mysqladmin -u root password 'newpassword'
mysql -u root -p
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY '**password**';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;
===== 啟動 =====
* 第一次啟動重新開機會比較快
service mysql restart
* 再來就可以查看 /var/log/mysqld.log
* 如果正常可以連入 mysql 命令列看到以下的狀況
show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
1 row in set (0.00 sec)
===== 增加第三台的方式 =====
* 依照前面步驟安裝與設定
* /etc/my.cnf 更改, 可參考 /etc/my.cnf@ct-percona2, 主要修改是
* server_id=3
* wsrep_node_name=db_03
* /etc/my.cnf@ct-percona3
[mysqld]
server_id=3
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.11.252"
wsrep_sst_auth=sstuser:**password**
wsrep_provider_options="gcache.size=4G"
wsrep_cluster_name=Percona
wsrep_sst_method=xtrabackup-v2
wsrep_node_name=db_03
wsrep_slave_threads=4
log_slave_updates
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
expire-logs-days = 30
#max_binlog_files = 20
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
* 重新開機後, 觀察 ct-percona1, ct-percona2 的 mysqld.log 可看到 ct-percona3 加入的紀錄
* 連入 mysql 命令列看到以下的狀況
show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)
===== 效能測試 =====
* 使用 sysbench 來進行效能測試
yum install sysbench
* 連入 mysql 建立 sbtest 測試資料庫
create database sbtest;
* 建立測試資料
sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**username** --mysql-password=**password** --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 prepare
* 開始進行測試
sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**username** --mysql-password=**password** --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=100000 --num-threads=100 run
* 跑出來的結果
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 100
Random number generator seed is 0 and will be ignored
Threads started!
OLTP test statistics:
queries performed:
read: 140000
write: 40000
other: 20000
total: 200000
transactions: 10000 (50.21 per sec.)
read/write requests: 180000 (903.82 per sec.)
other operations: 20000 (100.42 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 199.1551s
total number of events: 10000
total time taken by event execution: 19819.8799s
response time:
min: 117.05ms
avg: 1981.99ms
max: 10106.61ms
approx. 95 percentile: 4014.08ms
Threads fairness:
events (avg/stddev): 100.0000/5.25
execution time (avg/stddev): 198.1988/0.57
* 清除測試資料
sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**username** --mysql-password=**password** --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 cleanup
===== 加上 HAProxy 來進行負載平衡 =====
* 要開啟 iptables 允許 8080(haproxy stats), 9200(mysqlchk), 13306(haproxy-mysql)
* 調整 iptables
vi /etc/sysconfig/iptables
:
:
-A INPUT -m state --state NEW -m tcp -p tcp --dport 4568 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 8080 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9200 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 13306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
:
service iptables reload
* 安裝 haproxy
yum -y install haproxy xinetd
* 設定 mysqlchk Liston 9200
vi /etc/services
:
sun-as-jpda 9191/udp # Sun AppSvr JPDA
mysqlchk 9200/tcp # mysqlchk
wap-wsp 9200/tcp # WAP connectionless session service
:
* 啟動 xinetd
service xinetd restart
chkconfig xinetd on
* 建立檢核帳號 clustercheckuser 與設定 mysqlchk
mysql -u root -p
**只需要一台執行即可**
GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY '**password**';
FLUSH PRIVILEGES;
vi /etc/xinetd.d/mysqlchk
:
server = /usr/bin/clustercheck
server_args = clustercheckuser **password**
log_on_failure += USERID
:
service xinetd reload
* 設定 haproxy log
vi /etc/rsyslog.conf
:
# Save boot messages also to boot.log
local7.* /var/log/boot.log
# HAProxy log
local2.* /var/log/haproxy.log
# ### begin forwarding rule ###
:
service rsyslog restart
* 設定 haproxy Liston 13306 指向三台 Percona XC
vi /etc/haproxy/haproxy.cfg
:
defaults
mode http
log global
#option httplog
option tcplog
option dontlognull
#option http-server-close
#option forwardfor except 127.0.0.0/8
#option redispatch
retries 3
#timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
#timeout http-keep-alive 10s
timeout check 10s
maxconn 3000
frontend stats-front
bind *:8080
mode http
default_backend stats-back
frontend pxc-front
bind *:13306
mode tcp
default_backend pxc-back
frontend pxc-onenode-front
bind *:3308
mode tcp
default_backend pxc-onenode-back
backend stats-back
mode http
balance roundrobin
stats enable
stats hide-version
stats realm Haproxy\ Statistics
stats uri /haproxy/stats
stats auth admin:**password**
backend pxc-back
mode tcp
balance leastconn
option httpchk
server db_01 140.92.143.159:3306 check port 9200 inter 12000 rise 3 fall 3
server db_02 140.92.143.76:3306 check port 9200 inter 12000 rise 3 fall 3
server db_03 140.92.143.145:3306 check port 9200 inter 12000 rise 3 fall 3
backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server db_01 140.92.143.159:3306 check port 9200 inter 12000 rise 3 fall 3
#server db_02 140.92.143.76:3306 check port 9200 inter 12000 rise 3 fall 3
#server db_03 140.92.143.145:3306 check port 9200 inter 12000 rise 3 fall 3
* 啟動 HAProxy
service haproxy start
chkconfig haproxy on
* 查看 HAProxy 統計頁面 - http://140.92.143.159:8080/haproxy/stats
* {{:tech:haproxy_stats.png?700|}}
* 驗證 HAProxy + Percona XtraDB Cluster
mysql -u root -p**password** -h 127.0.0.1 -P 13306 -e "show variables like 'wsrep_node_name';"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| wsrep_node_name | db_02 |
+-----------------+-------+
這樣表示這次連線被 HAProxy 分配到 db_02
* 撰寫簡單驗證程式
vi t.pl
#!/usr/bin/perl
$n=10;
$user='root';
$pass='**password**';
$host='127.0.0.1';
$port=13306;
$sql_cmd="show variables like 'wsrep_node_name';";
for ($i=0;$i<$n;$i++) {
$str=`mysql -u $user -p$pass -h $host -P $port -e "$sql_cmd"`;
print("$str\n");
}
perl t.pl
[root@kvm-pxc2 ~]# perl t.pl
Warning: Using a password on the command line interface can be insecure.
Variable_name Value
wsrep_node_name db_02
Warning: Using a password on the command line interface can be insecure.
Variable_name Value
wsrep_node_name db_03
Warning: Using a password on the command line interface can be insecure.
Variable_name Value
wsrep_node_name db_01
:
Warning: Using a password on the command line interface can be insecure.
Variable_name Value
wsrep_node_name db_03
Warning: Using a password on the command line interface can be insecure.
Variable_name Value
wsrep_node_name db_01
Warning: Using a password on the command line interface can be insecure.
Variable_name Value
wsrep_node_name db_02
這樣表示 HAProxy 有動態分配流量連上 db_01 db_02 db_03
===== FAQ =====
* 當發現 ct-percona1 關閉重起後, 其他 node 重起就連不上 Cluster 時, 會出現以下錯誤訊息
....gcs/src/gcs_group.cpp:void group_post_state_exchange(gcs_group_t*)():319: Reversing history:....
可以將 /var/lib/mysql 內的 grastate.dat 與 ib_log* 刪除掉, 就可以啟動成功
rm grastate.dat ib_log*
* 加入一台全新的 node 時, 發現啟動後失敗無法同步原有的資料狀況時, 可以檢查 /etc/my.cnf 內
:
wsrep_sst_method=xtrabackup-v2
:
將 wsrep_sst_method 改成 xtrabackup-v2 同步速度變快, 且能成功啟動.
* 當發現 /var/lib/mysql 內出現大量的 mysql-bin.xxxxxx 佔據磁碟空間的處理方式
* 參考 - https://www.percona.com/blog/2018/03/28/safely-purging-binary-logs-from-master/
* 這是重要的系統資料紀錄, 用於資料還原, 不過可以規劃成只有多少個數量, 或是保留可還原的天數, 在 /etc/my.cnf 內加上這樣的設定參數, 重新啟動 mysql 即可依照設定刪除
* Exp. 保留可還原 20 天內的資料
[mysqld]
:
expire-logs-days = 20
:
* Exp. 保留 10 個資料紀錄檔
[mysqld]
:
max_binlog_files = 10
:
===== 參考網址 =====
* http://www.percona.com/downloads/
* http://www.percona.com/doc/percona-xtradb-cluster/5.6/installation/yum_repo.html
* http://blog.wu-boy.com/2014/01/percona-xtradb-cluster-reference-architecture-with-haproxy/
* http://blog.wu-boy.com/2014/01/sysbench-test-percona-xtradb-server-performance/
* https://github.com/codership/mysql-wsrep/issues/32
* http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/haproxy.html
{{tag>install dbcluster}}