mysql高级-05-Mycat（实战分库、分表）

Mycat介绍

是什么

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

干什么

1、读写分离

2、数据分片 

• 垂直拆分
• 水平拆分
• 垂直+水平拆分
  

3、多数据源整合

 

原理

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

 

安装启动

三个文件：

schema.xml 定义逻辑库，表、分片节点等内容

rule.xml 定义分片规则

server.xml 定义用户以及系统相关变量，如端口等

 

第一步：将mycat压缩包上传到linux服务器并解压！（略）

 

第二步：修改server.xml 

 

第三步：修改schema.xml 

 

第四步：验证数据库访问情况

mysql -u root -h 192.168.40.112 -P 3306  -p

mysql -u root -h 192.168.40.111 -P 3306  -p

 

如果本机远程访问报错，请建对应用户

grant all privileges on *.* to root@'缺少的host' identified by '密码'

 

第五步：启动程序

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

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

启动时可能出现报错：

域名解析失败

1、用vim 修改 /etc/hosts文件

2、修改后重新启动网络服务

 

第六步：登录

数据窗口： mysql -u root -P 8066 -h 192.168.40.111 -p

 

后台管理窗口：mysql -u root -P 9066 -h 192.168.40.111 -p

 

注意：此时还未开启读写分离

 

第七步：读写分离 

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执行，writeHost不负担读压力。

 

修改sechema.xml -->重启mycat

 

 

此时读写分离已经完成！！！！！

 

分库

如何选择分库表

## 客户表 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 order_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)
)

因为客户表没有和其他表连接查询，可以选择将客户表分库。

 

修改schema.xml文件

 

在两台linux服务器上创建两个相同的数据库。 

create database order0516;

 

重启mycat

在111服务器上创建对应的四张表。

 

分表

修改schema.xml文件

修改rule.xml文件

 

因为之前客户表在111服务器，在112服务器上创建orders表，重启mycat

 

在mycat中的TESTDB中插入数据

insert into customer values(100,'zhang3');
INSERT INTO customer VALUES(101,'li4');

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);

 

此时，我们查看两个数据库中orders表中的数据，id分别为 1 3 5 , 2 4 6 

可以发现mycat进行了拦截，并将数据分别写入了111，112的数据库中。

此时，我们在mycat中查询orders表中的所有数据是乱序，order by id 即可。

 

下一步：我们在111的数据库中插入详情信息

insert into order_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO order_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO order_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO order_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO order_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO order_detail(id,detail,order_id) VALUES(6,'detail1',6);

我们在mycat中进行连表查询操作。

#删除订单详情表数据
delete from order_detail;

 

垮库join

E-R表：为了相关联的表的行尽量分在一个库下。

 

在112服务器创建订单详情表

#订单详情表 rows:600万
create table order_detail(
id int auto_increment,
detail varchar(2000),
order_id int,
primary key(id)
)

 

修改schema.xml配置文件

重启mycat，再次插入详情表数据，再关联查询对应订单详情数据

 

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

订单字典表就为全局表。所以接下来看一下相关配置。

 

修改schema.xml 配置文件。

重启mycat，在112mysql中创建字典表，并插入数据。

#订单状态字典表 rows:20 
create table dict_order_type(
id int auto_increment,
order_type varchar(200),
primary key(id)
)

#在mycat中插入字典表数据
insert into dict_order_type values(101,'type1');
insert into dict_order_type values(102,'type2');

 

查看111  112 服务器上的mysql中的字典表数据。

 

 

全局序列（仅供参考。。无实操）

三种方式

第一种：本地文件（不推荐）

第二种：数据库方式

 

1. 数据库序列方式原理
   
2. 建库序列脚本

   create table MYCAT_SEQUENCE(name varchar(20) not null,current_value int not null,increment int not null default 100,primary key(name)) engine=innodb;

   
   
   
   
    
3. 修改mycat配置
   
   
    
4. 插入语句
   

 

第三种：时间戳方式（比较长）