mysql中如何快速搭建PXC集群以及Mycat分片，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

参考percona官方文档：

https://www.percona.com/doc/percona-xtradb-cluster/5.7/index.html

https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/

另外单独下载一个软件包：qpress-11-1.el7.x86_64.rpm

yum-yremovemari*1.3 开放端口

firewall-cmd--zone=public--add-port=3306/tcp--permanentfirewall-cmd--zone=public--add-port=4444/tcp--permanentfirewall-cmd--zone=public--add-port=4567/tcp--permanentfirewall-cmd--zone=public--add-port=4568/tcp--permanentfirewall-cmd--reload

vi/etc/selinux/config#把SELINUX属性值设置成disabledreboot2 安装软件(install PXC)2.1 在线安装

$sudoyuminstallPercona-XtraDB-Cluster-572.2本地安装

解压下载好的安装包，并将 qpress 包放入一起，然后进入目录，进行如下命令

$sudoyumlocalinstall*.rpm

[^说明]: You need to have root access on the node where you will be installing Percona XtraDB Cluster (either logged in as a user with root privileges or be able to run commands with sudo

chkconfigmysqldoff2.4 数据库初始化

启动服务

$sudoservicemysqlstart

查看MySQL初始密码

cat/var/log/mysqld.log|grep"Atemporarypassword"

用root账号登录

mysql-uroot-p

修改密码

mysql_secure_installation

创建远程管理员账号

CREATEUSER'YXC_admin'@'%'IDENTIFIEDBY'Yxc_123456';GRANTallprivilegesON*.*TO'YXC_admin'@'%';FLUSHPRIVILEGES;#或者如下：CREATEUSER'YXC'@'localhost'IDENTIFIEDBY'passw0rd';GRANTRELOAD,LOCKTABLES,PROCESS,REPLICATIONCLIENTON*.*TO'YXC'@'localhost';mysql>FLUSHPRIVILEGES;

停止服务

$sudoservicemysqlstop3 配置文件

Configure all nodes,配置文件内容：

#vim/etc/my.cnf[client]socket=/var/lib/mysql/mysql.sock[mysqld]server-id=31#server-id=1#PXC集群中MySQL实例的唯一ID，不能重复，必须是数字datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidlog-binlog_slave_updatesexpire_logs_days=7character_set_server=utf8bind-address=0.0.0.0#跳过DNS解析skip-name-resolve#Disablingsymbolic-linksisrecommendedtopreventassortedsecurityriskssymbolic-links=0##集群部分wsrep_provider=/usr/lib64/galera3/libgalera_smm.sowsrep_cluster_name=pxc3-cluster#PXC集群的名称wsrep_cluster_address=gcomm://192.168.56.31,192.168.56.32,192.168.56.33wsrep_node_name=pxc31#当前节点的名称wsrep_node_address=192.168.56.31#当前节点的IPwsrep_sst_method=xtrabackup-v2#同步方法（mysqldump、rsync、xtrabackup）wsrep_sst_auth=YXC_admin:Yxc_123456#同步使用的帐户#SSTmeansStateSnapshotTransferpxc_strict_mode=ENFORCING#同步严厉模式binlog_format=ROW#基于ROW复制（安全可靠）default_storage_engine=InnoDB#默认引擎innodb_autoinc_lock_mode=2#主键自增长不锁表4 节点启停

引导节点启停(Bootstrap the first node)

systemctlstartmysql@bootstrap.servicesystemctlstopmysql@bootstrap.servicesystemctlrestartmysql@bootstrap.service

非引导节点启停(add other nodes)

servicestartmysqlservicestopmysqlservicerestartmysql

判断引导节点

##cat/var/lib/mysql/grastate.dat#GALERAsavedstateversion:2.1uuid:4580a102-bd96-11e9-a653-2a1e935fbf67seqno:-1safe_to_bootstrap:0#safe_to_bootstrap:1,则为引导启动节点

如果最后关闭的PXC节点是安全退出的，那么下次启动要最先启动这个节点，而且要以引导节点启动
如最后关闭的PXC节点不是安全退出，要先修改/var/lib/mysql/grastate.dat 文件，把其中的 safe_to_bootstrap 属性值设置为1，再按照引导节点启动

showstatuslike'%wsrep%';##wsrep:writesetreplication#PerconaXtraDBClusterisbasedonPerconaServerrunningwiththeXtraDBstorageengine.ItusestheGaleralibrary,whichisanimplementationofthewritesetreplication(wsrep)APIdevelopedbyCodershipOy.ThedefaultandrecommendeddatatransfermethodisviaPerconaXtraBackup.

（1）安装JDK

#搜索JDK版本yumsearchjdk#安装JDK1.8开发版yuminstalljava-1.8.0-openjdk-devel.x86_64

（2）配置环境变量

#查看JDK安装路径ls-lrt/etc/alternatives/javavi/etc/profile#在文件结尾加上JDK路径，例如exportJAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64/source/etc/profile

（3）下载MyCat

http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

（4）上传MyCat压缩包到虚拟机

（5）安装unzip程序包，解压缩MyCat

yuminstallunzipunzipMyCAT压缩包名称

（6） 开放防火墙8066和9066端口，关闭SELINUX

（7） 修改MyCat的bin目录中的权限

chmod-R777./*.sh

（8） MyCat启动与关闭

#cdMyCat的bin目录./startup_nowrap.sh#启动MyCatps-aux#查看系统进程kill-9MyCat进程编号7.2 Mycat 配置7.2.1 schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><!--配置数据表--><schemaname="ecs"checkSQLschema="false"sqlMaxLimit="100"><tablename="t_user"dataNode="dn1,dn2"rule="mod-long"/><tablename="t_customer"dataNode="dn1,dn2"rule="sharding-customer"><childTablename="t_orders"primaryKey="ID"joinKey="customer_id"parentKey="id"></childTable></table></schema><!--配置分片关系--><dataNodename="dn1"dataHost="cluster1"database="ecs"/><dataNodename="dn2"dataHost="cluster2"database="ecs"/><!--配置连接信息--><dataHostname="cluster1"maxCon="1000"minCon="10"balance="2"writeType="1"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="w1"url="192.168.56.21:3306"user="admin"password="Abc_123456"><!--canhavemultireadhosts--><readHosthost="w1r1"url="192.168.56.22:3306"user="admin"password="Abc_123456"/><readHosthost="w1r2"url="192.168.56.23:3306"user="admin"password="Abc_123456"/></writeHost><writeHosthost="w2"url="192.168.56.22:3306"user="admin"password="Abc_123456"><!--canhavemultireadhosts--><readHosthost="w2r1"url="192.168.56.21:3306"user="admin"password="Abc_123456"/><readHosthost="w2r2"url="192.168.56.23:3306"user="admin"password="Abc_123456"/></writeHost></dataHost><dataHostname="cluster2"maxCon="1000"minCon="10"balance="2"writeType="1"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="w1"url="192.168.56.31:3306"user="admin"password="Abc_123456"><!--canhavemultireadhosts--><readHosthost="w1r1"url="192.168.56.32:3306"user="admin"password="Abc_123456"/><readHosthost="w1r2"url="192.168.56.33:3306"user="admin"password="Abc_123456"/></writeHost><writeHosthost="w2"url="192.168.56.32:3306"user="admin"password="Abc_123456"><!--canhavemultireadhosts--><readHosthost="w2r1"url="192.168.56.31:3306"user="admin"password="Abc_123456"/><readHosthost="w2r2"url="192.168.56.33:3306"user="admin"password="Abc_123456"/></writeHost></dataHost></mycat:schema>7.2.2 server.xml

<username="YXC_admin"defaultAccount="true"><propertyname="password">Yxc_123456</property><propertyname="schemas">ecs</property><!--表级DML权限设置--><!--<privilegescheck="false"><schemaname="TESTDB"dml="0110"><tablename="tb01"dml="0000"></table><tablename="tb02"dml="1111"></table></schema></privileges>--></user>7.2.3 rule.xml

<tableRulename="sharding-customer"><rule><columns>sharding_id</columns><algorithm>customer-hash-int</algorithm></rule></tableRule><functionname="customer-hash-int"class="io.mycat.route.function.PartitionByFileMap"><propertyname="mapFile">customer-hash-int.txt</property></function>8. Haproxy +keepalived负载均衡

用一个虚拟机实例部署Haproxy

安装Haproxy

yuminstall-yhaproxy

编辑配置文件

vi/etc/haproxy/haproxy.cfg

globallog127.0.0.1local2chroot/var/lib/haproxypidfile/var/run/haproxy.pidmaxconn4000userhaproxygrouphaproxydaemon#turnonstatsunixsocketstatssocket/var/lib/haproxy/statsdefaultsmodehttplogglobaloptionhttplogoptiondontlognulloptionhttp-server-closeoptionforwardforexcept127.0.0.0/8optionredispatchretries3timeouthttp-request10stimeoutqueue1mtimeoutconnect10stimeoutclient1mtimeoutserver1mtimeouthttp-keep-alive10stimeoutcheck10smaxconn3000listenadmin_statsbind0.0.0.0:4001modehttpstatsuri/dbsstatsrealmGlobal\statisticsstatsauthadmin:abc123456listenproxy-mysqlbind0.0.0.0:3306modetcpbalanceroundrobinoptiontcplog#日志格式servermycat_1192.168.99.131:3306checkport8066maxconn2000servermycat_2192.168.99.132:3306checkport8066maxconn2000optiontcpka#使用keepalive检测死链

启动Haproxy

servicehaproxystart

访问Haproxy监控画面

http://192.168.99.131:4001/dbs

用另外一个虚拟机同样按照上述操作安装Haproxy

在某个Haproxy虚拟机实例上部署Keepalived

开启防火墙的VRRP协议

#开启VRRPfirewall-cmd--direct--permanent--add-ruleipv4filterINPUT0--protocolvrrp-jACCEPT#应用设置firewall-cmd--reload

安装Keepalived

yuminstall-ykeepalived

编辑配置文件

vim/etc/keepalived/keepalived.conf

vrrp_instanceVI_1{stateMASTERinterfaceens33virtual_router_id51priority100advert_int1authentication{auth_typePASSauth_pass123456}virtual_ipaddress{192.168.99.133}}

启动Keepalived

servicekeepalivedstart

ping 192.168.99.133

在另外一个Haproxy虚拟机上，按照上述方法部署Keepalived

使用MySQL客户端连接192.168.99.133

看完上述内容，你们掌握mysql中如何快速搭建PXC集群以及Mycat分片的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！