配置mysql8.0使用PXC实现高可用。

准备好下面三台服务器

cat >> /etc/hosts << EOF
192.168.1.11 pxc1
192.168.1.12 pxc2
192.168.1.13 pxc3
EOF

三台服务器同时进行，下载安装包

[root@localhost ~]#yum module disable mysql                            
[root@localhost ~]#yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@localhost ~]#percona-release setup pxc-80 
[root@localhost ~]#yum install percona-xtradb-cluster

初始化数据库

[root@pxc1 ~]# vim /etc/my.cnf
...
[mysqld]
server-id=11 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800 

 启动 mysql，修改密码

# systemctl start mysqld
# tmp_pass=$(awk '/temporary password/ {print $NF}' /var/log/mysqld.log)
# mysql -uroot -p${tmp_pass}
mysql> alter user root@localhost identified by '123456';
Query OK, 0 rows affected (0.01 sec)mysql> CREATE USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'yh123456';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT all privileges ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)

创建集群

在192.68.1.11上建立集群

# 停止所有节点的mysqld服务
# systemctl stop mysqld[root@pxc1 ~]# vim /etc/my.cnf
...
######## wsrep ###############
# Path to Galera library
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://192.168.1.11,192.168.1.12,192.168.1.13 # PXC集群的所有ip# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW# Slave thread to use
wsrep_slave_threads=8wsrep_log_conflicts# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2 # 主键自增长不锁表# Node IP address
wsrep_node_address=192.168.1.11 # 当前节点的IP
# Cluster name
wsrep_cluster_name=pxc-cluster  # PXC集群的名称#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc1 # 当前节点的名称#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING# SST method
wsrep_sst_method=xtrabackup-v2 # 同步方法

其他服务器同理，只不过要改变当前的节点名称和IP wsrep_node_name，wsrep_node_address

将mysql目录下的*.pem拷贝给其他服务器 

[root@pxc1 ~]# scp /var/lib/mysql/*.pem pxc2:/var/lib/mysql/
[root@pxc1 ~]# scp /var/lib/mysql/*.pem pxc3:/var/lib/mysql/

pxc1第一个节点需要以引导模式启动

[root@pxc1 ~]# systemctl start [e-mail]

pxc2与pxc3正常启动数据库服务

[root@pxc2 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@pxc2 ~]# systemctl start mysqld
[root@pxc3 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@pxc3 ~]# systemctl start mysqld

 

.查询集群信息

mysql -uroot -pElysia123.
#查看状态表
mysql>  show status like 'wsrep%';

 节点下线

以引导模式来进行关闭：

# systemctl stop [e-mail]

其他节点则可以按照正常方式关闭：

# systemctl stop mysqld