阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL PXC 高可用集群搭建

205次阅读
没有评论

共计 8402 个字符,预计需要花费 22 分钟才能阅读完成。

一些名词介绍:

    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 -y  http://www.percona.com/downloads/percona-release/RedHat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install -y perl-DBD-MySQL.x86_64 perl-IO-Socket-SSL.noarch socat.x86_64 nc
yum install -y  openssl openssl-devel

#step 1 prepare :5.6.26 needs libssl.so.6 libcrypto.so.6 ()
cd /usr/lib64
ln -s libssl.so.1.0.1e libssl.so.6
ln -s  libcrypto.so.1.0.1e libcrypto.so.6

#step2:download pxc
cd /usr/local/src
wget 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.gz
cd /usr/local
mv Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz pxc_56

#step3:install pxc
cd /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            = 6000
socket          = /home/mysql/I6000/mysql.sock
default-character-set=utf8

[mysqld]
basedir = /usr/local/pxc_56
datadir = /home/mysql/I6000

pid-file        = /home/mysql/I6000/mysql.pid
character-set-server=utf8
init_connect = 'SET NAMES utf8'
log-bin=/home/mysql/I6000/log_bin
server-id = 9816000

innodb_buffer_pool_size = 100M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir = /home/mysql/I6000
innodb_file_per_table=1


skip-name-resolve


port            = 6000
socket          = /home/mysql/I6000/mysql.sock
user=mysql
log_error=/home/mysql/I6000/mysql_error.log

#pxc
user=mysql
log_error=error.log
binlog_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.so
wsrep_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=2
wsrep_cluster_name=pxc_zejin   #同一个 cluster 此值需要一致
wsrep_sst_method=xtrabackup-v2
wsrep_node_name=node6000_238  #不同节点修改为不同的名字
innodb_autoinc_lock_mode=2
wsrep_sst_auth="sstuser:123"
log-slave-updates


[mysql]
no-auto-rehash
default-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.sock  shutdown
 
六、安装过程中可能遇到的问题:
 
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/bi
n (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/' --de
faults-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.repo
yum 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():162 2016-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 (Con nection timed out) 2016-05-09 09:49:46 9932 [ERROR] WSREP: gcs connect failed: Connection timed out 2016-05-09 09:49:46 9932 [ERROR] WSREP: wsrep::connect(gcomm://192.168.1.240,192.168.1.238,192.168.1.81) failed: 7 2016-05-09 09:49:46 9932 [ERROR] Aborting 2016-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 end 2016-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.cnf

Percona XtraDB Cluster 的详细介绍 :请点这里
Percona XtraDB Cluster 的下载地址 :请点这里

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-06/132701.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计8402字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中