mycat高级应用
1. mycat的重要配置文件
cd /opt/mycat/conf
rule.xml 分片策略定义
schema.xml 主配置文件
server.xml mycat服务有关
log4j2.xml 记录日志有关
*.txt 分片策略使用的规则
2. 数据库用户的创建
sjk4
mysql -S /data/3307/mysql.sock
grant all on *.* to root@'192.168.80.%' identified by '123';
source /opt/world.sql
mysql -S /data/3308/mysql.sock
grant all on *.* to root@'192.168.80..%' identified by '123';
source /opt/world.sql
3. 主配置文件的结构介绍schema.xml
3.1 配置文件的基本格式
vim 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="sh1">
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "world" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.80.94:3307" user="root" password="123">
<readHost host="db2" url="192.168.80.94:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
说明：
3.2 参数：
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
3.2.1 balance 属性
负载均衡类型，目前的取值有3种：
| 数值 | 含义 |
|---|---|
| balance=“0” | 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上 |
| balance=“1” | 全部的readHost与standby writeHost参与select语句的负载均衡（当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参select语句的负载均衡） |
| balance=“2” | 所有读操作都随机的在writeHost、readhost上分发 |
3.2.2 writeType 属性
负载均衡类型，目前的取值有2种：
| 数值 | 含义 |
|---|---|
| writeType=“0” | 所有写操作发送到配置的第一个writeHost（第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties） |
| writeType=“1” | 所有写操作都随机的发送到配置的writeHost，但不推荐使用 |
3.2.3 switchType属性
| 取值 | 作用 |
|---|---|
| switchType=“1” | 默认值，自动切换 |
| switchType="-1" | 表示不自动切换 |
| switchType=“2” | 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 show slave status |
3.2.4 minCon maxCon
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
| maxCon=“1000” | 最大的并发连接数 |
|---|---|
| minCon="10 | mycat在启动之后，会在后端节点上自动开启的连接线程 |
3.2.5 tempReadHostAvailable=“1”
select user() 监测心跳
| tempReadHostAvailable=“1” | 这个一主一从时（1个writehost，1个readhost时），可以开启这个参数， 监测心跳 |
|---|---|
| <heartbeat>select user()</heartbeat> | 2个writehost，2个readhost时监测心跳,写在datahost的下一行即可 |
4. mycat实现1主1从的读写分离
4.1 配置文件
vim 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="sh1">
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "world" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.80.94:3307" user="root" password="123">
<readHost host="db2" url="192.168.80.94:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
4.2 启动测试
mycat start
mysql -uroot -p123456 -h 192.168.80.94 -P 8066
select @@server_id;
begin ;select @@server_id; commit;
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.01 sec)
mysql> begin ;select @@server_id; commit;
Query OK, 0 rows affected (0.02 sec)
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
4.3 报错解决
连接报错
mysql -uroot -p123456 -h 192.168.80.94 -P 8066
这个时候我们就可以查看
mycat console
查看报错的原因
例如我这个：
jvm 1 | at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:400)
jvm 1 | at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:327)
jvm 1 | at com.sun.org.apache.xerces.internal.impl.XMLScanner.reportFatalError(XMLScanner.java:1473)
jvm 1 | at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(XMLDocumentFr mentScannerImpl.java:1749)
就是配置文件schema.xml 的问题，检查配置文件重启
5. mycat高可用+读写分离
5.1 配置文件
<?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="sh1">
</schema>
<dataNode name="sh1" dataHost="yq" database= "world" />
<dataHost name="yq" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.80.94:3307" user="root" password="123">
<readHost host="db2" url="192.168.80.94:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.80.95:3307" user="root" password="123">
<readHost host="db4" url="192.168.80.95:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
5.2 说明：
第一个 writeHost: 192.168.80.94:3307 真正的写节点,负责写操作
第二个 writeHost: 192.168.80.95:3307 准备写节点,负责读,当 10.0.0.51:3307宕掉,会切换为真正的写节点
5.3 重启测试
mycat restart
mysql -uroot -p123456 -h 192.168.80.94 -P 8066
读的操作
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 19 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
1 row in set (0.00 sec)
写的操作：
mysql> begin ;select @@server_id; commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
6. 高级应用，垂直分表
6.1 什么是垂直分表呢？
架构：
垂直分表：
6.2 配置文件
<?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="sh1">
<table name="user" dataNode="sh1"/>
<table name="order_t" dataNode="sh2"/>
</schema>
<dataNode name="sh1" dataHost="yq1" database= "taobao" />
<dataNode name="sh2" dataHost="yq2" database= "taobao" />
<dataHost name="yq1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.80.94:3307" user="root" password="123">
<readHost host="db2" url="192.168.80.94:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.80.95:3307" user="root" password="123">
<readHost host="db4" url="192.168.80.95:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="yq2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.80.94:3308" user="root" password="123">
<readHost host="db2" url="192.168.80.94:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.80.95:3308" user="root" password="123">
<readHost host="db4" url="192.168.80.95:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
6.3 数据的准备
mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"
6.4 重启：mycat
mycat restart
mysql -uroot -p123456 -h 192.168.80.94 -P 8066
6.5 测试
mysql -uroot -p123456 -h 10.0.0.51 -P 8066
use TESTDB
insert into user(id ,name ) values(1,'a'),(2,'b');
commit;
insert into order_t(id ,name ) values(1,'a'),(2,'b');
commit;
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.07 sec)
6.6 查看数据是否成功
mysql -S /data/3307/mysql.sock
mysql> use taobao
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
3307这个主从上边只有user表
3308查看order_t
mysql -S /data/3308/mysql.sock
use taobao
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
+------------------+
1 row in set (0.00 sec)
mysql> select * from order_t;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)
这样我们就实现了垂直分离
7. mycat 水平分表
7.1 水平分表的介绍
一个表的数据量特别的大，我们就可以将数据进行分布式的存储，达到优化访问
7.2 分片的策略
分片策略：
范围 range 800w 1-400w 400w01-800w
取模 mod 取余数
枚举
哈希 hash
时间 流水
基本的流程：
7.3 范围分片 auto-sharding-long
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
配置文件
vim schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
下边添加
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
既然规则、我们就查找rule.xml
vim rule.xml
找到：auto-sharding-long
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
下一步找到：rang-long函数
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
下一步就是配置：autopartition-long.txt
范围分布的规则文件
范围分布的规则文件 autopartition-long.txt
vim autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
配置文件的解释
0-500M=0 0到500m行在定义的datanode 0就代表第一个，也就是下边的ssh1
500M-1000M=1 1就对应下班的ssh2
这是配置文件里边的定义的datanode
<dataNode name="sh1" dataHost="yq1" database= "taobao" />
<dataNode name="sh2" dataHost="yq2" database= "taobao" />
实验一下：
vim autopartition-long.txt
1-10=0
10-20=1
表的创建：
mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat，测试
mycat restart
mysql -uroot -p123456 -h 192.168.80.94 -P 8066
insert into t3(id,name) values(1,'a');
insert into t3(id,name) values(2,'b');
insert into t3(id,name) values(3,'c');
insert into t3(id,name) values(10,'d');
insert into t3(id,name) values(11,'aa');
insert into t3(id,name) values(12,'bb');
insert into t3(id,name) values(13,'cc');
insert into t3(id,name) values(14,'dd');
insert into t3(id,name) values(20,'dd');
mycat管理端查看
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 10 | d |
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
| 20 | dd |
+----+------+
9 rows in set (0.08 sec)
查看：
mysql -S /data/3307/mysql.sock
mysql> use taobao
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 10 | d |
+----+------+
4 rows in set (0.01 sec)
mysql -S /data/3308/mysql.sock
mysql> use taobao
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> use taobao
Database changed
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
| 20 | dd |
+----+------+
5 rows in set (0.00 sec)
说明水平拆分成功
7.4 取模分片（mod-long）
取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点
配置文件
vim schema.xml
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />
rule.xml文件
vim rule.xml
===========================================================================
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
============================================================================
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
====================================
有几个数据节点，我们这里两个
<property name="count">2</property>
测试环境
mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
测试
重启mycat
mycat restart
测试：
mysql -uroot -p123456 -h10.0.0.51 -P8066
use TESTDB
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');
分别登录后端节点查询数据
mysql -S /data/3307/mysql.sock -e "select * from taobao.t4;"
mysql -S /data/3308/mysql.sock -e "select * from taobao.t4;"
[root@sjk4 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.t4;"
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
+----+------+
[root@sjk4 conf]# mysql -S /data/3308/mysql.sock -e "select * from taobao.t4;"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
+----+------+
7. 5 枚举分片
t5 表
id name telnum
1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555
配置文件
vim schema.xml
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />
rule规则文件
vim rule.xml
===========================================================
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
===========================================================
我们这里使用的是name列，所以修改
===========================================================
<tableRule name="sharding-by-intfile">
<rule>
<columns>name</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
===========================================================
找到：hash-int
===========================================================
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
===========================================================
<function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
<property name="defaultNode">0</property>
</function>
解释：
<property name="type">1</property> 支持字符串的枚举类型
<property name="defaultNode">0</property> 默认在节点ssh1
===========================================================
partition-hash-int.txt文件
===========================================================
vim partition-hash-int.txt
bj=0
sh=1
DEFAULT_NODE=1
解释：
bj在ssh1
sh在ssh2
其他，ssh2
===========================================================
测试环境的准备
mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
测试：
mycat restart
mysql -uroot -p123456 -h 192.168.80.94 -P 8066
use TESTDB
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
| 5 | tj |
| 2 | sh |
| 4 | sh |
+----+------+
5 rows in set (0.07 sec)
查看：
[root@sjk4 conf]# mysql -S /data/3307/mysql.sock -e "use taobao;select * from t5;"
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
+----+------+
[root@sjk4 conf]# mysql -S /data/3308/mysql.sock -e "use taobao;select * from t5;"
+----+------+
| id | name |
+----+------+
| 2 | sh |
| 4 | sh |
| 5 | tj |
+----+------+
154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
