mysql pxc

最新推荐文章于 2022-04-26 14:27:38 发布

栉名结弦

最新推荐文章于 2022-04-26 14:27:38 发布

阅读量604

点赞数 1

分类专栏：笔记文章标签： linux mysql

本文链接：https://blog.csdn.net/qq_47499438/article/details/118176633

版权

笔记专栏收录该内容

16 篇文章 0 订阅

订阅专栏

一、PXC介绍

Percona Server由领先的MySQL咨询公司Percona发布。 Percona Server是一款独立的数据库产品，其可以完全与MySQL兼容，可以在不更改代码的情况了下将存储引擎更换成XtraDB 。XtraDB可以看做是InnoDB存储引擎的增强版本，它完全兼容InnoDB，且提供了很多InnoDB不具备的有用的功能。

Percona团队的最终声明是“Percona Server是最接近官方MySQL Enterprise发行版的版本”。

Percona XtraDB Cluster是MySQL高可用性和可扩展性的解决方案.

Percona XtraDB Cluster提供的特性有

1.同步复制，事务要么在所有节点提交或不提交。

2.多主复制，可以在任意节点进行写操作。

3.在从服务器上并行应用事件，真正意义上的并行复制。

4.节点自动配置。

5.数据一致性，不再是异步复制。

PXC的实现是在原代码上通过Galera、Percona XtraBackup将不通的Mysql实例链接起来，实现了multi-master的集群架构。

下图中有三个实例，组成了一个集群，而这三个节点与普通的主从架构不通，它们都可以做为主节点，三个节点是对等的，这种一般称为multi-master架构，当有客户端要写入或者读取数据时，随便链接哪个实例都是一样的，读到的数据是相同的，写入某一个节点之后，集群自己会将新数据同步道其它节点上面，这种架构不共享任何数据，是一种高冗余架构。

PXC特点：

1.集群是有节点组成的，推荐配置至少3个节点，但是也可以运行在2个节点上。

2.每个节点都是普通的mysql/percona服务器，可以将现有的数据库服务器组成集群，从PXC机器群可以拆分成单独的服务器。

3.每个节点都包含完整的数据副本。

PXC优点：

1.当执行一个查询时，在本地节点上执行。因为所有数据都在本地，无需远程访问。

2.无需集中管理。可以在任何时间点失去任何节点，但是集群将照常工作。

3.良好的读负载扩展，任意节点都可以查询。

PXC缺点：

1.加入新节点，开销大。需要复制完整的数据

2.不能有效的解决写缩放问题，所有的写操作都将发生在所有节点上。

3.有多少个节点就有多少重复的数据

二、部署pxc

1.环境规划

主机名	IP
pxc01	192.168.100.140
pxc02	192.168.100.141
pxc03	192.168.100.142

关闭防火墙（三台都要）

[root@pxc01 ~]# systemctl stop firewalld
[root@pxc01 ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.
[root@pxc01 ~]# setenforce 0
[root@pxc01 ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[root@pxc01 ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.140 pxc01
192.168.100.141 pxc02
192.168.100.142 pxc03

修改内核参数（三台都要）

[root@pxc01 ~]# vi /etc/security/limits.conf
mysql            soft    nproc           65535
mysql            hard    nproc           65535
mysql            soft    nofile          65535
mysql            hard    nofile          65535

2.安装xtrabackup（三台都要）

创建用户及用户组

[root@pxc01 ~]# groupadd mysql
[root@pxc01 ~]# useradd -g mysql mysql
[root@pxc01 ~]# mkdir -p /mysql/3306/{data,redo,binlog,tmp,logs}
[root@pxc01 ~]# chown -R mysql.mysql /mysql/3306
[root@pxc01 ~]# chmod -R 755 /mysql/3306

安装xtrabackuo

[root@pxc01 ~]# cd /usr/local/src
[root@pxc01 src]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/tarball/percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz
[root@pxc01 src]# tar -zxvf percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz 
[root@pxc01 src]# ls
percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12
percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz
[root@pxc01 src]# mv percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12 /usr/local/xtrabackup
[root@pxc01 src]# chown -R mysql:mysql /usr/local/xtrabackup
[root@pxc01 src]# chmod -R 755 /usr/local/xtrabackup
[root@pxc01 src]# ll /usr/local/
总用量 0
drwxr-xr-x. 2 root  root   6 8月  12 2015 bin
drwxr-xr-x. 2 root  root   6 8月  12 2015 etc
drwxr-xr-x. 2 root  root   6 8月  12 2015 games
drwxr-xr-x. 2 root  root   6 8月  12 2015 include
drwxr-xr-x. 2 root  root   6 8月  12 2015 lib
drwxr-xr-x. 2 root  root   6 8月  12 2015 lib64
drwxr-xr-x. 2 root  root   6 8月  12 2015 libexec
drwxr-xr-x. 2 root  root   6 8月  12 2015 sbin
drwxr-xr-x. 5 root  root  46 6月   4 16:56 share
drwxr-xr-x. 2 root  root  68 6月  24 17:56 src
drwxr-xr-x. 6 mysql mysql 88 3月  10 22:54 xtrabackup

3.安装pxc（三台都要）

[root@pxc01 src]# wget https://downloads.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.33-31.49/binary/tarball/Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.tar.gz
[root@pxc01 src]# tar -zxvf Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.tar.gz 
[root@pxc01 src]# mv Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12 /usr/local/mysql
[root@pxc01 src]# chown -R mysql.mysql /usr/local/mysql
[root@pxc01 src]# chmod -R 755 /usr/local/mysql
[root@pxc01 src]# ll /usr/local/
总用量 8
drwxr-xr-x.  2 root  root     6 8月  12 2015 bin
drwxr-xr-x.  2 root  root     6 8月  12 2015 etc
drwxr-xr-x.  2 root  root     6 8月  12 2015 games
drwxr-xr-x.  2 root  root     6 8月  12 2015 include
drwxr-xr-x.  2 root  root     6 8月  12 2015 lib
drwxr-xr-x.  2 root  root     6 8月  12 2015 lib64
drwxr-xr-x.  2 root  root     6 8月  12 2015 libexec
drwxr-xr-x. 13 mysql mysql 4096 3月  15 23:17 mysql
drwxr-xr-x.  2 root  root     6 8月  12 2015 sbin
drwxr-xr-x.  5 root  root    46 6月   4 16:56 share
drwxr-xr-x.  2 root  root  4096 6月  24 18:09 src
drwxr-xr-x.  6 mysql mysql   88 3月  10 22:54 xtrabackup

安装依赖

[root@pxc01 ~]# yum install -y epel-release
[root@pxc01 ~]# yum install -y git scons gcc gcc-c++ openssl check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel socat libcurl-devel socat nc   perl-IO-Socket-SSL    perl-Time-HiRes rsync  lsof

创建my.cnf配置文件

[root@pxc01 ~]# vi /mysql/3306/my.cnf 
[client]
default-character-set = utf8
port = 3306
socket = /mysql/3306/tmp/mysql.sock
[mysqld]
port = 3306
character_set_server = utf8
server-id = 100
gtid_mode = on
enforce_gtid_consistency = on
skip-slave-start = 1
relay_log_purge = 0
basedir = /usr/local/mysql
datadir = /mysql/3306/data
tmpdir = /mysql/3306/tmp
log-error = /mysql/3306/logs/alert_3306.log
pid-file = /mysql/3306/tmp/mysql.pid
log-bin = /mysql/3306/binlog/mysql-bin
slow_query_log_file = /mysql/3306/logs/slow.log
default-storage-engine = INNODB
innodb_log_group_home_dir = /mysql/3306/redo
innodb_file_per_table = 1
#PXC
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.100.140,192.168.100.141,192.168.100.142
wsrep_node_name=pxc01
wsrep_node_address=192.168.100.140
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxc:123456
innodb_autoinc_lock_mode=2

[root@pxc02 ~]# vi /mysql/3306/my.cnf
[client]
default-character-set = utf8
port = 3306
socket = /mysql/3306/tmp/mysql.sock
[mysqld]
port = 3306
character_set_server = utf8
server-id = 101
gtid_mode = on
enforce_gtid_consistency = on
skip-slave-start = 1
relay_log_purge = 0
basedir = /usr/local/mysql
datadir = /mysql/3306/data
tmpdir = /mysql/3306/tmp
log-error = /mysql/3306/logs/alert_3306.log
pid-file = /mysql/3306/tmp/mysql.pid
log-bin = /mysql/3306/binlog/mysql-bin
slow_query_log_file = /mysql/3306/logs/slow.log
default-storage-engine = InnoDB
innodb_log_group_home_dir = /mysql/3306/redo
innodb_file_per_table = 1
#PXC
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.100.140,192.168.100.141,192.168.100.142
wsrep_node_name=pxc02
wsrep_node_address=192.168.100.141
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxc:123456
innodb_autoinc_lock_mode=2

[root@pxc03 ~]# vi /mysql/3306/my.cnf
[client]
default-character-set = utf8
port = 3306
socket = /mysql/3306/tmp/mysql.sock
[mysqld]
port = 3306
character_set_server = utf8
server-id = 102
gtid_mode = on
enforce_gtid_consistency = on
skip-slave-start = 1
relay_log_purge = 0
basedir = /usr/local/mysql
datadir = /mysql/3306/data
tmpdir = /mysql/3306/tmp
log-error = /mysql/3306/logs/alert_3306.log
pid-file = /mysql/3306/tmp/mysql.pid
log-bin = /mysql/3306/binlog/mysql-bin
slow_query_log_file = /mysql/3306/logs/slow.log
default-storage-engine = InnoDB
innodb_log_group_home_dir = /mysql/3306/redo
innodb_file_per_table = 1
#PXC
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.100.140,192.168.100.141,192.168.100.142
wsrep_node_name=pxc03
wsrep_node_address=192.168.100.142
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxc:123456
innodb_autoinc_lock_mode=2

修改环境变量

[root@pxc01 ~]# su - mysql
[mysql@pxc01 ~]$ vi .bash_profile
export PATH=/usr/local/mysql/bin:$PATH:/usr/local/xtrabackup/bin
[mysql@pxc01 ~]$ source .bash_profile

初始化数据库

[mysql@pxc01 ~]$ mysqld --defaults-file=/mysql/3306/my.cnf --initialize

4.启动pxc01节点

[mysql@pxc01 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf --wsrep-new-cluster &
[1] 29593

查看密码并登录修改密码

[mysql@pxc01 ~]$ exit
登出
[root@pxc01 ~]# cat /mysql/3306/logs/alert_3306.log | grep 'temporary password'
2021-06-24T10:39:02.795668Z 1 [Note] A temporary password is generated for root@localhost: P-<)+tQjf7Av
[root@pxc01 ~]# su - mysql
[mysql@pxc01 ~]$ mysql -uroot -p'P-<)+tQjf7Av'

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password='000000';
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

添加用户

mysql> grant reload,lock tables,process,replication client on *.* to 'pxc'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

修改safe_to_bootstrap: 0为 safe_to_bootstrap: 1

[mysql@pxc01 ~]$ cat /mysql/3306/data/grastate.dat 
# GALERA saved state
version: 2.1
uuid:    3d8c171d-d4de-11eb-a75b-0f7691b4aafa
seqno:   -1
safe_to_bootstrap: 1

5.启动其他两个节点（要依次启动，不要同时启动，待pxc02同步完成后再启动pxc03）

pxc02：

[root@pxc02 ~]# su - mysql
[mysql@pxc02 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf &
[1] 35589
[mysql@pxc02 ~]$ exit
登出
[root@pxc02 ~]# cat /mysql/3306/logs/alert_3306.log |grep pxc02
2021-06-24T10:50:32.271169Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 3e4b0bfe-d4da-11eb-88ef-ce4cea469c8c from 1 (pxc02)
2021-06-24T10:50:33.299621Z 0 [Note] WSREP: Member 1.0 (pxc02) requested state transfer from '*any*'. Selected 0.0 (pxc01)(SYNCED) as donor.
2021-06-24T10:50:51.952435Z 0 [Note] WSREP: 0.0 (pxc01): State transfer to 1.0 pxc02) complete.
2021-06-24T10:50:57.696922Z 0 [Note] WSREP: 1.0 (pxc02): State transfer from 0.0 (pxc01) complete.  ##显示这个即为同步成功
2021-06-24T10:50:57.697352Z 0 [Note] WSREP: Member 1.0 (pxc02) synced with group.

pxc03：

[root@pxc03 ~]# su - mysql
[mysql@pxc03 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf &
[1] 35562
[mysql@pxc03 ~]$ exit
登出
[root@pxc03 ~]#  cat /mysql/3306/logs/alert_3306.log |grep pxc03
2021-06-24T10:52:42.685631Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 4d1fdda3-d4da-11eb-9724-968132a3c725 from 0 (pxc03)
2021-06-24T10:52:43.683381Z 0 [Note] WSREP: Member 0.0 (pxc03) requested state transfer from '*any*'. Selected 1.0 (pxc01)(SYNCED) as donor.
2021-06-24T10:52:57.944146Z 0 [Note] WSREP: 1.0 (pxc01): State transfer to 0.0 pxc03) complete.
2021-06-24T10:53:02.668989Z 0 [Note] WSREP: 0.0 (pxc03): State transfer from 1.0 (pxc01) complete.   ##显示这个即为同步成功
2021-06-24T10:53:02.669686Z 0 [Note] WSREP: Member 0.0 (pxc03) synced with group.

查看集群

mysql> show status like 'wsrep_incoming_addresses';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| wsrep_incoming_addresses | 192.168.100.141:3306,192.168.100.142:3306,192.168.100.140:3306 |
+--------------------------+----------------------------------------------------------------+
1 row in set (0.00 sec)

6.测试（在每个节点的数据库中都插入数据，看是否同步）

[mysql@pxc01 ~]$ mysql -uroot -p000000
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t1(id int PRIMARY KEY,name varchar(10));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)
[mysql@pxc02 ~]$ mysql -uroot -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33-36-49-log Percona XtraDB Cluster binary (GPL) 5.7.33-rel36-49, Revision a1ed9c3, wsrep_31.49

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1
    -> ;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.03 sec)


mysql> insert into t1 values(2,'b');
Query OK, 1 row affected (0.12 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

[mysql@pxc03 ~]$ mysql -uroot -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33-36-49-log Percona XtraDB Cluster binary (GPL) 5.7.33-rel36-49, Revision a1ed9c3, wsrep_31.49

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.05 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1
    -> ;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

三、向pxc中添加新节点

1.创建新的虚拟机配置好ip后关闭防火墙（此处虚拟机ip为192.168.100.143）

2.修改内核

[root@pxc04 ~]# vi /etc/security/limits.conf 
mysql            soft    nproc           65535
mysql            hard    nproc           65535
mysql            soft    nofile          65535
mysql            hard    nofile          65535

3.创建用户及用户组

[root@pxc04 ~]# groupadd mysql
[root@pxc04 ~]# useradd -g mysql mysql
[root@pxc04 ~]# mkdir -p /mysql/3306/{data,redo,binlog,tmp,logs}
[root@pxc04 ~]# chown -R mysql.mysql /mysql/3306
[root@pxc04 ~]# chmod -R 755 /mysql/3306

4.安装xtrabackup

[root@pxc04 src]# tar -zxvf percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12.tar.gz 
[root@pxc04 src]# mv percona-xtrabackup-2.4.22-Linux-x86_64.glibc2.12 /usr/local/xtrabackup
[root@pxc04 src]# chown -R mysql:mysql /usr/local/xtrabackup
[root@pxc04 src]# chmod -R 755 /usr/local/xtrabackup
[root@pxc04 src]# ll /usr/local/
总用量 4
drwxr-xr-x. 2 root  root     6 8月  12 2015 bin
drwxr-xr-x. 2 root  root     6 8月  12 2015 etc
drwxr-xr-x. 2 root  root     6 8月  12 2015 games
drwxr-xr-x. 2 root  root     6 8月  12 2015 include
drwxr-xr-x. 2 root  root     6 8月  12 2015 lib
drwxr-xr-x. 2 root  root     6 8月  12 2015 lib64
drwxr-xr-x. 2 root  root     6 8月  12 2015 libexec
drwxr-xr-x. 2 root  root     6 8月  12 2015 sbin
drwxr-xr-x. 5 root  root    46 6月  25 00:31 share
drwxr-xr-x. 2 root  root  4096 6月  25 17:02 src
drwxr-xr-x. 6 mysql mysql   88 3月  10 22:54 xtrabackup

5.安装pxc

[root@pxc04 src]# tar -zxvf Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.tar.gz 
[root@pxc04 src]# mv Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12 /usr/local/mysql
[root@pxc04 src]# chown -R mysql.mysql /usr/local/mysql
[root@pxc04 src]# chmod -R 755 /usr/local/mysql
[root@pxc04 src]# ll /usr/local/
总用量 8
drwxr-xr-x.  2 root  root     6 8月  12 2015 bin
drwxr-xr-x.  2 root  root     6 8月  12 2015 etc
drwxr-xr-x.  2 root  root     6 8月  12 2015 games
drwxr-xr-x.  2 root  root     6 8月  12 2015 include
drwxr-xr-x.  2 root  root     6 8月  12 2015 lib
drwxr-xr-x.  2 root  root     6 8月  12 2015 lib64
drwxr-xr-x.  2 root  root     6 8月  12 2015 libexec
drwxr-xr-x. 13 mysql mysql 4096 3月  15 23:17 mysql
drwxr-xr-x.  2 root  root     6 8月  12 2015 sbin
drwxr-xr-x.  5 root  root    46 6月  25 00:31 share
drwxr-xr-x.  2 root  root  4096 6月  25 17:04 src
drwxr-xr-x.  6 mysql mysql   88 3月  10 22:54 xtrabackup

6.安装依赖

[root@pxc04 src]# yum install -y epel-release
[root@pxc04 src]# yum install -y git scons gcc gcc-c++ openssl check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel socat libcurl-devel socat nc   perl-IO-Socket-SSL    perl-Time-HiRes rsync  lsof

7.创建my.cnf

[root@pxc04 src]# vi /mysql/3306/my.cnf
[client]
default-character-set = utf8
port = 3306
socket = /mysql/3306/tmp/mysql.sock
[mysqld]
port = 3306
character_set_server = utf8
server-id = 103
gtid_mode = on
enforce_gtid_consistency = on
skip-slave-start = 1
relay_log_purge = 0
basedir = /usr/local/mysql
datadir = /mysql/3306/data
tmpdir = /mysql/3306/tmp
log-error = /mysql/3306/logs/alert_3306.log
pid-file = /mysql/3306/tmp/mysql.pid
log-bin = /mysql/3306/binlog/mysql-bin
slow_query_log_file = /mysql/3306/logs/slow.log
default-storage-engine = InnoDB
innodb_log_group_home_dir = /mysql/3306/redo
innodb_file_per_table = 1

8.修改环境变量

[root@pxc04 src]# su - mysql 
[mysql@pxc04 ~]$ vi .bash_profile
export PATH=/usr/local/mysql/bin:$PATH:/usr/local/xtrabackup/bin
[mysql@pxc04 ~]$ source .bash_profile

9.初始化数据库

[mysql@pxc04 ~]$ mysqld --defaults-file=/mysql/3306/my.cnf --initialize

10.启动数据库

[mysql@pxc04 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf --wsrep-new-cluster &
[1] 35567

11.修改密码

[mysql@pxc04 ~]$ exit
登出
[root@pxc04 src]# cat /mysql/3306/logs/alert_3306.log | grep 'temporary password'
2021-06-25T09:31:17.872323Z 1 [Note] A temporary password is generated for root@localhost: GDruz9h=623T
[root@pxc04 src]# su - mysql 
[mysql@pxc04 ~]$ mysql -uroot -p'GDruz9h=623T'

mysql> set password='000000';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

12.在pxc03节点上配置主从用户

mysql> create user 'bak'@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected (1.20 sec)

mysql> grant replication slave on *.* to 'bak'@'192.168.100.%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (11.29 sec)

13.将pxc03的数据拷贝到pxc04上

mysql> quit
Bye
[mysql@pxc03 ~]$ mysqldump --single-transaction -uroot -p000000 -A --master-data=2 > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[mysql@pxc03 ~]$ ls 
all.sql
[mysql@pxc03 ~]$ pwd
/home/mysql
[mysql@pxc03 ~]$ scp all.sql root@192.168.100.143:/home/mysql/
root@192.168.100.143's password: 
all.sql                                       100%  855KB 854.6KB/s   00:00

14.pxc04上恢复数据

[mysql@pxc04 ~]$ ls
all.sql
[mysql@pxc04 ~]$ pwd
/home/mysql
[mysql@pxc04 ~]$ mysql -uroot -p000000

mysql> source /home/mysql/all.sql

15.查看pxc03上的master_log信息，并在pxc04上配置主从

pxc03： 
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 821
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e28e908-2af0-ee14-6faa-b5b70b97244e:1-7
1 row in set (0.00 sec)

pxc04：
mysql> change master to master_host='192.168.100.142',master_user='bak',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=821;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.142
                  Master_User: bak
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 821
               Relay_Log_File: pxc04-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

16.pxc04配置集群

在my.cnf中添加
[mysql@pxc04 ~]$ vi /mysql/3306/my.cnf 
#PXC
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.100.140,192.168.100.141,192.168.100.142,192.168.100.143
wsrep_node_name=pxc04
wsrep_node_address=192.168.100.143
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxc:123456
innodb_autoinc_lock_mode=2
将pxc03的grastate.dat拷贝到pxc04
[mysql@pxc03 ~]$ scp /mysql/3306/data/grastate.dat root@192.168.100.143:/mysql/3306/data/
root@192.168.100.143's password: 
grastate.dat                                  100%  113     0.1KB/s   00:00
给予权限
[mysql@pxc03 ~]$ chown -R mysql.mysql /mysql/3306/data/grastate.dat
重启虚拟机

17.pxc04启动mysql并检查同步

[mysql@pxc04 ~]$ mysqld_safe --defaults-file=/mysql/3306/my.cnf  &
[1] 36135
[mysql@pxc04 ~]$  cat /mysql/3306/logs/alert_3306.log | grep pxc04
2021-06-30T10:11:17.617438Z 0 [Note] WSREP: STATE EXCHANGE: got state msg: 8262e7ef-d98b-11eb-83ed-5602265692ac from 0 (pxc04)
2021-06-30T10:11:18.646804Z 0 [Note] WSREP: Member 0.0 (pxc04) requested state transfer from '*any*'. Selected 1.0 (pxc02)(SYNCED) as donor.
2021-06-30T10:11:31.305198Z 0 [Note] WSREP: 1.0 (pxc02): State transfer to 0.0 pxc04) complete.
2021-06-30T10:11:36.130596Z 0 [Note] WSREP: 0.0 (pxc04): State transfer from 1.0 (pxc02) complete.  ##出现这个说明同步完成
2021-06-30T10:11:36.131702Z 0 [Note] WSREP: Member 0.0 (pxc04) synced with group.

18. 在pxc01查看集群


mysql> show status like 'wsrep_incoming_addresses';
+--------------------------+-------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                               |
+--------------------------+-------------------------------------------------------------------------------------+
| wsrep_incoming_addresses | 192.168.100.143:3306,192.168.100.141:3306,192.168.100.142:3306,192.168.100.140:3306 |
+--------------------------+-------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
查看集群有几台
mysql> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 4     |
+--------------------+-------+
1 row in set (0.00 sec)

19. 测试

在pxc01中添加数据
mysql> use test
Database changed

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into t1 values(3,'c');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

在pxc04查看是否同步
[mysql@pxc04 ~]$ mysql -p000000

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test

Database changed
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

至此添加新节点完毕

栉名结弦

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql pxc

一、PXC介绍Percona Server由领先的MySQL咨询公司Percona发布。 Percona Server是一款独立的数据库产品，其可以完全与MySQL兼容，可以在不更改代码的情况了下将存储引擎更换成XtraDB 。XtraDB可以看做是InnoDB存储引擎的增强版本，它完全兼容InnoDB，且提供了很多InnoDB不具备的有用的功能。Percona团队的最终声明是“Percona Server是最接近官方MySQL Enterprise发行版的版本”。Percona XtraDB
复制链接

扫一扫