MySQL中间件之Mycat部署与实现读写分离

最新推荐文章于 2024-07-05 18:06:04 发布

不想敲代码的运维

最新推荐文章于 2024-07-05 18:06:04 发布

阅读量344

点赞数

分类专栏：数据库DBA 文章标签：数据库 linux mysql

本文链接：https://blog.csdn.net/qq_16942727/article/details/104416819

版权

数据库DBA 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Mycat介绍：

mycat管理端口：9066
mycat数据端口：8066
Mycat背后是阿里曾经开源的知名产品——Cobar，Cobar是阿里巴巴研发的关系型数据的分布式处理系统
从定义和分类来看，它是一个开源的分布式数据库系统，是一个实现了MySQL协议的server，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生（Native）协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或其他数据库里。
支持SQL 92标准
支持MySQL集群，可以作为Proxy使用
支持JDBC连接多数据库
支持各种数据库，包括MySQL、mongodb、oracle、sqlserver、hive、db2、postgresql。
支持galera for mysql进群，percona-cluster或者mariadb-cluster，提供高可用性数据分片集群
自动故障切换，高可用性
支持读写分离，支持MySQL双主多从，以及一主多从的模式
支持全局表，数据自动分片到多个节点，用户高效表关联查询
支持独有的基于E-R关系的分片策略，实现了高效的表关联查询
支持一致性Hash分片，有效解决分片扩容难题
多平台支持，部署和实施简单
支持Catelet开发，类似数据库存储过程，用户跨分片复杂SQL的人工智能编码实现
支持NIO与AIO两种网络通信机制，windows下建议用AIO，Linux下建议NIO
支持MySQL存储过程调用
以插件方式支持SQL拦截和改写
支持自增长主键、支持oracle的Sequence机制
Mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
图上，orders表被分为三个分片datenode（简称dn），这三个分片是分布在两台MySQL Server上（DataHost），即DataNode=database@datahost方式，因此可以用一台到N台服务器来分片，分片规则为（sharding rule）典型的字符串枚举分片规则，一个规则的定义是分片字段（sharding column）+分片函数（rule function），这里的分片字段为prov而分片函数为字符串枚举方式。
当Mycat收到一个SQL时，会先解析这个SQL，查找涉及到的表，然后看此表的定义，如果有分片规则，则获取到SQL里分片字段的值，并分配分片函数，得到该SQL对应的分片列表，然后将SQL发往这些分片去执行，最后收集和处理所有分片返回的结果数据，并输出到客户端。以select * from orders where prov=？语句为例，查到prov=wuhan，按照分片函数，wuhan返回dn1，于是SQL就发给了MySQL1，去取DB1上的查询结果，并返回给用户。

Mycat功能介绍：

遵守MySQL原生协议，跨语言，跨数据库的通用中间件代理
基于心跳的自动故障切换，支持读写分离，支持MOL——双主多从，以及一主多从
有效管理数据源连接，基于数据分库
基于NIO实现，有效管理线程，高并发问题
支持数据的多片自动路由与聚合，支持sum、count、max等常用的聚合函数
支持2表join，甚至基于callet的多表joine
支持通过全局表，ER关系的分片策略，实现了高效的多表join查询
支持多租户方案
支持分布式事务
支持全局序列号，解决分布式下的主键生成问题
分片规则丰富，插件化开发，易于扩展
强大的web，命令行监控
支持前端作为通用代理，后端JDBC方式支持oracle、db2、sql server等
集群基于zookeeper管理，在线升级，扩容，智能优化，大数据处理

Mycat应用场景：

单纯的读写分离
分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化
报表系统，借助于Mycat的分表能力，处理大规模报表的统计

Mycat分片规则

分表分库虽然能解决大表对数据库系统的压力，但它并不是万能的，也有一些不利之处，因此首要问题是，分不分库，分哪些库，什么规则分，分多少分片。
总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何where语句的查询SQL，会便利所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

原则一：

能不分就不分，1000万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解决性能问题。

原则二：

分片数量尽量少，分片尽量均匀分布在多个DataHost上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据的一个分片的结果，只在必要的时候进行扩容，增加分片数量。

原则三：

分片规则需要慎重选择。分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容。

原则四：

尽量不要在一个事务的SQL跨域多个分片，分布式事务一直是个不好处理的问题。

原则五：

查询条件尽量优化，尽量避免select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

数据拆分分类：

垂直拆分数据：

一个库中有三张表，垂直拆分就是把三张表拆到三个库中
缺点：user表访问量大，就会导致压力不均匀

水平拆分数据：
user表中的数据再次进行拆分到多个库中，效果很好

MySQL-Mycat安装及读写分离

MySQL版本为5.7
mycat-01 192.168.10.144 （本次使用多实例数据库3307、3309主从模式）

安装JAVA环境

[root@mycat-01 ~]# yum -y install java-openjdk

下载mycat软件包

[root@mycat-01 opt]# wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

解压

[root@mycat-01 opt]# tar -zxvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz -C /usr/local/

配置环境变量

[root@mycat-01 ~]# echo 'export PATH=/usr/local/mycat/bin:$PATH' >> /etc/profile
[root@mycat-01 ~]# source /etc/profile

启动Mycat

[root@mycat-01 ~]# mycat start
Starting Mycat-server...
[root@mycat-01 ~]# netstat -ntpl |grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      12087/java

进入Mycat程序（默认用户root，密码123456前提服务器上有mysql客户端）

[root@mycat-01 ~]# mysql -uroot -p123456 -h127.0.0.1 -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.6.1-release-20181031195535 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

日志文件说明

[root@mycat-01 ~]# ls /usr/local/mycat/logs/
mycat.log  #Mycat工作日志
mycat.pid  #pid文件
switch.log  
wrapper.log #Mycat启动相关日志

配置文件说明

/usr/local/mycat/conf/目录
schema.xml    主配置文件（读写分离、高可用、分表、节点控制）
server.xml    mycat软件本身相关的配置
rule.xml      分片规则配置文件（分片规则列表、使用方法）

配置读写分离

备份并编写配置文件

[root@mycat-01 ~]# \cp /usr/local/mycat/conf/schema.xml /usr/local/mycat/conf/schema.xml_bak
[root@mycat-01 ~]# vim /usr/local/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="olda" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="nat
ive" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="mycat-01" url="192.168.10.144:3307" user="root" password="000000">
        <readHost host="mycat-01" url="192.168.10.144:3309" user="root" password="000000" />
    </writeHost>
    </dataHost>
</mycat:schema>

配置文件说明

<?xml version="1.0"?>
#版本表示
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
#mycat官网
<mycat:schema xmlns:mycat="http://io.mycat/">
#定义mycat工作库TESTDB是mycat的一个逻辑库，可以自定义，但是涉及另外一个配置文件。不检查库。查看TESTDB库数据时，>
定义最大显示100行。TESTDB逻辑库对应dn1这个数据节点
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
#定义mycat工作库配置结束
</schema>
#定义名为dn1这个数据节点的逻辑主机地址。和真实的后端数据库中的giao库名
    <dataNode name="dn1" dataHost="localhost1" database="olda" />
#定义localhost1这个逻辑主机的最大并发连接数。最小连接（类似预热数据，提前连接好，节省临时连接的消耗。balance默认
为1，为负载均衡到所有从库读操作。writeType默认为0，写操作只分配到第一个writeHost数据库实例组。指定后端数据库软件
类型。dbDriver是驱动。switchType=“1”是主库宕机，自动切换到其他预备主数据库实例组
#dbDriver（使用native的话，因为这个值执行的是二进制的mysql协议，所以可以使用mysql和mariadb，其它类型的则需要使用
JDBC驱动来支持。）
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="nat
ive" switchType="1">
#mycat监控后端mysql是否可用，走的心跳信息
        <heartbeat>select user()</heartbeat>
#定义localhost1逻辑主机中的真实主机，这是逻辑主机中的一个真实“可写”节点
    <writeHost host="mycat-01" url="192.168.10.144:3307" user="root" password="000000">
#定义localhost1逻辑主机中的真实主机，这是逻辑主机中的一个真实“可读”节点
        <readHost host="mycat-01" url="192.168.10.144:3309" user="root" password="000000" />
#定义真实可写主机节点的结束行
    </writeHost>
#定义逻辑主机的结束行
    </dataHost>
#定义mycat逻辑TESTDB库的结束行
</mycat:schema>

创建root用户

mycat启动前创建用户完毕，自动同步密码到从节点

[root@mycat-01 ~]# mysql -uroot -p000000 -S /usr/local/mysql-3307/mysql.sock -e "grant all on *.* to 'root'@'192.168.10.%' identified by '000000';"

Mycat创建库

[root@mycat-01 ~]# mysql -uroot -p000000 -S /usr/local/mysql-3307/mysql.sock -e "create database giao;"

重启mycat

[root@mycat-01 ~]# mycat restart

查看mycat读写状态：

[root@mycat-01 ~]# mysql -uroot -p123456 -P9066 -h127.0.0.1 -e "show @@datasource;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME     | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+----------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | mycat-01 | mysql | 192.168.10.144 | 3307 | W    |      0 |   10 | 1000 |      12 |         0 |          0 |
| dn1      | mycat-01 | mysql | 192.168.10.144 | 3309 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
+----------+----------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+

查看当前可用数据节点的主机

[root@mycat-01 ~]# cat /usr/local/mycat/conf/dnindex.properties 
#update
#Fri Feb 21 05:15:42 EST 2020
localhost1=0

mycat创建表

mycat 8066端口执行

# 如果是MySQL-8.0版本之后，要在mycat节点的my.cnf配置文件中的 client标签下加入 default-auth=mysql_native_password
mysql -uroot -p123456 -h127.0.0.1 -P8066 -e "use TESTDB;CREATE TABLE stu(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生学号',name VARCHAR(50) NOT NULL COMMENT '学生姓名',age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '学生年龄',gender ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '学 生.别',birthday DATETIME NOT NULL DEFAULT NOW() COMMENT '学生入学时间');"

插入500条数据

mycat 8006端口执行

#!/usr/bin/bash
count=0
for i in `seq 500`;do
  even=`expr $i % 2`
  if [ $even -eq 0 ];then
    gender="男"
  else
    gender="女"
  fi
  count=$((a++))
  age=`tr -cd "0-9" < /dev/urandom |head -c 2`
  mysql -uroot -p123456 -h127.0.0.1 -P8066 -e "use TESTDB;insert into stu(name,age,gender) values('olda_$count','$age','$gender');"
done

执行查询语句

mycat 8006端口执行

[root@mycat-01 ~]# mysql -uroot -p123456 -h127.0.0.1 -P8066 -e "use TESTDB;select * from stu where id='7';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | birthday            |
+----+--------+-----+--------+---------------------+
|  7 | olda_5 |  35 | 男     | 2019-02-12 19:34:52 |
+----+--------+-----+--------+---------------------+