PXC集群搭建
实验环境:
主机名 IP 系统版本 mysql01 192.168.119.191 Centos 7.7 mysql02 192.168.119.192 Centos 7.7 mysql03 192.168.119.193 Centos 7.7
实验需求:
3台服务器均安装Percona-XtraDB-Cluster 配置PXC集群 测试PXC集群成功写入数据
实验步骤:
1. 防火墙及SeLinux
setenforce 0
$ vim /etc/selinux/config
SELINUX= disabled
$ firewall-cmd --add-port= 3306/tcp --permanent
$ firewall-cmd --add-port= 4444/tcp --permanent
$ firewall-cmd --add-port= 4567/tcp --permanent
$ firewall-cmd --add-port= 4568/tcp --permanent
$ firewall-cmd --reload
2. 卸载本地MySQL
$ rpm -qa | grep mysql
$ yum remove mysql-community-client mysql-community-common mysql-community-client-plugins -y
$ rm -rf /var/lib/mysql/
3. 安装
3.1 安装官方PXC源
$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
3.2 安装 Percona-XtraDB-Cluster
$ yum install Percona-XtraDB-Cluster-57 -y
https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/
$ cd pxc/
$ yum localinstall ./* -y
3.3 启动Percona-XtraDB-Cluster server
$ systemctl start mysql
3.4 修改数据库root密码
$ grep 'temporary password' /var/log/mysqld.log
2021-01-14T00:48:09.919937Z 6 [ Note] [ MY-010454] [ Server] A temporary password is generated for root@localhost: PyLUBnyM& 9K)
2021-03-01T12:16:11.639614Z 1 [ Note] A temporary password is generated for root@localhost: FcxE:t1hiwv8
$ mysql -uroot -pFcxE:t1hiwv8
$ alter user 'root' @'localhost' identified by 'Com.123456' ;
4. 创建SST用户
$ mysql -uroot -pCom.123456
mysql> create user 'sstuser' @'localhost' identified by 's3cretPass' ;
Query OK, 0 rows affected ( 0.03 sec)
mysql> grant reload,lock tables, process, replication client on *.* to'sstuser' @'localhost' ;
Query OK, 0 rows affected ( 0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected ( 0.00 sec)
$ systemctl stop mysql
5. 修改主配置文件
$ vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address= gcomm://192.168.119.191,192.168.119.192,192.168.119.193
wsrep_node_address= 192.168.119.191
wsrep_node_name= pxc-cluster-node-1
wsrep_sst_auth= "sstuser:s3cretPass"
$ vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server-id= 2
$ vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address= gcomm://192.168.119.191,192.168.119.192,192.168.119.193
wsrep_node_address= 192.168.119.192
wsrep_node_name= pxc-cluster-node-2
wsrep_sst_auth= "sstuser:s3cretPass"
$ vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
server-id= 3
$ vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address= gcomm://192.168.119.191,192.168.119.192,192.168.119.193
wsrep_node_address= 192.168.119.193
wsrep_node_name= pxc-cluster-node-3
wsrep_sst_auth= "sstuser:s3cretPass"
6. mysql01自举启动
$ systemctl start mysql@bootstrap.service
mysql> show status like 'wsrep%' ;
+----------------------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------------------+---------------------------------------------+
| wsrep_local_state_uuid | ee8052ba-7a87-11eb-a8ac-4369173e4150 |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 4 |
| wsrep_last_committed | 4 |
| 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 | 2 |
| wsrep_received_bytes | 157 |
| 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 | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.500000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 100, 100 ] |
| wsrep_flow_control_interval_low | 100 |
| wsrep_flow_control_interval_high | 100 |
| wsrep_flow_control_status | OFF |
| wsrep_flow_control_active | false |
| wsrep_flow_control_requested | false |
| 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_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 1320 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.119.191:3306 |
| wsrep_cluster_weight | 1 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 7.14e-07/2.2958e-06/3.872e-06/1.28959e-06/5 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 3cde3649-7a8b-11eb-a7d8-67a5e218ba26 |
| wsrep_gmcast_segment | 0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | ee8052ba-7a87-11eb-a8ac-4369173e4150 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy < info@codership.com> |
| wsrep_provider_version | 3.47( raf7cd63) |
| wsrep_ready | ON |
+----------------------------------+---------------------------------------------+
74 rows in set ( 0.00 sec)
7. 启动其他节点及同步
$ systemctl start mysql
8. 测试PXC集群成功写入数据
# mysql01
mysql> create database student;
Query OK, 1 row affected (0.00 sec)
# mysql02
mysql> use student;
Database changed
mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.03 sec)
# mysql03
mysql> use student;
Database changed
mysql> INSERT INTO student.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.00 sec)
实验需求:
将mysql01的数据库服务停止 然后再启动mysql01数据库服务,让其回归集群 将3台服务器全部关机,然后再开机; 让集群恢复正常
实验步骤:
1. 停止mysql01数据库服务
$ systemctl stop mysql
2. 启动服务,重新回归集群
$ vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address= gcomm://192.168.119.193,192.168.119.192,192.168.119.191
$ systemctl start mysql
$ mysql> show status like 'wsrep%' ;
| wsrep_cluster_size | 3
3. 关闭三台服务器
$ systemctl enable mysql
$ init 0
4. 恢复集群
safe_to_bootstrap: 1
$ systemctl start mysql@bootstrap.service
$ systemctl start mysql
$ mysql> show status like 'wsrep%' ;
+----------------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid | ee8052ba-7a87-11eb-a8ac-4369173e4150 |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 8 |
| wsrep_last_committed | 8 |
| 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 | 710 |
| 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 | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.250000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 173, 173 ] |
| wsrep_flow_control_interval_low | 173 |
| wsrep_flow_control_interval_high | 173 |
| wsrep_flow_control_status | OFF |
| wsrep_flow_control_active | false |
| wsrep_flow_control_requested | false |
| 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_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 1320 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.119.193:3306,192.168.119.191:3306,192.168.119.192: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 | 91161edc-7a90-11eb-af0b-b6b485b52beb |
| wsrep_gmcast_segment | 0 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | ee8052ba-7a87-11eb-a8ac-4369173e4150 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 1 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy < info@codership.com> |
| wsrep_provider_version | 3.47( raf7cd63) |
| wsrep_ready | ON |
+----------------------------------+----------------------------------------------------------------+
74 rows in set ( 0.00 sec)