Why PXC?
Percona XtraDB Cluster is a database clustering solution for MySQL. It ensures high availability, prevents downtime and data loss, and provides linear scalability for a growing environment.
Mysql 好用歸好用，但如果加入第三方的調校可以更方便管理的話，何樂而不為?
任何 RDBMS 的起手式主要都是 Backend 的架構與設定
這個主題主要說明大家常見的如何使用三個 Mysql 節點建立 PXCluster
OS Type
root@work:~# lsb_release -a No LSB modules are available. Distributor ID: Debian Description: Debian GNU/Linux 8.10 (jessie) Release: 8.10 Codename: jessie
1st node hostname: pxc-1 (192.168.50.1)
2nd node hostname: pxc-2 (192.168.50.2)
3rd node hostname: pxc-3 (192.168.50.3)
1st node: pxc-1 上要做的事情
確認沒有要更新的套件
root@pxc-1:~# aptitude upgrade No packages will be installed, upgraded, or removed. 0 packages upgraded, 0 newly installed, 0 to remove and 0 not upgraded. Need to get 0 B of archives. After unpacking 0 B will be used.
安裝 Percona 套件來源
root@pxc-1:~# wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb --2017-12-10 11:12:42-- https://repo.percona.com/apt/percona-release_0.1-4.jessie_all.deb Resolving repo.percona.com (repo.percona.com)... 74.121.199.234 Connecting to repo.percona.com (repo.percona.com)|74.121.199.234|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 6586 (6.4K) [application/octet-stream] Saving to: ‘percona-release_0.1-4.jessie_all.deb’ percona-release_0.1-4.jessie_all.deb 100%[=================================================================================================================================>] 6.43K --.-KB/s in 0s 2017-12-10 11:12:43 (57.3 MB/s) - ‘percona-release_0.1-4.jessie_all.deb’ saved [6586/6586]
用 dpkg 裝起來
root@pxc-1:~# dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb Selecting previously unselected package percona-release. (Reading database ... 30086 files and directories currently installed.) Preparing to unpack percona-release_0.1-4.jessie_all.deb ... Unpacking percona-release (0.1-4.jessie) ... Setting up percona-release (0.1-4.jessie) ...
確認 Percona 相關套件可以正確被擷取
root@pxc-1:~# apt-cache search percona | tail -3 percona-xtrabackup-test - Test suite for Percona XtraBackup xtrabackup - Transitional package for percona-xtrabackup percona-release - Package to install Percona gpg key and APT repo
連結常用的 ssl 套件
# ln -s /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/x86_64-linux-gnu/libcrypto.so.6 # ln -s /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/x86_64-linux-gnu/libssl.so.6
安裝 PXC 套件
# aptitude install percona-xtradb-cluster-client-5.7 percona-xtradb-cluster-server-5.7 percona-xtrabackup-24 percona-toolkit
停止 mysql 改 PXC 設定
# /etc/init.d/mysql stop
PXC 預設設定在 /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf
要修改的地方如下，因為新 DB 只有 root
# Cluster connection URL contains IPs of nodes wsrep_cluster_address=gcomm://192.168.50.1,192.168.50.2,192.168.50.3 # Node IP address wsrep_node_address=192.168.50.1 # Cluster name wsrep_cluster_name=pxc #If wsrep_node_name is not specified, then system hostname will be used wsrep_node_name=pxc-1 #Authentication for SST method wsrep_sst_auth="root:s3cretPass"
在 1 st node 用 bootstrap-pxc 啟動，並把自己當成 PXC 的 Lead
# /etc/init.d/mysql bootstrap-pxc
接下來可以從 /var/log/mysqld.log 觀察到 bootstrap-pxc 的啟動流程
2017-12-09T13:35:39.278010Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2017-12-09T13:35:39.284906Z mysqld_safe Assigning 1fe59d70-dcca-11e7-a482-7bd39ae0774e:2 to wsrep_start_position 2017-12-09T13:35:39.452043Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.19-17-57-log) starting as process 12169 ... 2017-12-09T13:35:39.478726Z 0 [Note] WSREP: Setting initial position to 1fe59d70-dcca-11e7-a482-7bd39ae0774e:2 2017-12-09T13:35:39.483989Z 0 [Note] WSREP: gcomm: connecting to group 'pxc', peer '192.168.50.1:,192.168.50.2:,192.168.50.3:' 2017-12-09T13:35:39.991227Z 0 [Note] WSREP: Node 37e468c4 state primary 2017-12-09T13:35:39.993429Z 0 [Note] WSREP: Current view of cluster as seen by this node 2017-12-09T13:35:40.486183Z 0 [Note] WSREP: gcomm: connected 2017-12-09T13:35:40.486270Z 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0) 2017-12-09T13:35:40.486329Z 0 [Note] WSREP: Waiting for SST/IST to complete. 2017-12-09T13:35:40.490235Z 0 [Note] WSREP: Restored state OPEN -> JOINED (2) 2017-12-09T13:35:40.491971Z 0 [Note] WSREP: Member 1.0 (pxc-1) synced with group. 2017-12-09T13:35:40.492288Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 2) 2017-12-09T13:35:40.807519Z 1 [Note] WSREP: Synchronized with group, ready for connections
檢查當下 wsrep 狀態
mysql> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 1 | +--------------------+-------+ 1 row in set (0.01 sec)
mysql> SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment'; +---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | wsrep_local_state_comment | Synced | +---------------------------+--------+ 1 row in set (0.01 sec)
—-
接著 2nd node: pxc-2 要做的事情也一樣，wsrep.cnf 需要修改的部分
wsrep_cluster_address=gcomm://192.168.50.1,192.168.50.2,192.168.50.3 wsrep_node_address=192.168.50.2 wsrep_cluster_name=pxc wsrep_node_name=pxc-2 wsrep_sst_auth="root:s3cretPass"
習慣是清空 pxc-2 的 datadir，讓 PXC Sync 同步 pxc-1 的資料庫檔案
# rm -fr /var/log/mysql/*
接著啟動 pxc-2 mysql 就可以觀察: /var/log/mysqld.log
2017-12-09T13:36:35.912482Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2017-12-09T13:36:36.121223Z 0 [Note] WSREP: gcomm: connecting to group 'pxc', peer '192.168.50.1:,192.168.50.2:,192.168.50.3:' 2017-12-09T13:36:36.124797Z 0 [Note] WSREP: (fa30796d, 'tcp://0.0.0.0:4567') connection established to 1e8e95ff tcp://192.168.50.1:4567 2017-12-09T13:36:36.623863Z 0 [Note] WSREP: declaring 1e8e95ff at tcp://192.168.50.1:4567 stable 2017-12-09T13:36:36.625040Z 0 [Note] WSREP: Node 1e8e95ff state primary 2017-12-09T13:36:37.122430Z 0 [Note] WSREP: gcomm: connected 2017-12-09T13:36:37.122540Z 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0) 2017-12-09T13:36:37.122608Z 0 [Note] WSREP: Waiting for SST/IST to complete. 2017-12-09T13:36:37.125985Z 0 [Note] WSREP: Restored state OPEN -> JOINED (0) 2017-12-09T13:36:37.127464Z 0 [Note] WSREP: SST complete, seqno: 0 2017-12-09T13:36:37.127818Z 0 [Note] WSREP: Member 1.0 (pxc-2) synced with group. 2017-12-09T13:36:37.128011Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0) 2017-12-09T13:36:37.287139Z 0 [Note] /usr/sbin/mysqld: ready for connections.
—-
接著 3rd node: pxc-3 要做的事情也一樣，wsrep.cnf 需要修改的部分
wsrep_cluster_address=gcomm://192.168.50.1,192.168.50.2,192.168.50.3 wsrep_node_address=192.168.50.3 wsrep_cluster_name=pxc wsrep_node_name=pxc-3 wsrep_sst_auth="root:s3cretPass"
習慣是清空 pxc-3 的 datadir，讓 PXC Sync 同步 pxc-1 的資料庫檔案
# rm -fr /var/log/mysql/*
接著啟動 pxc-3 mysql 就可以觀察: /var/log/mysqld.log
2017-12-09T13:38:19.440709Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2017-12-09T13:38:19.640357Z 0 [Note] WSREP: (37e468c4, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567 2017-12-09T13:38:19.641502Z 0 [Note] WSREP: gcomm: connecting to group 'pxc', peer '192.168.50.1:,192.168.50.2:,192.168.50.3:' 2017-12-09T13:38:19.643592Z 0 [Note] WSREP: (37e468c4, 'tcp://0.0.0.0:4567') connection established to 37e468c4 tcp://192.168.50.3:4567 2017-12-09T13:38:19.644237Z 0 [Warning] WSREP: (37e468c4, 'tcp://0.0.0.0:4567') address 'tcp://192.168.50.3:4567' points to own listening address, blacklisting 2017-12-09T13:38:19.645545Z 0 [Note] WSREP: (37e468c4, 'tcp://0.0.0.0:4567') connection established to fa30796d tcp://192.168.50.2:4567 2017-12-09T13:38:19.646183Z 0 [Note] WSREP: (37e468c4, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 2017-12-09T13:38:19.647901Z 0 [Note] WSREP: (37e468c4, 'tcp://0.0.0.0:4567') connection established to 1e8e95ff tcp://192.168.50.1:4567 2017-12-09T13:38:20.149613Z 0 [Note] WSREP: declaring 1e8e95ff at tcp://192.168.50.1:4567 stable 2017-12-09T13:38:20.150557Z 0 [Note] WSREP: declaring fa30796d at tcp://192.168.50.2:4567 stable 2017-12-09T13:38:20.154599Z 0 [Note] WSREP: Node 1e8e95ff state primary 2017-12-09T13:38:20.644675Z 0 [Note] WSREP: gcomm: connected 2017-12-09T13:38:20.644818Z 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0) 2017-12-09T13:38:20.644919Z 0 [Note] WSREP: Waiting for SST/IST to complete. 2017-12-09T13:38:20.650647Z 0 [Note] WSREP: Restored state OPEN -> JOINED (0) 2017-12-09T13:38:20.651603Z 1 [Note] WSREP: New cluster view: global state: 1fe59d70-dcca-11e7-a482-7bd39ae0774e:0, view# 11: Primary, number of nodes: 3, my index: 1, protocol version 3 2017-12-09T13:38:20.653600Z 0 [Note] WSREP: SST complete, seqno: 0 2017-12-09T13:38:20.654144Z 0 [Note] WSREP: Member 1.0 (pxc-3) synced with group. 2017-12-09T13:38:20.654384Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0) 2017-12-09T13:38:20.858577Z 0 [Note] /usr/sbin/mysqld: ready for connections.
—-
檢查 pxc-[1,2,3] 的 wsrep 狀態
mysql> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.01 sec)
mysql> SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment'; +---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | wsrep_local_state_comment | Synced | +---------------------------+--------+ 1 row in set (0.01 sec)
開 Database 測試有沒有同步到各節點
mysql> CREATE DATABASE hello_world; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hello_world | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
—-
大概到這邊告一個段落
接下來是 PMM, ProxySQL 的測試
還有怎麼設計 2node , 3node 的 PXC Scaling and High Availability 機制