框架高级课程系列之MyCat
1 入门概述
1.1 是什么
1.1.1 MyCat由来
Cobar属于阿里B2B事业群，始于2008年，在阿里服役3年多，接管3000+个MySQL数据库的schema,集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职，Cobar停止维护。
Mycat是开源社区在阿里cobar基础上进行二次开发，解决了cobar存在的问题，并且加入了许多新的功能在其中。
1.1.2 Mycat的官网
1.2 作用
1.2.1 读写分离
1.2.2 数据分片
垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）
1.2.3 多数据源整合
1.3 原理
Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用Mycat还是MySQL。
2 安装启动
2.1 安装
2.1.1 解压后即可使用
2.1.2 三个配置文件
①schema.xml：定义逻辑库，表、分片节点等内容
②rule.xml：定义分片规则
③server.xml：定义用户以及系统相关变量，如端口等
2.2 配置
2.2.1 修改配置文件server.xml
2.2.2 修改配置文件schema.xml
删除
| <?xml version=”1.0”?> <!DOCTYPE mycat:schema SYSTEM “schema.dtd”> <schema name=”TESTDB“ checkSQLschema=”false” sqlMaxLimit=”100” dataNode=”dn1”> </schema> <dataNode name=”dn1” dataHost=”host1” database=”mytestdb” /> <dataHost name=”host1” maxCon=”1000” minCon=”10” balance=”0” writeType=”0” dbType=”mysql” dbDriver=”native” switchType=”1” slaveThreshold=”100”> <heartbeat>select user()</heartbeat> </dataHost> |
|---|
2.2.3 验证数据库访问情况
启动mycat:
./mycat console
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log 表示启动成功！
Mycat作为数据库中间件要和数据库部署在不同机器上，所以要验证远程访问情况。
2.2.4 启动程序
①控制台启动 ：去mycat/bin 目录下执行 ./mycat console 推荐
②后台启动 ：去mycat/bin 目录下 ./mycat start
为了能第一时间看到启动日志，方便定位问题，我们选择①控制台启动。
2.3 登录
2.3.1 登录后台管理窗口
此登录方式用于管理维护Mycat
| mysql -umycat -p123456 -P 9066 -h 192.168.200.168 #常用命令如下： show database  Show @@help 查看帮助命令 |
|---|
2.3.2 登录数据窗口
此登录方式用于通过Mycat查询数据，我们选择这种方式访问Mycat
| mysql -umycat -p123456 -P 8066 -h 192.168.200.168 show databases; use TESTDB; show tables; select * from mytbl;#查询的哪个库？{应该是主机} 修改从机的数据，再次查询！ |
|---|
3 搭建读写分离
3.1 搭建MySQL数据库主从复制
4 MySQL主从复制原理
5 主机配置
| 修改配置文件：vim /etc/my.cnf #主服务器唯一ID server-id=1 #启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql binlog-ignore-db=information_schema #设置需要复制的数据库 binlog-do-db=需要复制的主数据库名字 #设置logbin格式 binlog_format=STATEMENT |
|---|
6 binlog日志三种格式
1.Statement：每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).
2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
3.Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。
7 从机配置
| 修改配置文件：vim /etc/my.cnf #从服务器唯一ID server-id=2 #启用中继日志 relay-log=mysql-relay |
|---|
8 主机、从机重启MySQL服务
9 主机从机都关闭防火墙
10 在主机上建立帐户并授权slave
| #在主机MySQL里执行授权命令 GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘123456’; flush privileges; #查询master的状态 show master status; #记录下File和Position的值 #执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化 |
|---|
11 在从机上配置需要复制的主机
| #复制主机的命令 CHANGE MASTER TO MASTER_HOST=’主机的IP地址’, MASTER_USER=’slave’, MASTER_PASSWORD=’123456’, MASTER_LOG_FILE=’mysql-bin.具体数字’,MASTER_LOG_POS=具体值; #启动从服务器复制功能 start slave; #查看从服务器状态 show slave status\G; #下面两个参数都是Yes，则说明主从配置成功！ # Slave_IO_Running: Yes # Slave_SQL_Running: Yes |
|---|
12 主机新建库、新建表、insert记录，从机复制
13 如何停止从服务复制功能
| stop slave; 功能说明：停止I/O 线程和SQL线程的操作。 |
|---|
14 如何重新配置主从
| 在从机上执行！ reset slave; 用于删除功能说明：SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件； 在主机上执行！ reset master; 功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作； |
|---|
3.2 修改Mycat的配置文件schema.xml
之前的配置已分配了读写主机，是否已实现读写分离？ | 默认：balance=”0” | | —- |
修改
的balance属性，通过此属性配置读写分离的类型 | 负载均衡类型，目前的取值有4 种：
（1）balance=”0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
（2）balance=”1”，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
（3）balance=”2”，所有读操作都随机的在 writeHost、readhost 上分发。
（4）balance=”3”，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力 | | —- |为了能看到读写分离的效果，把balance设置成2，会在两个主机间切换查询 (2只限于测试，生产环境请选择1或3) | …
writeType=”0” dbType=”mysql” dbDriver=”native” switchType=”1” slaveThreshold=”100”>
… | | —- |
3.3 重启Mycat
./mycat console
多次执行查询语句即可看到效果
select * from mytbl ;
15 垂直拆分——分库
一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同 的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：
15.1 如何划分
一个问题：在两台主机上的两个数据库中的表，能否关联查询？
答案：不可以关联查询。
分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。
| #客户表 rows:20万 CREATE TABLE customer( id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) ); #订单表 rows:600万 CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ); #订单详细表 rows:600万 CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); #订单状态字典表 rows:20条 CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ); |
|---|
以上四个表如何分库？客户表分在一个数据库，另外三张都需要关联查询，分在另外一个数据库。
15.2 实现分库
15.2.1 停止mycat，停止主从，修改schema配置文件
在从机上执行 mysql> stop slave;
| … … |
|---|
15.2.2 新增两个空白库
分库操作不是在原来的老数据库上进行操作，需要准备两台机器分别安装新的数据库
| #在数据节点dn1、dn2上分别创建数据库orders CREATE DATABASE orders; #四张表如何建立，建立在什么位置？ order 以及其他两张表建立dn1 ,customer dn2 |
|---|
15.2.3 启动Mycat
./mycat console |
|---|
15.2.4 访问Mycat进行分库
| #访问Mycat mysql -umycat -p123456 -h 192.168.200.168 -P 8066 #切换到TESTDB #创建4张表 执行上述创建表的sql 语句！ #查看表信息，可以看到成功分库   |
|---|
16 水平拆分——分表
相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中，如图： 
16.1 实现分表
16.1.1 选择要拆分的表
MySQL单表存储数据条数是有瓶颈的，单表达到500万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。
例如：例子中的orders、orders_detail都已经达到600万行数据，需要进行分表优化。
16.1.2 分表字段
以orders表为例，可以根据不同自字段进行分表
| 编号 | 分表字段 | 效果 |
|---|---|---|
| 1 | id,createtime （主键、或创建时间） |
查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。 |
| 2 | customer_id（客户id） | 根据客户id去分，两个节点访问平均，一个客户的所有订单都在同一个节点 |
16.1.3 停止mycat修改配置文件schema.xml
| #为orders表设置数据节点为dn1、dn2，并指定分片规则为mod_rule（自定义的名字） #如下图  |
|---|
16.1.4 停止mycat修改配置文件rule.xml
| #在rule配置文件里新增分片规则mod_rule，并指定规则适用字段为customer_id， #还有选择分片算法mod-long（对字段求模运算），customer_id对两个节点求模，根据结果分片 #新增配置算法mod-long参数count为2，两个节点 … 在原有的配置中将 3—->2即可！ |
|---|
16.1.5 在数据节点dn2上建orders表
| CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ); |
|---|
16.1.6 重启Mycat配置生效，将配置文件重新导入
16.1.7 访问Mycat灌数据实现分片
| #在mycat里向orders表插入数据，INSERT时字段不能省略 INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020); #在mycat、dn1、dn2中查看orders表数据，分表成功  |
|---|
16.2 Mycat 的分片“join”
Orders订单表已经进行分表操作了，和它关联的orders_detail订单详情表如何进行join查询。
我们要对orders_detail也要进行分片操作。Join的原理如下图：
16.2.1 E-R表
子表的记录与所关联的父表记录存放在同一个数据分片上
| #修改schema.xml配置文件 … … #在dn2创建orders_detail表 CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); #导入配置文件，重启Mycat #访问Mycat向orders_detail表插入数据 INSERT INTO orders_detail(id,detail,order_id) VALUES(1,’detail1’,1); INSERT INTO orders_detail(id,detail,order_id) VALUES(2,’detail1’,2); INSERT INTO orders_detail(id,detail,order_id) VALUES(3,’detail1’,3); INSERT INTO orders_detail(id,detail,order_id) VALUES(4,’detail1’,4); INSERT INTO orders_detail(id,detail,order_id) VALUES(5,’detail1’,5); INSERT INTO orders_detail(id,detail,order_id) VALUES(6,’detail1’,6); #在mycat、dn1、dn2中运行两个表join语句 select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;  |
|---|
16.2.2 全局表
在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：
- 变动不频繁
- 数据量总体变化不大
- 数据规模不大，很少有超过数十万条记录
鉴于此，Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：
- 全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性
- 全局表的查询操作，只从一个节点获取
- 全局表可以跟任何一个表进行 JOIN 操作
将字典表或者符合字典表特性的一些表定义为全局表，则从另外一个方面，很好的解决了数据 JOIN 的难题。通过全局表+基于 E-R 关系的分片策略，Mycat 可以满足 80%以上的企业应用开发!
| #修改schema.xml配置文件 … …  #在dn2创建dict_order_type表 CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ); #导入配置文件，重启Mycat #访问Mycat向dict_order_type表插入数据 INSERT INTO dict_order_type(id,order_type) VALUES(101,’type1’); INSERT INTO dict_order_type(id,order_type) VALUES(102,’type2’); #在Mycat、dn1、dn2中查询表数据  |
|---|
16.3 全局序列
在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。
为此，Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式
16.3.1 本地文件
此方式 Mycat 将 sequence 配置到文件中，当使用到 sequence 中的配置后，Mycat 会更下
classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。
/usr/local/mycat/conf/sequence_conf.properties
优点：本地加载，读取速度较快
缺点：抗风险能力差，Mycat所在主机宕机后，无法读取本地文件。
16.3.2 时间戳方式
全局序列ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) 换算成十进制为 18 位数的 long 类型，每毫秒可以并发 12 位二进制的累加。
优点：配置简单
缺点：18位ID过长
16.3.3 自主生成全局序列
可在java项目里自己生成全局序列，如下：
根据业务逻辑组合
可以利用 redis的单线程原子性 incr来生成序列
但，自主生成需要单独在工程中用java代码实现，增加了分布式项目的复杂性
16.3.4 数据库方式★
建库序列脚本 | #在dn1主机上创建全局序列表
CREATE TABLE MYCAT_SEQUENCE (
NAME VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 100,
PRIMARY KEY(NAME))
ENGINE=INNODB;
# 查询数据
SELECT FROM MYCAT_SEQUENCE;
#清空数据
TRUNCATE TABLE MYCAT_SEQUENCE;
# truncate : 表示直接情况所有数据，不留任何痕迹！使用回滚无效！
#创建全局序列所需函数：官方提供
DELIMITER
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval=”-999999999,null”;
SELECT CONCAT(CAST(current_value AS CHAR),”,”,CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END
DELIMITER
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END
DELIMITER
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END
#初始化序列表记录
INSERT INTO *MYCAT_SEQUENCE(NAME,current_value,increment) VALUES (‘ORDERS’, 400000,100);
|
| —- |修改Mycat配置 | #修改sequence_db_conf.properties
vim sequence_db_conf.properties
#意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml
#修改server.xml
vim server.xml
16行#全局序列类型：0-本地文件，1-数据库方式，2-时间戳方式。此处应该修改成1。
#重启Mycat | | —- |验证全局序列 | #登录Mycat，插入数据
insert into orders(id,amount,customer_id,order_type)
values(next value for MYCATSEQ_ORDERS,1000,101,102);
#查询数据
#重启Mycat后，再次插入数据，再查询(模拟Mycat备机上线)
|
| —- |
总结：
- 利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。
- Mycat会预加载一部分号段到Mycat的内存中，这样大部分读写序列都是在内存中完成的。
- 如果内存中的号段用完了 Mycat会再向数据库要一次。
- 问：如果Mycat崩溃了 ，内存中的序列岂不是都没了？
- 是的。如果是这样，那么Mycat启动后会向数据库申请新的号段，原有号段会弃用。
- 也就是说如果Mycat重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复。
若有收获，就点个赞吧
0 人点赞
