### 一、环境准备
系统: `CentOS7`
数据库: `Percona-XtraDB-Cluster-57`
服务器:
`master1`: `172.16.0.100/db1`
`master2`: `172.16.0.101/db2`
`master2`: `172.16.0.102/db3`
 
 
### 二、准备工作
`a.删除mysql-community`
```bash
yum remove mysql-community-client mysql-community-server -y #仅安装了mysql-community需要执行
```
`b.关闭防火墙`
`c.关闭Selinux`
 
 
### 三、安装PXC集群
`所有节点`
安装Percona库
```bash
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
```
安装Percona-XtraDB-Cluster-57
```bash
yum install Percona-XtraDB-Cluster-57 -y
```
启动MySQL
```bash
systemctl start mysql.service
```
获取初始密码
```bash
grep 'temporary password' /var/log/mysqld.log
```
```
2019-08-09T03:22:26.358453Z 1 [Note] A temporary password is generated for root@localhost: so*WrNqjm3(e
```
修改root密码
```sql
mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass';
mysql> exit
```
停止MySQL
```bash
systemctl stop mysql.service
```
 
 
### 四、引导第一个节点
修改配置
`Master1`
```bash
vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
```
```ini
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm:// #配置wsrep_cluster_address=gcomm:// 为了初始化集群，添加其他节点之后再修改回正常的配置，再重启节点即可
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc-cluster-test #集群名字
wsrep_node_name=db1 #节点名
wsrep_node_address=172.16.0.100 #当前节点IP
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2 #SST传输方法
wsrep_sst_auth=sstuser:passw0rd #SST账号
```
bootstrap启动节点
```bash
systemctl start mysql@bootstrap.service
```
查看集群信息
```sql
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------+
| wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 |
| ... | ... |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| ... | ... |
| wsrep_incoming_addresses | 172.16.0.100:3306 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| ... | ... |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
```
`上面信息显示集群几点为1，节点状态为Synced`
创建SST用户
```sql
mysql CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
```
 
 
### 五、添加节点到集群
#### 添加Master2到集群
修改配置
`Master2`
```bash
vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
```
```bash
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc-cluster-test
wsrep_node_name=db2
wsrep_node_address=172.16.0.101
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
```
启动节点
```bash
systemctl start mysql
```
查看集群状态
```sql
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------+
| wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 |
| ... | ... |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| ... | ... |
| wsrep_incoming_addresses | 172.16.0.101:3306,172.16.0.100:3306 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| ... | ... |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
```
`上面信息显示集群现在为2个节点,172.16.0.101:3306,172.16.0.100:3306`
#### 添加Master3到集群
修改配置
`Master3`
```bash
vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
```
```bash
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc-cluster-test
wsrep_node_name=db3
wsrep_node_address=172.16.0.102
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
```
启动节点
```bash
systemctl start mysql
```
查看集群状态
```sql
mysql> show status like 'wsrep%';
+----------------------------+-------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 |
| ... | ... |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| ... | ... |
| wsrep_incoming_addresses | 172.16.0.102:3306,172.16.0.101:3306,172.16.0.100:3306 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| ... | ... |
| wsrep_ready | ON |
+----------------------------+-------------------------------------------------------+
```
`上面信息显示集群现在为3个节点,172.16.0.102:3306,172.16.0.101:3306,172.16.0.100:3306`
重新添加Master1到集群
停止MySQL
`Master1`
```bash
systemctl stop mysql@bootstrap.service
```
修改配置
`Master1`
```sql
vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
```
```ini
...
#wsrep_cluster_address=gcomm:// 修改前
wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 #修改后
...
```
启动数据库
`Master1`
```bash
systemctl start mysql
```
 
 
### 六、验证集群
在Master2节点插入测试数据
`Master2`
```sql
mysql> CREATE DATABASE percona;
mysql> USE percona;
mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
mysql> INSERT INTO example VALUES (1, 'percona1');
```
所有节点查询数据
`所有节点`
```sql
mysql> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
+---------+-----------+
```
`测试发现所有数据都已经同步，说明集群搭建成功!`
 
 
### 七、从节点在线转换成PXC节点
#### **`说明:测试使用db1做主节点,db3做从节点`**
#### 重置db3
`a.清理旧的数据并初始化数据库`
```bash
systemctl stop mysql
mv /var/lib/mysql/ /var/lib/mysql_bak/
mkdir /var/lib/mysql
mv /etc/percona-xtradb-cluster.conf.d/wsrep.cnf /tmp #移除PXC相关配置
chown -R mysql.mysql /var/lib/mysql
systemctl start mysql #启动并初始化数据库
```
`b.获取初始密码`
```bash
grep 'temporary password' /var/log/mysqld.log
```
```
2019-08-09T03:22:26.358453Z 1 [Note] A temporary password is generated for root@localhost: so*WrNqjm3(e
```
`c.修改root密码`
```sql
mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass';
mysql> exit
```
 
#### 创建复制账号
`db1`
```sql
mysql> create user 'bak'@'172.16.0.%' identified by '123456';
mysql> grant replication slave on *.* to 'bak'@'172.16.0.%';
mysql> flush privileges;
```
#### 备份数据库并发送到db3
`db1`
```bash
mysqldump -hlocalhost -uroot -pxxxxxx --single-transaction -A --master-data=2 > all.sql
scp all.sql db3:/root
```
#### 从节点执行数据库恢复
`db3`
```bash
mysql -hlocalhost -uroot -prootPass </root/all.sql
```
#### 配置从节点同步主节点
`db3`
```sql
mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.100',MASTER_USER='bak',MASTER_PASSWORD='test123',MASTER_LOG_FILE='db1-bin.000004', MASTER_LOG_POS=88754;
mysql> start slave;
```
#### 停止从节点获取同步位置
`db3`
```sql
mysql> stop slave; #停止slave
mysql> show slave status\G #查看同步位置
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.0.100
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db1-bin.000004
Read_Master_Log_Pos: 190214
Relay_Log_File: db3-relay-bin.000003
Relay_Log_Pos: 101778
Relay_Master_Log_File: db1-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 190214
```
`上面的输出显示同步文件:db1-bin.000004，位置为:190214`
#### 从节点重置slave配置(`下次启动直接使用PXC去同步`)
`db3`
```sql
mysql> reset slave all;
```
#### 从节点停止MySQL
`db3`
```bash
systemctl stop mysql
```
#### 主节点查看同步的Position对应Xid
`db1`
```bash
mysqlbinlog db1-bin.000004 |grep Xid|grep 190214
```
```
#190809 8:57:53 server id 1 end_log_pos 190214 CRC32 0x4cfb3e3b Xid = 675
```
`确认Postion 190214对应的Xid为675`
#### 主节点拷贝grastate.dat文件到从节点
`db1`
```bash
scp grastate.dat db3:/var/lib/mysql
```
#### 从节点修改grastate.dat
`db3`
```bash
cat grastate.dat
```
```ini
# GALERA saved state
version: 2.1
uuid: ee7be278-ba54-11e9-9621-8ee7979a72d4
seqno: 675 #-1修改为上面的Xid
safe_to_bootstrap: 0
```
```bash
chown mysql.mysql /var/lib/mysql/grastate.dat
```
#### 加入PXC相关配置
`db3`
```bash
vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
```
```ini
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc-cluster-test
wsrep_node_name=db3
wsrep_node_address=172.16.0.102
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
```
#### 启动数据库
`db3`
```bash
systemctl start mysql
```
#### 查看集群信息
`db3`
```sql
mysql> show status like 'wsrep%';
+----------------------------------+-------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 1431 |
| wsrep_last_committed | 1431 |
| 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 | 247 |
| wsrep_received_bytes | 77425 |
| 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.004049 |
| wsrep_local_cached_downto | 1191 |
| 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_cert_deps_distance | 75.099585 |
| wsrep_apply_oooe | 0.654762 |
| wsrep_apply_oool | 0.009259 |
| wsrep_apply_window | 5.033069 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 2.513228 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 116 |
| wsrep_cert_bucket_count | 210 |
| wsrep_gcache_pool_size | 88288 |
| 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 | 172.16.0.101:3306,172.16.0.100:3306,172.16.0.102:3306 |
+----------------------------------+-------------------------------------------------------+
```
参考：https://www.percona.com/doc/percona-xtradb-cluster/5.7/index.html#introduction
参考书籍: MySQL王者晋级之路/张甦
