CentOS6环境下部署Percona XtraDB Cluster

23rfe

主机规划:

1 2 3 4 5

192.168.0.221     os6—221 192.168.0.222     os6—222 192.168.0.223     os6—223 192.168.0.224     os6—224  haproxy 192.168.0.225     os6—225  haproxy

安装脚本:

1 2 3 4 5 6 7 8 9 10 11 12

#!/bin/bash sed  -i "s/SELINUX=enforcing/SELINUX=disabled/g"   /etc/selinux/config setenforce 0 service iptables  start iptables  -F iptables  -A  INPUT -p  tcp -i eth0  --dport 3306 -m state --state NEW,ESTABLISHED -j   ACCEPT iptables  -A  INPUT -p  tcp -i eth0  --dport 4444 -m state --state NEW,ESTABLISHED -j   ACCEPT iptables  -A  INPUT -p  tcp -i eth0  --dport 4567 -m state --state NEW,ESTABLISHED -j   ACCEPT iptables  -A  INPUT -p  tcp -i eth0  --dport 4568 -m state --state NEW,ESTABLISHED -j   ACCEPT yum  install  ntp  -y ntpdate cn.pool.ntp.org echo "*/1 * * * * root  ntpdate cn.pool.ntp.org"   >> /etc/crontab

安装percona之前,必须删除mysql-libs,这个包和percona冲突.

1	yum remove   mysql-libs  -y

1 2	rpm  -ivh  http://dl.fedoraproject.org/pub/ ... ease-6-8.noarch.rpm yum  install socat  -y

1 2	yum install http://www.percona.com/downloads ... elease-0.1-3.noarch  -y yum install Percona-XtraDB-Cluster-56  -y

配置文件:/etc/my.cnf
引导服务器配置,即os6---221

1 2 3 4 5 6 7 8 9 10 11

[mysqld] #wsrep wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_cluster_address=gcomm://os6---221,os6-222,os6---223 binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_node_address=os6---221 wsrep_sst_method=xtrabackup-v2 wsrep_cluster_name=singulax wsrep_sst_auth=“wsrep:wsrep”

启动os6—221数据库

1	/etc/init.d/myslq  bootstrap-pxc

创建replication用户(此步骤只需要在init服务器上执行,启动其他服务器时会自动复制.请参考PXC复制原理)

1 2	GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO  wsrep@'localhost'  IDENTIFIED BY ‘wsrep'; FLUSH PRIVILEGES;

os6---221服务器

1 2 3 4 5 6 7 8 9 10 11

[mysqld] #wsrep wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_cluster_address=gcomm://os6---221,os6-222,os6---223 binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_node_address=os6---222 wsrep_sst_method=xtrabackup-v2 wsrep_cluster_name=singulax wsrep_sst_auth=“wsrep:wsrep”

启动os6—222数据库

1	/etc/init.d/mysql  start  ||   service  mysql start

os6---223服务器

1 2 3 4 5 6 7 8 9 10 11

[mysqld] #wsrep wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_cluster_address=gcomm://os6---221,os6-222,os6---223 binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 wsrep_node_address=os6---223 wsrep_sst_method=xtrabackup-v2 wsrep_cluster_name=singulax wsrep_sst_auth=“wsrep:wsrep”

启动os6—223数据库

1	/etc/init.d/mysql  start  ||   service  mysql start

pxc安装完毕后,会自动clustercheck命令和9200端口检测mysql服务的正常.

用法:

1	/usr/bin/clustercheck <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>

添加检测用户:

1	grant  process  on *.*   to  clustercheckuser@'localhost'  identified by ‘clustercheckuser';

检测返回结果:200  或者  503.例如

1 2 3 4 5 6

clustercheck     clustercheckuser       clustercheckuser HTTP/1.1 200 OK Content-Type: text/plain Connection: close Content-Length: 40 Percona XtraDB Cluster Node is synced.

9200端口:先安装xinetd,利用xinetd生成9200端口.

1	yum  install  xinetd   -y

xinetd安装完毕后,后生成mysqlchk文件,并配置

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

# default: on # description: mysqlchk service mysqlchk { # this is a config for xinetd, place it in /etc/xinetd.d/         disable = no         flags           = REUSE         socket_type     = stream         type            = UNLISTED         port            = 9200         wait            = no         user            = nobody         server          = /usr/bin/clustercheck         log_on_failure  += USERID         only_from       = 0.0.0.0/0         #         # Passing arguments to clustercheck         # <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"         # Recommended: server_args   = user pass 1 /var/log/log-file 0 /etc/my.cnf.local"         # Compatibility: server_args = user pass 1 /var/log/log-file 1 /etc/my.cnf.local"         # 55-to-56 upgrade: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.extra"         server_args = clustercheckuser  clustercheckuser         # recommended to put the IPs that need         # to connect exclusively (security purposes)         per_source      = UNLIMITED }

然后,在/etc/services为9200端口添加新服务

1	mysqlchk 9200/tcp # mysqlchk

启动xinetd

1 2	service  xinetd  start chkconfig  xinetd  on

Haproxy负载均衡和检测
安装haproxy

1	yum  install haproxy   -y   #centos6官方自带安装包,直接yum安装即可

配置防火墙

1 2 3

iptables   -A INPUT  -p  tcp --dport 8080 -m  state  --state  NEW,ESTABLISHED  -j  ACCEPT iptables   -A INPUT  -p  tcp --dport 3306 -m  state  --state  NEW,ESTABLISHED  -j  ACCEPT iptables   -A INPUT  -p  tcp --dport 3307 -m  state  --state  NEW,ESTABLISHED  -j  ACCEPT

配置文件:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

frontend stats-front bind *:8080 mode http default_backend stats-back frontend pxc-front bind *:3307 mode tcp default_backend pxc-back frontend pxc-onenode-front bind *:3308 mode tcp default_backend pxc-onenode-back backend stats-back mode http balance roundrobin stats uri /haproxy/stats stats auth haproxy:haproxy backend pxc-back mode tcp balance leastconn option httpchk server c1 192.168.115.221:3306 check port 9200 inter 12000 rise 3 fall 3 server c2 192.168.115.222:3306 check port 9200 inter 12000 rise 3 fall 3 server c3 192.168.115.223:3306 check port 9200 inter 12000 rise 3 fall 3 backend pxc-onenode-back mode tcp balance leastconn option httpchk server c1 192.168.115.221:3306 check port 9200 inter 12000 rise 3 fall 3 server c2 192.168.115.222:3306 check port 9200 inter 12000 rise 3 fall 3 backup server c3 192.168.115.223:3306 check port 9200 inter 12000 rise 3 fall 3 backup

启动

1 2	service haproxy  start service haproxy  stop

访问:

1	http://192.168.115.224:8080/haproxy/stats