Linux-Mycat总结(Linux MYCAT summary)

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
————————

< strong > MYCAT can be summarized as < / strong >

  • A completely open source large database cluster for enterprise application development
  • Support transaction, acid and enhanced database that can replace MySQL
  • An enterprise database that can be regarded as a MySQL Cluster is used to replace the expensive Oracle cluster
  • A new SQL Server integrating memory cache technology, NoSQL technology and HDFS big data
  • A new generation of enterprise database products combining traditional database and new distributed data warehouse
  • A novel database middleware product

Mycat 官网::http://www.mycat.org.cn/

MYCAT < > key features

  • Support SQL92 standard
  • Comply with MySQL native protocol, cross language, cross platform and cross database general middleware agent
  • Heartbeat based automatic failover supports read-write separation, MySQL master-slave and Galera cluster
  • 支持Galera for MySQL集群,Percona Cluster或者MariaDB cluster
  • NiO based implementation, effective thread management, high concurrency problem
  • Support multi slice automatic routing and aggregation of data, support common aggregation functions such as sum, count and Max, and support cross database paging
  • Support any join within a single database, cross database 2 table join, and even multi table join based on cattlet
  • It supports the fragmentation strategy of global table and ER relationship, and realizes efficient multi table join query
  • Support multi tenant scheme
  • Support distributed transactions (weak XA)
  • Support global serial number and solve the problem of primary key generation under distributed
  • The fragmentation rules are rich, plug-in development, and easy to expand
  • Powerful web, command line monitoring
  • 支持前端作为mysq通用代理,后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉
  • Support password encryption
  • Support service degradation
  • Support IP whitelist
  • Support SQL blacklist and SQL injection attack interception
  • Support sub table (1.6)
  • The cluster is based on zookeeper management, online upgrade, capacity expansion, intelligent optimization and big data processing (development version 2.0)

< strong > why use MYCAT < / strong >

Here we need to make clear the difference between MYCAT and MySQL (the core role of MYCAT). We can regard the upper layer as the abstraction of the lower layer. For example, the operating system is the abstraction of all kinds of computer hardware. So when do we need abstraction? If there is only one kind of hardware, do we need to develop an operating system? For another example, when a project needs only one person to complete, there is no need for a leader, but when it needs dozens of people to complete, there should be a manager to play the role of communication and coordination, and this manager is the abstraction of the project team for his upper layer. Similarly, when our application only needs one database server, we don’t need MYCAT, and if you need to divide databases or even tables, At this time, when the application has to face many databases, it needs to make an abstraction of the database layer to manage these databases, while the top application only needs to face the abstraction of one database layer or database middleware, which is the core role of MYCAT. Therefore, it can be understood as follows: database is the abstraction of underlying storage files, while MYCAT is the abstraction of database

< strong > how MYCAT works < / strong >

One of the most important verbs in the principle of MYCAT is “intercept”. It intercepts the SQL statements sent by the user. First, it makes some specific analysis of the SQL statements: such as fragment analysis, routing analysis, read-write separation analysis, cache analysis, etc., then sends the SQL to the real database at the back end, processes the returned results appropriately, and finally returns them to the user

< strong > MYCAT application scenario < / strong >

MYCAT is applicable to a wide range of scenarios. The following are some typical application scenarios

  • Simple read-write separation. At this time, the configuration is the simplest. It supports read-write separation and master-slave switching
  • Sub table and sub database: for more than 10 million tables, it supports 100 billion single table segmentation at most
  • For multi tenant applications, each application has a library, but the application is only connected to MYCAT, so it does not transform the program itself to realize multi tenant
  • The report system can handle the statistics of large-scale reports with the help of the table splitting ability of MYCAT
  • Replace HBase and analyze big data
  • As a simple and effective scheme for real-time query of massive data, for example, 10 billion frequently queried records need to query the results within 3 seconds. In addition to the query based on primary key, there may also be range query or other attribute query. At this time, MYCAT may be the simplest and effective option
  • MYCAT long term Roadmap
  • Strengthen the functions of distributed database middleware, make it have rich plug-ins, powerful database intelligent optimization function, comprehensive system monitoring ability, and convenient data operation and maintenance tools, and realize advanced functions such as online data expansion and migration
  • Further advance into the field of big data computing, deeply combine with distributed real-time stream engines such as spark stream and storm, which can complete the ability of OLAP direction such as fast giant table Association, sorting and grouping aggregation, and integrate some popular and commonly used real-time analysis algorithms, making it easier for engineers and DBAs to realize some advanced data analysis and processing functions with MYCAT
  • Continuously strengthen the technical level of MYCAT open source community, attract more it technical experts, make MYCAT community become China’s Apache, and push MYCAT to the Apache foundation to become the top open source project in China. Finally, some volunteers can become full-time MYCAT developers, and their glory and strength can be improved together

< strong > MYCAT unsuitable application scenario < / strong >

  • When designing and using MYCAT, there are non fragment field queries. Please use MYCAT carefully and consider giving up!
  • There is paging sorting when designing and using MYCAT. Please use MYCAT carefully and consider giving up!
  • When designing and using MYCAT, if you want to join tables, you should ensure that the associated fields of the two tables have the same data distribution. Otherwise, please use MYCAT carefully and consider giving up!
  • When designing and using MYCAT, if there are distributed transactions, we must first see whether we need to ensure strong consistency of transactions. Otherwise, please use MYCAT carefully and consider giving up!

< strong > high availability of MYCAT: < / strong >

Note: in the production environment, the MYCAT node is best to use dual nodes, that is, dual machine hot standby environment, to prevent single point of failure in the MYCAT layer The high availability cluster methods that can be used are:

  • Keepalived+Mycat+Mysql
  • Keepalived+LVS+Mycat+Mysql
  • Keepalived+Haproxy+Mycat+Mysql