PXC在线增加从库

一.基本信息
现有pxc三节点：
集群名：pcx_zengxw
节点一：10.16.24.107
节点二：10.16.24.108
节点三：10.16.24.109
在节点三下增加一个从库：
节点四: 10.16.24.106(从库)

二.软件下载
由于从库可能随时提升为pxc节点，软件安装我们按照安装pxc节点一样的做法。
Percona‐Xtradb‐Cluster 5.6 版本下载
wget
http://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraD-Cluster-5.6.21-25.8/binary/tarball/Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64.tar.gz

三.基本包安装和配置
yum install openssl openssl-devel
yum install -y perl-DBD-MySQL.x86_64 perl-IO-Socket-SSL.noarch socat.x86_64 nc
yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install -y http://apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
yum install -y https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.3/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.3-1.el6.x86_64.rpm
mkdir -p /opt/mysql
cd /data/software
tar -zxvf Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64.tar.gz -C /opt/mysql

cd /usr/local
ln -s /opt/mysql/Percona-XtraDB-Cluster-5.6.21-rel70.1-25.8.938.Linux.x86_64 mysql

建立目录如下：
mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}

关闭防火墙和selinux:
/etc/selinux/config中：SELINUX=disabled，并重启OS
service iptables stop ，并chkconfig iptables off

从其它pxc主机将已准备好的my.cnf文件copy到/etc/下，并修改3376为3306:
scp /etc/my.cnf 10.16.24.106:/etc/

并调整my.cnf的参数：
server-id       =1063306

创建mysql的用户及组
groupadd mysql
useradd -g mysql -s /bin/bash -d /usr/local/mysql mysql

创建启动文件：
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

chown -R mysql:mysql /data/mysql/mysql_3306

ln -sf /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6

ln -sf /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6

vi /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

source /etc/profile

四.my.cnf配置和mysql初始化
在/etc/my.cnf文件中标识与wsrep相关的参数:
#percona xtradb cluster
default_storage_engine=InnoDB
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
wsrep_provider_options="gcache.size=1999M"
#wsrep_cluster_name='pcx_zengxw'
#wsrep_cluster_address="gcomm://10.16.24.107,10.16.24.108,10.16.24.109"
#wsrep_node_address="10.16.24.106"
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
#wsrep_sst_method=rsync
#wsrep_sst_method=xtrabackup-v2
#wsrep_sst_auth=sst:zengxw1

初始化mysql:
cd /usr/local/mysql
./scripts/mysql_install_db

启动：
/etc/init.d/mysql start

五.导出mysql
10.16.24.109:
cd /data/mysql/mysql_3306
mysqldump -uroot -psafe2016 --master-data=2 --single-transaction -A >20160626.sql
scp 20160626.sql root@10.16.24.106:/data/mysql/mysql_3306
grant replication slave on *.* to repl@'%' identified by 'replsave';

六.导入mysql
cd /data/mysql/mysql_3306
mysql -uroot <20160626.sql
查看是否数据有导入成功:
(product)root@localhost [demo]> select * from demo1;
+----+-----+
| ID | OID |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
|  5 |   5 |
|  6 |   6 |
|  7 |   8 |
+----+-----+
7 rows in set (0.00 sec)

七.建立主从复制关系

more 20160626.sql，查看binlog file和position信息：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=523;

根据上面编写建立复制的语句：
CHANGE MASTER TO MASTER_HOST='10.16.24.109',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='replsave',MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=523;

启动slave:star slave

检查同步：show slave status\G

八.在pxc其它节点插入数据，验证是否同步

pxc节点10.16.24.109:
 insert into demo1 select 8,8;
从库10.16.24.106:
select * from demo1;
+----+-----+
| ID | OID |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
|  5 |   5 |
|  6 |   6 |
|  7 |   8 |
|  8 |   8 |
+----+-----+
8 rows in set (0.00 sec)

pxc节点10.16.24.108:
 insert into demo1 select 9,9;

从库10.16.24.106:
select * from demo1;
+----+-----+
| ID | OID |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
|  5 |   5 |
|  6 |   6 |
|  7 |   8 |
|  8 |   8 |
|  9 |   9 |
+----+-----+
9 rows in set (0.00 sec)

pxc节点10.16.24.107:
 insert into demo1 select 10,10;
从库10.16.24.106:
select * from demo1;
+----+-----+
| ID | OID |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
|  5 |   5 |
|  6 |   6 |
|  7 |   8 |
|  8 |   8 |
|  9 |   9 |
| 10 |  10 |
+----+-----+
10 rows in set (0.00 sec)

确认pxc及从库之间是同步的。