关于数据库:MyCat学习笔记

1、数据库中间件：

是一类连贯软件组件和利用的计算机软件，以便于软件各部件之间的沟通。例子：Tomcat，web 中间件。数据库中间件：连贯 java 应用程序和数据库

2、为什么要用 Mycat？

Java 与数据库紧耦合
高访问量高并发对数据库的压力
读写申请数据不统一

mysql 单表瓶颈 1000w 数据, 单库瓶颈 5000w 数据

数据库中间件比照:

Mycat 的官网: http://www.mycat.io/

Mycat 能干什么:

读写拆散
数据分片 垂直拆分（分库）、程度拆分（分表）、垂直 + 程度拆分（分库分表)
多数据源整合

原理:

Mycat 的原理中最重要的一个动词是“拦挡”，它拦挡了用户发送过去的 SQL 语句，首先对 SQL 语句做了一些特定的剖析：如分片剖析、路由剖析、读写拆散剖析、缓存剖析等，而后将此 SQL 发往后端的实在数据库，并将返回的后果做适当的解决，最终再返回给用户

1、解压后即可应用 解压缩文件拷贝到 linux 下 /usr/local/ 2、三个配置文件

schema.xml：定义逻辑库，表、分片节点等内容
rule.xml：定义分片规定
server.xml：定义用户以及零碎相干变量，如端口等

3、批改配置文件 server.xml 批改用户信息，与 MySQL 辨别

<!– 用户名 –>
<user name=”root”>
<property name=”password”>123456</property> <!– 明码 –>
<property name=”schemas”>TESTDB</property> <!– 治理的库 –>
</user>

schemas: 数据库名，这里会和 schema.xml 中的配置关联，多个用逗号离开，例如须要这个用户须要治理两个数据库 db1,db2，则配置 db1,db2

4. 批改配置文件 schema.xml

删除 <schema> 标签间的表信息配置 dataNode="dn1"，<dataNode> 标签只留一个，<dataHost>标签只留一个，<writeHost>,<readHost>只留一对

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

<!– 逻辑库 –>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<!– 逻辑表 –>
</schema>

<!– 数据节点 database 填写实在数据库 –>
<dataNode name=”dn1″ dataHost=”host1″ database=”testdb” />

<!– 数据主机 –>
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”0″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>
<!– 心跳检测 –>
<heartbeat>select user()</heartbeat>
<!– 写主机 –>
<writeHost host=”hostM1″ url=”192.168.107.132:3306″ user=”root”
password=”000000″>
<!– 读主机 –>
<readHost host=”hostS2″ url=”192.168.107.108:3306″ user=”hzy” password=”000000″/>
</writeHost>

</dataHost>

</mycat:schema>

5. 验证数据库拜访状况

Mycat 作为数据库中间件要和数据库部署在不同机器上，所以要验证近程拜访状况。

mysql -uhzy -p000000 -h 192.168.107.108 -P 3306
mysql -uroot -p000000 -h 192.168.107.132 -P 3306

grant all privileges on . to root@’ 短少的 host’ identified by ‘000000’;

6. 启动 mycat

控制台启动：去 mycat/bin 目录下执行 ./mycat console
后盾启动：去 mycat/bin 目录下 ./mycat start

为了能第一工夫看到启动日志，不便定位问题，咱们抉择控制台启动。

7. 登录 mycat

登录后盾治理窗口

此登录形式用于治理保护 Mycat

mysql -umycat -p123456 -P 9066 -h 192.168.140.128

show database;
show @@help;

登录数据窗口

此登录形式用于通过 Mycat 查问数据，咱们抉择这种形式拜访 Mycat

mysql -uroot -p123456 -P 8066 -h 192.168.107.132

咱们通过 Mycat 和 MySQL 的 主从复制 配合搭建数据库的 读写拆散，实现 MySQL 的高可用性。咱们将搭建：一主一从、双主双从两种读写拆散模式。

mysql 主从复制原理:

与 redis 不同的是,redis 从机会把主机的数据从头到尾复制, 而 mysql 从机只会从接入点开始复制; 并且会产生屡次 IO 有延时问题

批改配置文件：
vim /etc/my.cnf

server-id=1

log-bin=mysql-bin

binlog-ignore-db=mysql
binlog-ignore-db=information_schema

binlog-do-db= 须要复制的主数据库名字

binlog_format=STATEMENT

binlog 的三种模式:https://blog.csdn.net/vhomes/article/details/8082734

vim /etc/my.cnf

server-id=2

relay-log=mysql-relay

重启服务:systemctl restart mysqld
确认防火墙已敞开:systemctl status firewalld
在主机 MySQL 里执行受权命令: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

如果报错:Your password does not satisfy the current policy requirements, 请执行:

set global validate_password_policy=0;
set global validate_password_length=4;

查看主机状态:

show master status;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	438	testdb	mysql,information_schema

1 row in set (0.01 sec)

记录下 File 和 Position 的值, 执行完此步骤后不要再操作主服务器 MySQL，避免主服务器状态值变动

在从机上配置须要复制的主机

CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;

start slave;

show slave statusG

例子:

CHANGE MASTER TO MASTER_HOST=’192.168.107.132′,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin.000003′,
MASTER_LOG_POS=154;

如果之前配置过主从复制, 须要先重置stop slave;, reset master;

接着启动从服务器复制性能 start slave;

查看从服务器状态 show slave statusG(不要加分号)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

主机新建库(my.cnf 中规定的库)、新建表、insert 记录，从机复制

<!– 写主机 –>

    <writeHost host="hostM1" url="192.168.107.132:3306" user="root"
               password="000000">

<!– 读主机 –>

    <readHost host="hostS2" url="192.168.107.108:3306" user="hzy" password="000000"/>

启动 mycat

在写主机数据库表 mytbl 中插入带零碎变量数据，造成主从数据不统一 INSERT INTO mytbl VALUES(2,@@hostname);

应用 mycat 查问, 能够看到 mycat 操作的是主机数据

起因是咱们没有指定数据库拜访的 负载策略balance

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 不累赘读压力

双主双从选 1, 单主单从或多从选 3

批改 schema.xml

<!– 数据主机 –>

<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

一个主机 m1 用于解决所有写申请，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写申请，m1、m2 互为备机。

Master1 配置

批改配置文件：vim /etc/my.cnf

server-id=1

log-bin=mysql-bin

binlog-ignore-db=mysql
binlog-ignore-db=information_schema

binlog-do-db= 须要复制的主数据库名字

binlog_format=STATEMENT

log-slave-updates

auto-increment-increment=2

auto-increment-offset=1

Master2 配置

批改配置文件：vim /etc/my.cnf

server-id=3

log-bin=mysql-bin

binlog-ignore-db=mysql
binlog-ignore-db=information_schema

binlog-do-db= 须要复制的主数据库名字

binlog_format=STATEMENT

log-slave-updates

auto-increment-increment=2

65535 auto-increment-offset=2

留神:auto-increment-offset 不能够是反复的

Slave1 配置

server-id=2

relay-log=mysql-relay

Slave2 配置

server-id=4

relay-log=mysql-relay

双主机、双从机重启 mysql 服务
主机从机都敞开防火墙
在两台主机上建设帐户并受权 slave

GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘123123’;

查看两台主机的状态show master status;, 记录 binlogid 和接入点
设置两台从机的主机,Slava1 复制 Master1，Slava2 复制 Master2
两台主机互备,Master2 复制 Master1，Master1 复制 Master2

stop slave;
reset master;

CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;

start slave;

show slave statusG

批改 schema.xml

<heartbeat>select user()</heartbeat>                
<!-- can have multi write hosts -->                 
<writeHost host="hostM1" url="192.168.140.128:3306" user="root"                                    password="123123">                         
    <!-- can have multi read hosts -->                         
    <readHost host="hostS1" url="192.168.140.127:3306" user="root" password="123123"/>                 
</writeHost>    

<writeHost host="hostM2" url="192.168.140.126:3306" user="root"                                    password="123123">                         
    <!-- can have multi read hosts -->                         
    <readHost host="hostS2" url="192.168.140.125:3306" user="root" password="123123"/>                 
</writeHost>

</dataHost>

验证主从复制:Master1 主机新建库、新建表、insert 记录，Master2 和从机复制
验证读写拆散:

在写主机 Master1 数据库表 mytbl 中插入带零碎变量数据，造成主从数据不统一

INSERT INTO mytbl VALUES(3,@@hostname);,

在 Mycat 里查问 mytbl 表, 能够看到查问语句在 Master2（host81）、Slava1（host80）、Slava2（host82）主从三个主机间切换;

进行数据库 Master1 在 Mycat 里插入数据仍然胜利，Master2 主动切换为写主机

依照业务将表进行分类（或者 Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分

分库的准则：有严密关联关系的表应该在一个库里，互相没有关联关系的表能够分到不同的库里, 因为分库的表不能够关联查问

CREATE TABLE customer(

id INT AUTO_INCREMENT, 
NAME VARCHAR(200), 
PRIMARY KEY(id)

);

CREATE TABLE orders(

id INT AUTO_INCREMENT, 
order_type INT, 
customer_id INT, 
amount DECIMAL(10,2),     
PRIMARY KEY(id)

);

CREATE TABLE orders_detail(

id INT AUTO_INCREMENT,     
detail VARCHAR(2000),     
order_id INT,     
PRIMARY KEY(id)

);

CREATE TABLE dict_order_type(

id INT AUTO_INCREMENT,     
order_type VARCHAR(200),     
PRIMARY KEY(id)

);

客户表分在一个数据库，另外三张都须要关联查问，分在另外一个数据库。

1. 批改 schema 配置文件

<!– 逻辑库 –>

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <!-- 配置 customer 表调配到 dn2 -->
    <table name="customer" dataNode="dn2" ></table> 
</schema>

<!-- 数据节点 -->
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />

<!-- host1 -->
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    <!-- 心跳检测 -->
    <heartbeat>select user()</heartbeat>
    <!-- 写主机 -->
    <writeHost host="hostM1" url="192.168.107.132:3306" user="root"
               password="000000">
    </writeHost>
    
</dataHost>

<!-- host2 -->
<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="hostM1" url="192.168.107.108:3306" user="hzy"
               password="000000">
    </writeHost>
    
</dataHost>

通过 mycat 操作逻辑库默认执行到 dataNode="dn1", 咱们指定了customer 调配到dataNode="dn2"

balance="0"不设置读写拆散

2. 两台机器别离创立新的的数据库

CREATE DATABASE orders;

3. 启动 Mycat

./mycat console

4. 拜访 Mycat 进行分库

mysql -umycat -p123456 -h 192.168.140.128 -P 8066

依据表中的数据的逻辑关系依照某个字段的某种规定，将同一个表中的数据依照某种条件拆分到多台数据库（主机）下面，这种切分称之为数据的程度（横向）切分

MySQL 单表存储数据条数是有瓶颈的，单表白到 1000 万条数据就达到了瓶颈，会影响查问效率，须要进行程度拆分（分表）进行优化。例如：例子中的 orders、orders_detail 都曾经达到 600 万行数据，须要进行分表优化。

1. 分表字段 , 以 orders 表为例，能够依据不同自字段进行分表

id 主键或创立工夫:查问订单重视时效，历史订单被查问的次数少，如此分片会造成一个节点拜访多，一个拜访少，不均匀。
customer_id（客户 id）: 依据客户 id 去分，两个节点拜访均匀，一个客户的所有订单都在同一个节点

2. 批改 schema 配置文件

<!– 逻辑库 –>

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <!-- 配置 customer 表调配到 dn2 -->
    <table name="customer" dataNode="dn2" ></table>
    <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table> 
</schema>

orders表被划分到两个数据节点 dn1,dn2,mod_rule 分片规定名称

3.批改 rule 配置文件

定义 mod_rule 分片规定, 并指定规定实用字段为 customer_id，抉择分片算法 mod-long（对字段求模运算），customer_id 对两个节点求模，依据后果分片

<tableRule name="mod_rule">
        <rule>
            <columns>customer_id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
</tableRule>
<!-- 配置算法 mod-long 参数 count 为 2，两个节点 -->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- 多少个数据节点 -->
        <property name="count">2</property>
</function>

4. 测试分表

在数据节点 dn2 上建 orders 表
重启 Mycat，让配置失效
拜访 Mycat 实现分片
在 mycat 里向 orders 表插入数据，INSERT 字段不能省略 INSERT INTO

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 表数据，分表胜利

Orders 订单表曾经进行分表操作了，和它关联的 orders_detail 订单详情表如何进行 join 查问。

orders_detail 也要进行分片操作。Join 的原理如下图：

Mycat 借鉴了 NewSQL 畛域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提出了 Table Group 的概念，其将子表的存储地位依赖于主表，并且物理上紧邻寄存，因而彻底解决了 JION 的效率和性能问题，依据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录寄存在同一个数据分片上

批改 schema.xml 配置文件

配置 orders 的子表 orders_detail, 应用外键 order_id 进行分片, 这样与 orders 表的数据分布严密

在 dn2 创立 orders_detail 表 重启 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;

在分片的状况下，当业务表因为规模而进行分片当前，业务表与这些从属的字典表之间的关联，就成了比拟辣手的问题，思考到字典表具备以下几个个性：① 变动不频繁 ② 数据量总体变动不大 ③ 数据规模不大，很少有超过数十万条记录

鉴于此，Mycat 定义了一种非凡的表，称之为“全局表”，全局表具备以下个性：

全局表的插入、更新操作会实时在所有节点上执行，放弃各个分片的数据一致性
全局表的查问操作，只从一个节点获取
全局表能够跟任何一个表进行 JOIN 操作

将字典表或者合乎字典表个性的一些表定义为全局表，则从另外一个方面，很好的解决了数据 JOIN 的难题。通过全局表 + 基于 E-R 关系的分片策略，Mycat 能够满足 80% 以上的企业应用开发

批改 schema.xml 配置文件

<!– 定义全局字典表 –>

在 dn2 创立 dict_order_type 表 重启 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 中查问表数据

罕用分片规定
——

### 取模

此规定为对分片字段求摸运算。也是程度分表最罕用规定。

### 分片枚举

通过在配置文件中配置可能的枚举 id，本人配置分片，本规定实用于特定的场景，比方有些业务须要依照省份或区县来做保留，而全国省份区县固定的，这类业务应用本条规定。

* 批改 schema.xml 配置文件

<!– 分片枚举 –>

* 批改 rule.xml 配置文件

<tableRule name=”sharding_by_intfile”>
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
…
<function name=”hash-int” class=”io.mycat.route.function.PartitionByFileMap”>
<property name=”mapFile”>partition-hash-int.txt</property> <property name=”type”>1</property>
<property name=”defaultNode”>0</property>
</function>

columns：分片字段，algorithm：分片函数

mapFile：标识配置文件名称，type：分片字段类型 0 为 int 型、非 0 为 String

defaultNode：默认节点: 小于 0 示意不设置默认节点，大于等于 0 示意设置默认节点，设置默认节点如果碰到不辨认的枚举值，就让它路由到默认节点，如不设置不辨认就报错

* 批改 partition-hash-int.txt 配置文件

110=0
120=1
#110 走 dn1, 120 走 dn2

* 重启 Mycat

* 拜访 Mycat 创立表

#订单归属区域信息表
CREATE TABLE orders_ware_info (
id INT AUTO_INCREMENT comment ‘ 编号 ’,
order_id INT comment ‘ 订单编号 ’,
address VARCHAR(200) comment ‘ 地址 ’,
areacode VARCHAR(20) comment ‘ 区域编号 ’,
PRIMARY KEY(id)
);

插入数据

INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,’ 北京 ’,’110′); INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,’ 天津 ’,’120′);

### 范畴约定

此分片实用于，提前布局好分片字段某个范畴属于哪个分片。

* 批改 schema.xml 配置文件

* 批改 rule.xml 配置文件

<tableRule name=”auto_sharding_long”>
<rule>
<columns>order_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
…
<function name=”rang-long” class=”io.mycat.route.function.AutoPartitionByLong”>
<property name=”mapFile”>autopartition-long.txt</property> <property name=”defaultNode”>0</property>
</function>

columns：分片字段，algorithm：分片函数 mapFile：标识配置文件名称

* 批改 autopartition-long.txt 配置文件

0-102=0
103-200=1

* 重启 Mycat

* 拜访 Mycat 创立表

# 领取信息表
CREATE TABLE payment_info (
id INT AUTO_INCREMENT comment ‘ 编号 ’,
order_id INT comment ‘ 订单编号 ’,
payment_status INT comment ‘ 领取状态 ’,
PRIMARY KEY(id)
);

* 插入数据

INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3,103,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,104,1);

* 查问 Mycat、dn1、dn2 能够看到数据分片成果

### 日期 (天) 分片

* 批改 schema.xml 配置文件

<!– 日期分片 –>

* 批改 rule.xml 配置文件

<tableRule name=”sharding_by_date”>
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
…
<function name=”shardingByDate” class=”io.mycat.route.function.PartitionByDate”>
<property name=”dateFormat”>yyyy-MM-dd</property>
<property name=”sBeginDate”>2019-01-01</property>
<property name=”sEndDate”>2019-01-04</property>
<property name=”sPartionDay”>2</property>
</function>

columns：分片字段，algorithm：分片函数 dateFormat：日期格局 sBeginDate：开始日期

sEndDate：完结日期, 则代表数据达到了这个日期的分片后循环从开始分片插入

sPartionDay：分区天数，即默认从开始日期算起，分隔 2 天一个分区

* 重启 Mycat

* 拜访 Mycat 创立表

* 用户信息表

CREATE TABLE login_info (
id INT AUTO_INCREMENT comment ‘ 编号 ’,
user_id INT comment ‘ 用户编号 ’,
login_date date comment ‘ 登录日期 ’,
PRIMARY KEY(id)
);

INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,’2019-01-01′);
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,’2019-01-02′);
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,’2019-01-03′);
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,’2019-01-04′);
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,’2019-01-05′);
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,’2019-01-06′);

* 查问 Mycat、dn1、dn2 能够看到数据分片成果

全局序列
—-

在实现分库分表的状况下，数据库自增主键已无奈保障自增主键的全局惟一。为此，Mycat 提供了全局 sequence，并且提供了蕴含本地配置和数据库配置等多种实现形式

1、本地文件 此形式 Mycat 将 sequence 配置到文件中，当应用到 sequence 中的配置后，Mycat 会更新 classpath 中的 sequence_conf.properties 文件中 sequence 以后的值。

* 长处：本地加载，读取速度较快

* 毛病：抗危险能力差，Mycat 所在主机宕机后，无奈读取本地文件。

2、数据库形式 利用数据库一个表来进行计数累加。然而并不是每次生成序列都读写数据库，这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中，这样大部分读写序列都是在内存中实现的。如果内存中的号段用完了 Mycat 会再向数据库要一次。

问：那如果 Mycat 解体了，那内存中的序列岂不是都没了？是的。如果是这样，那么 Mycat 启动后会向数据库申请新的号段，原有号段会弃用。也就是说如果 Mycat 重启，那么损失是以后的号段没用完的号码，然而不会因而呈现主键反复

* 全局序列表

#在 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;

* 创立全局序列所需函数

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 $$ D
ELIMITER ;

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 ;

* 初始化序列表记录

INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES (‘ORDERS’, 400000, 100);

* 批改 Mycat 配置

批改sequence_db_conf.properties

#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1

意思是 ORDERS 这个序列在 dn1 这个节点上，具体 dn1 节点是哪台机子，请参考 schema.xml

* 批改 server.xml

全局序列类型：0- 本地文件，1- 数据库形式，2- 工夫戳形式。此处应该批改成 1。
title: MyCat 学习笔记 # 题目
date: 2019/10/26 16:23:46 # 工夫
categories: # 分类
– 数据库
tags: # 标签
– 数据库中间件
– 分库分表

Mycat 数据库中间件
===========

1、数据库中间件：

是一类连贯软件组件和利用的计算机软件，以便于软件各部件之间的沟通。例子：Tomcat，web 中间件。数据库中间件：连贯 java 应用程序和数据库

<!–more–>

2、为什么要用 Mycat？

* Java 与数据库紧耦合

* 高访问量高并发对数据库的压力

* 读写申请数据不统一

mysql 单表瓶颈 1000w 数据, 单库瓶颈 5000w 数据

数据库中间件比照:

Mycat 的官网: http://www.mycat.io/

Mycat 能干什么:

* 读写拆散

* 数据分片 垂直拆分（分库）、程度拆分（分表）、垂直 + 程度拆分（分库分表)

* 多数据源整合 原理:

装置启动
—-

1、解压后即可应用 解压缩文件拷贝到 linux 下 /usr/local/ 2、三个配置文件

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

* rule.xml：定义分片规定

* server.xml：定义用户以及零碎相干变量，如端口等

3、批改配置文件 server.xml 批改用户信息，与 MySQL 辨别

<!– 用户名 –>
<user name=”root”>
<property name=”password”>123456</property> <!– 明码 –>
<property name=”schemas”>TESTDB</property> <!– 治理的库 –>
</user>

schemas: 数据库名，这里会和 schema.xml 中的配置关联，多个用逗号离开，例如须要这个用户须要治理两个数据库 db1,db2，则配置 db1,db2

4. 批改配置文件 schema.xml

删除 <schema> 标签间的表信息配置 dataNode="dn1"，<dataNode> 标签只留一个，<dataHost>标签只留一个，<writeHost>,<readHost>只留一对

</dataHost>

</mycat:schema>

5. 验证数据库拜访状况

Mycat 作为数据库中间件要和数据库部署在不同机器上，所以要验证近程拜访状况。

mysql -uhzy -p000000 -h 192.168.107.108 -P 3306
mysql -uroot -p000000 -h 192.168.107.132 -P 3306

# 如近程拜访报错，请建对应用户
grant all privileges on . to root@’ 短少的 host’ identified by ‘000000’;

6. 启动 mycat

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

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

为了能第一工夫看到启动日志，不便定位问题，咱们抉择控制台启动。

7. 登录 mycat

* 登录后盾治理窗口

此登录形式用于治理保护 Mycat

mysql -umycat -p123456 -P 9066 -h 192.168.140.128

#常用命令如下：
show database;
show @@help;

* 登录数据窗口

此登录形式用于通过 Mycat 查问数据，咱们抉择这种形式拜访 Mycat

mysql -uroot -p123456 -P 8066 -h 192.168.107.132

读写拆散
—-

咱们通过 Mycat 和 MySQL 的 主从复制 配合搭建数据库的 读写拆散，实现 MySQL 的高可用性。咱们将搭建：一主一从、双主双从两种读写拆散模式。

mysql 主从复制原理:

与 redis 不同的是,redis 从机会把主机的数据从头到尾复制, 而 mysql 从机只会从接入点开始复制; 并且会产生屡次 IO 有延时问题

### 主机配置

> binlog 的三种模式:https://blog.csdn.net/vhomes/article/details/8082734

### 从机配置

vim /etc/my.cnf
#从服务器惟一 ID
server-id=2
#启用中继日志
relay-log=mysql-relay

* 重启服务:systemctl restart mysqld

* 确认防火墙已敞开:systemctl status firewalld

* 在主机 MySQL 里执行受权命令: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

如果报错:Your password does not satisfy the current policy requirements, 请执行:

set global validate_password_policy=0;
set global validate_password_length=4;

* 查看主机状态:

记录下 File 和 Position 的值, 执行完此步骤后不要再操作主服务器 MySQL，避免主服务器状态值变动

* 在从机上配置须要复制的主机

# 复制主机的命令
CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;
#启动从服务器复制性能
start slave;
#查看从服务器状态
show slave statusG

例子:

CHANGE MASTER TO MASTER_HOST=’192.168.107.132′,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin.000003′,
MASTER_LOG_POS=154;

如果之前配置过主从复制, 须要先重置stop slave;, reset master;

接着启动从服务器复制性能 start slave;

查看从服务器状态 show slave statusG(不要加分号)

# 这两项尾 Yes 即为胜利
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#如果失败可查看 Error 项
Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

主机新建库(my.cnf 中规定的库)、新建表、insert 记录，从机复制

### 一主一从

<!– 写主机 –>
<writeHost host=”hostM1″ url=”192.168.107.132:3306″ user=”root”
password=”000000″>
<!– 读主机 –>
<readHost host=”hostS2″ url=”192.168.107.108:3306″ user=”hzy” password=”000000″/>

启动 mycat

在写主机数据库表 mytbl 中插入带零碎变量数据，造成主从数据不统一 INSERT INTO mytbl VALUES(2,@@hostname);

应用 mycat 查问, 能够看到 mycat 操作的是主机数据

起因是咱们没有指定数据库拜访的 负载策略balance

* 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 不累赘读压力

双主双从选 1, 单主单从或多从选 3

批改 schema.xml

<!– 数据主机 –>
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”3″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>

### 双主双从

Master1 配置

批改配置文件：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
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#示意自增长字段每次递增的量，指字段一次递增多少，其默认值是 1，取值范畴是 1 .. 65535
auto-increment-increment=2
# 示意自增长字段从哪个数开始，指自增字段的起始值，他的取值范畴是 1 .. 65535
auto-increment-offset=1

Master2 配置

批改配置文件：vim /etc/my.cnf
#主服务器惟一 ID
server-id=3
#启用二进制日志
log-bin=mysql-bin
#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置须要复制的数据库
binlog-do-db= 须要复制的主数据库名字
#设置 logbin 格局
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
#示意自增长字段每次递增的量，指自增字段的起始值，其默认值是 1，取值范畴是 1 .. 65535
auto-increment-increment=2
# 示意自增长字段从哪个数开始，指自增字段的起始值，他的取值范畴是 1 .. 6553
65535 auto-increment-offset=2

留神:auto-increment-offset 不能够是反复的

Slave1 配置

# 从服务器惟一 ID
server-id=2
#启用中继日志
relay-log=mysql-relay

Slave2 配置

# 从服务器惟一 ID
server-id=4
#启用中继日志
relay-log=mysql-relay

* 双主机、双从机重启 mysql 服务

* 主机从机都敞开防火墙

* 在两台主机上建设帐户并受权 slave

# 在主机 MySQL 里执行受权命令
GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘123123’;

* 查看两台主机的状态show master status;, 记录 binlogid 和接入点

* 设置两台从机的主机,Slava1 复制 Master1，Slava2 复制 Master2

* 两台主机互备,Master2 复制 Master1，Master1 复制 Master2

stop slave;
reset master;
#复制主机的命令
CHANGE MASTER TO MASTER_HOST=’ 主机的 IP 地址 ’,
MASTER_USER=’slave’,
MASTER_PASSWORD=’123123′,
MASTER_LOG_FILE=’mysql-bin. 具体数字 ’,
MASTER_LOG_POS= 具体值;
#启动从服务器复制性能
start slave;
#查看从服务器状态
show slave statusG

* 批改 schema.xml

<dataNode name=”dn1″ dataHost=”host1″ database=”testdb” />
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”1″ writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″ >
<heartbeat>select user()</heartbeat>
<!– can have multi write hosts –>
<writeHost host=”hostM1″ url=”192.168.140.128:3306″ user=”root” password=”123123″>
<!– can have multi read hosts –>
<readHost host=”hostS1″ url=”192.168.140.127:3306″ user=”root” password=”123123″/>
</writeHost>

<writeHost host=”hostM2″ url=”192.168.140.126:3306″ user=”root” password=”123123″>
<!– can have multi read hosts –>
<readHost host=”hostS2″ url=”192.168.140.125:3306″ user=”root” password=”123123″/>
</writeHost>
</dataHost>
#balance=”1″: 全副的 readHost 与 stand by writeHost 参加 select 语句的负载平衡。
#writeType=”0″: 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个
#writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties。
#switchType=”1″: 1 默认值，主动切换。
# -1 示意不主动切换
# 2 基于 MySQL 主从同步的状态决定是否切换。

* 验证主从复制:Master1 主机新建库、新建表、insert 记录，Master2 和从机复制

* 验证读写拆散:

在写主机 Master1 数据库表 mytbl 中插入带零碎变量数据，造成主从数据不统一

INSERT INTO mytbl VALUES(3,@@hostname);,

在 Mycat 里查问 mytbl 表, 能够看到查问语句在 Master2（host81）、Slava1（host80）、Slava2（host82）主从三个主机间切换;

进行数据库 Master1 在 Mycat 里插入数据仍然胜利，Master2 主动切换为写主机

垂直拆分——分库
——–

依照业务将表进行分类（或者 Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分

分库的准则：有严密关联关系的表应该在一个库里，互相没有关联关系的表能够分到不同的库里, 因为分库的表不能够关联查问

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

客户表分在一个数据库，另外三张都须要关联查问，分在另外一个数据库。

1. 批改 schema 配置文件

<!– 逻辑库 –>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<!– 配置 customer 表调配到 dn2 –>

</schema>

<!– 数据节点 –>
<dataNode name=”dn1″ dataHost=”host1″ database=”orders” />
<dataNode name=”dn2″ dataHost=”host2″ database=”orders” />

<!– host1 –>
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”0″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>
<!– 心跳检测 –>
<heartbeat>select user()</heartbeat>
<!– 写主机 –>
<writeHost host=”hostM1″ url=”192.168.107.132:3306″ user=”root”
password=”000000″>
</writeHost>

</dataHost>

<!– host2 –>
<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=”hostM1″ url=”192.168.107.108:3306″ user=”hzy”
password=”000000″>
</writeHost>

</dataHost>

通过 mycat 操作逻辑库默认执行到 dataNode="dn1", 咱们指定了customer 调配到dataNode="dn2"

balance="0"不设置读写拆散

2. 两台机器别离创立新的的数据库

CREATE DATABASE orders;

3. 启动 Mycat

./mycat console

4. 拜访 Mycat 进行分库

mysql -umycat -p123456 -h 192.168.140.128 -P 8066
#切换到 TESTDB
#创立 4 张表
#查看表信息，能够看到胜利分库

程度拆分——分表
——–

1. 分表字段 , 以 orders 表为例，能够依据不同自字段进行分表

* id 主键或创立工夫:查问订单重视时效，历史订单被查问的次数少，如此分片会造成一个节点拜访多，一个拜访少，不均匀。

* customer_id（客户 id）: 依据客户 id 去分，两个节点拜访均匀，一个客户的所有订单都在同一个节点

2. 批改 schema 配置文件

<!– 逻辑库 –>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<!– 配置 customer 表调配到 dn2 –>

</schema>

orders表被划分到两个数据节点 dn1,dn2,mod_rule 分片规定名称

3.批改 rule 配置文件

定义 mod_rule 分片规定, 并指定规定实用字段为 customer_id，抉择分片算法 mod-long（对字段求模运算），customer_id 对两个节点求模，依据后果分片

<tableRule name=”mod_rule”>
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!– 配置算法 mod-long 参数 count 为 2，两个节点 –>
<function name=”mod-long” class=”io.mycat.route.function.PartitionByMod”>
<!– 多少个数据节点 –>
<property name=”count”>2</property>
</function>

4. 测试分表

* 在数据节点 dn2 上建 orders 表

* 重启 Mycat，让配置失效

* 拜访 Mycat 实现分片

* 在 mycat 里向 orders 表插入数据，INSERT 字段不能省略 INSERT INTO

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 表数据，分表胜利

Mycat 的分片“join”
—————-

Orders 订单表曾经进行分表操作了，和它关联的 orders_detail 订单详情表如何进行 join 查问。

orders_detail 也要进行分片操作。Join 的原理如下图：

### ER 表

批改 schema.xml 配置文件

配置 orders 的子表 orders_detail, 应用外键 order_id 进行分片, 这样与 orders 表的数据分布严密

在 dn2 创立 orders_detail 表 重启 Mycat 拜访 Mycat 向 orders_detail 表插入数据

在 mycat、dn1、dn2 中运行两个表 join 语句

Select o.*,od.detail from orders o
inner join orders_detail od on o.id=od.order_id;

鉴于此，Mycat 定义了一种非凡的表，称之为“全局表”，全局表具备以下个性：

全局表的插入、更新操作会实时在所有节点上执行，放弃各个分片的数据一致性
全局表的查问操作，只从一个节点获取
全局表能够跟任何一个表进行 JOIN 操作

批改 schema.xml 配置文件

<!– 定义全局字典表 –>

在 dn2 创立 dict_order_type 表 重启 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 中查问表数据

罕用分片规定
——

### 取模

此规定为对分片字段求摸运算。也是程度分表最罕用规定。

### 分片枚举

* 批改 schema.xml 配置文件

<!– 分片枚举 –>

* 批改 rule.xml 配置文件

columns：分片字段，algorithm：分片函数

mapFile：标识配置文件名称，type：分片字段类型 0 为 int 型、非 0 为 String

* 批改 partition-hash-int.txt 配置文件

110=0
120=1
#110 走 dn1, 120 走 dn2

* 重启 Mycat

* 拜访 Mycat 创立表

插入数据

INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,’ 北京 ’,’110′); INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,’ 天津 ’,’120′);

### 范畴约定

此分片实用于，提前布局好分片字段某个范畴属于哪个分片。

* 批改 schema.xml 配置文件

* 批改 rule.xml 配置文件

columns：分片字段，algorithm：分片函数 mapFile：标识配置文件名称

* 批改 autopartition-long.txt 配置文件

0-102=0
103-200=1

* 重启 Mycat

* 拜访 Mycat 创立表

* 插入数据

* 查问 Mycat、dn1、dn2 能够看到数据分片成果

### 日期 (天) 分片

* 批改 schema.xml 配置文件

<!– 日期分片 –>

* 批改 rule.xml 配置文件

columns：分片字段，algorithm：分片函数 dateFormat：日期格局 sBeginDate：开始日期

sEndDate：完结日期, 则代表数据达到了这个日期的分片后循环从开始分片插入

sPartionDay：分区天数，即默认从开始日期算起，分隔 2 天一个分区

* 重启 Mycat

* 拜访 Mycat 创立表

* 用户信息表

CREATE TABLE login_info (
id INT AUTO_INCREMENT comment ‘ 编号 ’,
user_id INT comment ‘ 用户编号 ’,
login_date date comment ‘ 登录日期 ’,
PRIMARY KEY(id)
);

* 查问 Mycat、dn1、dn2 能够看到数据分片成果

全局序列
—-

* 长处：本地加载，读取速度较快

* 毛病：抗危险能力差，Mycat 所在主机宕机后，无奈读取本地文件。

* 全局序列表

#在 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;

* 创立全局序列所需函数

* 初始化序列表记录

INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES (‘ORDERS’, 400000, 100);

* 批改 Mycat 配置

批改sequence_db_conf.properties

#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1

意思是 ORDERS 这个序列在 dn1 这个节点上，具体 dn1 节点是哪台机子，请参考 schema.xml

* 批改 server.xml

全局序列类型：0- 本地文件，1- 数据库形式，2- 工夫戳形式。此处应该批改成 1。

* 重启 Mycat

* 验证全局序列登录 Mycat，插入数据,id 应用 MYCATSEQ_ORDERS 全局序列表中的下一个值

insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);

* 重启 Mycat 后，再次插入数据，再查问

* 重启 Mycat

* 验证全局序列登录 Mycat，插入数据,id 应用 MYCATSEQ_ORDERS 全局序列表中的下一个值

insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);

* 重启 Mycat 后，再次插入数据，再查问