MySQL数据库的集群方案

MySQL 高可用架构：主从备份

• 为了防止数据库的突然，挂机，我们需要对数据库进行高可用架构主从备份是常见的场景
• 通常情况下都是一主一从/(多从)
  正常情况下，都是主机进行工作，从机进行备份主机数据，如果主机某天突然意外宕机，从机可以立刻工作而不会数据丢失…
  

MySql 主从复制原理

mysql主节点(称master) | 从(称slave)复制

• 首先我们要先开启主机的日志记录master主机，每进行 DML的操作，向日志中写入一条消息记录到二进制日志事件(binary log)

• 从节点，中会指定主节点的信息：ip 端口二进制日志 的位置文件...
  并启动一个线程，异步实时的将master的binary log events拷贝到它的中继日志(relay log)
  slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

注意：

• 通常为了保证数据准确，主从备份之前要：确保
• 主DB server和从DB server数据库的版本一致
• 主DB server和从DB server数据库数据一致
• 主DB server开启二进制日志,主DB server和从DB server的server_id服务id都必须唯一

环境准备：

• percona-5-7-23.tar
  Mysql的衍生版本Percona通过docker进行搭建服务。这里本人直接使用提供好的tar 进行生成Docker镜像
  有网络条件的朋友可以直接 拉取最新的镜像！

Docker 搭建Mysql 主从备份：

• 正好，前段时间，看了一段时间Dokcer 本次就直接通过Dokcer进行搭建了…正常情况其实也无疑..

搭建Master01节点：

#本人安装软件的目录：[root@hadoop1 ~]# cd /usr/local[root@hadoop1 local]# lsapache-phoenix-5.0.0-HBase-2.0-bin elasticsearch haproxy-1-9-3.tar kafka_2.10-0.10.2.1 MR-1.0-SNAPSHOT.jar partition.csv zookeeper-3.4.13 apache-tomcat-7.0.85 elasticsearch-6.2.4 hbase-2.1.0 kibana-6.2.4-linux-x86_64 Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz percona-5-7-23.tar apollo hadoop jdk1.8.0_171 logstash-6.3.0 mysql wordcount.txt#本人没有下载,就直接讲 tar包转换成镜像文件了...[root@hadoop1 local]# docker load --input percona-5-7-23.tarf972d139738d: Loading layer[==================================================>]208.8MB/208.8MB 793d15bf88da: Loading layer[==================================================>]6.144kB/6.144kB f34bc9e37309: Loading layer[==================================================>]303.1kB/303.1kB 39df7a5abc61: Loading layer[==================================================>]22.46MB/22.46MB#省略..#查看所有镜像#percona Mysql衍生版本#haproxy 代理服务器[root@hadoop1 ~]# docker imagesREPOSITORY TAG IMAGE ID CREATED SIZE haproxy 1.9.3 d1ba0f09266d 2 years ago 72.1MB percona 5.7.23 9db94bcf28b3 2 years ago 577MB 正片开始😀 Mysql master主节点搭建#回到根目录 创建文件目录(用户区分之前安装的应用...)[root@hadoop1 /]# cd /[root@hadoop1 /]# mkdir -p /data/mysql/master01#进入一号主节点目录[root@hadoop1 /]# cd /data/mysql/master01#创建两个目录：conf配置文件 data日志文件,为了方便操作容器内,创建数据卷[root@hadoop1 master01]# mkdir conf data[root@hadoop1 master01]# lsconfdata#linux 设置文件权限 777 最大！[root@hadoop1 master01]# chmod 777 * -R[root@hadoop1 master01]# cd /data/mysql/master01/conf#创建编写配置文件 👇👇[root@hadoop1 conf]# vim my.cnf#生成容器#-v 设置数据卷：容器与宿主机进行文件管理共享,为了方便更改查看配置文件！#-p 设置端口,因为对外访问的是linux的端口,容器内部的端口要与宿主机进行映射所以之后的 3306只能有一个！(后面就是3307 3316别搞混)#-e 配置数据库默认密码 root[root@hadoop1 conf]# docker create --name percona-master01 -v /data/mysql/master01/data:/var/lib/mysql -v /data/mysql/master01/conf:/etc/my.cnf.d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.23cde032cb98a0465f08cee6554e83a4d53ce2b72bc95f662746d712418d4d1c91#启动mysql 容器 && (并且查看他的启动日志：如果启动报错就可以实时的发现！[root@hadoop1 conf]# docker start percona-master01 && docker logs -f percona-master01#...[root@hadoop1 ~]# docker ps -acde032cb98a0 percona:5.7.23"/docker-entrypoint.…"15 hours ago Exited(255)15 minutes ago 0.0.0.0:3306->3306/tcp,:::3306->3306/tcp percona-master01

my.cnf 文件内容

#配置mysql基础信息[mysqld]#开启二进制日志,集群必须开启,主从互备直接通信就是通过: 二进制日志！ (从节点这个可以忽略！)log-bin=mysql-bin#服务id,同一个集群环境下服务id不可重复！server-id=1

• 从节点可以忽略：log-bin=mysql-bin
  因为，从节点只需要读取，主节点的二进制日志即可！

主节点 测试连接：

• 查看二进制日志文件：文件名文件位置...

设置：主节点配置信息

#创建同步账户以及授权CREATEUSER'wf'@'%'IDENTIFIEDBY'wf';#配置权限, 读库读表的操作 *.*GRANTREPLICATIONSLAVEON*.*TO'wf'@'%';#刷新MySQL的系统权限相关表FLUSHPRIVILEGES;#查看master状态SHOWMASTERSTATUS;#查看二进制日志相关的配置项SHOWGLOBALVARIABLESLIKE'binlog%';#查看server相关的配置项SHOWGLOBALVARIABLESLIKE'server%';

注意：

• 出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and错误解决方案，在my.cnf配置文件中设置
  sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
  ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

搭建从节点 Slave01：

• 基本与创建Master 无区别！

##创建目录,创建数据卷，编写my.cnf[root@hadoop1 mysql]# mkdir -p /data/mysql/slave01[root@hadoop1 slave01]# cd /data/mysql/slave01[root@hadoop1 slave01]# mkdir conf data[root@hadoop1 slave01]# chmod 777 * -R[root@hadoop1 slave01]# cd /data/mysql/slave01/conf[root@hadoop1 conf]# vi my.cnf#创建容器[root@hadoop1 conf]# docker create --name percona-slave01 -v /data/mysql/slave01/data:/var/lib/mysql -v /data/mysql/slave01/conf:/etc/my.cnf.d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.233ac806767c09ce3adb6c468f45f61020afeef701cd0162e439dcae01a040338a#启动容器[root@hadoop1 conf]# docker start percona-slave01 && docker logs -f percona-slave01percona-slave01

• slave的my.cnf文件不需要配置，log-bin=mysql-bin开启二进制日志！

从节点测试：

• 根据个人实际情况进行更改！

#设置master相关信息CHANGE MASTERTOMASTER_HOST='192.168.1.110',#注意端口MASTER_USER='wf',MASTER_PASSWORD='wf',MASTER_PORT=3316,#主节点的端口MASTER_LOG_FILE='mysql-bin.000004',#指定要监听主节点的 二级制日志文件;MASTER_LOG_POS=154;#二级制文件位置#启动同步STARTSLAVE;#查看master状态SHOWSLAVESTATUS;#(Waiting for master to send event):等待主服务器发送事件#错误：#ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread； run STOP SLAV-- mysql从机上已经进行过绑定主机，如果继续绑定需要先进行重置。#停止已经启动的绑定STOP SLAVE#重置绑定RESET MASTER

测试：

• 此时为了检测主从是否同步，备份…
  直接在主节点上创建一个库/表新增数据…
  在查看从机上是否同步...

• ok，验证成功，主机发生更改从机，立刻就会同步数据！

Mysql 高可用，主从备份总结：

• Mysql主从备份…总的来说并不难，本人使用的是Docker进行本机搭建的…
• 实际开发中，其实也就是相当于安装两个数据库一个当Master 一个当Slave
• 主机开启日志记录从机实时开启一个线程读取主机的执行SQL同步执行数据…

Mycat + MySql 读写分离

读写分离原理

• 我们一般应用对数据库而言都是 “读多写少”也就说对数据库读取数据的压力比较大

• 解决方案：
  采用数据库集群的方案：
  其中一个是主库，负责写入数据，我们称之为：写库；
  其它都是从库，负责读取数据，我们称之为： 读库；一主n从主从互备 读写分离架构！
  那么，对我们的要求是：1. 读库和写库的数据一致2. 写数据必须写到写库3. 读数据必须到读库

• 👈图：
  数据库从之前的单节点变为多节点提供服务
  主节点数据，同步到从节点数据
  应用程序需要连接到2个数据库节点，并且在程序内部实现判断读写操作
  这种架构存在2个问题：
  应用程序需要连接到多个节点，对应用程序而言开发变得复杂可以通过中间件解决
  如果在程序内部实现，可使用Spring的AOP功能实现

• 👉图：
  中间件解决:读写分离
  应用程序只需要连接到中间件即可，无需连接多个数据库节点
  应用程序无需区分读写操作，对中间件直接进行读写操作即可
  在中间件中进行区分读写操作，读发送到从节点，写发送到主节点一切交给中间件操作,无需关心数据库

Mycat 学习

• 需要的朋友可以了解一下Mycat

环境准备

• Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz
  Linux 安装tar包

环境搭建：

• Mycat安装非常简单，Windwos / Linux基本解压即用。需要了解注意的是它的配置文件
  rule 设置分片规则
  schema 设置分片逻辑库信息…
  server 设置 Mycat 用户/密码

#查看本人的软件目录[root@hadoop1 ~]# cd /usr/local[root@hadoop1 local]# lsapache-phoenix-5.0.0-HBase-2.0-bin elasticsearch haproxy-1-9-3.tar kafka_2.10-0.10.2.1 MR-1.0-SNAPSHOT.jar Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz percona-5-7-23.tar apache-tomcat-7.0.85 elasticsearch-6.2.4 hbase-2.1.0 kibana-6.2.4-linux-x86_64 mycat mysql wordcount.txt apollo hadoop jdk1.8.0_171 logstash-6.3.0 mycat2 partition.csv zookeeper-3.4.13#Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz 压缩包..#Mycat Mycat2(后面准备搭建集群)#启动Mycat[root@hadoop1 local]# cd /usr/local/mycat[root@hadoop1 mycat]# cd bin[root@hadoop1 bin]# lsdataMigrate.sh init_zk_data.sh mycat rehash.sh startup_nowrap.sh wrapper-linux-ppc-64 wrapper-linux-x86-32 wrapper-linux-x86-64#Mycat命令 ./mycat { console | start启动 | stop停止 | restart | status运行状态 | dump }[root@hadoop1 bin]# ./mycat startStarting Mycat-server...#查看服务进程是否启动[root@hadoop1 bin]# jps2721WrapperSimpleApp2742Jps

• Linux解压命令：tar -zxvf 要解压的文件

注意：

• Mycat 启动，报错不易被发现，可以查看log，进行查看.

配置文件:

• 这里提供的并不完善，而是重要的要修改的地方…
• 而且，Linux中存在，中文字符编码的问题不建议手动在Linux中进行修改编写。
  可以直接通过工具对文件直接上传覆盖运行…

• 详细请关注 https://blog.csdn.net/qq_45542380/article/details/115738373?spm=1001.2014.3001.5501

rule.xml

<tableRulename="mod-long"><rule><columns>id</columns><algorithm>mod-long</algorithm></rule></tableRule><!-- .... --><functionname="mod-long"class="io.mycat.route.function.PartitionByMod"><!-- how many data nodes --><propertyname="count">1</property><!-- 这里目前是一个Mysql主从架构。 根据多少分片规则而定！等下更改为2个 --></function><!-- .... -->

schema.xml

<?xml version="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="wf"checkSQLschema="false"sqlMaxLimit="100"><tablename="wsm"dataNode="dn1"rule="mod-long"/><!-- <table name="wsm" dataNode="dn1,dn2" rule="mod-long" /> --></schema><dataNodename="dn1"dataHost="cluster1"database="wsm"/><!--<dataNode name="dn2" dataHost="cluster2" database="wsm" /> --><dataHostname="cluster1"maxCon="1000"minCon="10"balance="3"writeType="1"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHosthost="W1"url="192.168.1.110:3306"user="root"password="root"><readHosthost="W1R1"url="192.168.1.110:3307"user="root"password="root"/></writeHost></dataHost><!-- <dataHost name="cluster2" maxCon="1000" minCon="10" balance="3" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="W1" url="192.168.1.110:3316" user="root" password="root"> <readHost host="W1R1" url="192.168.1.110:3317" user="root" password="root" /> </writeHost> </dataHost> --></mycat:schema>

• 注释的内容是数据库分片的内容
  逻辑库——逻辑表表名要与数据库匹配——数据节点database指定对应的数据库——数据库 主机配置
• 注意：逻辑表名要与实际相同！database指定的数节点要与数据库相同！详情参考上面连接学习！

server.xml

• 配置Mycat用户密码连接的逻辑表

<username="wf"defaultAccount="true"><propertyname="password">wf</property><propertyname="schemas">wf</property></user>

测试：

• 连接成功，并可以操作数据库wsm

Mycat + Mysql多个数据分片:

数据分片：

什么是数据库分片

• 简单来说，就是指通过某种特定的条件
• 将我们存放在同一个数据库中的数据分散存放到多个数据库主机上，以达到分散单台设备负载的效果。

三种分片模式：垂直切分水平切分混合切分

在次搭建一套Mysql 主从架构集群：

重复操作…不详细解释了

• 注意更改端口，容器名！

Master02

#创建目录mkdir-p/data/mysql/master02cd/data/mysql/master02cd/data/mysql/master02chmod777*-R#创建配置文件cd/data/mysql/master02/confvimmy.cnf#创建容器dockercreate--namepercona-master02-v/data/mysql/master02/data:/var/lib/mysql-v/data/mysql/master02/conf:/etc/my.cnf.d-p3316:3306-eMYSQL_ROOT_PASSWORD=root percona:5.7.23#启动dockerstart percona-master02&&dockerlogs-fpercona-master02

• 别忘了，Maste节点要开启日志my.cnf设置用户，分片权限

Slave02

mkdir/data/mysql/slave02cd/data/mysql/slave02mkdirconf datachmod777*-R#创建配置文件cd/data/mysql/slave02/confvimmy.cnf#创建容器dockercreate--namepercona-slave02-v/data/mysql/slave02/data:/var/lib/mysql-v/data/mysql/slave02/conf:/etc/my.cnf.d-p3317:3306-eMYSQL_ROOT_PASSWORD=root percona:5.7.23#启动dockerstart percona-slave02&&dockerlogs-fpercona-slave02

• my.cnf别忘了

查看所有运行的容器：

[root@hadoop1 bin]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES a2f509b44da7 percona:5.7.23"/docker-entrypoint.…"18hours ago Up15seconds0.0.0.0:3317->3306/tcp, :::3317->3306/tcp percona-slave02 1d03a6315278 percona:5.7.23"/docker-entrypoint.…"18hours ago Up4minutes0.0.0.0:3316->3306/tcp, :::3316->3306/tcp percona-master02 3ac806767c09 percona:5.7.23"/docker-entrypoint.…"21hours ago Up About an hour0.0.0.0:3307->3306/tcp, :::3307->3306/tcp percona-slave01 cde032cb98a0 percona:5.7.23"/docker-entrypoint.…"21hours ago Up About an hour0.0.0.0:3306->3306/tcp, :::3306->3306/tcp percona-master01

测试：

• Mycat 是对 master01 和 master02 的汇总
• 使用时候只需要，指定Mycat的ip 端口进行,增删改查mycat内部会对数据进行分片，读写分离

haproxy + Mycat中间件集群：

• mycat做了数据库的代理，在高并发的情况下，必然也会面临单节点性能问题，所以需要部署多个
  不然，万一它挂了，下面的Mysql服务即使没挂，也调用不了了！

haproxy

环境搭建：

Mycat2 创建：

• 直接copy上一个服务因为同一个ip 下端口要不同更改一下端口配置即可：

[root@hadoop1 local]# lsapache-phoenix-5.0.0-HBase-2.0-bin elasticsearch haproxy-1-9-3.tar kafka_2.10-0.10.2.1 MR-1.0-SNAPSHOT.jar Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz percona-5-7-23.tar apache-tomcat-7.0.85 elasticsearch-6.2.4 hbase-2.1.0 kibana-6.2.4-linux-x86_64 mycat mysql wordcount.txt apollo hadoop jdk1.8.0_171 logstash-6.3.0 mycat2 partition.csv zookeeper-3.4.13[root@hadoop1 local]#搭建多节点mycat： 直接copy 一个更改配置！cpmycat mycat2-Rvimwrapper.conf#设置jmx端口wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1982(默认)1985(修改)vimserver.xml#设置服务端口以及管理端口<propertyname="serverPort">8066/8067</property><propertyname="managerPort">9066/9067</property>#重新启动服务

haproxy 安装：

#拉取镜像dockerpull haproxy:1.9.3#创建数据卷 创建容器...[root@hadoop1 bin]# mkdir -p /haoke/haproxy[root@hadoop1 bin]# docker create --name haproxy --net host -v /haoke/haproxy:/usr/local/etc/haproxy haproxy:1.9.3b6ab17da4aaa0ef43a4a5253ab23b46366db9873e0b03a307291f0086613c820#编辑配置文件[root@hadoop1 bin]# vim /haoke/haproxy/haproxy.cfg#启动容器...打印日志[root@hadoop1 bin]# docker restart haproxy && docker logs -f haproxyhaproxy[WARNING]109/153326(1):config:'option forwardfor'ignoredforproxy'proxy-mysql'as it requires HTTP mode.[NOTICE]109/153326(1):New worker#1 (7) forked[WARNING]109/153328(7):Server proxy-mysql/mycat_1 is DOWN, reason: Layer4 timeout, check duration: 2000ms.1active and0backup servers left.0sessions active,0requeued,0remaininginqueue.[WARNING]109/153329(7):Server proxy-mysql/mycat_2 is DOWN, reason: Layer4 timeout, check duration: 2023ms.0active and0backup servers left.0sessions active,0requeued,0remaininginqueue.[ALERT]109/153329(7):proxy'proxy-mysql'has no server available!#查看运行容器 运行成功！[root@hadoop1 bin]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES b6ab17da4aaa haproxy:1.9.3"/docker-entrypoint.…"2minutes ago Up17seconds haproxy a2f509b44da7 percona:5.7.23"/docker-entrypoint.…"39minutes ago Up39minutes0.0.0.0:3317->3306/tcp, :::3317->3306/tcp percona-slave02

haproxy 配置文件

vim /haoke/haproxy/haproxy.cfg

#创建文件本人目前只是应用就不一一解释:vim/haoke/haproxy/haproxy.cfg#日志管理# maxconn 最大连接数4000global log127.0.0.1 local2 maxconn4000daemon#支持http …defaults mode http log global option httplog option dontlognull option http-server-close option forwardfor except127.0.0.0/8 option redispatch retries3timeouthttp-request 10stimeoutqueue 1mtimeoutconnect 10stimeoutclient 1mtimeoutserver 1mtimeouthttp-keep-alive 10stimeoutcheck 10s maxconn3000#自带的一套ur 管理页面监听web模式4001.. 账户密码listen admin_statsbind0.0.0.0:4001 mode http stats uri /dbs stats realm Global\statistics stats auth admin:admin123#监听服务 4002 指定监听mycat配置,监听地址#balance roundrobin 轮询模式.. server 两个mycat服务listen proxy-mysqlbind0.0.0.0:4002 mode tcp balance roundrobin option tcplog server mycat_1192.168.1.110:8066 check port8066maxconn2000server mycat_2192.168.1.110:8067 check port8067maxconn2000

• Linux建议删除中文...

• 从页面中，可以看出已经存在了2个mycat代理服务。
• 通过mysql客户端进行测试：
  
• 因为，害怕 单个Mycat挂调，影响服务正常使用，对Mycat进行集群架构！但就，出现了多个ip
• haproxy 就是对Mycat集群Ip 进行的汇总…

有人说 haproxy挂了怎么办？

• haproxy 不会挂！😒
• 它主要是，做代理服务器，浏览转发而不做任务… 一般情况下几乎不可能，如果出现意味抓紧喊运维！