Percona XtraDB（PXC）实现数据库集群高可用
Percona XtraDB实现数据库高可用
https://www.percona.com/downloads/
架构
| 主机名 | IP | 角色 |
|---|---|---|
| pxc01 | 192.168.139.31 | 主MySQL |
| pxc02 | 192.168.139.32 | 从MySQL |
| pxc03 | 192.168.139.33 | 从MySQL |
| pxc04 | 192.168.139.34 | MHA管理端 |
1)配置yum源
-
四个节点都要配置（我演示一个）
[root@pxc01 ~]# cat > /etc/yum.repos.d/pxc.repo << EOF
[percona]
name=percona_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/\$releasever/RPMS/\$basearch
enabled = 1
gpgcheck = 0
EOF
[root@pxc01 ~]# yum clean all
2）配置mysql及集群配置文件
-
配置前三个主机
2.1）配置mysql
[root@pxc01 ~]# yum install Percona-XtraDB-Cluster-57 -y
[root@pxc01 ~]# cat /etc/my.cnf
.............. # 该配置文件不用修改
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
[root@pxc01 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=31 # 重要
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
symbolic-links=0
[root@pxc02 ~]# yum install Percona-XtraDB-Cluster-57 -y
[root@pxc02 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=32 # 重要
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
symbolic-links=0
[root@pxc03 ~]# yum install Percona-XtraDB-Cluster-57 -y
[root@pxc03 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=33 # 重要
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
symbolic-links=0
2.2)配置percona-xtradb
-
前三个节点都要配置
[root@pxc01 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.139.31,192.168.139.32,192.168.139.33 ##三个节点的IP
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.139.31 ##各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster-stangj
wsrep_node_name=pxc-cluster-node-31 ##各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" ##取消本行注释
[root@pxc02 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.139.31,192.168.139.32,192.168.139.33
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.139.32 ##各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster-stangj
wsrep_node_name=pxc-cluster-node-32 ##各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" ##取消本行注释
[root@pxc03 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.139.31,192.168.139.32,192.168.139.33
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.139.33 ##各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster-stangj
wsrep_node_name=pxc-cluster-node-33 ##各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" ##取消本行注释
3）启动集群第一个节点
[root@pxc01 ~]# systemctl enable --now mysql@bootstrap.service
[root@pxc01 ~]# ss -tnl | grep -Ew '3306|4567'
LISTEN 0 128 *:4567 *:*
LISTEN 0 80 [::]:3306 [::]:*
`获取密码`
[root@pxc01 ~]# grep "temporary password" /var/log/mysqld.log
2023-12-31T12:30:28.676417Z 1 [Note] A temporary password is generated for root@localhost: jHW/s>lZ+47L
`修改密码`
[root@pxc01 ~]# mysql -uroot -p'jHW/s>lZ+47L'
mysql> alter user 'root'@'localhost' identified by 'stj@123';
mysql> \q
[root@pxc01 ~]# mysql -uroot -p'stj@123'
# 创建配置文件定义的账号sstuser;密码s3cretPass
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
# 查看相关变量
mysql> SHOW VARIABLES LIKE 'wsrep%'\G
# 查看相关状态变量
mysql> SHOW STATUS LIKE 'wsrep%'\G
# 重点关注下面内容
mysql> show status like 'wsrep%';
.........................
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 66438f22-a7d8-11ee-a75d-ba0343ee772a |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.65(rf47405c) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
4)启动剩下两个节点
[root@pxc02 ~]# systemctl enable --now mysql
[root@pxc03 ~]# systemctl enable --now mysql
5）验证
5.1）验证节点是否加入
[root@pxc01 ~]# mysql -uroot -p'stj@123' -e "show status like 'wsrep%'\G" | grep -A2 size
mysql: [Warning] Using a password on the command line interface can be insecure.
Variable_name: wsrep_cert_index_size
Value: 1
*************************** 46. row ***************************
--
Variable_name: wsrep_gcache_pool_size
Value: 3424
*************************** 48. row ***************************
--
Variable_name: wsrep_cluster_size
Value: 3 # 显然已经加入
*************************** 67. row ***************************
以上表示节点加入集群没有问题
5.2)人为造一些数据造一些数据看各个节点是否可以同步数据
`第一个节点造部分数据`
[root@pxc01 ~]# mysql -uroot -p'stj@123'
mysql> create database hellodb;
mysql> use hellodb;
mysql> CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
mysql> insert student (name,age)values('xiaoming',20);
mysql> insert student (name,age)values('wangwu',36);
mysql> insert student (name,age)values('tanghao',18);
`在第二三个节点创建数据库`
[root@pxc02 ~]# mysql -uroot -p'stj@123' -e "create database db1"
[root@pxc03 ~]# mysql -uroot -p'stj@123' -e "create database db2"
`其他两个节点验证数据`
[root@pxc02 ~]# mysql -uroot -p'stj@123' -e "select * from hellodb.student"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 3 | xiaoming | 20 | M |
| 6 | wangwu | 36 | M |
| 9 | tanghao | 18 | M |
+----+----------+------+--------+
[root@pxc03 ~]# mysql -uroot -p'stj@123' -e "select * from hellodb.student"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 3 | xiaoming | 20 | M |
| 6 | wangwu | 36 | M |
| 9 | tanghao | 18 | M |
+----+----------+------+--------+
`所有节点验证数据库信息`
[root@pxc01 ~]# mysql -uroot -p'stj@123' -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@pxc02 ~]# mysql -uroot -p'stj@123' -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@pxc03 ~]# mysql -uroot -p'stj@123' -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
以上表示数据复制没有问题，且三个节点都为主节点
6）扩容集群节点
6.1）配置相应信息
[root@pxc04 ~]# yum install Percona-XtraDB-Cluster-57 -y
`修改配置文件`
[root@pxc04 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=34 # 重要
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
symbolic-links=0
[root@pxc04 ~]# grep '^[^#]' /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.139.31,192.168.139.32,192.168.139.33,192.168.139.34
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.139.34 ##各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster-stangj
wsrep_node_name=pxc-cluster-node-34 ##各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" ##取消本行注释
6.2）启动服务
[root@pxc04 ~]# systemctl enable --now mysql
6.3)验证集群节点情况
[root@pxc01 ~]# mysql -uroot -p'stj@123' -e "show status like 'wsrep%'\G" | grep -A2 size
mysql: [Warning] Using a password on the command line interface can be insecure.
Variable_name: wsrep_cert_index_size
Value: 7
*************************** 46. row ***************************
--
Variable_name: wsrep_gcache_pool_size
Value: 5848
*************************** 48. row ***************************
--
Variable_name: wsrep_cluster_size
Value: 4 # 显示集群已经添加成功
*************************** 67. row ***************************
6.4）验证数据是否同步
[root@pxc04 ~]# mysql -uroot -p'stj@123' -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@pxc04 ~]# mysql -uroot -p'stj@123' -e "select * from hellodb.student"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 3 | xiaoming | 20 | M |
| 6 | wangwu | 36 | M |
| 9 | tanghao | 18 | M |
+----+----------+------+--------+
以上则表示扩容集群节点成功