Centos 7 搭建 PXC 8.0.18 mysql 集群

Centos 7 搭建 PXC 8.0.18 mysql 集群

下载

https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/#

axel -n 32 https://downloads.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-8.0.18-9.3/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-8.0.18-r167-el7-x86_64-bundle.tar
curl -O https://downloads.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-8.0.18-9.3/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-8.0.18-r167-el7-x86_64-bundle.tar
wget https://downloads.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-8.0.18-9.3/binary/redhat/7/x86_64/Percona-XtraDB-Cluster-8.0.18-r167-el7-x86_64-bundle.tar

安装

# 解压
tar -zxvf Percona-XtraDB-Cluster-8.0.18-r167-el7-x86_64-bundle.tar 

# yum 本地安装
yum localinstall *.rpm

#启动
systemctl start mysql
systemctl enable mysql

# 查看初始化⽣成的随机密码
grep 'temporary password' /var/log/mysqld.log

# 使⽤查看到的密码进⾏登录
mysql -u root -p

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

# 关闭服务
systemctl stop mysqld

# 开放 端口
firewall-cmd --permanent --add-port={3306/tcp,4567/tcp,4444/tcp,4568/tcp}
firewall-cmd --reload
Pecona XtraDB Cluster 使用 4 个 TCP 端口:
常规的MySQL端口(默认3306)
(Galera)(默认4567)
状态传输端口（默认4444）
增量状态传输端口(默认是:组通信端口(4567)+ 1 = 4568)

集群配置

• 编辑配置文件 vim /etc/my.cnf

# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 解决mysql 5.7以上，groub by 分组语法错误问题
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800

######## 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://

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Slave thread to use
wsrep_slave_threads=8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address 需要修改的节点IP地址
wsrep_node_address=192.168.10.3
# Cluster name
wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-21

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
#pxc_strict_mode=ENFORCING
pxc_strict_mode=PERMISSIVE

# SST method
wsrep_sst_method=xtrabackup-v2

• 初始化集群
  systemctl restart mysql@bootstrap

• 查看状态

# 登录,输入密码
mysql -u root -p  
# 查看状态
show status like 'wsrep%'; 
可以看到集群数量时 1，本节点为 Synced 状态，表示连接成功

• 其他节点的配置与启动
  同样的下载安装 PXC，不启动，修改配置文件vim /etc/my.cnf，将 wsrep_cluster_address=gcomm:// 写初始化节点的IP，wsrep_node_address= 写本机IP地址。
  每个节点安装好pxc后将修改my.cnf并启动数据库即可
  systemctl start mysqld

集群挂掉重启

看 cat /var/lib/mysql/grastate.dat 文件
seqno 非正常关闭与正在运行为 -1 ，正常关闭有数据，是最后提交的事务编号
safe_to_bootstrap 为 1 代表最后一个挂的节点，也就是主节点，其他节点为 0
修改my.cnf文件：
主节点上清空 wsrep_cluster_address=gcomm://
其他的节点wsrep_cluster_address=gcomm://主节点IP

主节点 systemctl start mysql@bootstrap 启动
其他的节点 systemctl start mysqld 启动