MySQL PXC 高可用集群搭建

一些名词介绍： WS：write set 写数据集
IST: Incremental State Transfer 增量同步
SST：State Snapshot Transfer 全量同步 pxc环境所涉及的端口： #mysql实例端口 1. Regular MySQL port, default 3306.   #pxc cluster相互通讯的端口
2. Port for group communication, default 4567. It can be changed by the option:
wsrep_provider_options ="gmcast.listen_addr=tcp://0.0.0.0:4010; " #用于SST传送的端口
3. Port for State Transfer, default 4444. It can be changed by the option:
wsrep_sst_receive_address=10.11.12.205:5555 #用于IST传送的端口
4. Port for Incremental State Transfer, default port for group communication + 1 （4568）. It can be changed by the option:
wsrep_provider_options = "ist.recv_addr=10.11.12.206:7777; "搭建步骤：一、环境情况： node1:192.168.1.81:6000 node2:192.168.1.240:6000 node3:192.168.1.241:6000 二、防火墙设置：-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #本实验需要改为6000-A INPUT -m state --state NEW -m tcp -p tcp --dport 4444 -j ACCEPT #本实验需要改为6020-A INPUT -m state --state NEW -m tcp -p tcp --dport 4567 -j ACCEPT #本实验需要改为6030-A INPUT -m state --state NEW -m tcp -p tcp --dport 4568 -j ACCEPT #本实验需要改为6031
setenforce 0 二、下载安装，在node1,node2,node3分别进行如下安装 yum install -yhttp://www.percona.com/downloads/percona-release/RedHat/0.1-3/percona-release-0.1-3.noarch.rpmyum install -y perl-DBD-MySQL.x86_64 perl-IO-Socket-SSL.noarch socat.x86_64 ncyum install -yopenssl openssl-devel#step 1 prepare :5.6.26 needs libssl.so.6 libcrypto.so.6 （）cd /usr/lib64ln -s libssl.so.1.0.1e libssl.so.6ln -slibcrypto.so.1.0.1e libcrypto.so.6#step2:download pxccd /usr/local/srcwget https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.26-25.12/binary/tarball/Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gzcd /usr/localmv Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz pxc_56#step3:install pxccd /usr/local/pxc_56
./scripts/mysql_install_db --defaults-file=/home/mysql/my6000.cnf --basedir=/usr/local/pxc_56 chown -R mysql. /home/mysql chown -R mysql. /usr/local/pxc_56   配置文件：my6000.cnf [client]port= 6000socket= /home/mysql/I6000/mysql.sockdefault-character-set=utf8[mysqld]basedir = /usr/local/pxc_56datadir = /home/mysql/I6000pid-file= /home/mysql/I6000/mysql.pidcharacter-set-server=utf8init_connect = "SET NAMES utf8"log-bin=/home/mysql/I6000/log_binserver-id = 9816000innodb_buffer_pool_size = 100Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_data_home_dir = /home/mysql/I6000innodb_file_per_table=1skip-name-resolveport= 6000socket= /home/mysql/I6000/mysql.sockuser=mysqllog_error=/home/mysql/I6000/mysql_error.log#pxcuser=mysqllog_error=error.logbinlog_format=ROW#6000 for mysqld port, 6020 for sst port, 6031 for ist port, 6030 for cluster communication port 端口作用及说明见上面wsrep_cluster_address="gcomm://192.168.1.81:6030,192.168.1.238:6030,192.168.1.240:6030" #三个节点都保持一样wsrep_provider=/usr/local/pxc_56/lib/libgalera_smm.sowsrep_sst_receive_address=192.168.1.238:6020#根据不同的node ip进行修改wsrep_node_incoming_address=192.168.1.238:6000#根据不同的node ip进行修改wsrep_node_address=192.168.1.238:6000#根据不同的node ip进行修改wsrep_provider_options = "gmcast.listen_addr=tcp://192.168.1.238:6030;ist.recv_addr=192.168.1.238:6031;"#根据不同的node ip进行修改wsrep_slave_threads=2wsrep_cluster_name=pxc_zejin #同一个cluster此值需要一致wsrep_sst_method=xtrabackup-v2wsrep_node_name=node6000_238#不同节点修改为不同的名字innodb_autoinc_lock_mode=2wsrep_sst_auth="sstuser:123"log-slave-updates[mysql]no-auto-rehashdefault-character-set=utf8 三、实例启动：#安装实例/usr/local/pxc_56/scripts/mysql_install_db --defaults-file=/home/mysql/my6000.cnf --basedir=/usr/local/pxc_56#启动第一个实例/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/my6000.cnf --ledir=/usr/local/pxc_56/bin/ --wsrep-new-cluster& #修改密码/usr/local/pxc_56/bin/mysqladmin -u root -P6000 password "000000" -S /home/mysql/I6000/mysql.sock#登陆并增加用户/usr/local/pxc_56/bin/mysql -uroot -p -P6000 -S /home/mysql/I6000/mysql.sock grant all on *.* to "sstuser"@"localhost" identified by "123";flush privielges;  mysql> show global status like "wsrep_cluster_size";+--------------------+-------+| Variable_name| Value |+--------------------+-------+| wsrep_cluster_size | 1 |+--------------------+-------+1 row in set （0.00 sec）四、启动第二、三个node #不需要加--wsrep-new-cluster/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/I6000/my6000.cnf --ledir=/usr/local/pxc_56/bin/ &

整个过程大约如下： 1.决断是否可以IST 2.不可以的话Requesting state transfer 3.Cleaning the existing datadir and innodb-data/log directories 会将之前实例的内容全部删除。#所以不能将my6000.cnf文件放在与datadir目录下，要不会被删除，并且在重新启动服务时出错 4.Waiting for SST streaming to complete 5.启动服务。   官方说明： XtraBackup SST works in two stages:
• Stage I on joiner checks if it is SST or IST based on presence of xtrabackup_ist file.
• In Stage II it starts the data transfer, if it’s SST, it empties the data directory sans few files （galera.cache, sst_in_progress, grastate.dat） and then proceed with the SST or if it’s IST, proceeds as before.加入成功：07:49:13 root@127.0.0.1:[test] 6000>show global status like "wsrep_cluster_size";+--------------------+-------+| Variable_name| Value |+--------------------+-------+| wsrep_cluster_size | 3 |+--------------------+-------+1 row in set （0.00 sec）五、操作过程中经常用到的命令：安装：/usr/local/pxc_56/scripts/mysql_install_db --defaults-file=/home/mysql/my6000.cnf --basedir=/usr/local/pxc_56启动：/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/my6000.cnf --ledir=/usr/local/pxc_56/bin/ &（第一个节点要加上--wsrep-new-cluster ）登陆：/usr/local/pxc_56/bin/mysql -uroot -p -P6000 -S /home/mysql/I6000/mysql.sock 关闭：/usr/local/pxc_56/bin/mysqladmin -uroot -p -P6000 -S /home/mysql/I6000/mysql.sockshutdown 六、安装过程中可能遇到的问题： A.节点启动时：/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/my3307.cnf --ledir=/usr/local/pxc_56/bin/ &有可能启动会提示：WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/usr/local/pxc_56//bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/opt/lamp/mysql/bin:/root/bin （20160509 10:58:04.404）2016-05-09 10:58:04 54809 [ERROR] WSREP: Failed to read "ready <addr>" from: wsrep_sst_xtrabackup-v2 --role "joiner" --address "192.168.1.238" --datadir "/home/mysql/I3307/" --defaults-file "/home/mysql/I3307/my3307.cnf" --defaults-group-suffix "" --parent "54809""" Read: "（null）"2016-05-09 10:58:04 54809 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role "joiner" --address "192.168.1.238" --datadir "/home/mysql/I3307/" --defaults-file "/home/mysql/I3307/my3307.cnf" --defaults-group-suffix "" --parent "54809""" : 2 （No such file or directory）2016-05-09 10:58:04 54809 [ERROR] WSREP: Failed to prepare for "xtrabackup-v2" SST. Unrecoverable.2016-05-09 10:58:04 54809 [ERROR] Aborting原因及解决方法：socat 没有安装到，之前源包没有，可以进行如下安装：wget --no-cache http://www.convirture.com/repos/definitions/rhel/6.x/convirt.repo -O /etc/yum.repos.d/convirt.repoyum install socat B.第一个节点如果直接这样启动：
/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/my6000.cnf &在errorlog里面会出现：2016-05-09 09:49:46 9932 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 （Connection timed out） at gcomm/src/pc.cpp:connect（）:1622016-05-09 09:49:46 9932 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open（）:206: Failed to open backend connection: -110 （Connection timed out）2016-05-09 09:49:46 9932 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open（）:1379: Failed to open channel "pxc_zejin" at "gcomm://192.168.1.240,192.168.1.238,192.168.1.81": -110 （Connection timed out）2016-05-09 09:49:46 9932 [ERROR] WSREP: gcs connect failed: Connection timed out2016-05-09 09:49:46 9932 [ERROR] WSREP: wsrep::connect（gcomm://192.168.1.240,192.168.1.238,192.168.1.81） failed: 72016-05-09 09:49:46 9932 [ERROR] Aborting2016-05-09 09:49:46 9932 [Note] WSREP: Service disconnected.2016-05-09 09:49:47 9932 [Note] WSREP: Some threads may fail to exit.2016-05-09 09:49:47 9932 [Note] Binlog end2016-05-09 09:49:47 9932 [Note] /usr/local/pxc_56/bin/mysqld: Shutdown complete解决方法：第一个节点启动要加上：--wsrep-new-cluster /usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/my6000.cnf --ledir=/usr/local/pxc_56/bin --wsrep-new-cluster  &  C.#第二个节点或第三个节点启动报错：WSREP_SST: [ERROR] Cleanup after exit with status:1 （20160509 19:25:50.483）2016-05-09 19:25:50 44334 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role "joiner" --address "192.168.1.240:6020" --datadir "/home/mysql/I6000/" --defaults-file "/home/mysql/I6000/my6000.cnf" --defaults-group-suffix "" --parent "44334""" : 1 （Operation not permitted）2016-05-09 19:25:50 44334 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.2016-05-09 19:25:50 44334 [ERROR] WSREP: SST script aborted with error 1 （Operation not permitted）2016-05-09 19:25:50 44334 [ERROR] WSREP: SST failed: 1 （Operation not permitted）2016-05-09 19:25:50 44334 [ERROR] Aborting原因及解决方法：配置文件my6000.cnf放在与datadir目录下，在Cleaning the existing datadir阶段被删除，在重启服务时就会找不到配置文件，在重新启动服务就会出错，把配置文件放在非datadir目录下即可。如/etc/my6000.cnfPercona XtraDB Cluster 的详细介绍：请点这里
Percona XtraDB Cluster 的下载地址：请点这里本文永久更新链接地址