centos mysql使用pxc高可用

centos mysql使用pxc高可用

1.    配置镜像源为国内的
[root@localhost percona-xtradb-cluster.conf.d]#cat /etc/yum.repos.d/pxc.repo 
[percona]
name=percona_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled=1
gpgcheck=0
[root@localhost percona-xtradb-cluster.conf.d]#
2.    安装集群
yum install Percona-XtraDB-Cluster-57 -y
3.    修改配置文件
[root@localhost percona-xtradb-cluster.conf.d]#ll
total 12
-rw-r--r--. 1 root root  381 Dec  3 11:58 mysqld.cnf
-rw-r--r--. 1 root root  440 Sep 16 14:40 mysqld_safe.cnf
-rw-r--r--. 1 root root 1139 Dec  3 14:42 wsrep.cnf
[root@localhost percona-xtradb-cluster.conf.d]#pwd
/etc/percona-xtradb-cluster.conf.d
[root@localhost percona-xtradb-cluster.conf.d]#
主要修改wsrep.cnf
[root@localhost percona-xtradb-cluster.conf.d]#cat wsrep.cnf | grep -v "^$\|^#"
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.19.10,192.168.19.11,192.168.19.12
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.19.10
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"
[root@localhost percona-xtradb-cluster.conf.d]#
4.    启动第一个节点
systemctl restart mysql@bootstrap.service
5.    修改root密码，创建复制用户
#查看root密码 
[root@pxc1 ~]#grep "temporary password" /var/log/mysqld.log 
2019-11-30T02:53:54.292659Z 1 [Note] A temporary password is generated for root@localhost: =tWFP0oRJl8t 
[root@pxc1 ~]#mysql -uroot -p'=tWFP0oRJl8t' 
#修改root密码 
mysql> alter user 'root'@'localhost' identified by 'xiapi';
 Query OK, 0 rows affected (0.01 sec) 
# 创建用户
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 
-> 'sstuser'@'localhost'; 
Query OK, 0 rows affected (0.01 sec) 
6.    配置其他节点
[root@localhost percona-xtradb-cluster.conf.d]#cat wsrep.cnf | grep -v "^$\|^#"
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.19.10,192.168.19.11,192.168.19.12
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.19.11
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-2
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"
[root@localhost percona-xtradb-cluster.conf.d]#
7.    启动其他节点
systemctl start mysql
8.    查看状态
# 查看节点名
mysql>  SHOW VARIABLES LIKE 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name   | Value              |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-1 |
+-----------------+--------------------+
1 row in set (0.00 sec)
# 查看节点地址
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+---------------+
| Variable_name      | Value         |
+--------------------+---------------+
| wsrep_node_address | 192.168.19.10 |
+--------------------+---------------+
1 row in set (0.00 sec)
# 查看是否开启
mysql> SHOW VARIABLES LIKE 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on      | ON    |
+---------------+-------+
1 row in set (0.00 sec)
# 查看集群成员数量
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)
mysql> 
# 查看数据状态
wsrep_local_state_comment 状态为Synced(4)，表示数据已同步完成(因为是第一个引导节点，无数 据需要同步)。 如果状态是Joiner, 意味着 SST 没有完成. 只有所有节点状态是Synced，才可以加新节点
mysql> SHOW STATUS LIKE 'wsrep_local_state_comment';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+
1 row in set (0.00 sec)
# wsrep_cluster_status为Primary，且已经完全连接并准备好
mysql> SHOW STATUS LIKE 'wsrep_cluster_status';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_status | Primary |
+----------------------+---------+
1 row in set (0.00 sec)
9.    新加节点
新加入节点，直接配置完配置文件，启动即可。
最后记得在其它节点的配置文件添加进入这个ip就行
10.    修复故障节点
故障节点，启动就会同步数据。可以考虑使用rsync 协议