[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 versionsssl101: for CentOS 6 and CentOS 7ssl102: 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.gz3.安装前的准备
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++ -y3.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 reload3.3 关闭selinux
临时关闭
setenforce 0永久关闭：
vi /etc/sysconfig/selinux
SELINUX=disabled确认已经关闭
[root@mydbt001 local]# getenforce
Disabled3.4 创建MySQL 用户
groupadd mysql
useradd -r -g mysql -s /bin/false mysql4 安装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 mysql4.3 授与basedir和datadir权限
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /usr/local/mysql5 搭建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:Password123pxc02
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:Password123pxc03
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:Password1235.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=mysqlcp /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 mysql5.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 start5.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 start5.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.2046.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 mysql6.4.1 授与basedir和datadir权限
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql
chown -R mysql:mysql /usr/local/mysql6.4.2 将prepare后的数据文件复制到datadir
mv /pxc/* /data/mysql/
chown -R mysql:mysql /data/mysql6.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 107476.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\G6.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: 127986.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: 06.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: 0seqno 的值变为-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)