====== 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}}