Managing MySQL PXC Cluster Operations

Percona Software provides database solusions with the following key features for PXC clusters:

  • Synchronous replication - transactions are either committed successfully on all nodes or not committed at all
  • Multi-master replication - each node can handle both read and write operations
  • Strong data consistency - all nodes maintain identical data
  • Node count optimization - more nodes result in slower synchronization, recommended to use 3 nodes
  • Uniform node configuration - cluster synchronization speed is determined by the slowest node
  • InnoDB engine only support

Installation

Each node requires the following steps:

1. Remove other MySQL components, such as mariadb-libs bundled with CentOS
   yum -y remove mari*

2. Configure firewall rules - PXC clusters depend on 4 ports:
   3306 - MySQL service port
   4444 - Full synchronization (SST) request port
   4567 - Communication port between database nodes
   4568 - Incremental synchronization (IST) request port
   
   Example: firewall-cmd --zone=public --add-port=3306/tcp --permanent
   Example: firewall-cmd reload

3. Disable SELINUX and reboot
   vim /etc/selinux/config
   Change SELINUX=disabled

4. Download installation packages
   https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/LATEST/
   https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
   Additionally, install qpress-11-1.el7.x86_64

Final package list:
percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
percona-xtrabackup-24-debuginfo-2.4.24-1.el7.x86_64.rpm
percona-xtrabackup-test-24-2.4.24-1.el7.x86_64.rpm
Percona-XtraDB-Cluster-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-57-debuginfo-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-client-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-devel-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-full-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-garbd-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-server-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-compat-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-test-57-5.7.35-31.53.1.el7.x86_64.rpm
qpress-11-1.el7.x86_64.rpm

Upload to home directory and extract if needed
yum -y localinstall *.rpm

# Start database
systemctl start mysqld

# Check default password
cat /var/log/mysqld.log | grep "A temporary password"
# Change password
mysql_secure_installation
y/2/y/y/y/y/y/y
New password: Abc*123456
# Create remotely accessible user
CREATE USER 'admin'@'%' IDENTIFIED BY 'Abc*123456';
GRANT all privileges ON *.* TO 'admin'@'%';
FLUSH PRIVILEGES;

Cluster Initialization

Edit /etc/my.cnf for each node (each should be different):

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
# Database character set
character_set_server=utf8
# Allow remote access
bind_address=0.0.0.0
# Skip DNS resolution
skip-name-resolve
# Table name case insensitivity
lower_case_table_names=1
# Maximum connections
max-connections=2000

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Unique MySQL instance ID in PXC cluster, cannot be duplicated, must be numeric
server-id=1
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# PXC cluster name
wsrep_cluster_name=pxc-cluster
# All node IPs in the cluster
wsrep_cluster_address=gcomm://192.168.45.51,192.168.45.52,192.168.45.53
# Current node name
wsrep_node_name=pxc-1
# Current node IP
wsrep_node_address=192.168.45.51
# Synchronization method: mysqldump, rsync, xtrabackup
wsrep_sst_method=xtrabackup-v2
# Account used for synchronization
wsrep_sst_auth=admin:Abc*123456
# Synchronization strict mode for strong consistency
pxc_strict_mode=ENFORCING
# ROW-based replication for security and reliability
binlog_format=ROW
# Default engine
default_storage_engine=InnoDB
# Auto-increment primary keys without table locking
innodb_autoinc_lock_mode=2

Create the cluster:

First, stop all MySQL instances:
systemctl stop mysql
# Select 192.168.45.51 as the primary node
Primary node: systemctl start mysql@bootstrap.service
Other nodes: systemctl start mysql

Log in to any node and check cluster status:

show status like 'wsrep_cluster%';

wsrep_cluster_weight:3
wsrep_cluster_conf_id:3
wsrep_cluster_size:3 # Number of cluster nodes
wsrep_cluster_state_uuid:61b64b6d-75c3-11ec-a4b2-ef8a94cef881
wsrep_cluster_status:Primary
# More status information
show status like 'wsrep%';

PXC Node Joining and Leaving

# How to start and stop
Primary node: 
systemctl start mysql@bootstrap.service
systemctl stop mysql@bootstrap.service
Other nodes: 
systemctl start mysql
systemctl stop mysql

# Important file
cat /var/lib/mysql/grastate.dat

# GALERA saved state
version: 2.1
uuid:    61b64b6d-75c3-11ec-a4b2-ef8a94cef881
seqno:   -1
# Last node shut down, this value will become 1. When starting next time, start this node as primary because its data is the most recent
safe_to_bootstrap: 0

1. If it was a safe shutdown, start the last shut down node as primary
2. If it was an unexpected shutdown, check /var/lib/mysql/grastate.dat for nodes with safe_to_bootstrap=1 and start one as primary
3. If it was an unexpected shutdown and all nodes in /var/lib/mysql/grastate.dat have safe_to_bootstrap=0, modify one node's configuration to set it to 1 and start as primary
4. When bringing a service online, if the cluster is still available, start other nodes normally with systemctl start mysql
Applicable scenarios for a 3-node cluster:
- Safe shutdown of 2 nodes
- Unexpected shutdown of 1 node
- Unexpected shutdown of 2 nodes is not possible because the remaining node can no longer be used. The PXC cluster needs to be rebuilt.

Important Notes

# Remove MySQL auto-startup because if a PXC node goes down and restarts, it will start synchronizing data, making MySQL slow. The correct approach is to copy data from another node before starting to minimize the impact of synchronization data transfer.
chkconfig mysqld off

# After execution, you might find systemctl start mysqld no longer works because the previous command似乎删除了链接
systemctl enable mysql.service
or
service mysqld restart

MySQL Password Recovery

1. vim /etc/my.cnf
   Add skip-grant-tables under [mysqld]
2. Restart systemctl restart mysqld
3. Log in directly with mysql
   mysql> use mysql;
   mysql> update user set password=password('newpwd') where user = 'root';

For MySQL 5.7 and above, change the password field to authentication_string
4. Remove the previous configuration and restart (previously it was mysqld, now it's mysql)
systemctl restart mysql

MySQL Middleware

JDK Installation

yum install -y java-1.8.0-openjdk-devel.x86_64
Configure JAVA_HOME environment variable
ls -lrt /etc/alternatives/java
/etc/alternatives/java -> /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.322.b06-1.el7_9.x86_64/jre/bin/java
vim /etc/profile
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.322.b06-1.el7_9.x86_64
source /etc/profile

Mycat Installation and Startup

Download path: http://dl.mycat.org.cn/

chmod 777 ./*.sh
cd mycat/bin
mycat start
mycat stop
mycat restart
mycat status

Mycat Management

For configuration reloading, SQL monitoring and analysis, etc.: https://www.cnblogs.com/wxzhe/p/10290201.html

Mycat Configuration Files

schema.xml defines Mycat's virtual database:

<?xml version="1.0"??>
<schema xmlns:mycat="http://io.mycat/">
    
    <schema checksqlschema="false" datanode="dn1" name="nacos" sqlmaxlimit="-1"></schema>
    
    
    <datanode database="nacos" datahost="pxc" name="dn1"></datanode>
    
    <datahost balance="2" dbdriver="native" dbtype="mysql" maxcon="1000" mincon="10" name="pxc" slavethreshold="100" switchtype="1" writetype="0">
        <heartbeat>select user()</heartbeat>
        <writehost host="W1" password="Abc*17894" url="192.168.33.51:3306" user="admin">
            <readhost host="R1" password="Abc*17894" url="192.168.33:3306" user="admin" weight="1"></readhost>
            <readhost host="R2" password="Abc*17894" url="192.168.33:3306" user="admin" weight="1"></readhost>
        </writehost>
    </datahost>
</schema>

server.xml defines connection configuration (8066 is connection port, 9066 is management port for cluster status):

<?xml version="1.0" encoding="UTF-8"??>
<server xmlns:mycat="http://io.mycat/">
    <system>
        <property name="nonePasswordLogin">0</property>
        <property name="ignoreUnknownCommand">0</property>
        <property name="useHandshakeV10">1</property>
        <property name="removeGraveAccent">1</property>
        <property name="useSqlStat">0</property>
        <property name="useGlobleTableCheck">0</property>
        <property name="sqlExecuteTimeout">300</property>
        <property name="sequenceHandlerType">1</property>
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
        <property name="subqueryRelationshipCheck">false</property>
        <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>
        <property name="useOffHeapForMerge">0</property>
        <property name="memoryPageSize">64k</property>
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">false</property>
        <property name="strictTxIsolation">true</property>
        <property name="parallExecute">0</property>
        <property name="serverBacklog">2048</property>
    </system>
    
    
    <firewall>
        <whitehost>
            
            <host host="%" user="dev"></host>
        </whitehost>
        <blacklist check="false"></blacklist>
    </firewall>
    <user name="dev">
        <property name="password">123456</property>
        
        <property name="schemas">mall_db</property>
        
        <property name="readOnly">true</property>
    </user>
</server>

Backup/Restore

(Online) Cold backup: Stop the service and package all MySQL data files (Online) Hot backup: Backup without stopping the service (recommended)

# Full hot backup of the database every Sunday at 0:00
0 0 * * 0  /home/backup/backup.sh > /home/backup/backup.log 2>&1

#!/bin/bash
time=$(date "+%Y-%m-%d %H:%M:%S")
echo "Executing full hot backup at ${time}"
filename=$(date "+%Y%m%d")
innobackupex --default-file=/etc/my.cnf --host=10.10.20.51 --user=admin --password=Abc*17894 --port=3306 --stream=xbstream > /home/backup/${filename}.xbstream

# Full cold restore
# Extract streamed backup
mkdir /home/backup/20220901
xbstream -x < /home/backup/20220901.xbstream -C  /home/backup/20220901
rm -rf /var/lib/mysql
innobackupex --default-file=/etc/my.cnf --copy-back /home/backup/20220901/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# Incremental hot backup
innobackupex --default-file=/etc/my.cnf --host=10.10.20.51 --user=admin --password=Abc*17894 --port=3306 --incremental-basedir=/home/backup/20220505 --incremental /home/backup/increment

# Binlog flashback tool: https://github.com/danfengcao/binlog2sql
binlog2sql

yum -y install epel-release
yum -y install python-pip
pip install -r requirements.txt

python binlog2sql.py -uadmin -p'Abc*17894' -dflash -t table_name --start-file='localhost-bin.00010' > flash.sql

Common Operational SQL Commands

-- Create new user
create user 'mmall'@'%' IDENTIFIED BY 'MyNewPass4!';
-- Create database
create database if not exists `mmall` default character set utf8 collate utf8_general_ci;
-- Grant permissions
grant all privileges on mmall.* to mmall@'%' identified by 'MyNewPass4!' with grant option;
-- Refresh privileges
flush privileges;
-- Revoke permissions
revoke all on *.* FROM 'mmall'@'%';
-- Check permissions
show grants for 'mmall'@'%';
-- Delete user
drop user 'mmall'@'%';
-- Export database
mysqldump -u root -p123456 test > dump.sql
-- Import database
mysql -u root -p123456 --default-character-set=utf8 test < change.sql
-- Check current connections
SHOW PROCESSLIST;
-- Clear connections
KILL [connection_id];
-- Check total MySQL connections
SHOW STATUS LIKE 'Threads_connected';
-- Check maximum connections
SHOW VARIABLES LIKE 'max_connections';

Single MySQL Configuration File

Path: /etc/percona-server.conf.d

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
character_set_server=utf8
bind_address=0.0.0.0
skip-name-resolve
lower_case_table_names=1
max-connections=3000

symbolic-links=0

server-id=1
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

Tags: MySQL percona-xtradb-cluster galera MyCAT database-clustering

Posted on Thu, 10 Sep 2026 16:49:00 +0000 by newhen

Hot Tags

©2026 Freaks City