5.2 MySQL 中间件代理服务器
5.2.1 关系型数据库和 NoSQL 数据库
数据库主要分为两大类：关系型数据库与 NoSQL 数据库。
- 关系型数据库，是建立在关系模型基础上的数据库，其借助于集合代数等数学概念和方法来处理数据库中的数据。主流的 MySQL、Oracle、MS SQL Server 和 DB2 都属于这类传统数据库。
- NoSQL 数据库，全称为 Not Only SQL，意思就是适用关系型数据库的时候就使用关系型数据库，不适用的时候也没有必要非使用关系型数据库不可，可以考虑使用更加合适的数据存储。主要分为临时性键值存储（Redis、memcached）、永久性键值存储（ROMA、Redis）、面向文档的数据库（MongoDB、CouchDB）、面向列的数据库（Cassandra、HBase），每种 NoSQL 都有其特有的使用场景及优点。
Oracle，mysql 等传统的关系数据库非常成熟并且已大规模商用，为什么还要用 NoSQL 数据库呢？主要是由于随着互联网发展，数据量越来越大，对性能要求越来越高，传统数据库存在着先天性的缺陷，即单机（单库）性能瓶颈，并且扩展困难。这样既有单机单库瓶颈，却又扩展困难，自然无法满足日益增长的海量数据存储及其性能要求，所以才会出现了各种不同的 NoSQL 产品，NoSQL 根本性的优势在于在云计算时代，简单、易于大规模分布式扩展，并且读写性能非常高
RDBMS和NOSQL的特点及优缺点：
5.2.2 数据切分
简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机） 上面，以达到分散单台设备负载的效果。
数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。
- 一种是按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据 表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。
- 垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小， 业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。 根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。
- 水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中， 对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。
5.2.2.1 垂直切分
一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：
系统被切分成了，用户，订单交易，支付几个模块。 一个架构设计较好的应用系统，其总体功能肯定是由很多个功能模块所组成的，而每一个功能模块所需要的数据对应到数据库中就是一个或者多个表。而在架构设计中，各个功能模块相互之间的交互点越统一越少，系统的耦合度就越低，系统各个模块的维护性以及扩展性也就越好。这样的系统，实现数据的垂直切分也就越容易。
但是往往系统之有些表难以做到完全独立，存在着跨库 join 的情况，对于这类表，就需要去做平衡， 是数据库让步业务，共用一个数据源，还是分成多个库，业务之间通过接口来做调用。在系统初期，数据量比较 少，或者资源有限的情况下，会选择共用数据源，但是当数据发展到了一定的规模，负载很大的情况，就需要必须去做分割。
一般来讲业务存在着复杂 join 的场景是难以切分的，往往业务独立的易于切分。如何切分，切分到何种程度是考验技术架构的一个难题。
垂直切分的优缺点：
优点：
- 拆分后业务清晰，拆分规则明确
- 系统之间整合或扩展容易
- 数据维护简单
缺点：
- 部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度
- 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高
- 事务处理复杂
由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶颈，所以就需要水平拆分来做解决。
5.2.2.2 水平切分
相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中，如图：
拆分数据就需要定义分片规则。关系型数据库是行列的二维模型，拆分的第一原则是找到拆分维度。比如： 从会员的角度来分析，商户订单交易类系统中查询会员某天某月某个订单，那么就需要按照会员结合日期来拆分， 不同的数据按照会员 ID 做分组，这样所有的数据查询 join 都会在单库内解决；如果从商户的角度来讲，要查询某个商家某天所有的订单数，就需要按照商户 ID 做拆分；但是如果系统既想按会员拆分，又想按商家数据，则会有 一定的困难。如何找到合适的分片规则需要综合考虑衡量。
几种典型的分片规则包括：
- 按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中
- 按照日期，将不同月甚至日的数据分散到不同的库中
- 按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中
如图，切分原则都是根据业务找到适合的切分规则分散到不同的库，下面用用户 ID 求模举例：
既然数据做了拆分有优点也就优缺点。
优点：
- 拆分规则抽象良好，join 操作基本都可以数据库完成
- 不存在单库大数据，高并发的性能瓶颈
- 应用端改造较少
- 提高了系统的稳定性跟负载能力
缺点：
- 拆分规则难以抽象
- 分片事务一致性难以解决
- 数据多次扩展难度跟维护量极大
- 跨库 join 性能较差
前面讲了垂直切分跟水平切分的不同跟优缺点，会发现每种切分方式都有缺点，但共同特点缺点有：
- 引入分布式事务的问题
- 跨节点 Join 的问题
- 跨节点合并排序分页问题
- 多数据源管理问题
针对数据源管理，目前主要有两种思路：
A. 客户端模式，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个数据库， 在模块内完成数据的整合
B. 通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明； 可能 90%以上的人在面对上面这两种解决思路的时候都会倾向于选择第二种，尤其是系统不断变得庞大复杂 的时候。确实，这是一个非常正确的选择，虽然短期内需要付出的成本可能会相对更大一些，但是对整个系统的 扩展性来说，是非常有帮助的。
MySQL中间件服务器可以通过将数据切分解决传统数据库的缺陷，又有了 NoSQL 易于扩展的优点。通过中间代理层规避了多数 据源的处理问题，对应用完全透明，同时对数据切分后存在的问题，也做了解决方案。
由于数据切分后数据 Join 的难度在此也分享一下数据切分的经验：
第一原则：能不切分尽量不要切分
第二原则：如果要切分一定要选择合适的切分规则，提前规划好。
第三原则：数据切分尽量通过数据冗余或表分组（Table Group）来降低跨库 Join 的可能
第四原则：由于数据库中间件对数据 Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量少使用多表 Join。
5.2.3 MySQL 中间件各种应用
- mysql-proxy：Oracle，https://downloads.mysql.com/archives/proxy/
- Atlas：Qihoo，https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
- dbproxy：美团，https://github.com/Meituan-Dianping/DBProxy
- Cetus：网易乐得，https://github.com/Lede-Inc/cetus
- Amoeba：https://sourceforge.net/projects/amoeba/
- Cobar：阿里巴巴，Amoeba的升级版， https://github.com/alibaba/cobar
- Mycat：基于Cobar http://www.mycat.io/ (原网站)
http://www.mycat.org.cn/
https://github.com/MyCATApache/Mycat-Server - ProxySQL：https://proxysql.com/
5.2.4 Mycat
5.2.4.1 Mycat 介绍
在整个IT系统架构中，数据库是非常重要，通常又是访问压力较大的一个服务，除了在程序开发的本身做优化，如：SQL语句优化、代码优化，数据库的处理本身优化也是非常重要的。主从、热备、分表分库等都是系统发展迟早会遇到的技术问题问题。Mycat是一个广受好评的数据库中间件，已经在很多产品上进行使用了。
Mycat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理（类似于Mysql Proxy），用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。
Mycat发展到目前的版本，已经不是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。而在最终用户看来，无论是那种存储方式，在MyCat里，都是一个传统的数据库表，支持标准的SQL语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发难度，提升开发速度
Mycat 可以简单概括为
- 一个彻底开源的，面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
Mycat 官网：http://www.mycat.org.cn/
Mycat 关键特性
- 支持SQL92标准
- 遵守MySQL 原生协议，跨语言，跨平台，跨数据库的通用中间件代理
- 基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群
- 支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster
- 基于Nio实现，有效管理线程，高并发问题
- 支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数,支持跨库分页
- 支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join
- 支持通过全局表，ER关系的分片策略，实现了高效的多表join查询
- 支持多租户方案
- 支持分布式事务（弱xa）
- 支持全局序列号，解决分布式下的主键生成问题
- 分片规则丰富，插件化开发，易于扩展
- 强大的web，命令行监控
- 支持前端作为mysq通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉
- 支持密码加密
- 支持服务降级
- 支持IP白名单
- 支持SQL黑名单、sql注入攻击拦截
- 支持分表（1.6）
- 集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）
为什么要用MyCat
这里要先搞清楚Mycat和MySQL的区别（Mycat的核心作用）。我们可以把上层看作是对下层的抽象，例如操作系统是对各类计算机硬件的抽象。那么我们什么时候需要抽象？假如只有一种硬件的时候，我们需要开发一个操作系统吗？再比如一个项目只需要一个人完成的时候不需要leader，但是当需要几十人完成时，就应该有一个管理者，发挥沟通协调等作用，而这个管理者对于他的上层来说就是对项目组的抽象
同样的，当我们的应用只需要一台数据库服务器的时候我们并不需要Mycat，而如果你需要分库甚至分表，这时候应用要面对很多个数据库的时候，这个时候就需要对数据库层做一个抽象，来管理这些数据库，而最上面的应用只需要面对一个数据库层的抽象或者说数据库中间件就好了，这就是Mycat的核心作用。所以可以这样理解：数据库是对底层存储文件的抽象，而Mycat是对数据库的抽象
Mycat工作原理
Mycat的原理中最重要的一个动词是”拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户
Mycat应用场景
Mycat适用的场景很丰富，以下是几个典型的应用场景
- 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
- 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
- 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化
- 报表系统，借助于Mycat的分表能力，处理大规模报表的统计
- 替代Hbase，分析大数据
- 作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择
- Mycat长期路线图
- 强化分布式数据库中间件的方面的功能，使之具备丰富的插件、强大的数据库智能优化功能、全面的系统监控能力、以及方便的数据运维工具，实现在线数据扩容、迁移等高级功能
- 进一步挺进大数据计算领域，深度结合Spark Stream和Storm等分布式实时流引擎，能够完成快速的巨表关联、排序、分组聚合等 OLAP方向的能力，并集成一些热门常用的实时分析算法，让工程师以及DBA们更容易用Mycat实现一些高级数据分析处理功能
- 不断强化Mycat开源社区的技术水平，吸引更多的IT技术专家，使得Mycat社区成为中国的Apache，并将Mycat推到Apache基金会，成为国内顶尖开源项目，最终能够让一部分志愿者成为专职的Mycat开发者，荣耀跟实力一起提升
Mycat不适合的应用场景
- 设计使用Mycat时有非分片字段查询，请慎重使用Mycat，可以考虑放弃！
- 设计使用Mycat时有分页排序，请慎重使用Mycat，可以考虑放弃！
- 设计使用Mycat时如果要进行表JOIN操作，要确保两个表的关联字段具有相同的数据分布，否则请慎重使用Mycat，可以考虑放弃！
- 设计使用Mycat时如果有分布式事务，得先看是否得保证事务得强一致性，否则请慎重使用Mycat，可以考虑放弃！
MyCat的高可用性：
需要注意: 在生产环境中, Mycat节点最好使用双节点, 即双机热备环境, 防止Mycat这一层出现单点故障.可以使用的高可用集群方式有:
- Keepalived+Mycat+Mysql
- Keepalived+LVS+Mycat+Mysql
- Keepalived+Haproxy+Mycat+Mysql
5.2.4.2 Mycat 安装
mycat安装目录结构：
- bin mycat命令，启动、重启、停止等
- catlet catlet为Mycat的一个扩展功能
- conf Mycat 配置信息,重点关注
- lib Mycat引用的jar包，Mycat是java开发的
- logs 日志文件，包括Mycat启动的日志和运行的日志
- version.txt mycat版本说明
logs目录:
- wrapper.log mycat启动日志
- mycat.log mycat详细工作日志
Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：
- server.xml Mycat软件本身相关的配置文件，设置账号、参数等
- schema.xml Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、节点控制
- rule.xml Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等
5.2.4.3 Mycat 主要配置文件说明
server.xml
存放Mycat软件本身相关的配置文件，比如：连接Mycat的用户，密码，数据库名称等
server.xml文件中配置的参数解释说明：
参数 说明user 用户配置节点name 客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。password 客户端登录MyCAT的密码schemas 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2privileges 配置用户针对表的增删改查的权限readOnly mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false
注意：
- server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息
- 逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！
- 这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！
schema.xml
是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的
schema.xml文件中配置的参数解释说明：
参数 说明
schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
dataNode 分片信息，也就是分库相关配置
dataHost 物理数据库，真正存储数据的数据库
配置说明
name属性唯一标识dataHost标签，供上层的标签使用。
maxCon属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数
minCon属性指定每个读写实例连接池的最小连接，初始化连接池的大小
每个节点的属性逐一说明
schema:属性 说明name 逻辑数据库名，与server.xml中的schema对应checkSQLschema 数据库前缀相关设置，这里为falsesqlMaxLimit select 时默认的limit，避免查询全表table属性 说明name 表名，物理数据库中表名dataNode 表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的nameprimaryKey 主键字段名，自动生成主键时需要设置autoIncrement 是否自增rule 分片规则名，具体规则下文rule详细介绍dataNode属性 说明name 节点名，与table中dataNode对应datahost 物理数据库名，与datahost中name对应database 物理数据库中数据库名dataHost属性 说明name 物理数据库名，与dataNode中dataHost对应balance 均衡负载的方式writeType 写入方式dbType 数据库类型heartbeat 心跳检测语句，注意语句结尾的分号要加
schema.xml文件中有三点需要注意：balance=”1″，writeType=”0″ ,switchType=”1″
schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。balance 属性负载均衡类型，目前的取值有 4 种：
- balance=”0″：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上,即读请求仅发送到writeHost上
- balance=”1″：一般用此模式，读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。即全部的readHost与stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1, S2 都参与 select 语句的负载均衡
- balance=”2″：读请求随机分发到当前dataHost内所有的writeHost和readHost上。即所有读操作都随机的在writeHost、 readhost 上分发
- balance=”3″：读请求随机分发到当前writeHost对应的readHost上。即所有读请求随机的分发到wiriterHost 对应的 readhost 执行, writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有
writeHost和readHost 标签
这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。
唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。
在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，
那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的
检测到，并切换到备用的writeHost上去
注意：
Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的
主从复制将数据复制到readhost
5.2.4.4 ★★实战案例：利用 Mycat 实现 MySQL 的读写分离★★
5.2.4.4.1、在10.0.0.11和10.0.0.8上配置主从复制
##主从复制
##主服务器
#启用二进制日志
mysql8.0默认开启我就不配置了
#设置serverid
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=11
#重启服务
[root@master ~]#systemctl restart mysqld.service
#查看二进制日志节点
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#创建用户主从复制的账号
mysql> create user lala@'10.0.0.%' identified by 'sunxiang';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to lala@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
##从服务器
#设置serverid
[root@slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=8
#重启服务
[root@slave ~]#systemctl restart mysqld.service
#获取一下范例
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.11',
MASTER_USER='lala',
MASTER_PASSWORD='sunxiang',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=156,
MASTER_CONNECT_RETRY=10;
#配置主从复制
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.11',
-> MASTER_USER='lala',
-> MASTER_PASSWORD='sunxiang',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000002',
-> MASTER_LOG_POS=156,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.02 sec)
#开启主从复制
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#查看一下主从状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.11
Master_User: lala
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 673
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 838
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
##主服务
#导入一个数据库测试一下是否可以正常复制
[root@master ~]#mysql < hellodb_innodb.sql
##从服务器
#查看数据库信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
#主从服务ok
5.2.4.4.2、在MySQL代理服务器10.0.0.10安装mycat并启动
##Mycat
#下载Java
[root@mycat ~]# yum install -y java
#确认安装成功
[root@mycat ~]#java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)
#下载mycat
[root@Centos7 ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
#创建指定目录解压软件包
[root@mycat ~]# mkdir /mycat
[root@mycat ~]# tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C
#查看解压后的目录
[root@mycat ~]# ls /mycat/
mycat
[root@mycat ~]# tree /mycat/ -d
/mycat/
└── mycat
├── bin
├── catlet
├── conf
│ ├── zkconf
│ └── zkdownload
├── lib
└── logs
8 directories
[root@mycat ~]# ls /mycat/mycat/bin/
dataMigrate.sh mycat startup_nowrap.sh wrapper-linux-x86-32
init_zk_data.sh rehash.sh wrapper-linux-ppc-64 wrapper-linux-x86-64
#配置环境变量
[root@mycat ~]# echo 'PATH=/mycat/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]# . /etc/profile.d/mycat.sh
#查看一下端口，内存
[root@mycat ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
LISTEN 0 128 [::]:3306 [::]:*
LISTEN 0 128 [::]:111 [::]:*
[root@mycat ~]# free -h #内存太小了服务可能起不来
total used free shared buff/cache available
Mem: 972M 535M 97M 1.9M 338M 287M
Swap: 2.0G 22M 2.0G
[root@mycat ~]# free -h
total used free shared buff/cache available
Mem: 2.9G 622M 2.0G 1.9M 319M 2.3G
Swap: 2.0G 21M 2.0G
#启动服务
[root@mycat ~]# mycat start
Starting Mycat-server...
#查看日志观察是否启动成功
[root@mycat ~]# tail -f /mycat/mycat/logs/wrapper.log
STATUS | wrapper | 2021/12/14 23:48:31 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/12/14 23:48:31 | Launching a JVM...
INFO | jvm 1 | 2021/12/14 23:48:32 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/12/14 23:48:32 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/12/14 23:48:32 |
INFO | jvm 1 | 2021/12/14 23:48:33 | MyCAT Server startup successfully. see logs in logs/mycat.log #启动成功
#再次查看端口 此时发现多了很多端口，其中的8066为mycat的默认端口
[root@mycat ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 50 [::]:39007 [::]:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 100 [::]:8066 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
LISTEN 0 100 [::]:9066 [::]:*
LISTEN 0 50 [::]:37674 [::]:*
LISTEN 0 128 [::]:3306 [::]:*
LISTEN 0 128 [::]:111 [::]:*
#客户端尝试连接（默认密码为123456）
root@ubuntu1804:~# mysql -uroot -p'123456' -h'10.0.0.10' -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
#查看一下数据 有一个数据库
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
#进入数据库（因为还没有连接真正的数据库所以卡在这里了）
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
5.2.4.4.3、在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@mycat ~]# vim /mycat/mycat/conf/server.xml
#修改连接端口号默认为8066，修改为3306（注意此行默认被注释掉的所以要删掉注释符）
<property name="serverPort">3306</property> <property name="managerPort">9066</property>
#修改root帐号密码（默认为123456修改为sunxiang）
<user name="root" defaultAccount="true">
<property name="password">sunxiang</property>
#可选项（数据库名）
<user name="root" defaultAccount="true">
<property name="password">sunxiang</property>
<property name="schemas">TESTDB</property> #默认为TESTDB
<property name="defaultSchema">TESTDB</property>
5.2.4.4.4、修改schema.xml实现读写分离策略
[root@mycat ~]# vim /mycat/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="***false***" sqlMaxLimit="100" randomDataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="***mycat***" /> #其中mycat表示后端服务器实际的数据库名称
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="***1***"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
***<writeHost host="hostM1" url="10.0.0.11:3306" user="root"
password="123456">***
***<readHost host="host2" url="10.0.0.8:3306" user="root"
password="123456"/>***
</writeHost>
</dataHost>
</mycat:schema>
#以上***部分表示原配置文件中需要修改的内容
#注意大小写
#最终文件内容
[root@mycat ~]# vim /mycat/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.11:3306" user="root" password="123456">
<readHost host="host2" url="10.0.0.8:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
#重启mycat
[root@mycat ~]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
#查看日志确认是否启动
[root@mycat ~]# tail /mycat/mycat/logs/wrapper.log
INFO | jvm 1 | 2021/12/14 23:48:32 |
INFO | jvm 1 | 2021/12/14 23:48:33 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2021/12/15 00:44:20 | TERM trapped. Shutting down.
STATUS | wrapper | 2021/12/15 00:44:21 | <-- Wrapper Stopped
STATUS | wrapper | 2021/12/15 00:44:22 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/12/15 00:44:22 | Launching a JVM...
INFO | jvm 1 | 2021/12/15 00:44:23 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/12/15 00:44:23 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/12/15 00:44:23 |
INFO | jvm 1 | 2021/12/15 00:44:24 | MyCAT Server startup successfully. see logs in logs/mycat.log
#查看端口是否成为3306
[root@mycat ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 50 [::]:33176 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 50 [::]:39839 [::]:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 100 [::]:3306 [::]:*
LISTEN 0 100 [::]:9066 [::]:*
LISTEN 0 128 [::]:111 [::]:*
#客户端连接（此时使用密码为sunxiang 端口为mysql的默认端口3306）
root@ubuntu1804:~# mysql -uroot -p'sunxiang' -h'10.0.0.10'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.11为主库，10.0.0.8为从库
注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！
schema.xml配置图防止看不清格式
5.2.4.4.5、在后端主服务器创建用户并对mycat授权
mysql> create user root@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to root@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
5.2.4.4.6、在Mycat服务器上连接并测试
root@ubuntu1804:~# mysql -uroot -p'sunxiang' -h'10.0.0.10'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
#此时为读，我们定义的读写分离，读服务器的服务id为8
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 8 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 8 |
+-------------+
1 row in set (0.00 sec)
5.2.4.4.7、通过通用日志确认实现读写分离
在mysql中查看通用日志
show variables like 'general_log'; #查看日志是否开启
set global general_log=on; #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置
在主和从服务器分别启用通用日志，查看读写分离
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
general_log=ON
[root@centos8 ~]#systemctl restart mysql
[root@centos8 ~]#tail -f /var/lib/mysql/centos8.log
5.2.4.4.8、分别停止主从节点
#停止从节点，MyCAT自动调度读请求至主节点
[root@slave ~]#systemctl stop mariadb
root@ubuntu1804:~# mysql -uroot -p'sunxiang' -h'10.0.0.10'
MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 11 |
+-------------+
1 row in set (0.00 sec)
MySQL [(none)]>
#停止主节点，MyCAT不会自动调度写请求至从节点
[root@master ~]#systemctl stop mariadb
root@ubuntu1804:~# mysql -uroot -p'sunxiang' -h'10.0.0.10'
MySQL [TESTDB]> insert teachers values(5,'test',30,'M');
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
5.2.4.4.9、MyCAT对后端服务器的健康性检查方法select user()
#开启通用日志
[root@master ~]#mysql
mysql> set global general_log=1;
[root@slave ~]#mysql
mysql> set global general_log=1;
#查看通用日志
[root@master ~]#tail -f /var/lib/mysql/master.log
/usr/libexec/mysqld, Version: 8.0.17 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2021-12-15T08:52:57.086198Z 17 Query select user()
2021-12-15T08:53:07.086340Z 24 Query select user()
2021-12-15T08:53:17.086095Z 16 Query select user()
2021-12-15T08:53:27.086629Z 18 Query select user()
[root@slave ~]#tail -f /var/lib/mysql/slave.log
/usr/libexec/mysqld, Version: 8.0.17 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2021-12-15T08:46:01.437376Z 10 Query select user()
2021-12-15T08:46:11.438172Z 11 Query select user()
2021-12-15T08:46:21.437458Z 12 Query select user()
2021-12-15T08:46:31.437742Z 13 Query select user()
5.2.5ProxySQL
5.2.5.1 ProxySQL 介绍
ProxySQL： MySQL中间件
两个版本：官方版和percona版，percona版是基于官方版基础上修改
C++语言开发，轻量级但性能优异，支持处理千亿级数据
具有中间件所需的绝大多数功能，包括：
- 多种方式的读/写分离
- 定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
- 缓存查询结果
- 后端节点监控
官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki
5.2.5.2 ProxySQL 安装
基于YUM仓库安装
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
#基于RPM下载安装：https://github.com/sysown/proxysql/releases
yum install proxysql
ProxySQL组成
- 服务脚本：/etc/init.d/proxysql
- 配置文件：/etc/proxysql.cnf
- 主程序：/usr/bin/proxysql
- 基于SQLITE的数据库文件：/var/lib/proxysql/
启动ProxySQL：
service proxysql start
启动后会监听两个默认端口
- 6032：ProxySQL的管理端口
- 6033：ProxySQL对外提供服务的端口
连接ProxySQL的管理端口
使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：
mysql -uadmin -padmin -P6032 -h127.0.0.1
数据库说明：
- main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示
- proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载
- disk 是持久化到硬盘的配置，sqlite数据文件
- stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等
- monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
说明：
- 在main和monitor数据库中的表， runtime开头的是运行时的配置，不能修改，只能修改非runtime表
- 修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效
- 执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中
- global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
- 参考: https://github.com/sysown/proxysql/wiki/Global-variables
5.2.5.3 实战案例：利用 ProxySQL 实现读写分离
- 环境准备：
准备三台主机，一台ProxySQL服务器:192.168.8.7，另外两台主机实现主从复制192.168.8.17,27
注意：slave节点需要设置read_only=1 -
安装ProxySQL，并向ProxySQL中添加MySQL节点，以下操作不需要use main也可成功
MySQL> show tables; MySQL > select * from sqlite_master where name='mysql_servers'\G MySQL > select * from mysql_servers; MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.17',3306); MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.27',3306); MySQL > load mysql servers to runtime; MySQL > save mysql servers to disk; - 添加监控后端节点的用户，连接每个节点的read_only值来自动调整主从节点是属于读组还是写组
#在master上执行 MySQL> grant replication client on *.* to monitor@'192.168.8.%' identified by 'sunxiang'; #ProxySQL上配置监控 MySQL [(none)]> set mysql-monitor_username='monitor'; MySQL [(none)]> set mysql-monitor_password='sunxiang'; #加载到RUNTIME，并保存到disk MySQL [(none)]> load mysql variables to runtime; MySQL [(none)]> save mysql variables to disk; - 查看监控
监控模块的指标保存在monitor库的log表中
#查看监控连接是否正常的 (对connect指标的监控），如果connect_error的结果为NULL则表示正常
MySQL> select * from mysql_server_connect_log;
#查看监控心跳信息 (对ping指标的监控)：
MySQL> select * from mysql_server_ping_log;
-
设置分组信息
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：
writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20MySQL> insert into mysql_replication_hostgroups values(10,20,"test"); #将mysql_replication_hostgroups表的修改加载到RUNTIME生效 MySQL> load mysql servers to runtime; MySQL> save mysql servers to disk; #Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组 MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+--------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+--------------+------+--------+--------+ | 10 | 192.168.8.17 | 3306 | ONLINE | 1 | | 20 | 192.168.8.27 | 3306 | ONLINE | 1 | - 配置访问数据库的SQL 用户
#在master节点上创建访问用户 MySQL> grant all on *.* to sqluser@'192.168.8.%' identified by 'sunxiang'; #在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库 MySQL> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10); MySQL> load mysql users to runtime; MySQL> save mysql users to disk; #使用sqluser用户测试是否能路由到默认的10写组实现读、写数据 mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'select @@server_id' mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'create database testdb' mysql -usqluser -psunxiang testdb -P6033 -h127.0.0.1 -e 'create table t(id int)' - 在proxysql上配置路由规则，实现读写分离
与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持
插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT…FOR UPDATE它会申请写锁，应路由到10的写组MySQL> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); MySQL> load mysql query rules to runtime; MySQL> save mysql query rules to disk; #注意：因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id - 测试ProxySQL
#读操作是否路由给20的读组 mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'select @@server_id' #测试写操作，以事务方式进行测试 mysql -usqluser -psunxiang -P6033 -h127.0.0.1 \ -e 'start transaction;select @@server_id;commit;select @@server_id' mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)' mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'select id from testdb.t' #路由的信息：查询stats库中的stats_mysql_query_digest表 MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; #测试读操作是否路由给20的读组 mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'select @@server_id' #测试写操作，以事务方式进行测试 mysql -usqluser -psunxiang -P6033 -h127.0.0.1 \ -e 'start transaction;select @@server_id;commit;select @@server_id' mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)' mysql -usqluser -psunxiang -P6033 -h127.0.0.1 -e 'select id from testdb.t' #路由的信息：查询stats库中的stats_mysql_query_digest表 MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;