MySQL5.7之PXC集群搭建

新建pxc001服務(wù)器：
系統(tǒng)：centos7
內(nèi)存：2G
磁盤：128G
IP地址：192.168.0.201（第一節(jié)點(diǎn)）
192.168.0.202
192.168.0.203

#vi /etc/selinux/config
SELINUX=disabled #修改該項(xiàng)為disabled
#執(zhí)行命令 setenforce 0
#systemctl restart firewalld
開放防火墻這4個(gè)端口
firewall-cmd --add-port=4444/tcp --permanent
firewall-cmd --add-port=4567/tcp --permanent
firewall-cmd --add-port=4568/tcp --permanent
firewall-cmd --add-port=3306/tcp --permanent

安裝集群
#yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
#yum -y update percona-release
#yum -y install Percona-XtraDB-Cluster-57

啟動(dòng)mysql，并修改密碼

service mysql start

grep 'temporary password' /var/log/mysqld.log

[Note] A temporary password is generated for root@localhost: u8E;M:,p_T&H
#mysql -uroot -pu8E;M:,p_T&H
mysql> update mysql.user set authentication_string=password('BingoData@1024.com') where user='root' and Host = 'localhost';

使用下面方式啟動(dòng)第一臺(tái)機(jī)器（201服務(wù)器）,這臺(tái)叫引導(dǎo)節(jié)點(diǎn)。
只有這一臺(tái)是這樣啟動(dòng)。別的服務(wù)器用service mysql start啟動(dòng)
#ssh 192.168.0.201
#systemctl start mysql@bootstrap.service
進(jìn)入mysql
#mysql -uroot -pBingoData@1024.com
#set wsrep_causal_reads =1; 避免各個(gè)節(jié)點(diǎn)的數(shù)據(jù)不一致，這種情況需要等待全同步復(fù)制 將此文件復(fù)制到master1、master2，注意要把 wsrep_node_name 和 wsrep_node_address 改成相應(yīng)節(jié)點(diǎn)的 hostname 和 ip。

創(chuàng)建配置文件中對(duì)應(yīng)的用戶 所有節(jié)點(diǎn)的IP都要?jiǎng)?chuàng)建
#mysql -uroot -pBingoData@1024.com
mysql> grant all privileges on . to 'pxcuser'@'%' identified by 'BingoData@1024.com';
報(bào)錯(cuò)如下：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
解決辦法：
mysql> set password = password('BingoData@1024.com');
mysql> grant all privileges on . to 'pxcuser'@'%' identified by 'BingoData@1024.com';
mysql> grant all privileges on . to 'pxcuser'@'localhost' identified by 'BingoData@1024.com';
mysql> flush privileges;

進(jìn)入202和203服務(wù)器配置
#ssh 192.168.0.203
注意203和202的啟動(dòng)方式
#service mysql start
創(chuàng)建配置文件中對(duì)應(yīng)的用戶 所有節(jié)點(diǎn)的IP都要?jiǎng)?chuàng)建
mysql> grant all privileges on . to 'pxcuser'@'%' identified by 'BingoData@1024.com';
mysql> grant all privileges on . to 'pxcuser'@'localhost' identified by 'BingoData@1024.com';
mysql> flush privileges;

注意：服務(wù)的啟動(dòng)和停止要對(duì)應(yīng)
service mysql stop ------> 啟動(dòng)時(shí)用service mysql start
systemctl stop mysql@bootstrap.service -----> 啟用是用 systemctl start mysql@bootstrap.service

注意避坑：/var/lib/mysql/grastate.dat文件中safe_to_bootstrap為1的服務(wù)器是主庫(kù),用主庫(kù)的方式啟動(dòng)??！我們想讓誰做主庫(kù)也可以手動(dòng)設(shè)置該服務(wù)器,而且三臺(tái)集群的uuid也相同才可以啟動(dòng)。

seqno為-1表示已加入到集群，數(shù)據(jù)庫(kù)關(guān)閉后seqno會(huì)變。
下面這臺(tái)服務(wù)器201做主庫(kù)啟動(dòng)后，seqno從4變成了-1，表示已經(jīng)加入集群。

203服務(wù)器做從庫(kù)一直無法啟動(dòng)。
啟動(dòng)方式是：service mysql start
報(bào)錯(cuò)信息如下#tail -fn 100 /var/log/mysqld.log
2020-01-10T09:25:56.982598Z 0 [Note] WSREP: (32d9017e, 'tcp://0.0.0.0:4567') turning message relay requesting off
2020-01-10T09:26:23.489499Z 0 [Note] WSREP: (32d9017e, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.0.202:4567
2020-01-10T09:26:24.489792Z 0 [Note] WSREP: (32d9017e, 'tcp://0.0.0.0:4567') reconnecting to 30d8eb06 (tcp://192.168.0.202:4567), attempt 0
2020-01-10T09:26:24.494967Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out)
at gcomm/src/pc.cpp:connect():159
2020-01-10T09:26:24.495007Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -110 (Connection timed out)
2020-01-10T09:26:24.495376Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1514: Failed to open channel 'pxc-cluster-test' at 'gcomm://192.168.0.202,192.168.0.203,192.168.0.201': -110 (Connection timed out)
2020-01-10T09:26:24.495402Z 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2020-01-10T09:26:24.495415Z 0 [ERROR] WSREP: Provider/Node (gcomm://192.168.0.202,192.168.0.203,192.168.0.201) failed to establish connection with cluster (reason: 7)
2020-01-10T09:26:24.495423Z 0 [ERROR] Aborting

解決辦法：把203服務(wù)器的grastate.dat文件的uuid修改的和202，201一模一樣就可以啟動(dòng)了。
safe_to_bootstrap: 設(shè)置為0
那么UUID是什么呢？
為什么同一個(gè)pxc集群的UUID是相同的呢？

驗(yàn)證集群：show status like 'wsrep%';

| wsrep_incoming_addresses | 192.168.0.202:3306,192.168.0.203:3306,192.168.0.201:3306 |
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | e9e76881-338f-11ea-90d6-3b07eeeaf3a4 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 9d5bc8a5-3374-11ea-9aac-b3599f4e37e4 |
| 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 <info@codership.com> |
| wsrep_provider_version | 3.41(rb3295e6) |
| wsrep_ready | ON