[TOC]

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

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.log|grep 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
4
5
6
mysql> CREATE USER 'pxcuser'@'localhost' IDENTIFIED BY 'Pxcuser123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO'pxcuser'@'localhost';
Query OK, 0 rows affected (0.09 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
4
5
6
7
8
9
10
11
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)

mysql> show variables like '%wsrep_cluster_address%'
    -> ;
+-----------------------+-------------------------------------------------+
| Variable_name         | Value                                           |
+-----------------------+-------------------------------------------------+
| wsrep_cluster_address | gcomm://10.30.105.74,10.30.104.245,10.31.91.204 |
+-----------------------+-------------------------------------------------+
1 row in set (0.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
12
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)

mysql>

通过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
37
38

| 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)

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
5
6
7
8
9
10
11
12
13
14
15
16
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:11 Executing UNLOCK TABLES
190702 09:43:11 All tables unlocked
190702 09:43:11 [00] Copying ib_buffer_pool to /pxc/ib_buffer_pool
190702 09:43:11 [00]        ...done
190702 09:43:11 Backup created in directory '/pxc/'
190702 09:43:11 [00] Writing /pxc/backup-my.cnf
190702 09:43:11 [00]        ...done
190702 09:43:11 [00] Writing /pxc/xtrabackup_info
190702 09:43:11 [00]        ...done
xtrabackup: Transaction log of lsn (2638555) to (2647392) was copied.
190702 09:43:11 completed OK!
[root@mydbt001 pxc]#

备份完成时间是 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
9
| 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
7
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)

mysql> show slave status\G

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
13
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)

mysql>

其状态为 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
61
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
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
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: 10.30.105.74
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 13858
               Relay_Log_File: fengshuo-relay-bin.000007
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            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: 1062
                   Last_Error: Could not execute Write_rows event on table pxc.e; Duplicate entry '2019-07-02 10:38:45' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 13032
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 12798
              Relay_Log_Space: 1590
              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: 1062
               Last_SQL_Error: Could not execute Write_rows event on table pxc.e; Duplicate entry '2019-07-02 10:38:45' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 13032
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1003306
                  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: 190704 05:17:32
               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)

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

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

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

mysql> show slave status\G
Empty set (0.00 sec)

在线添加节点完成。

原文作者: liups.com

原文链接: http://liups.cn/posts/9a9d8ec7/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议