Deploy Percona XtraDB Cluster in CentOS6 Environment

Deploy Percona XtraDB Cluster in CentOS6 Environment

Host planning:

192.168.0.221os6—221192.168.0.222os6—222192.168.0.223os6—223192.168.0.224os6—224haproxy192.168.0.225os6—225haproxy

Installation script:

#!/bin/bashsed-i"s/SELINUX=enforcing/SELINUX=disabled/g"/etc/selinux/configsetenforce0serviceiptablesstartiptables-Fiptables-AINPUT-ptcp-ieth0--dport3306-mstate--stateNEW,ESTABLISHED-jACCEPTiptables-AINPUT-ptcp-ieth0--dport4444-mstate--stateNEW,ESTABLISHED-jACCEPTiptables-AINPUT-ptcp-ieth0--dport4567-mstate--stateNEW,ESTABLISHED-jACCEPTiptables-AINPUT-ptcp-ieth0--dport4568-mstate--stateNEW,ESTABLISHED-jACCEPTyuminstallntp-yntpdatecn.pool.ntp.orgecho"*/1****rootntpdatecn.pool.ntp.org">>/etc/crontab

Before installing percona, you must delete mysql-libs. This package conflicts with percona.

yumremovemysql-libs-yrpm-ivhhttp://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpmyuminstallsocat-yyuminstallhttp://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch-yyuminstallPercona-XtraDB-Cluster-56-y

Configuration File:/etc/my. cnf

Boot server configuration, that is, os6 --- 221

[mysqld]#wsrepwsrep_provider=/usr/lib64/libgalera_smm.sowsrep_cluster_address=gcomm://os6---221,os6-222,os6---223binlog_format=ROWdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2wsrep_node_address=os6---221wsrep_sst_method=xtrabackup-v2wsrep_cluster_name=singulaxwsrep_sst_auth=“wsrep:wsrep”

Start os6-221 Database

/etc/init.d/myslqbootstrap-pxc

Create a replication user (this step only needs to be performed on the init server and will be automatically copied when other servers are started. See PXC replication Principles)

GRANTRELOAD,LOCKTABLES,REPLICATIONCLIENTON*.*TOwsrep@'localhost'IDENTIFIEDBY‘wsrep';FLUSHPRIVILEGES;

Os6 --- 221 Server

[mysqld]#wsrepwsrep_provider=/usr/lib64/libgalera_smm.sowsrep_cluster_address=gcomm://os6---221,os6-222,os6---223binlog_format=ROWdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2wsrep_node_address=os6---222wsrep_sst_method=xtrabackup-v2wsrep_cluster_name=singulaxwsrep_sst_auth=“wsrep:wsrep”

Start os6-222 Database

/etc/init.d/mysqlstart||servicemysqlstart

Os6 --- 223 Server

[mysqld]#wsrepwsrep_provider=/usr/lib64/libgalera_smm.sowsrep_cluster_address=gcomm://os6---221,os6-222,os6---223binlog_format=ROWdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2wsrep_node_address=os6---223wsrep_sst_method=xtrabackup-v2wsrep_cluster_name=singulaxwsrep_sst_auth=“wsrep:wsrep”

Start os6-223 Database

/etc/init.d/mysqlstart||servicemysqlstart

After pxc is installed, the clustercheck command and port 9200 are automatically executed to check whether the mysql service is normal.

Usage:

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

Add detection User:

grantprocesson*.*toclustercheckuser@'localhost'identifiedby‘clustercheckuser';

Check Result: 200 or 503. For example:

clustercheckclustercheckuserclustercheckuserHTTP/1.1200OKContent-Type:text/plainConnection:closeContent-Length:40PerconaXtraDBClusterNodeissynced.

Port 9200: Install xinetd and use xinetd to generate port 9200.

yuminstallxinetd-y

After xinetd is installed, the mysqlchk file is generated and configured.

#default:on#description:mysqlchkservicemysqlchk{#thisisaconfigforxinetd,placeitin/etc/xinetd.d/disable=noflags=REUSEsocket_type=streamtype=UNLISTEDport=9200wait=nouser=nobodyserver=/usr/bin/clusterchecklog_on_failure+=USERIDonly_from=0.0.0.0/0##Passingargumentstoclustercheck#<user><pass><available_when_donor=0|1><log_file><available_when_readonly=0|1><defaults_extra_file>"#Recommended:server_args=userpass1/var/log/log-file0/etc/my.cnf.local"#Compatibility:server_args=userpass1/var/log/log-file1/etc/my.cnf.local"#55-to-56upgrade:server_args=userpass1/var/log/log-file0/etc/my.cnf.extra"server_args=clustercheckuserclustercheckuser#recommendedtoputtheIPsthatneed#toconnectexclusively(securitypurposes)per_source=UNLIMITED}

Then, add a new service for port 9200 in/etc/services.

mysqlchk9200/tcp#mysqlchk

Start xinetd

servicexinetdstartchkconfigxinetdon

Haproxy load balancing and Detection

Install haproxy

yuminstallhaproxy-y# Centos6 official built-in installation package, directly install yum

Configure Firewall

iptables-AINPUT-ptcp--dport8080-mstate--stateNEW,ESTABLISHED-jACCEPTiptables-AINPUT-ptcp--dport3306-mstate--stateNEW,ESTABLISHED-jACCEPTiptables-AINPUT-ptcp--dport3307-mstate--stateNEW,ESTABLISHED-jACCEPT

Configuration file:

frontendstats-frontbind*:8080modehttpdefault_backendstats-backfrontendpxc-frontbind*:3307modetcpdefault_backendpxc-backfrontendpxc-onenode-frontbind*:3308modetcpdefault_backendpxc-onenode-backbackendstats-backmodehttpbalanceroundrobinstatsuri/haproxy/statsstatsauthhaproxy:haproxybackendpxc-backmodetcpbalanceleastconnoptionhttpchkserverc1192.168.115.221:3306checkport9200inter12000rise3fall3serverc2192.168.115.222:3306checkport9200inter12000rise3fall3serverc3192.168.115.223:3306checkport9200inter12000rise3fall3backendpxc-onenode-backmodetcpbalanceleastconnoptionhttpchkserverc1192.168.115.221:3306checkport9200inter12000rise3fall3serverc2192.168.115.222:3306checkport9200inter12000rise3fall3backupserverc3192.168.115.223:3306checkport9200inter12000rise3fall3backup

Start

servicehaproxystartservicehaproxystop

Access:

http://192.168.115.224:8080/haproxy/stats