PXC集群

一、环境介绍

操作系统版本：龙蜥OS 8.9
pxc版本：8.4
节点规划

节点	主机名	CPU	内存	硬盘	IP
pxc	pxc1	4核心	8G	100G	10.99.99.21
pxc	pxc2	4核心	8G	100G	10.99.99.22
pxc	pxc3	4核心	8G	100G	10.99.99.23
proxysql核心	proxysql1	4核心	8G	100G	10.99.99.28
proxysql核心	proxysql2	4核心	8G	100G	10.99.99.29
proxysql卫星	proxysql2	4核心	8G	100G	10.99.99.27

二、PXC安装

注：在三个节点上都安装

1、关闭默认mysql模块

yum module disable mysql

2、安装yum源

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

3、准备pxc安装环境

percona-release setup pxc-84-lts

4、安装pxc

yum install percona-xtradb-cluster

5、启动mysql，并更改root密码

启动

service mysql start

获取随机密码

grep 'temporary password' /var/log/mysqld.log

登陆服务器

mysql -u root -p

修改root密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass';
mysql> exit
$ service mysql stop

三、搭建PXC集群

1、编辑/etc/my.cnf 配置文件

（1）pxc1节点配置文件

[client]
socket=/var/lib/mysql/mysql.sock[mysqld]
# PXC集群中每个MySQL实例的唯一标识，不能重复
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
# 二进制日志到期时间7天，单位秒
binlog_expire_logs_seconds=604800# 启用老版本加密方式,proxy-admin需要用
#mysql_native_password=ON######## pxc集群相关配置 ###############
# Galera library库文件位置
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
# 集群名称，同一集群需要一样
wsrep_cluster_name=pxc-cluster
# 集群成员
wsrep_cluster_address=gcomm://10.99.99.21,10.99.99.22,10.99.99.23
# 开启冲突日志记录
wsrep_log_conflicts
# 设置 InnoDB 的自增主键锁策略为“交错模式（interleaved）”
innodb_autoinc_lock_mode=2
# 集群在进行 SST（State Snapshot Transfer） 时所用的方法
wsrep_sst_method=xtrabackup-v2
# pxc_strict_mode值为: DISABLED,PERMISSIVE,ENFORCING,MASTER；
# 阻止在 Percona XtraDB Cluster 中使用技术预览功能和不支持的功能
pxc_strict_mode=ENFORCING
# 服务器端加密
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
# pxc流量加密证书
wsrep_provider_options="socket.ssl=yes;socket.ssl_ca=/etc/mysql/ssl/ca.pem;socket.ssl_cert=/etc/mysql/ssl/server-cert.pem;socket.ssl_key=/etc/mysql/ssl/server-key.pem"
# 当前节点名称
wsrep_node_name=pxc1 # 节点替换
# 当前节点地址
wsrep_node_address=10.99.99.21 # 节点替换[sst]
encrypt = 4
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem[xtrabackup]
use-memory=32M

（2）pxc2节点配置文件

[mysqld]
log-error=/var/log/mysql/error.log
# PXC集群中每个MySQL实例的唯一标识，不能重复
server-id=2 # 节点替换
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 二进制日志到期时间7天，单位秒
binlog_expire_logs_seconds=604800######## pxc集群相关配置 ###############
# Galera library库文件位置
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
# 集群名称，同一集群需要一样
wsrep_cluster_name=pxc-cluster
# 集群成员
wsrep_cluster_address=gcomm://10.99.99.21,10.99.99.22,10.99.99.23
# 开启冲突日志记录
wsrep_log_conflicts
# 设置 InnoDB 的自增主键锁策略为“交错模式（interleaved）”
innodb_autoinc_lock_mode=2
# 集群在进行 SST（State Snapshot Transfer） 时所用的方法
wsrep_sst_method=xtrabackup-v2
# pxc_strict_mode值为: DISABLED,PERMISSIVE,ENFORCING,MASTER；
# 阻止在 Percona XtraDB Cluster 中使用技术预览功能和不支持的功能
pxc_strict_mode=ENFORCING
# 服务器端加密
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
# pxc流量加密证书
wsrep_provider_options="socket.ssl=yes;socket.ssl_key=/etc/mysql/ssl/server-key.pem;socket.ssl_cert=/etc/mysql/ssl/server-cert.pem;socket.ssl_ca=/etc/mysql/ssl/ca.pem"
# 当前节点名称
wsrep_node_name=pxc2 # 节点替换
# 当前节点地址
wsrep_node_address=10.99.99.22 # 节点替换[sst]
encrypt = 4
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem[xtrabackup]
use-memory=32M

（3）pxc3节点配置文件

[client]
socket=/var/lib/mysql/mysql.sock[mysqld]
# PXC集群中每个MySQL实例的唯一标识，不能重复
server-id=3 # 节点替换
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 二进制日志到期时间7天，单位秒
binlog_expire_logs_seconds=604800######## pxc集群相关配置 ###############
# Galera library库文件位置
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
# 集群名称，同一集群需要一样
wsrep_cluster_name=pxc-cluster
# 集群成员
wsrep_cluster_address=gcomm://10.99.99.21,10.99.99.22,10.99.99.23
# 开启冲突日志记录
wsrep_log_conflicts
# 设置 InnoDB 的自增主键锁策略为“交错模式（interleaved）”
innodb_autoinc_lock_mode=2
# 集群在进行 SST（State Snapshot Transfer） 时所用的方法
wsrep_sst_method=xtrabackup-v2
# pxc_strict_mode值为: DISABLED,PERMISSIVE,ENFORCING,MASTER；
# 阻止在 Percona XtraDB Cluster 中使用技术预览功能和不支持的功能
pxc_strict_mode=ENFORCING
# 服务器端加密
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
# pxc流量加密证书
wsrep_provider_options="socket.ssl=yes;socket.ssl_key=/etc/mysql/ssl/server-key.pem;socket.ssl_cert=/etc/mysql/ssl/server-cert.pem;socket.ssl_ca=/etc/mysql/ssl/ca.pem"
# 当前节点名称
wsrep_node_name=pxc3 # 节点替换
# 当前节点地址
wsrep_node_address=10.99.99.23 # 节点替换[sst]
encrypt = 4
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem[xtrabackup]
use-memory=32M

2、引导第一个节点（pxc1）

systemctl start [e-mail]

登陆mysql 查看状态

 show status like 'wsrep%';

3、创建证书并分发

证书报错信息如下

（1）、创建证书目录

mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

（2）、生成 CA 证书（根证书）

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem -subj "/C=CN/ST=Beijing/L=Beijing/O=MyOrg/OU=MyUnit/CN=MyCA"

（3）、分发ca证书和key到所有节点

scp ca* 10.99.99.22:/etc/mysql/ssl
scp ca* 10.99.99.23:/etc/mysql/ssl

（4）、用 CA 签发服务器证书

pxc1：

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem -subj "/C=CN/ST=Beijing/L=Beijing/O=MyOrg/OU=DB/CN=pxc1"
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

pxc2：

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem -subj "/C=CN/ST=Beijing/L=Beijing/O=MyOrg/OU=DB/CN=pxc2"
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

pxc3：

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem -subj "/C=CN/ST=Beijing/L=Beijing/O=MyOrg/OU=DB/CN=pxc3"
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

（5）、验证证书

openssl verify -CAfile ca.pem server-cert.pem

（5）、设置文件权限

chown mysql.mysql /etc/mysql/ssl/*

4、启动第二节点（pxc2）

systemctl start mysql

登陆mysql 查看状态

 show status like 'wsrep%';

5、启动第三节点（pxc3）

systemctl start mysql

登陆mysql 查看状态

 show status like 'wsrep%';

6、关闭引导节点（pxc1）

关闭引导

systemctl stop [e-mail]

正常启动mysql

systemctl start mysql

登陆mysql 查看状态

show status like 'wsrep%';

四、验证PXC集群复制

1、在pxc2点上新建一个数据库

CREATE DATABASE percona;

2、在pxc3节点上创建一个表

USE percona;
CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));

3、在pxc1节点上插入记录

INSERT INTO percona.example VALUES (1, 'percona1');

4、在pxc2节点上检索记录

SELECT * FROM percona.example;

五、安装proxysql集群

注：proxysql 无中心化设计，应尽量和应用部署在一起
注：一个proxysql集群至少需要两个核心（core）节点，可以有多个卫星节点（CoreSatellite），核心节点负责纠察、同步，卫星节点则不参加，只是从core节点拉取配置

1、添加proxysql软件存储库

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key
EOF

2、安装proxysql

dnf install proxysql

3、下载pxc 客户端软件包

https://www.percona.com/downloads#percona-xtradb-cluster

4、安装pxc 客户端

dnf localinstall percona-xtradb-cluster-client-8.4.3-3.1.el8.x86_64.rpm

5、配置核心节点

10.99.99.28、29两个节点上配置

vi /etc/proxysql.cnf

添加更改如下

cluster_sync_interfaces=falseadmin_variables=
{admin_credentials="radmin:radmin;cluster_user:cluster_pass"mysql_ifaces="0.0.0.0:6032"cluster_username="cluster_user"cluster_password="cluster_pass"
}// Setup the Core nodes of our cluster
proxysql_servers =
({hostname="10.99.99.28"port=6032weight=0comment="proxysql-1"},{hostname="10.99.99.29"port=6032weight=0comment="proxysql-2"}
)

启动

systemctl start proxysql
systemctl enable proxysql

查看状态

mysql -u radmin -pradmin -h 127.0.0.1 -P 6032

SELECT * FROM runtime_checksums_values;

SELECT * FROM stats_proxysql_servers_checksums;

核心节点信息

SELECT * FROM stats_proxysql_servers_metrics;

客户端节点信息

SELECT * FROM stats_proxysql_servers_clients_status;

6、配置卫星节点

vi /etc/proxysql.cnf

添加更改如下

cluster_sync_interfaces=falseadmin_variables=
{admin_credentials="radmin:radmin;cluster_user:cluster_pass"mysql_ifaces="0.0.0.0:6032"cluster_username="cluster_user"cluster_password="cluster_pass"
}

使用动态添加就行
登陆proxysql

mysql -u radmin -pradmin -h 127.0.0.1 -P 6032

-- 添加已有集群核心节点
INSERT INTO proxysql_servers (hostname, port, weight, comment) VALUES
('10.99.99.28', 6032, 0, 'proxysql-1'),
('10.99.99.29', 6032, 0, 'proxysql-2');-- 加载并保存
LOAD PROXYSQL SERVERS TO RUNTIME;
SAVE PROXYSQL SERVERS TO DISK;

看客户端状态

SELECT * FROM stats_proxysql_servers_clients_status;

核心节点上

插入一个不存在的mysql节点

INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight) 
VALUES (2, '192.168.99.99', 3306, 'ONLINE', 100);LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

在卫星节点上看到信息

SELECT * FROM mysql_servers WHERE hostname = '192.168.99.99';

删除插入的测试mysql节点

DELETE FROM mysql_servers WHERE hostname = '192.168.99.99';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

卫星节点上看已经没了

六、proxysql添加pxc集群

注：pxc集群是所有节点都可以写入的，即多主模式，但是会存在乐观锁问题，导致多主写入可能会出现不同步问题，建议还是限制单主使用；集群最大容忍度为N/2+1 向上取整。三个节点只允许有一个节点宕机。

1、在proxysql 核心节点上添加pxc集群节点

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'10.99.99.21',3306,100);
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'10.99.99.22',3306,100);
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'10.99.99.23',3306,100);

2、设置pxc集群在proxysql中的行为

注：writer_is_also_reader = 2 主写节点是否参与读操作（0=否，1=是，2=仅在无读节点时参与）

INSERT INTO mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind
) VALUES (2, 4, 3, 1, 1, 1, 2, 100
);

3、在pxc集群中创建监控用户

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorPass';
GRANT USAGE ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

4、在 ProxySQL 中配置监控账号

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitorPass' WHERE variable_name='mysql-monitor_password';

5、创建proxysql 客户端用户

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent, active) VALUES ('root', 'rootPass', 2, 1, 1);

6、设置读写分离路由

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply,re_modifiers
) VALUES
(1, 1, NULL, '^SELECT.*FOR UPDATE', 2, 1, 'CASELESS'),
(2, 1, NULL, '^SELECT', 3, 1, 'CASELESS');

6、proxysql加载并保存配置

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

第5步执行完，会自动重新分组

select * from runtime_mysql_servers;

7、客户端登陆

mysql -uroot -prootPass -h 127.0.0.1 -P 6033

8、读写分离测试

测试脚本：

注意： 连接绑定：proxysql 一旦客户端执行了写操作（INSERT/UPDATE/DELETE/SELECT FOR
UPDATE等），ProxySQL会把这个连接绑定到写节点。 接下来该连接所有的操作（包括普通SELECT）都会走写节点。
这样设计的目的是保证事务的一致性和读-写顺序一致。 如果连接里没有写操作，普通SELECT会走读节点。 如果有写操作，整个连接都走写节点。
这也意味着，同一连接内的所有查询不再区分读写，都走写节点。
要让写入和后续的查询走同一连接，必须把它们放到一个单次mysql客户端会话里执行，或者用BEGIN; …
COMMIT;包裹，保证事务和连接复用。

#!/bin/bashUSER="root"
PASS="rootPass"
PROXYSQL_HOST="127.0.0.1"
PROXYSQL_PORT=6033# 设置环境变量来避免密码警告
export MYSQL_PWD=$PASSecho "初始化库和表:"
mysql -u$USER -h$PROXYSQL_HOST -P$PROXYSQL_PORT <<EOF
CREATE DATABASE IF NOT EXISTS test_db_for_proxy_test;
USE test_db_for_proxy_test;
CREATE TABLE IF NOT EXISTS test_table (id INT PRIMARY KEY AUTO_INCREMENT,val VARCHAR(50)
);
EOF
echo "完成!"echo "普通 SELECT 测试:"
host=$(mysql -u$USER -h$PROXYSQL_HOST -P$PROXYSQL_PORT -sN -e "SELECT @@hostname;")
echo "$host"echo "SELECT ... FOR UPDATE 测试:"
host=$(mysql -u$USER -h$PROXYSQL_HOST -P$PROXYSQL_PORT -sN -e "BEGIN; SELECT @@hostname FOR UPDATE; COMMIT;")
echo "$host"echo "INSERT 测试:"
host=$(mysql -u$USER -h$PROXYSQL_HOST -P$PROXYSQL_PORT -sN <<EOF
USE test_db_for_proxy_test;
BEGIN;
INSERT INTO test_table (val) VALUES ('test_value');
SELECT @@hostname;
COMMIT;
EOF
)
echo "$host"echo "读数据测试:"
mysql -u$USER -h$PROXYSQL_HOST -P$PROXYSQL_PORT -e "SELECT 'test1', @@hostname;"# 清除环境变量（安全）
unset MYSQL_PWD

查看节点状态：

 select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;

七、proxysql+keepalive高可用集群（可选）

注：这里使用proxysql-admin 进行配置proxysql，此工具在pxc官方的proxysql软件包里；虚拟ip使用10.99.99.25

1、下载proxysql

https://www.percona.com/download-proxysql

2、安装proxysql

dnf localinstall proxysql2-2.7.3-1.1.el8.x86_64.rpm

3、修改下proxysql管理员密码

改成radmin:radmin

vi /etc/proxysql.cnf

4、修改proxysql-admin配置文件

vi /etc/proxysql-admin.cnf 

修改如下：

export PROXYSQL_DATADIR='/var/lib/proxysql'# --------------------------------
# encrypted login credentials file options
#
#export LOGIN_FILE='/path/to/loginfile'
#export LOGIN_PASSWORD_FILE='/path/to/loginfile/password'# --------------------------------
# proxysql admin interface credentials.
#
export PROXYSQL_USERNAME='radmin'
export PROXYSQL_PASSWORD='radmin'
export PROXYSQL_HOSTNAME='localhost'
export PROXYSQL_PORT='6032'# --------------------------------
# PXC admin credentials for connecting to pxc-cluster-node.
#
export CLUSTER_USERNAME='root'
export CLUSTER_PASSWORD='rootPass'
export CLUSTER_HOSTNAME='10.99.99.21'
export CLUSTER_PORT='3306'# --------------------------------
# proxysql monitoring user. proxysql admin script will create
# this user in pxc to monitor pxc-nodes.
#
export MONITOR_USERNAME='monitor'
export MONITOR_PASSWORD='monitorPass'# --------------------------------
# Application user to connect to pxc-node through proxysql
#
export CLUSTER_APP_USERNAME='root'
export CLUSTER_APP_PASSWORD='rootPass'# --------------------------------
# ProxySQL hostgroup IDs
#
export WRITER_HOSTGROUP_ID='10'
export READER_HOSTGROUP_ID='11'
export BACKUP_WRITER_HOSTGROUP_ID='12'
export OFFLINE_HOSTGROUP_ID='13'# --------------------------------
# ProxySQL read/write configuration mode.
#
export MODE='singlewrite'# --------------------------------
# max_connections default (used only when INSERTing a new mysql_servers entry)
#
export MAX_CONNECTIONS='1000'# --------------------------------
# Determines the maximum number of writesets a node can have queued
# before the node is SHUNNED to avoid stale reads.
#
export MAX_TRANSACTIONS_BEHIND=100# --------------------------------
# Connections to the backend servers (from ProxySQL) will use SSL
#
export USE_SSL='no'# --------------------------------
# Determines if a node should be added to the reader hostgroup if it has
# been promoted to the writer hostgroup.
# If set to 'yes', then all writers (including backup-writers) are added to
# the read hostgroup.
# If set to 'no', then none of the writers (including backup-writers) are added.
# If set to 'backup', then only the backup-writers will be added to
# the read hostgroup.
#
export WRITERS_ARE_READERS='backup'

5、启动proxysql

systemctl start proxysql

6、自动添加pxc集群至proxysql

proxysql-admin --config-file=/etc/proxysql-admin.cnf  --enable

7、更改pxc 用户授权

可以看到数据库表里新添加了用户，但是通过以下配置的root有用户权限较少
或者这里不要用root用户，比如appuser

# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME='root'
export CLUSTER_APP_PASSWORD='rootPass'

手动添加授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

8、第二台proxysql初始化

proxysql-admin --config-file=/etc/proxysql-admin.cnf  --enable

注意如下，选择N

9、验证两台proxysql工作状态

登录

mysql -uradmin -pradmin -h 127.0.01 -P 6032

查看mysql_servers

select * from mysql_servers;

查看分组规则

 select * from mysql_galera_hostgroups;

查看读写分离路由

select * from mysql_query_rules\G;

查看运行状态

select * from runtime_mysql_servers;

可以看到写组10，3个节点中主动屏蔽2个，符合单主；读组11，读节点2个；备写组12，备写节点2个；

脚本测试结果

10、安装keepalived

dnf install keepalived

11、配置keepalived

路径通常是 /etc/keepalived/keepalived.conf
10.99.99.28–》master

! Configuration File for keepalivedglobal_defs {     #global全局配置段#notification_email {
#        root@localhost     #设置邮件的接受人地址
#}
#   notification_email_from  [e-mail]   #发送邮件的人，此处可以自行定义；
#   smtp_server 127.0.0.1    #配置邮件服务器地址，默认为本地即可；
#   smtp_connect_timeout 30   #连接邮件服务器的超时时间；
#   router_id LVS_DEVEL   
}vrrp_script chk_proxysql {    #检测proxysql是否在线脚本；如果proxysql服务不在线，则当前节点权重减10；script "/etc/keepalived/proxysql_check.sh"interval 1weight -10}vrrp_instance VI_1 {   #第一个虚拟路由器实例1配置state MASTER    #初始状态为主角色；interface ens192   #虚拟地址所在的网卡接口名称；virtual_router_id 240  #虚拟路由器实例的id号，同一个虚拟路由器实例两端id号必须相同；priority 100   #优先级，数字越大，优先级越高；advert_int 1  #健康状态监测时长间隔；authentication {   #认证相关配置auth_type PASS  #字符认证auth_pass 123456  #认证密码}virtual_ipaddress {  10.99.99.25 #配置虚拟ip地址；   }track_script {chk_proxysql    #此处调用上面的proxysql状态检查脚本}
}

10.99.99.29–》backup

! Configuration File for keepalivedglobal_defs {     #global全局配置段#notification_email {
#        root@localhost     #设置邮件的接受人地址
#}
#   notification_email_from  [e-mail]   #发送邮件的人，此处可以自行定义；
#   smtp_server 127.0.0.1    #配置邮件服务器地址，默认为本地即可；
#   smtp_connect_timeout 30   #连接邮件服务器的超时时间；
#   router_id LVS_DEVEL   
}vrrp_script chk_proxysql {    #检测proxysql是否在线脚本；如果proxysql服务不在线，则当前节点权重减10；script "/etc/keepalived/proxysql_check.sh"interval 1weight -10}vrrp_instance VI_1 {   #第一个虚拟路由器实例1配置state BACKUP    #初始状态为主角色；interface ens192   #虚拟地址所在的网卡接口名称；virtual_router_id 240  #虚拟路由器实例的id号，同一个虚拟路由器实例两端id号必须相同；priority 99   #优先级，数字越大，优先级越高；advert_int 1  #健康状态监测时长间隔；authentication {   #认证相关配置auth_type PASS  #字符认证auth_pass 123456  #认证密码}virtual_ipaddress {  10.99.99.25 #配置虚拟ip地址；   }track_script {chk_proxysql    #此处调用上面的proxysql状态检查脚本}
}

proxysql检查脚本

vi /etc/keepalived/proxysql_check.sh

#!/bin/bash# 检查 proxysql 进程数
counter=$(pgrep -c proxysql)if [ "$counter" -eq 0 ]; thenecho "ProxySQL 未运行，尝试启动..."systemctl start proxysqlsleep 2# 再次检查 proxysql 是否启动成功counter=$(pgrep -c proxysql)if [ "$counter" -eq 0 ]; thenecho "ProxySQL 启动失败，停止 keepalived"systemctl stop keepalivedelseecho "ProxySQL 启动成功"fi
elseecho "ProxySQL 正常运行"
fi

添加执行权限

chmod +x /etc/keepalived/proxysql_check.sh

12、启动并检查keepalived工作状态

systemctl start keepalived

八、Haproxy+keepalived 高可用集群（可选）

注意：读写分离应该在应用程序上做，使用不同的端口来实现，事务全部走到写端口上

1、安装haproxy

dnf install haproxy

2、更改haproxy配置文件

vi /etc/haproxy/haproxy.cfg

更改如下：

globallog /dev/log local0log /dev/log local1 noticechroot /var/lib/haproxypidfile /var/run/haproxy.pidmaxconn 10000user haproxygroup haproxydaemondefaultslog globalmode tcpoption tcplogoption dontlognulltimeout connect 5stimeout client 50stimeout server 50sretries 3# ------------------------
# 写请求入口（主写 + 两个备写）
# ------------------------
frontend mysql_write_frontbind *:33306default_backend mysql_write_backend# ------------------------
# 读请求入口（两只读 + 主写备读）
# ------------------------
frontend mysql_read_frontbind *:33307default_backend mysql_read_backend# ------------------------
# 写后端（主写 + 权重优先备写）
# ------------------------
backend mysql_write_backendbalance leastconnoption httpchk GET /http-check expect status 200default-server inter 2s fall 3 rise 2# 主写节点（最高优先）server pxc1 10.99.99.21:3306 check port 9200 weight 100# 第一备写节点（优先使用）server pxc2 10.99.99.22:3306 check port 9200 weight 80 backup# 第二备写节点（最后使用）server pxc3 10.99.99.23:3306 check port 9200 weight 50 backup# ------------------------
# 读后端（只读优先 + 主写作为备读）
# ------------------------
backend mysql_read_backendbalance roundrobinoption httpchk GET /http-check expect status 200default-server inter 2s fall 3 rise 2# 优先读节点（推荐）server pxc2 10.99.99.22:3306 check port 9200 weight 100server pxc3 10.99.99.23:3306 check port 9200 weight 100# 主写节点作为最后读备选server pxc1 10.99.99.21:3306 check port 9200 weight 10 backup

3、启动haproxy

systemctl start haproxy

4、pxc集群健康检查脚本

官方给了自带的一个脚本clustercheck的脚本

5、创建haproxy用户用于clustercheck

CREATE USER 'haproxy'@'localhost' IDENTIFIED BY 'haproxyPass';
GRANT USAGE ON *.* TO 'haproxy'@'localhost';
FLUSH PRIVILEGES;

6、pxc集群上创建监听服务

vi /etc/systemd/system/clustercheck.service

添加如下

[Unit]
Description=PXC ClusterCheck for HAProxy
After=network.target[Service]
ExecStart=/usr/bin/socat TCP-LISTEN:9200,fork,reuseaddr EXEC:"/usr/bin/clustercheck haproxy haproxyPass"
Restart=always[Install]
WantedBy=multi-user.target

启动并添加自启动

systemctl daemon-reexec
systemctl daemon-reload
systemctl enable --now clustercheck
systemctl start clustercheck

九、测试

1、安装sysbench

dnf install epel-release -y
dnf install sysbench -y

2、连接数据库创建测试表

mysql -uroot -prootPass -h 127.0.0.1 -P 6033

CREATE DATABASE sbtest;

3、初始化测试数据

20张表，每张表100万条数据

sysbench /usr/share/sysbench/oltp_read_write.lua \--mysql-host=127.0.0.1 \--mysql-port=6033 \--mysql-user=root \--mysql-password=rootPass \--mysql-db=sbtest \--tables=20 \--table-size=1000000 \prepare

4、读写混合测试

sysbench /usr/share/sysbench/oltp_read_write.lua \--mysql-host=127.0.0.1 \--mysql-port=6033 \--mysql-user=root \--mysql-password=rootPass \--mysql-db=sbtest \--tables=20 \--table-size=1000000 \--threads=32 \--time=300 \run

此为4核心8G 千兆网络测试结果：

此为16核心64G 千兆网络测试结果：

5、Percona Toolkit 优化工具安装与使用

https://www.percona.com/percona-toolkit

dnf localinstall percona-toolkit-3.7.0-2.el8.x86_64.rpm -y

执行查看优化参数

pt-variable-advisor --user root --password rootPass localhost

因为这个工具不会识别集群，需要自己根据实际情况加入配置，下面列列举常用的配置优化，以16核心64GB内存为例子：

[mysqld]##### 优化参数 ############
# 基础设置
max_connections = 500
skip-name-resolve
table_open_cache = 4096
thread_cache_size = 100# InnoDB 引擎优化
innodb_buffer_pool_size = 48G               # 内存 75%
innodb_buffer_pool_instances = 8
innodb_redo_log_capacity = 8G                  # 必须配套重启时删除旧日志文件
innodb_flush_log_at_trx_commit = 2          # 性能优先，这里会每一秒提交一次事务，有丢失1s的风险
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 4000# MyISAM 安全
key_buffer_size = 32M
delay_key_write = OFF
myisam_recover_options = BACKUP,FORCE# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 2# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci# PXC 集群优化
wsrep_provider_options = "gcache.size=2G;gcs.fc_limit=512"    # IST 从这个 gcache 中读取缺失事务# 避免自增主键冲突
auto_increment_increment = 3
auto_increment_offset = 1                             # 节点2为2，节点3为3

重启生效

systemctl stop mysql
rm -f /var/lib/mysql/ib_logfile*
systemctl start mysql

查询集群运行状态

SHOW STATUS LIKE 'wsrep_%';

问题处理合集

1、dbeaver 连接问题

proxysql 日志出现问题如下
1193, Unknown system variable ‘query_cache_size’
这个是驱动版本问题
解决：换mariadb 驱动 连接就没有问题了

2、navicate 连接问题

9006 - ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 11
这个是gui问题 SET PROFILING = 1；官方已经明确不会支持gui一些设定，使用路由规则过滤掉最好

解决：

INSERT INTO mysql_query_rules (active, digest, match_pattern, replace_pattern, apply) VALUES (1, '0x2FBDEB5F84F177DE', '.*', 'SELECT 0 AS QUERY_ID, 0 AS SUM_DURATION FROM DUAL WHERE 1=0', 1);
INSERT INTO mysql_query_rules (active, digest, OK_msg, apply) VALUES (1, '0x271E5C1B7D850ED0', '', 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

3、三节点宕机，无法启动集群

https://docs.percona.com/percona-xtradb-cluster/8.4/crash-recovery.html#scenario-6-all-nodes-went-down-without-a-proper-shutdown-procedure
需要手动找到引导节点

cat /var/lib/mysql/grastate.dat

seqno:   423 
这个值越大说明数据越全
-1的情况：
节点在 启动未完成时就宕机。
节点从未成功加入过集群。
mysqld crash 或 kill -9 导致未能正常写入 grastate.dat。
数据目录或状态文件丢失或损坏。

safe_to_bootstrap: 1
0标识非引导节点
1表示引导节点

找到safe_to_bootstrap: 1 且seqno 值 最大的节点；如果没有safe_to_bootstrap: 1 就手动设置seqno最大的节点为1
三节点正常关闭恢复：（seqno 都是正常数字 不是-1）

注意警告：
只允许一个节点使用 _WSREP_NEW_CLUSTER=1 启动！ 其他节点绝对不能这样启动。
最好在执行前，备份该节点的/var/lib/mysql，以防误操作。
如果你用了 ProxySQL，请等集群稳定后再加载后端

步骤1：引导主节点

systemctl start [e-mail]

查看状态：

mysql -uroot -prootPass -e "SHOW STATUS LIKE 'wsrep_cluster_status';"

步骤2：其他节点加入

systemctl start mysql

步骤3：
查看集群数量

mysql -uroot -prootPass -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

步骤4：
退出引导模式 重新启动

systemctl stop [e-mail]
systemctl start mysql

4、三节点非法关机、集群崩溃信息不全

处理思路：
a、第一先确定哪个节点数据最完整（seqno最大），把这个节点设置为引导节点（safe_to_bootstrap: 1），引导第一个节点（systemctl start [e-mail]），如果期间出现oom现象，就缩小innodb_buffer_pool_size大小和限制SST使用内存大小；
b、第一个节点引导成功后，依次加入第二、第三节点（system start mysql）；
c、全部启动成功后，重新正常启动第一个节点（systemctl stop [e-mail]）(systemctl start mysql)，
注意：期间更改的innodb_buffer_pool_size 集群正常后可以改回，另这innodb_buffer_pool_size设置75%大小，会在出现sst时候oom，推荐50%。

三个节点全部显示（甚至集群id都没了）

执行下面命令 找到seqno的大的节点

sudo -u mysql mysqld --wsrep-recover --log-error=/tmp/wsrep-recover.log

从日志中找到

找到seqno 这个最大的节点，然后编辑/var/lib/mysql/grastate.dat
改seqno -1 为上面正确最大的数
改safe_to_bootstrap: 0为safe_to_bootstrap: 1
这里注意不要使用制表符 要使用空格

然后启动引导节点

systemctl start [e-mail]

这里如果显示超时退出，使用手动引导

sudo -u mysql mysqld --wsrep-new-cluster

这里需要看日志tail -f /var/log/mysqld.log 如果日志没有错误输出，而引导退错误退出了 再次执行命令引导
下面这样就行了

登录mysql 产看状态如下两个synced和primary就行了

确保这synced和primary这两个状态后加入第二个节点

systemctl start mysql

观察日志/var/lib/mysql中wsrep_recovery_verbose这个开头的，这里是恢复操作时候所产生的日志
注意：这里遇到问题是启动第二个节点后，第一个引导节点进程被杀，oom了 ，限制SST使用内存大小和减小innodb_buffer_pool_size到8G，如不配置将使用innodb_buffer_pool_size

[sst]
inno-apply-opts="--use-memory=500M"

当三个节点都准备好了

退出引导节点

systemctl stop [e-mail]

或者使用

mysqladmin -uroot  -prootPass shutdown

正常启动
观察集群是否正常收敛

SHOW STATUS LIKE 'wsrep_%';

集群收敛后，正常启动

systemctl start mysql

5、TLS加密通讯通讯问题

pxc默认启动加密通讯，使用的证书默认在数据目录，但是这个证书我碰到了会重新生成的情况；
手动生成证书必须一下方式写配置文件[mysqld]才能用

# 服务器端加密
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
# pxc流量加密证书
wsrep_provider_options="socket.ssl=yes;socket.ssl_key=/etc/mysql/ssl/server-key.pem;socket.ssl_cert=/etc/mysql/ssl/server-cert.pem;socket.ssl_ca=/etc/mysql/ssl/ca.pem"

期间我们可以通过浏览器访问https://10.99.99.21:4567/ 看看证书是否替换了

6、pxc 节点oom问题

减小innodb_buffer_pool_size 大小，提升节点内存和swap 交换分区大小

systemctl stop tuned
systemctl disable tuned

lsmod | grep -i balloon
vmw_balloon 24576 0
这部分内存 是被VMware 调走给其他虚拟机用了 说明虚拟化环境比较资源紧张