Centos 6.x 搭建 PXC 集群及在线添加节点

发表于 分类于 ，

[TOC]

Centos 6.x 搭建 PXC 集群及在线添加节点

By:liups@liups.com

1.环境介绍

1.1操作系统版本

CentOS release 6.8 (Final)

1.2 默认端口

端口

用途

3306

数据库端口

4444

SST 全量传输端口

4567

组内成员通信端口

4568

IST增量传输端口

1.3 主机信息

IP

hostname

用途

base

datadir

192.168.51.101

cet701

pxc01

/usr/local/mysql

/data/mysql

192.168.51.102

cet702

pxc02

/usr/local/mysql

/data/mysql

192.168.51.103

cet703

pxc02

/usr/local/mysql

/data/mysql

2. 软件下载

2.1 关于软件包的说明

Percona provides generic tarballs with all required files and binaries for manual installation.

You can download the appropriate tarball package from https://www.percona.com/downloads/Percona-XtraDB-Cluster-57

There are multiple tarballs in the Linux - Generic section depending on the OpenSSL library available in your distribution:

  • ssl100: for Debian prior to 9 and Ubuntu prior to 14.04 versions
  • ssl101: for CentOS 6 and CentOS 7
  • ssl102: for Debian 9 and Ubuntu versions starting from 14.04

这里的环境是 CentOS7 所以选择 ssl101 的软件包。

2.2 获取二进制软件包

1
2
3
cd /usr/local/src
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.25-31.35/binary/tarball/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101.tar.gz
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/tarball/percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt153.tar.gz

3.安装前的准备

3.1 安装依赖软件

1
yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat scons nc make libaio libaio-devel boost-devel rsync asio-devel readline-devel libev bison gcc gcc-c++  -y

3.2 防火墙关闭或者开放端口

1
service iptables stop

或者开放端口：ports 3306, 4444, 4567 and 4568

vi /etc/sysconfig/iptables

1
2
3
4
5
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 4444 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 4567 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 4568 -j ACCEPT
service iptables reload

3.3 关闭selinux

临时关闭

1
setenforce 0

永久关闭：

1
2
vi /etc/sysconfig/selinux
SELINUX=disabled

确认已经关闭

1
2
[root@mydbt001 local]# getenforce
Disabled

3.4 创建MySQL 用户

1
2
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

4 安装Percona-XtraDB-Cluster软件

采用二进制软件包的方式按照，直接复制软件包到 basedir，并创建软连

以下在3个节点都执行

1
cp /usr/local/src/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101.tar.gz /usr/local/

4.1 解压软件包

4.2 创建软连

1
2
cd /usr/local/
ln -s Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101 mysql

4.3 授与basedir和datadir权限

1
2
3
mkdir -p /data/mysql
chown -R mysql:mysql  /data/mysql
chown -R mysql:mysql  /usr/local/mysql

5 搭建pxc环境

5.1 配置参数文件

pxc01

1
2
3
4
5
6
7
8
9
10
11
12
server-id = 1003306
log-bin = /data/mysql/mysql-bin
binlog_format=ROW
#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://192.168.56.103,192.168.56.101,192.168.56.102
wsrep_node_address=192.168.56.101
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxcuser:Password123

pxc02

1
2
3
4
5
6
7
8
9
10
11
12
server-id = 2003306
log-bin = /data/mysql/mysql-bin
binlog_format=ROW
#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://192.168.56.103,192.168.56.101,192.168.56.102
wsrep_node_address=192.168.56.102
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxcuser:Password123

pxc03

1
2
3
4
5
6
7
8
9
10
11
12
server-id = 3003306
log-bin = /data/mysql/mysql-bin
binlog_format=ROW
#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://192.168.56.103,192.168.56.101,192.168.56.102
wsrep_node_address=192.168.56.103
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxcuser:Password123

5.2 节点1初始化MySQL

1
2
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
1
2
[root@cet701 mysql]# cd /usr/local/mysql/bin
[root@cet701 bin]# ./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
1
cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql

⚠️ 通过二进制安装的pxc软件，修改 /etc/init.d/mysql 中的basedir和datadir，否则会出现以下错误

sh: wsrep_sst_xtrabackup-v2: command not found

5.3 启动第一个节点

启动第一个节点的时候需要 采用 bootstrap-pxc 选项。

1
2
3
4
5
6
[root@cet701 data]# service mysql bootstrap-pxc  
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (P[  确定  ]aDB Cluster).
[root@cet701 data]# ps -ef grep mysql
root     15705     1  0 16:17 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid --wsrep-new-cluster
mysql    16704 15705 17 16:17 pts/2    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/mysql/plugin --user=mysql --wsrep-provider=/usr/local/mysql/lib/libgalera_smm.so --wsrep-new-cluster --log-error=/data/mysql/error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root     16745  8991  0 16:17 pts/2    00:00:00 grep --color=auto mysql

5.4 修改密码

1
2
[root@cet701 bin]# cat  /data/mysql/error.loggrep password
2019-06-30T08:09:14.665946Z 1 [Note] A temporary password is generated for root@localhost: 1uZPZS1gou&y
1
2
3
4
[root@cet701 data]# mysqladmin -uroot -p password        
Enter password: 
New password: 
Confirm new password:

新密码为：Password123

5.5 创建sst用户

1
2
CREATE USER 'pxcuser'@'localhost' IDENTIFIED BY 'Pxcuser123';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO'pxcuser'@'localhost';
1
2
3
mysql> CREATE USER 'pxcuser'@'localhost' IDENTIFIED BY 'Pxcuser123';
Query OK, 0 rows affected (0.00 sec)

5.6 查看pxc状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> show status like 'wsrep%st%';
+---------------------------------+--------------------------------------+
 Variable_name                    Value                                
+---------------------------------+--------------------------------------+
 wsrep_local_state_uuid           868f27cb-9b0f-11e9-81ec-ebbaefc4a7ba 
 wsrep_last_applied               3                                    
 wsrep_last_committed             3                                    
 wsrep_flow_control_status        OFF                                  
 wsrep_cert_deps_distance         1.000000                             
 wsrep_local_state                4                                    
 wsrep_local_state_comment        Synced                               
 wsrep_ist_receive_status                                              
 wsrep_ist_receive_seqno_start    0                                    
 wsrep_ist_receive_seqno_current  0                                    
 wsrep_ist_receive_seqno_end      0                                    
 wsrep_cluster_weight             1                                    
 wsrep_evs_evict_list                                                  
 wsrep_evs_state                  OPERATIONAL                          
 wsrep_cluster_conf_id            1                                    
 wsrep_cluster_size               1                                    
 wsrep_cluster_state_uuid         868f27cb-9b0f-11e9-81ec-ebbaefc4a7ba 
 wsrep_cluster_status             Primary                              
+---------------------------------+--------------------------------------+

5.7 初始化节点2

1
2
3
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql

⚠️ ：需要同样修改 /etc/init.d/mysql 中的basedir和datadir

5.8 启动节点2

1
/etc/init.d/mysql start

5.9 初始化节点3

1
2
3
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user = mysql
cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql

⚠️：需要同样修改 /etc/init.d/mysql 中的basedir和datadir

5.10 启动节点3

1
/etc/init.d/mysql start

5.11 查看查看pxc状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
mysql> show status like 'wsrep%'
    -> ;
+----------------------------------+--------------------------------------------------------+
 Variable_name                     Value                                                  
+----------------------------------+--------------------------------------------------------+
 wsrep_local_state_uuid            b25b348e-9c06-11e9-84f5-ce5173cbcbb4                   
 wsrep_protocol_version            8                                                      
 wsrep_last_applied                54                                                     
 wsrep_last_committed              54                                                     
 wsrep_replicated                  0                                                      
 wsrep_replicated_bytes            0                                                      
 wsrep_repl_keys                   0                                                      
 wsrep_repl_keys_bytes             0                                                      
 wsrep_repl_data_bytes             0                                                      
 wsrep_repl_other_bytes            0                                                      
 wsrep_received                    77                                                     
 wsrep_received_bytes              19487                                                  
 wsrep_local_commits               0                                                      
 wsrep_local_cert_failures         0                                                      
 wsrep_local_replays               0                                                      
 wsrep_local_send_queue            0                                                      
 wsrep_local_send_queue_max        1                                                      
 wsrep_local_send_queue_min        0                                                      
 wsrep_local_send_queue_avg        0.000000                                               
 wsrep_local_recv_queue            0                                                      
 wsrep_local_recv_queue_max        2                                                      
 wsrep_local_recv_queue_min        0                                                      
 wsrep_local_recv_queue_avg        0.012987                                               
 wsrep_local_cached_downto         4                                                      
 wsrep_flow_control_paused_ns      0                                                      
 wsrep_flow_control_paused         0.000000                                               
 wsrep_flow_control_sent           0                                                      
 wsrep_flow_control_recv           0                                                      
 wsrep_flow_control_interval       [ 173, 173 ]                                           
 wsrep_flow_control_interval_low   173                                                    
 wsrep_flow_control_interval_high  173                                                    
 wsrep_flow_control_status         OFF                                                    
 wsrep_cert_deps_distance          15.882353                                              
 wsrep_apply_oooe                  0.000000                                               
 wsrep_apply_oool                  0.000000                                               
 wsrep_apply_window                1.000000                                               
 wsrep_commit_oooe                 0.000000                                               
 wsrep_commit_oool                 0.000000                                               
 wsrep_commit_window               1.000000                                               
 wsrep_local_state                 4                                                      
 wsrep_local_state_comment         Synced                                                 
 wsrep_cert_index_size             6                                                      
 wsrep_cert_bucket_count           58                                                     
 wsrep_gcache_pool_size            18312                                                  
 wsrep_causal_reads                0                                                      
 wsrep_cert_interval               0.000000                                               
 wsrep_ist_receive_status                                                                 
 wsrep_ist_receive_seqno_start     0                                                      
 wsrep_ist_receive_seqno_current   0                                                      
 wsrep_ist_receive_seqno_end       0                                                      
 wsrep_incoming_addresses        192.168.56.101:3306,192.168.56.102:3306,192.168.56.103:3306
 wsrep_desync_count                0                                                      
 wsrep_evs_delayed                                                                        
 wsrep_evs_evict_list                                                                     
 wsrep_evs_repl_latency            0/0/0/0/0                                              
 wsrep_evs_state                   OPERATIONAL                                            
 wsrep_gcomm_uuid                  eaa2ae94-9c61-11e9-8403-1a605ed6ff1d                   
 wsrep_cluster_conf_id             21                                                     
 wsrep_cluster_size                3                                                      
 wsrep_cluster_state_uuid          b25b348e-9c06-11e9-84f5-ce5173cbcbb4                   
 wsrep_cluster_status              Primary                                                
 wsrep_connected                   ON                                                     
 wsrep_local_bf_aborts             0                                                      
 wsrep_local_index                 1                                                      
 wsrep_provider_name               Galera                                                 
 wsrep_provider_vendor             Codership Oy <info@codership.com>                      
 wsrep_provider_version            3.26(r)                                                
 wsrep_ready                       ON                                                     
+----------------------------------+--------------------------------------------------------+
68 rows in set (0.00 sec)

6 添加节点

6.1 环境说明

源pxc集群环境

IP

hostname

用途

base

wsrep_cluster_name

10.30.105.73

mydbt001

pxc01

/usr/local/mysql

pxc_enmo

10.30.104.245

mydbt002

pxc02

/usr/local/mysql

pxc_enmo

新增节点环境

IP

hostname

用途

base

wsrep_cluster_name

10.31.91.204

fengshuo

pxc03

/usr/local/mysql

pxc_enmo

添加节点支持全量和增量，本次使用增量的方式，先搭建一个slave，然后将slave加入到pxc集群。

6.2 参数配置

在pxc原集群节点上修改参数

在 pxc01和pxc02节点上配置参数，并修改参数文件。此参数可以在线修改，立即生效，不需要重启数据库。

1
set global wsrep_cluster_address='gcomm://10.30.105.74,10.30.104.245,10.31.91.204';
1
2
3
mysql> set global wsrep_cluster_address='gcomm://10.30.105.74,10.30.104.245,10.31.91.204';
Query OK, 0 rows affected (8.00 sec)

1
wsrep_cluster_address=gcomm://10.30.105.74,10.30.104.245,10.31.91.204

6.3 搭建SLAVE

搭建slave的时候，模拟数据库有数据插入。

在添加节点之前创建一些临时数据。

1
2
3
4
5
6
7
8
9
10
11
mysql> show create table e;
+-------+------------------------------------------------------------------------------------------------------+
 Table  Create Table                                                                                         
+-------+------------------------------------------------------------------------------------------------------+
 e      CREATE TABLE `e` (
  `i` datetime NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过xtrabackup 备份数据库，在备份的时候模拟pxc集群有插入数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

 2019-07-02 09:42:42 
+---------------------+
23 rows in set (0.00 sec)

mysql> insert into e values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into e values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into e values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into e values(now());
ERROR 1062 (23000): Duplicate entry '2019-07-02 09:42:57' for key 'PRIMARY'
mysql> insert into e values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into e values(now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into e values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into e values(now());
ERROR 1062 (23000): Duplicate entry '2019-07-02 09:43:00' for key 'PRIMARY'
mysql> insert into e values(now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into e values(now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into e values(now());
Query OK, 1 row affected (0.00 sec)

6.3.1 xtrabackup进行备份

备份开始时间是 190702 09:42:52

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[root@mydbt001 pxc]# xtrabackup --defaults-file=/etc/my.cnf -uroot -pPassword123 --backup --target-dir=/pxc/
xtrabackup: recognized server arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT
xtrabackup: recognized client arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --port=3306 --socket=/tmp/mysql.sock --user=root --password=* --backup=1 --target-dir=/pxc/
190702 09:42:52  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
190702 09:42:52  version_check Connected to MySQL server
190702 09:42:52  version_check Executing a version check against the server...
190702 09:42:52  version_check Done.
190702 09:42:52 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 5.7.22-22-29.26-log
xtrabackup version 2.4.14 based on MySQL server 5.7.19 Linux (x86_64) (revision id: ef675d4)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1024M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
190702 09:42:52 >> log scanned up to (2638564)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
190702 09:42:52 [01] Copying ./ibdata1 to /pxc/ibdata1
190702 09:42:53 >> log scanned up to (2638564)

……

备份完成

1
2
3
4
xtrabackup: The latest check point (for incremental): '2647383'
xtrabackup: Stopping log copying thread.
.190702 09:43:11 >> log scanned up to (2647392)

备份完成时间是 190702 09:43:43

6.3.2 传输备份到目标主机

1
scp -r pxc 10.31.91.204:/

6.3.3 目标节点 prepare 数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@fengshuo local]# xtrabackup  --defaults-file=/etc/my.cnf --prepare target-dir=/pxc/
xtrabackup: recognized server arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT
xtrabackup: recognized client arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --port=3306 --socket=/tmp/mysql.sock --prepare=1
xtrabackup: Error: unknown argument: 'target-dir=/pxc/'
[root@fengshuo local]# xtrabackup  --defaults-file=/etc/my.cnf --prepare --target-dir=/pxc/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=268435456 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=268435456 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 --prepare=1 --target-dir=/pxc/
xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /pxc/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2638555)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1024M:autoextend
xtrabackup:   innodb_log_group_home_dir = .

……

1
2
3
4
5
6
7
8
9
10
11
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: page_cleaner: 1000ms intended loop took 7793ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.19 started; log sequence number 2648597
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2648616
190702 10:06:08 completed OK!

6.4 安装 pxc 软件

采用二进制软件包的方式按照，直接复制软件包到 basedir，并创建软连。

1
2
3
cp /usr/local/src/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101.tar.gz /
/usr/local/
ln -s Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101 mysql

6.4.1 授与basedir和datadir权限

1
2
3
mkdir -p /data/mysql
chown -R mysql:mysql  /data/mysql
chown -R mysql:mysql  /usr/local/mysql

6.4.2 将prepare后的数据文件复制到datadir

1
2
mv /pxc/* /data/mysql/
chown -R mysql:mysql /data/mysql

6.4.3 授权并启动数据库服务

1
2
3
4
[root@fengshuo data]# chown  -R mysql:mysql mysql
[root@fengshuo data]# /etc/init.d/mysql  start
Starting MySQL (Percona XtraDB Cluster).Logging to '/data/mysql/error.log'.
. SUCCESS!

登录数据库，查看在备份期间的数据 09:42到9:43 已经存在。

1
2
3
4
5
6
7
8
 2019-07-02 09:42:59 
 2019-07-02 09:43:00 
 2019-07-02 09:43:01 
 2019-07-02 09:43:05 
 2019-07-02 09:43:06 
 2019-07-02 09:43:07 
+---------------------+
33 rows in set (0.01 sec)

6.5 配置slave

6.5.1 找post点

1
2
[root@fengshuo mysql]# cat xtrabackup_binlog_info
mysql-bin.000002    10747

6.5.2 创建复制用户

1
2
3
mysql> create user repl@'%' identified by 'Repl1234';
Query OK, 0 rows affected (0.04 sec)
 grant replication slave, replication client on *.* to repl@'%';

6.5.3 change master

1
2
3
4
5
6
mysql>  CHANGE MASTER TO MASTER_HOST='10.30.105.74',MASTER_USER='repl',MASTER_PASSWORD='Repl1234',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=10747;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

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

6.5.4 查看复制状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Slave_IO_State: Waiting for master to send event
               Master_Host: 10.30.105.74
               Master_User: repl
               Master_Port: 3306
             Connect_Retry: 60
           Master_Log_File: mysql-bin.000002
       Read_Master_Log_Pos: 11208
            Relay_Log_File: fengshuo-relay-bin.000003
             Relay_Log_Pos: 781
     Relay_Master_Log_File: mysql-bin.000002
          Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
           Replicate_Do_DB:
       Replicate_Ignore_DB:

随便插入一些数据。

1
2
3
4
5
6
7
8
9
 2019-07-02 09:43:07 
 2019-07-02 10:21:25 
 2019-07-02 10:21:27 
 2019-07-02 10:21:28 
 2019-07-02 10:21:29 
 2019-07-02 10:21:31 
 2019-07-02 10:21:32 
+---------------------+
39 rows in set (0.00 sec)

6.6 slave 节点转换成pxc节点

slave节点通过 show slave status 找到 Exec_Master_Log_Pos，并stop slave。

1
2
Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 12798

6.6.1 在pxc01节点上通过Exec_Master_Log_Pos 找到 Xid

1
2
3
4
[root@mydbt001 pxc]# mysqlbinlog -vv /data/mysql/mysql-bin.000002 grep 12798
#190702 10:21:32 server id 1003306  end_log_pos 12798 CRC32 0x5be4942f  Xid = 50
# at 12798
[root@mydbt001 pxc]#

6.6.2 pxc01节点添加参数

1
2
3
4
5
6
7
8
9
#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://10.30.105.74,10.30.104.245,10.31.91.204
wsrep_node_address=10.31.91.204
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="pxcuser:Pxcuser123"

6.6.3 创建grastate.dat文件

将 pxc01节点的grastate.dat拷贝新节点，修改seqno为上面对象的 Xid值即 50

1
2
3
4
5
# GALERA saved state
version: 2.1
uuid:    b25b348e-9c06-11e9-84f5-ce5173cbcbb4
seqno:   50
safe_to_bootstrap: 0

6.6.4 启动新节点

1
2
3
4
5
6
7
8
2019-07-03T12:27:34.666497Z 0 [Warning] WSREP: Fail to access the file (/data/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2019-07-03T12:27:34.669345Z 0 [Warning] WSREP: (ef903445, 'tcp://0.0.0.0:4567') address 'tcp://10.31.91.204:4567' points to own listening address, blacklisting
2019-07-03T12:27:35.669742Z 1 [Warning] WSREP: Gap in state sequence. Need state transfer.
        2019-07-03T12:27:36.832833Z WSREP_SST: [INFO] xtrabackup_ist received from donor: Running IST
        2019-07-03T12:27:36.834756Z WSREP_SST: [INFO] Galera co-ords from recovery: b25b348e-9c06-11e9-84f5-ce5173cbcbb4:50
2019-07-03T12:27:37.405886Z 0 [Warning] CA certificate ca.pem is self signed.
2019-07-03T12:27:37.458437Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=fengshuo-relay-bin' to avoid this problem.
2019-07-03T12:27:37.470574Z 0 [Warning] Recovery from master pos 12798 and file mysql-bin.000002 for channel ''. Previous relay log pos and relay log file had been set to 320, ./fengshuo-relay-bin.000005 respectively.

加入集群成功

6.6.5 检查pxc集群状态

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show status like '%wsrep_cluster%'
    -> ;
+--------------------------+--------------------------------------+
 Variable_name             Value                                
+--------------------------+--------------------------------------+
 wsrep_cluster_conf_id     21                                   
 wsrep_cluster_size        3                                    
 wsrep_cluster_state_uuid  b25b348e-9c06-11e9-84f5-ce5173cbcbb4 
 wsrep_cluster_status      Primary                              
+--------------------------+--------------------------------------+
4 rows in set (0.00 sec)

其状态为 Primary。

6.6.6 检查grastate.dat

1
2
3
4
5
6
[root@fengshuo mysql]# cat grastate.dat
# GALERA saved state
version: 2.1
uuid:    b25b348e-9c06-11e9-84f5-ce5173cbcbb4
seqno:   -1
safe_to_bootstrap: 0

seqno 的值变为-1.

6.6.7 清除 SLAVE 信息

检查slave的状态，仍然有slave的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.30.105.74
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 12798
               Relay_Log_File: fengshuo-relay-bin.000006
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 12798
              Relay_Log_Space: 1767
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: 8a79a560-9c06-11e9-b48f-00163e064c24
             Master_Info_File: /u01/data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

这里手动测试start slave，会报主键冲突，因为数据增量已经同步过来了。

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

可以看到 Exec_Master_Log_Pos: 12798 仍然是 在 12798的位置。所以slave的信息已经没有用处了，进行slave信息清除。

1
2
3
4
5
6
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)