我爱海鲸

mysql学习之mycat分库、分表

我爱海鲸 2022-02-17 15:34:46 暂无标签

简介分库、分表等相关设置

1.1、 mycat是什么

数据库中间件,前身是阿里的 cobar

1.2 mycat做什么的

（1）读写分离

   （2）数据分片：
            垂直拆分、水平拆分
            垂直+水平拆分

（3）多数据源整合

1.3 MYCAT 原理

“拦截”：Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语
句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数
据库，并将返回的结果做适当的处理，最终再返回给用户。

这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用 mycat 还是 mysql。

1.4 安装启动

（1）解压缩文件拷贝到 linux 下 /usr/local/

   （2）三个配置文件
            schema.xml 定义逻辑库，表、分片节点等内容
            rule.xml 定义分片规则
            server.xml 定义用户以及系统相关变量，如端口等.

（3）启动前先修改 schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1"

slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM1" url="localhost:3306" user="root

password="123456">

</writeHost>

</dataHost>

</mycat:schema>

（4）再修改 server.xm

<property name="schemas">TESTDB</property>

</user>

（5）启动程序

控制台启动：去 mycat/bin 目录下 mycat console

后台启动：去mycat/bin 目录下 mycat start

（6）启动时可能出现报错

域名解析失败

用 vim 修改 /etc/hosts 文件,在 127.0.0.1 后面增加你的机器名

修改后重新启动网络服务

（7）后台管理窗口登录

mysql -u 用户名（mycat） -p 密码 -P9066 -h 主机 ip

#命令：

show database;

show @@help

（8）数据窗口登录

mysql -u 用户名（mycat） -p 密码 -P8066 -h 主机 ip

1.5 读写分离

（1）修改配置文件 schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

<dataHost name="host1" maxCon="1000" minCon="10" balance="2

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="hostm1" url="192.168.67.1:3306" user="root

password="123123">

<readHost host="hosts1" url="192.168.67.131:3306" user="root

password="123123">

</readHost>

</writeHost>

</dataHost>

</mycat:schema>

（2） Balance

负载均衡类型，目前的取值有 4 种

balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上

balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当
双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负
载均衡。

balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。

balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

（3）验证读写分离

#创建表

create table t_replica
( id int auto_increment , name varchar(200)
);
#分别在两个库下插入数据
insert into t_replica(name) values (@@hostname);
#然后再 mycat 下执行，可以查看读写情况
select * from t_replic

1.6 分库

（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)
);
select o.*,od.detail,d.order_type
from orders o
inner join orders_detail od on o.id =od.order_id
inner join dict_order_type d on o.order_type=d.id
where o.customer_id=xxxx

（2）修改配置文件 schema.xml

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

<dataHost name="host1" maxCon="1000" minCon=" 10" balance="2"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host= "hostm1" url="192.168.67.1:3306" user="root'

password="123123">

<readHost host="hosts1" url="192.168.67.131:3306" user="root"

password="123123">

</readHost>

</writeHost>

</dataHost>

<dataHost name="host2" maxCon="1000" minCon=" 10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="hostm2" url="192.168.67.1:3306" user="root"

password="123123">

</writeHost>

</dataHost>

</mycat:schema>

1.7 水平分表
（1）修改配置文件 schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</schema>

......

（2）修改配置文件 rule.xml

<rule>

<columns>customer_ id</columns>

</rule>

</tableRule>

</function>

（3）跨库 join ER 表

为了相关联的表的行尽量分在一个库下

修改配置文件 schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</table>

</schema>

......

（4）全局表

设定为全局的表，会直接复制给每个数据库一份，所有写操作也会同步给多个库。
所以全局表一般不能是大数据表或者更新频繁的表。一般是字典表或者系统表为宜。

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

</table>

</schema>

......

（5）建相关表

1.8 全局序列

1.8.1 本地文件

不推荐，存在宕机序列丢失问题。

1.8.2 数据库方式

（1）数据库序列方式原理

利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。
mycat 会预加载一部分号段到 mycat 的内存中，这样大部分读写序列都是在内存中完成的。如果内存中的号
段用完了 mycat 会再向数据库要一次。
如果 mycat 崩溃了，内存中的序列都没了，那么 mycat 启动后会向数据库申请新的号段，原有号段会弃用。
也就是说如果 mycat 重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复。

（2）建库序列脚本

在 win10 的 mysql 库里创建
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;

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;

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 ;

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 $$
DELIMITER;

SELECT * FROM MYCAT_SEQUENCE
TRUNCATE TABLE MYCAT_SEQUENCE
##增加要用的序列
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000, 100);

（3）修改 mycat 配置

sequence_db_conf.properties

vim sequence_db_conf.properties