pxc

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 获取二进制软件包

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 安装依赖软件

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 防火墙关闭或者开放端口

service iptables stop

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

vi /etc/sysconfig/iptables

-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

临时关闭

setenforce 0

永久关闭:

vi /etc/sysconfig/selinux
SELINUX=disabled

确认已经关闭

[root@mydbt001 local]# getenforce
Disabled

3.4 创建MySQL 用户

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

4 安装Percona-XtraDB-Cluster软件

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

以下在3个节点都执行

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 创建软连

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

4.3 授与basedir和datadir权限

mkdir -p /data/mysql
chown -R mysql:mysql  /data/mysql
chown -R mysql:mysql  /usr/local/mysql

5 搭建pxc环境

5.1 配置参数文件

pxc01

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

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

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

cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
[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
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 选项。

[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 修改密码

[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
[root@cet701 data]# mysqladmin -uroot -p password        
Enter password: 
New password: 
Confirm new password: 

新密码为:Password123

5.5 创建sst用户

CREATE USER 'pxcuser'@'localhost' IDENTIFIED BY 'Pxcuser123';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO'pxcuser'@'localhost';
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状态

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

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

/etc/init.d/mysql start

5.9 初始化节点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

/etc/init.d/mysql start

5.11 查看查看pxc状态

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节点上配置参数,并修改参数文件。此参数可以在线修改,立即生效,不需要重启数据库。

 set global wsrep_cluster_address='gcomm://10.30.105.74,10.30.104.245,10.31.91.204';
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)
wsrep_cluster_address=gcomm://10.30.105.74,10.30.104.245,10.31.91.204

6.3 搭建SLAVE

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

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

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集群有插入数据。


    | 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

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

……

备份完成

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 传输备份到目标主机

scp -r pxc 10.31.91.204:/

6.3.3 目标节点 prepare 数据

[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 = .

……

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,并创建软连。

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权限

mkdir -p /data/mysql
chown -R mysql:mysql  /data/mysql
chown -R mysql:mysql  /usr/local/mysql

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

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

6.4.3 授权并启动数据库服务

[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 已经存在。

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

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

6.5.2 创建复制用户

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

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 查看复制状态

   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:

随便插入一些数据。

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

          Master_Log_File: mysql-bin.000002
          Exec_Master_Log_Pos: 12798

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

[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节点添加参数

#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

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

6.6.4 启动新节点

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集群状态

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

[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的信息。

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,会报主键冲突,因为数据增量已经同步过来了。

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信息清除。

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)