day17-mysql集群解决方案主从复制、pxc mycat haproxy

多种集群架构的在我们的系统架构中，DBservr方面我们只是使用了单节点服务，如果面对大并发，海量数据的，显然单节点的系统架构将存在很严重的问题，所以接下来，实现yS的集群，来应对大并发、海量数据等问题。2、MySQL、读写分离架我们一般应用对数据库而言都是“读多写少”，也就说对数据库数据的压力比较大，有一个思路就是说采用数据库集群的方案：其中一个是主库，负责写入数据，我们称之为：写库；其它都是从库，负责数据，我们称之为：读库；读库和写库的数写数据必须写到写库读数据必须到读主从之间的同步，是异步完成，也就意味着这是弱一致 、中间决，如下：从架构中，可以看出应用程序无需区分读写操作，对中间件直接进行读写操这样的话，中间件的可靠性得到了保证，但是也带来了新的问题，应用系统依然是需要连接到2个中间件，又为应用、负载均为了解决以上问题，继续优化架构，在应用程序和中间件之间增加pxy，由来完成负载均衡的功能，应用程序只需要对接到pxy即可。·、PXC集群架构面的架构中，都是基于yS致中，显然这种架构是不能应对的，比如：交易数据。架构如下、混合架面的C架构中，虽然可以实现了事务的强一致性，但是它是通过牺牲了性能换来的一致性，如果在某些业务场景下，如果没有强一致，那么使用PC就不合适了。所以，在我们的系统架构中，需要将这两种方式综合起来，这样才是一个较为完善的架构。、主从原master将数据改变记录到二进制日志(iarylog中,也即是配置文件log-i指定的文件(这些记录叫做二进制日志事件，binarylgvents)slave将master的binarylogevents拷贝到它的中继日志(relaylog)主从配置需要注主DBserver和从DBserver数据库的版本一致主DBserver和从DBserver数据库数据一致主DBserver开启二进制日志,主DBserver和从DBserver的server_id、主库配置文件1123456789#开启主，主库的配log-bin=mysql-binSHOWMASTERSTATUS、在主库创建同步用11234 用户slave使登录grantreplicationslaveon*.*to'slave01'@''identifiedby'123456';flush、从库配置文件1123456789#指定serverid，只要不重复即可，从库也只有这一个配置，其他都在SQL语句中操server-#以下执行CHANGEMASTERSTART#查看同步SHOWSLAVE、搭建主1#创2mkdir3456789mkdirconfdataod777*-#创建配置 log-bin=mysql-bin#开启二进制日志server-id=1#服务id，不可重复#创建容dockercreate--namepercona-master01-v/data/mysql/master01/data:/var/lib/mysql- f.d-p3306:3306-eMYSQL_ROOT_PASSWORD=root#启dockerstartpercona-master01&&dockerlogs-fpercona-#创建同步createuser''@'%'identifiedby'';grantreplicationslaveon*.*to''@'%';flushprivileges;#出现[Err]1055-Expression#1ofORDERBYclauseisnotinGROUPBYclauseand错误解 #查看master状showmaster#查看二进制日志相关showglobalvariableslike#查看server相关showglobalvariableslikemaster状态：、搭建从1123456789#创mkdir/data/mysql/slave01cd/data/mysql/slave01mkdirconfdataod777*-#创建配置cdf#输入如下server-id=2#服务id，不可重复 #创建容dockercreate--namepercona-slave01-v/data/mysql/slave01/data:/var/lib/mysql- f.d-p3307:3306-eMYSQL_ROOT_PASSWORD=root#启dockerstartpercona-slave01&&dockerlogs-fpercona-slave01#设置master相关CHANGEMASTER master_log_file='mysql-#启动同start#查看master状showslavestatus;mysql>showslavestatus***************************1.rowSlave_IO_State:Waitingformastertosend Master_Host:Master_Port:Connect_Retry:Master_Log_File:mysql-Read_Master_Log_Pos:Relay_Log_File:dfcb7c833aeb-relay-Relay_Log_Pos:Relay_Master_Log_File:mysql-Slave_IO_Running:Slave_SQL_Running:Last_Errno:Skip_Counter:Exec_Master_Log_Pos:Relay_Log_Space:Until_Condition:Until_Log_Pos:0Master_SSL_Allowed:NoSeconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_SQL_Errno:01444b04d6-249d-11e9-affd-SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Auto_Position:01rowinset(0.00、主从模(statement-basedreplication,SBR)，基于行的(row-basedreplication,RBR)，混合模式(mixed-basedreplication,MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数，last_insert_id()，以及user-defined不记录每条sl过程、或fucio、或trigger的调用和触发无法被正确的问题。缺点是会产生大量的日志，尤其是trtable的时候会让日志暴112345678#dockerrestartpercona-master01&&dockerlogs-fpercona-#showglobalvariableslike、简一个可以视为yS集群的企业级数据库，用来替代昂贵的Oacle一个融合内存缓存技术、NSL技术、FS大数据的新型SQLSever结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品、优基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀的架构和性能以及众多成使用案例使MYCAT一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能看到更远。业界优秀的开源项目和创新思路被泛融入到MYCAT的中，使得MYCAT在很多方面都领先于目前其他一些同类的开源项目，甚至某些商业产品C背后有一支强大的技术团队，其参与者都是5年以上资深软件工程师、架构师、DA等，优秀的技术团队保证了CT的产品质量。、关键特支持MySQL、Oracle、DB2、SQLServer、PostgreSQL等DB的常见SQL语法基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galeracluster集群。支持GaleraforMySQL集群，PerconaCluster或者MariaDBcluster支持数据的多片自动路由与聚合，支持su,cot,ax等常用的聚合函数,ji，支持跨库2表in，甚至基于cltlt的多表ji。支持前端作为MySQL通用，后端JDBC方式支持Oracle、DB2、SQLServer、mongodb、巨杉。支持IP支持非堆内存(DirectMemory)聚合计算（1.6）支持mysl和rcle过程，ot参数、多结果集返回（.6）支持zokeper协调主从切换、zk序列、配置zk化（6）支持库内分表、读写分主端容器角percona-percona-1123456789<?xmlversion="1.0"encoding="UTF-<!DOCTYPEmycat:serverSYSTEM<mycat:server<property<property<property<property<property<property<property<property<property<property<property<property<property<property<!--这里是设置 用户和虚拟逻辑库--<user "<propertyname="password"><propertyname="schemas">1123456789<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schema<!--配置数据表<schema"checkSQLschema="false"<tablename="tb_ad"dataNode="dn1"rule="mod-long"<!--配置分片关系<dataNodename="dn1"dataHost="cluster1"<!--配置连接信息"<dataHostname="cluster1"maxCon="1000"minCon="10"balance="3"writeType="1"dbType="mysql"dbDriver="native"switchType="1"<heartbeat>select<writeHosthost="W1"url="8:3306"user="root"<readHosthost="W1R1"url="8:3307"user="root"password="root"/>负载均衡类型，目前的取值有3balance="0",不开启读写分离机制，所有读操作都发送到当前可用的writeHostbalance="1"，全部的readHost与standbywriteHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负balance="2"，所有读操作都随机的在writeHost、readhostbalance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力，注意balance=3只在1.4及其以后版本有，1.3没有。<function<functionname="mod-long"<property测试12345678--CREATETABLE`tb_ad``id`bigint(20)NOTNULL`type`int(10)DEFAULTNULLCOMMENT'类型`title`varchar(100)DEFAULTNULLCOMMENT'描述`url`varchar(200)DEFAULTNULLCOMMENT'URL地址`created`datetimeDEFAULT`updated`datetimeDEFAULT99PRIMARYKEY)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8 表--INSERTINTO`tb_ad`(`id`,`type`,`title`,`url`,`created`,`updated`)VALUES'1','UniCity万科天空之城', /images/2018/11/26/15432029097062227.jpg','2018-11-26'2018-11-2615 、数据分片MySQL集群主端容器角percona-percona-MySQL集群主端容器角percona-percona-、配置1123456789mkdir/data/mysql/master02cd/data/mysql/master02mkdirconfdataod777*-#创建配置cdf#输入如下log-bin=mysql-bin#开启二进制日志server-id=1#服务id，不可重复#dockercreate--namepercona-master02-v/data/mysql/master02/data:/var/lib/mysql- f.d-p3316:3306-eMYSQL_ROOT_PASSWORD=root#dockerstartpercona-master02&&dockerlogs-fpercona-createuser'@'%'identifiedbygrantreplicationslaveon*.*toflush#查看mastershowmaster、配置1123456789#搭建从#创mkdir/data/mysql/slave02cd/data/mysql/slave02mkdirconfdataod777*-#创建配置cdf#输入如下server-id=2#服务id，不可重复#创建容dockercreate--namepercona-slave02-v/data/mysql/slave02/data:/var/lib/mysql- f.d-p3317:3306-e#启dockerstartpercona-slave02&&dockerlogs-fpercona-#设置master相关信CHANGEMASTER#启动同start#查看master状showslaveCREATECREATETABLE`tb_ad``id`bigint(20)NOTNULL`type`int(10)DEFAULTNULLCOMMENT 类型`title`varchar(100)DEFAULTNULLCOMMENT'描述`url`varchar(200)DEFAULTNULLCOMMENT URL地址`created`datetimeDEFAULT`updated`datetimeDEFAULTPRIMARYKEY)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8 表、配置1234<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schemaxmlns:mycat="<!--配置数据表556789<schema"checkSQLschema="false"<tablename="tb_ad"dataNode="dn1,dn2"rule="mod-long"<dataNodename="dn1"dataHost="cluster1"<dataNodename="dn2"dataHost="cluster2"""<dataHostname="cluster1"maxCon="1000"minCon="10"balance="3"writeType="1"dbType="mysql"dbDriver="native"switchType="1"<heartbeat>select<writeHosthost="W1"url="8:3306"user="root"<readHosthost="W1R1"url="8:3307"user="root"password="root"/><dataHostname="cluster2"maxCon="1000"minCon="10"balance="3"writeType="1"dbType="mysql"dbDriver="native"switchType="1"<heartbeat>select<writeHosthost="W2"url="8:3316"user="root"<readHosthost="W2R1"url="8:3317"user="root"password="root"/><function<functionname="mod-long"<property重新启动mycat进试11./startup_nowrap.sh&&tail-f11INSERTINTO`tb_ad`(`id`,`type`,`title`,`url`,`created`,`updated`)VALUES('1','1','UniCity万科天空之城',' /images/2018/11/26/15432029097062227.jpg','2018-11-26'2018-11-2623INSERTINTO`tb_ad`(`id`,`type`,`title`,`url`,`created`,`updated`)VALUES('2','1','天和尚海庭前',' /images/2018/11/26/1543202958579877.jpg','2018-11-26'2018-11-2645INSERTINTO`tb_ad`(`id`,`type`,`title`,`url`,`created`,`updated`)VALUES('3','1','[奉贤南桥]光语著',' /images/2018/11/26/15432029946721854.jpg','2018-11-26'2018-11-2667INSERTINTO`tb_ad`(`id`,`type`,`title`,`url`,`created`,`updated`)VALUES'1',周边嘉兴海逸长洲',-/images/2018/11/26/15432030275359146.jpg','2018-11-26'2018-11-268结果查看、mycat集yct做了数据库的，在高并发的情况下，必然也会单节点性能问题，所以需要部署多个yct架构：1123456789cpmycatmycat2-vimwrapper.confvim#设置服务端口以及管理端<property<property#重新启动tail-f多节点的mycat搭建完成面架构中，虽然对yct做了集群，保障了myct的可靠性，但是，应用程序需要连接到多个yctHAPxy。、简5.3、部5.3、部署安装112345678#拉取镜dockerpull#创，用于存放mkdir#创建容dockercreate--namehaproxy--nethost-v编写配置文件123456789#创建文vim optionhttp-server-optionforwardfor except/8 timeouthttp-request timeoutqueue timeout timeout timeoutserver timeout modestats statsrealmGlobal\statisticsstatsauth modebalance mycat_18:8066checkport8066maxconnmycat_28:8067checkport8067maxconn启动容器##启动容dockerrestarthaproxy&&dockerlogs-f5.4、测6、PXC集、简PerconaXtraDBCluster（简称PXC）是针对MySQL用户的高可用性和扩展性解决方案，基于PerconaServer。其包括了WriteSetREPlication补丁，使用Galera2.0库，这是一个针对事务性应用程序的同步多主机插件。PerconaServer是MySQL的改进版本，使用XtraDB引擎，在功能和性能上较MySQL有着很显著的提升，如提升了在高负载情况下的InnoDB的性能，为DBA提供了一些非常有用的性能诊断工具，另外有的参数和命令来PerconaXtraDBCluster 官网 /software/mysql-database/percona-xtradb-图示、架、部署安节端容器数据实施11234567#创建数据卷路径dockervolumecreatev1dockervolumecreatev2dockervolumecreate#拉取镜dockerpullpercona/percona-xtradb-#重命dockertagpercona/percona-xtradb-cluster:5.7pxc#创建网dockernetworkcreate--subnet=/24pxc-##第一节dockercreate-p13306:3306-vv1:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=root-eCLUSTER_NAME=pxc--name=pxc_node1--net=pxc-network--ip=pxc#第二节点（增加了CLUSTER_JOIN参数dockercreate-p13307:3306-vv2:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=root-eip=pxc#第三节点（增加了CLUSTER_JOIN参数dockercreate-p13308:3306-vv3:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=root-eip=pxc#查看集群showstatuslike、测1CREATETABLE`tb_ad`2`id`bigint(20)NOTNULL3`type`int(10)DEFAULTNULLCOMMENT 类型4`title`varchar(100)DEFAULTNULLCOMMENT'描述5`url`varchar(200)DEFAULTNULLCOMMENT' URL地址6`created`datetimeDEFAULT7`updated`datetimeDEFAULT8PRIMARYKEY9)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8 表--插入测试数INSERTINTO`tb_ad`(`id`,`type`,`title`,`url`,`created`,`updated`)VALUES'1','UniCity万科天空之城', /images/2018/11/26/15432029097062227.jpg','2018-11-26'2018-11-26效果数据、集群的说、PXC集群方案与Replication区PC集群方案所有节点都是可读可写的，Rlicti从节点不能写入，因为主从同步是单向的，无法从slv节点向master点同步。PC同步机制是同步进行的，这也是它能保证数据强一致性的根本原因，Rlicti同步机制是异步进行的，它如果从节点停止同步，依然可以向主节点插入数据，正确返回，造成数据主从数据的不一致性。PC是用牺牲性能保证数据的一致性，Rlici在性能上是高于PC的。所以两者用途也不一致。PC重要信息的，例如：订单、用户信息等。Rictin用于一般信息的，能够数据丢失，例如：购物车，用户行为日志等。前面学习了主从架构、yt中间件、HAPxy负载均衡、PC集群架构，在实际的项目中，往往不单单是一种架构，的使用的混合架构，下面好客租房项目采用混合架构的方式进行完善数据库集群。、架说明部署了2个Mycat节点、部署PXC集群集群节端容器数据haoke-haoke-集群节端容器数据haoke-haoke-#创建数据卷 路径dockervolumecreatehaoke-dockervolumecreatehaoke-dockervolumecreatehaoke-dockervolumecreatehaoke-v4#拉取镜dockerpullpercona/percona-xtradb-cluster:5.7#创建网dockernetworkcreate--subnet=/24pxc-network#创建容#集群1，第一节dockercreate-p13306:3306-vhaoke-v1:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=root-eCLUSTER_NAME=pxc--name=pxc_node1--net=pxc-network--ip=pxc#第二节点（增加了CLUSTER_JOIN参数dockercreate-p13307:3306-vhaoke-v2:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=root-eCLUSTER_NAME=pxc--name=pxc_node2-eCLUSTER_JOIN=pxc_node1--net=pxc-network--ip=pxc#集群#第一节dockercreate-p13308:3306-vhaoke-v3:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=root-eCLUSTER_NAME=pxc--name=pxc_node3--net=pxc-network--ip=pxc#第二节点（增加了CLUSTER_JOIN参数dockercreate-p13309:3306-vhaoke-v4:/var/lib/mysql-eMYSQL_ROOT_PASSWORD=root-eCLUSTER_NAME=pxc--name=pxc_node4-eCLUSTER_JOIN=pxc_node3--net=pxc-network--ip=pxc#启dockerstartpxc_node1&&dockerlogs-fdockerstartpxc_node2&&dockerlogs-fpxc_node2dockerstartpxc_node3&&dockerlogs-fdockerstartpxc_node4&&dockerlogs-fpxc_node4#查看集群showstatuslike结果12123456789CREATETABLE`tb_house_resources``id`bigint(20)NOTNULL`title`varchar(100)DEFAULTNULLCOMMENT'房源标题`estate_id`bigint(20)DEFAULTNULLCOMMENT'楼盘`building_num`varchar(5)DEFAULTNULLCOMMENT'楼号（栋`building_unit`varchar(5)DEFAULTNULLCOMMENT'单元号`building_floor_num`varchar(5)DEFAULTNULLCOMMENT'门牌号`rent`int(10)DEFAULTNULLCOMMENT `rent_method`tinyint(1)DEFAULTNULLCOMMENT'租赁方式，1-整租，2-合租`payment_method`tinyint(1)DEFAULTNULLCOMMENT'支付方式六押一，4-年付押一，5-其它一，2-付三押一，3-`house_type`varchar(255)DEFAULTNULLCOMMENT'户型，如：2室1厅1卫`covered_area`varchar(10)DEFAULTNULLCOMMENT'建筑面积`use_area`varchar(10)DEFAULTNULLCOMMENT'使用面积`floor`varchar(10)DEFAULTNULLCOMMENT'楼层，如：`orientation`varchar(2)DEFAULTNULLCOMMENT'朝向：东、南、西、北`decoration`tinyint(1)DEFAULTNULLCOMMENT'装修，1-精装，2-简装，3-毛坯`facilities`varchar(50)DEFAULTNULLCOMMENT'配套设施，`pic`varchar(1000)DEFAULTNULLCOMMENT' ，最多5张`house_desc``house_desc`varchar(200)DEFAULTNULLCOMMENT'描述`contact`varchar(10)DEFAULTNULLCOMMENT'联系人`mobile`varchar(11)DEFAULTNULLCOMMENT 号`time`tinyint(1)DEFAULTNULLCOMMENT'看房时间，1-上午，2-中午，3-下午，4-晚上，5-全天`property_cost`varchar(10)DEFAULTNULLCOMMENT'物业费`created`datetimeDEFAULT`updated`datetimeDEFAULTPRIMARYKEY)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8COMMENT='房源表、部署主从集1123456789#创mkdir/data/mysql/haoke/master01-pcd/data/mysql/haoke/master01mkdirconfdataod777*-#创建配置cdf#输入如下log-bin=mysql-bin#开启二进制日志server-id=1#服务id，不可重复#创建容dockercreate--namepercona-haoke-master01-/data/mysql/haoke/master01/data:/var/lib/mysql- f.d-p23306:3306-eMYSQL_ROOT_PASSWORD=rootpercona:5.7.23#启dockerstartpercona-haoke-master01&&dockerlogs-fpercona-haoke-#创建同步createuser'@'%'identifiedbygrantreplicationslaveon*.*toflush#查看master状showmaster1123456789#mkdir/data/mysql/haoke/slave01-pcd/data/mysql/haoke/slave01mkdirconfdataod777*-cdfserver-id=2#服务id，不可重复#dockercreate--namepercona-haoke-slave01-/data/mysql/haoke/slave01/data:/var/lib/mysql- f.d-p23307:3306-eMYSQL_ROOT_PASSWORD=rootpercona:5.7.23#dockerstartpercona-haoke-slave01&&dockerlogs-fpercona-haoke-CHANGEMASTERstartslave;showslavestatus;说明主从搭建成创建haoke数据库以及创建表CREATECREATETABLE`tb_ad``id`bigint(20)NOTNULL`type`int(10)DEFAULTNULLCOMMENT 类型`title`varchar(100)DEFAULTNULLCOMMENT'描述`url`varchar(200)DEFAULTNULLCOMMENT URL地址`created`datetimeDEFAULT`updated`datetimeDEFAULTPRIMARYKEY)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8 表、部署cdcdmkdircp/haoke/mycat.-mvmycat/mycat-11234<?xmlversion="1.0"encoding="UTF-<!DOCTYPEmycat:serverSYSTEM<mycat:server56789<property<property<property<property<property<property<property<property<property<property<property<property<property<property<!--这里是设置的用户和虚拟逻辑库--<username=""<propertyname="password"><property123456789<?xml<!DOCTYPEmycat:schemaSYSTEM<mycat:schemaxmlns:mycat="<!--配置数据表<schemaname="haoke"checkSQLschema="false"<tablename="tb_house_resources"dataNode="dn1,dn2"rule="mod-long"<tablename="tb_ad"<!--配置分片关系<dataNodename="dn1"dataHost="cluster1"database="haoke"<dataNodename="dn2"dataHost="cluster2"database="haoke"<dataNodename="dn3"dataHost="cluster3"database="haoke"<!--配置连接信息<dataHostname="cluster1"maxCon="1000"minCon="10"balance="2"writeType="1"dbType="mysql"dbDriver="native"switchType="1"<heartbeat>select<writeHosthost="W1"url="8:13306"user="root"<readHosthost="W1R1"url="8:13307"user="root"password="root"/><dataHostname="cluster2"maxCon="1000"minCon="10"balance="2"writeType="1"dbType="mysql"dbDriver="native"switchType="1"<heartbeat>select<writeHosthost="W2"url="8:13308"user="root" <readHosthost="W2R1"url="8:13309"password="root"<dataHostname="cluster3"maxCon="1000"minCon="10"writeType="1"dbType="mysql"dbDriver="native"<heartbeat>select<writeHosthost="W3"url="8:23306" <readHosthost="W3R1"url="