环境
192.168.65.132 MyCAT
192.168.65.2 MySQL
192.168.65.7 MySQL
基础环境准备
描述:
1.192.168.65.2、192.168.65.7 2台主机上各装 2 个实例,端口如图;
2.2台机的 3306 为主,3307 为从,且 192.168.65.2 与 192.168.65.7 的 3306 互为主从(Mycat 高可用功能需要);
主从搭建的过程不是本文重点,不做详细描述,如有需要请参考我其他文章。
MyCAT安装与配置
1.下载
可以直接下去官网下载:http://www.mycat.org.cn/
本次试验我下载的是:Mycat-server-1.6.7.4-release-20200105164103-linux.tar
2.安装
安装 java
yum install java -y,我这里安装的是 java-1.8.0-openjdk.x86_64 1:1.8.0.121-1.b13.el6
解压下载好的 mycat 包
tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar
到这里就安装基本完成
mycat start 启动
查看状态
[root@test4 mycat]# netstat -antl|grep 8066
tcp 0 0 :::8066 :::* LISTEN
[root@test4 mycat]# netstat -antl|grep 9066
tcp 0 0 :::9066 :::* LISTEN
登陆管理接口
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mycat 启动后有 2 个端口,8066、9066,8066为数据接入端口,也就是给客户端连的端口,9066为管理端口。
如果启动没有成功 ,则无法连接 8066 端口,具体信息查看日志,路径 ./mycat/logs/wrapper.log
3.软件结构
bin:程序目录,mycat 程序,启动和关闭 mycat 中间件
conf:配置相关
schema.xml:主配置文件(读写分离、高可用、分布式策略定制、节点控制)
rule.xml:分片配置(分片规则配置文件,记录分片规则列表、使用方法等)
server.xml:mycat 服务相关配置(mycat 软体本身相关的配置)
4.schema.xml 配置
这里只给出基础配置(配置根据需求不同而不同,后面实验时会详细描述)
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="ty" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="dh1" database="ty" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="db1" url="192.168.65.2:3306" user="root" password="123">
</writeHost>
</dataHost>
</mycat:schema>
参数说明
schema | name | 逻辑库名 |
checkSQLschema | 表明是否检查并过滤 SQL 中包含 schema 的情况,如逻辑库为 ty,则可能写为 select * from ty.a,此 时会自动过滤 ty,SQL 变为 select * from a 。若不会出现上述写法,则可以关闭属性为 false。 | |
sqlMaxLimit | 默认返回最大记录数限制 | |
dataNode | 数据节点名 | |
dataNode | name | 数据节点名 |
dataHost | 数据主机名 | |
database | 数据库名,需要访问的真实数据库名 | |
dataHost | name | 数据主机名 |
maxCon | 最大连接数,实际作用于每个子host | |
balance | 负载均衡类型,目前的取值有3种: 1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。 2. balance="1",全部的readHost与standby writeHost参与select语句的负载均衡,简单的说, 当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。 3. balance="2",所有读操作都随机的在writeHost、readhost上分发。 | |
writeType | 负载均衡类型,目前的取值有2种: 1. writeType="0", 所有写操作发送到配置的第一个writeHost, 第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为主,切换记录在配置文件中:dnindex.properties . 2. writeType=“1”,所有写操作都随机的发送到配置的writeHost,但不推荐使用 | |
switchType | -1 表示不自动切换 1 默认值,自动切换 2 基于MySQL主从同步的状态决定是否切换 ,心跳语句为 show slave status | |
heartbeat | select user() | 监测心跳 |
writeHost | host | 主机名,随意取 |
url | 真实的主机地址 | |
user | 登陆数据库的用户名 | |
password | 账号密码 | |
readHost | 属性与 writeHost 一致 |
实验
1.读写分离
1.1数据准备
192.168.65.2:3360
[root@test2 ~]# mysql -S /data/3306/mysql.sock -uroot -p123 -e "grant all on *.* to root@'%' identified by '123'
1.2配置文件
server.xml(这里只用改如下 2 个位置)
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">ty</property>
<property name="defaultSchema">ty</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">ty</property>
<property name="readOnly">true</property>
<property name="defaultSchema">ty</property>
</user>
password:表示登陆mycat时用的账号密码
schemas:表示创建的逻辑库的名字
schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="ty" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="dh1" database="ty" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.65.2:3306" user="root" password="123">
<readHost host="db2" url="192.168.65.2:3307" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
配置完后重启 MyCat
[root@test4 mycat]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
1.3测试
连接 8066 接口测试
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686527 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "begin;select @@server_id;commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686526 |
+-------------+
这里用 select @@server_id 测试读,begin;select @@server_id;commit; 测试写。
server_id=1921686526 为 192.168.65.2 上的3306 实例
server_id=1921686527 为 192.168.65.2 上的3307 实例
可以看到读和写连接到的不同实例。
2.负载均衡及高可用
schema.xml修改如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="ty" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="dh1" database="ty" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.65.2:3306" user="root" password="123">
<readHost host="db2" url="192.168.65.2:3307" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.65.7:3306" user="root" password="123">
<readHost host="db4" url="192.168.65.7:3307" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
也就是在 dataHost 节点中增加了一个 writeHost 和 readHost
2.1测试负载均衡
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "begin;select @@server_id;commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686526 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686527 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686576 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686576 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686577 |
+-------------+
可以看到,虽然是双主,但是在高可用架构下,写节点还是只有 192.168.65.2 3306,而读操作则平均的分配到了其他
3 个节点。
2.2测试高可用
先 kill 掉 192.168.65.2 3306 ,然后执行上一步的测试语句,结果如下:
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686577 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686577 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "begin;select @@server_id;commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686576 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "begin;select @@server_id;commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686576 |
+-------------+
可以看出,写操作自动都发送给了 192.168.65.7:3306,而读操作都发送给了 192.168.65.7:3307,怎么没有 192.168.65.2:3307 呢?
这时因为 192.168.65.2:3306是主,而3307是从,当192.168.65.2:3306 被 kill 后,MyCat 会人为 192.168.65.2:3307 无法提供最新的数据,故其上的 3307 也无法使用。
当 192.168.65.2:3306 恢复后呢?
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "begin;select @@server_id;commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686576 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "begin;select @@server_id;commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686576 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686577 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686526 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686527 |
+-------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1921686527 |
+-------------+
即使 192.168.65.2:3306 恢复了,写操作还是在 192.168.65.7:3306,但是读操作则又可以分配给192.168.65.2 上的 2 个
节点了。
总结:
1.高可用架构下,虽然是双主双从的架构,但对外提供写操作的只有1个主,其他3个节点提供写操作。
2.当主节点挂掉后,会自动转移到另一个主节点,原主节点的从节点也无法提供服务。
3.当原主节点恢复后,只能提供读操作,不会切换为主。
4.当主节点挂掉后,MyCat 只负责切换,不会补 binlog,也就是说,如果主节点正在执行完某个事物,且还没有传
到另一个主时挂了,MyCat 切换后不会管这个事物数据。所以 MyCat 自带的这个高可用架构有点不靠谱。建议
搭配其他产品用,如 MHA 等。
3.分库实验
3.1模拟说明
说明:现有总库 ty,包含订单和会员 2 大业务。 因业务扩大,现需将业务切分,及订单业务分配到 orderDB,会员
业务分配到 VIP DB。
3.2数据准备
192.168.65.2 新增实例 3308
192.168.65.2:3306 orderdb
192.168.65.2:3308 vipdb
模拟数据
mysql -S /data/3306/mysql.sock -uroot -p123 -e "create database orderdb;"
mysql -S /data/3306/mysql.sock -uroot -p123 -e "use orderdb;create table a(id int);insert into a(id) values(11),(12),(13);"
mysql -S /data/3306/mysql.sock -uroot -p123 -e "use orderdb;create table b(id int);insert into b(id) values(21),(22),(23);"
mysql -S /data/3306/mysql.sock -uroot -p123 -e "use orderdb;create table c(id int);insert into c(id) values(31),(32),(33);"
mysql -S /data/3308/mysql.sock -uroot -p123 -e "create database vipdb;"
mysql -S /data/3308/mysql.sock -uroot -p123 -e "use vipdb;create table d(id int);insert into d(id) values(41),(42),(43);"
mysql -S /data/3308/mysql.sock -uroot -p123 -e "use vipdb;create table e(id int);insert into e(id) values(51),(52),(53);"
mysql -S /data/3308/mysql.sock -uroot -p123 -e "use vipdb;create table f(id int);insert into f(id) values(61),(62),(63);"
修改 server.xml 如下:
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">orderdb,vipdb</property>
<property name="defaultSchema">orderdb,vipdb</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">orderdb,vipdb</property>
<property name="readOnly">true</property>
<property name="defaultSchema">orderdb,vipdb</property>
</user>
修改 schema.xml 如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="orderdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<schema name="vipdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
</schema>
<dataNode name="dn1" dataHost="dh1" database="orderdb" />
<dataNode name="dn2" dataHost="dh2" database="vipdb" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.65.2:3306" user="root" password="123">
<readHost host="db2" url="192.168.65.2:3307" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.65.7:3306" user="root" password="123">
<readHost host="db4" url="192.168.65.7:3307" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="dh2" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db5" url="192.168.65.2:3308" user="root" password="123">
</writeHost>
</dataHost>
</mycat:schema>
重启 MyCat
[root@test4 ~]# mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
数据验证
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| DATABASE |
+----------+
| orderdb |
| vipdb |
+----------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "use orderdb;show tables;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| Tables_in_orderdb |
+-------------------+
| a |
| b |
| c |
+-------------------+
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -e "use vipdb;show tables;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| Tables_in_vipdb |
+-----------------+
| d |
| e |
| f |
+-----------------+
通过访问 MyCat ,则可以同时看到 2 个库。
4.分表实验
4.1全局表
全局表也就是在写入数据的时候,每个节点都会写一份数据,也就是每个节点都有一张同样的表且数据一样。
之前的数据都清掉,重新创建如下数据
mysql -S /data/3306/mysql.sock -uroot -p123 -e "create database orderdb_a;"
mysql -S /data/3306/mysql.sock -uroot -p123 -e "use orderdb_a;create table a(id int);"
mysql -S /data/3308/mysql.sock -uroot -p123 -e "create database orderdb_b;"
mysql -S /data/3308/mysql.sock -uroot -p123 -e "use orderdb_b;create table a(id int);"
server.xml修改如下:
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">orderdb</property>
<property name="defaultSchema">orderdb</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">orderdb</property>
<property name="readOnly">true</property>
<property name="defaultSchema">orderdb</property>
</user>
schema.xml 修改如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="orderdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="a" dataNode="dn1,dn2" />
</schema>
<dataNode name="dn1" dataHost="dh1" database="orderdb_a" />
<dataNode name="dn2" dataHost="dh2" database="orderdb_b" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.65.2:3306" user="root" password="123">
<readHost host="db2" url="192.168.65.2:3307" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.65.7:3306" user="root" password="123">
<readHost host="db4" url="192.168.65.7:3307" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="dh2" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db5" url="192.168.65.2:3308" user="root" password="123">
</writeHost>
</dataHost>
</mycat:schema>
MyCat 重启
[root@test4 ~]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
数据验证
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into a(id) values(1);"
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
+------+
可以看到通过 MyCat 写进去的数据成功在 2 个节点都有数据
4.2水平分表 -- 范围(auto-sharding-long)
清理 192.168.65.2 3306、3308 2个节点中 a 表的数据
schema.xml 修改如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="orderdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="a" dataNode="dn1,dn2" rule="auto-sharding-long" />
</schema>
<dataNode name="dn1" dataHost="dh1" database="orderdb_a" />
<dataNode name="dn2" dataHost="dh2" database="orderdb_b" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.65.2:3306" user="root" password="123">
<readHost host="db2" url="192.168.65.2:3307" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.65.7:3306" user="root" password="123">
<readHost host="db4" url="192.168.65.7:3307" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="dh2" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db5" url="192.168.65.2:3308" user="root" password="123">
</writeHost>
</dataHost>
</mycat:schema>
mycat/conf/autopartition-long.txt 原内容如下
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0 0~500万的数据存到节点 0
500M-1000M=1 500万~1000万的数据存到节点 1
1000M-1500M=2 1000万~1500万的数据存到节点 2
修改为如下:
0-5=0
5-10=1
为什么要修改 mycat/conf/autopartition-long.txt?
在 schema.xml 文件中范围分区应用到规则 rule="auto-sharding-long"
所以在 rule.xml 文件中找到 tableRule name="auto-sharding-long" 节点
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns> <!-- 按照 id 列分 -->
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
其中 <algorithm>rang-long</algorithm> 值为 rang-long,然后在文件中查找 rang-long ,找到
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
这样就知道该算法是跟哪个文件相关联的
修改完后重启 MyCat
测试:
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into a(id) values(1),(2),(6),(7);"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "select * from a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
| 2 |
| 6 |
| 7 |
+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
| 2 |
+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 6 |
| 7 |
+------+
可以看到通过 MyCat insert 的数据,按照之前的定义的分片规则,分到了 3306 和 3308 2个节点中。
4.3水平分表 -- 枚举 (sharding-by-intfile)
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property> <!--如果枚举的值为中文,则要加这一行-->
</function>
schema.xml 修改如下:
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="orderdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="a" dataNode="dn1,dn2" rule="sharding-by-intfile" />
</schema>
<dataNode name="dn1" dataHost="dh1" database="orderdb_a" />
<dataNode name="dn2" dataHost="dh2" database="orderdb_b" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.65.2:3306" user="root" password="123">
<readHost host="db2" url="192.168.65.2:3307" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.65.7:3306" user="root" password="123">
<readHost host="db4" url="192.168.65.7:3307" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="dh2" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db5" url="192.168.65.2:3308" user="root" password="123">
</writeHost>
</dataHost>
</mycat:schema>
测试:
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into a(id) values(1),(2);"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 2 |
+------+
可以看到通过 MyCat insert 的数据,按照之前的定义的分片规则,分到了 3306 和 3308 2个节点中。
4.4水平分表 -- 枚举 (mod-long)
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">2</property>
</function>
<!-- 这里2表示是2个节点,有几个节点这里就写多少 -->
schema.xml
<table name="a" dataNode="dn1,dn2" rule="mod-long" />
<!-- table 属性中的rule 改成 mod-long,其他不变 -->
重启 MyCat,insert 测试数据,查看结果
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into a(id) values(1),(2),(3),(4);"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 2 |
| 4 |
+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.a;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
| 3 |
+------+
可以看到通过 MyCat insert 的数据,按照之前的定义的分片规则,分到了 3306 和 3308 2个节点中。
4.5水平分表 -- 日期 (sharding-by-date)
rule.xml 修改如下内容:
<tableRule name="sharding-by-date">
<rule>
<columns>ct</columns>
<algorithm>partbyday</algorithm>
</rule>
</tableRule>
<function name="partbyday" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property> <!-- 日期格式 -->
<property name="sNaturalDay">0</property> <!-- 自然日 -->
<property name="sBeginDate">2020-04-20</property> <!-- 分区开始日期 -->
<property name="sEndDate">2020-04-26</property> <!-- 分区结束日期 -->
<property name="sPartionDay">1</property> <!-- 分区间隔(天) -->
<property name=""defaultNode"">0</property> <!--如果超出定义范围的则默认插入到0节点 -->
</function>
schema.xml 修改如下内容
<table name="b" dataNode="dn1,dn2" rule="sharding-by-date" />
重启 MyCat,创建测试数据
mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "create table orderdb_a.b(ct date);"
mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "create table orderdb_b.b(ct date);"
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into b(ct) values('2020-04-21'),('2020-04-25');"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.b;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| ct |
+------------+
| 2020-04-21 |
+------------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.b;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| ct |
+------------+
| 2020-04-25 |
+------------+
然后插入一个超出定义范围的日志
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into b(ct) values('2020-04-28');"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.b;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| ct |
+------------+
| 2020-04-21 |
| 2020-04-28 |
+------------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.b;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| ct |
+------------+
| 2020-04-25 |
+------------+
可以看到插入到了第一个节点
可以看到通过 MyCat insert 的数据,按照之前的定义的分片规则,分到了 3306 和 3308 2个节点中。
4.6水平分表 -- ER
创建数据
mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "create table orderdb_a.e(id int,age int);create table orderdb_a.f(id int,eid int);"
mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "create table orderdb_b.e(id int,age int);create table orderdb_b.f(id int,eid int);"
mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into e(id,age) values(1,11),(2,22);"
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.e;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | age |
+------+------+
| 2 | 22 |
+------+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.e;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | age |
+------+------+
| 1 | 11 |
+------+------+
这个时候是按照取模分片的,结果正确
然后 insert f表的记录
mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into f(id,eid) values(111,1);"
mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into f(id,eid) values(222,1);"
mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into f(id,eid) values(333,1);"
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.e;select * from orderdb_a.f;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | age |
+------+------+
| 2 | 22 |
+------+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.e;select * from orderdb_b.f;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | age |
+------+------+
| 1 | 11 |
+------+------+
+------+------+
| id | eid |
+------+------+
| 111 | 1 |
| 222 | 1 |
| 333 | 1 |
+------+------+
可以看出,insert 到 f 表的记录全部分配到 3308 实例了,可以看出跟当 e.id 和 f.eid 存在联系时,e 表的记录在哪个节点,f 表中相关的记录就会在哪个节点,这个就是 ER 分片。
如果 insert 到 f 表的记录跟 e 表没关系呢?那数据按照什么规则插入对应的节点?
mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into f(id,eid) values(7777,3);"
mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into f(id,eid) values(7777,4);"
mysql -uroot -p123456 -h192.168.65.4 -P8066 -Dorderdb -e "insert into f(id,eid) values(7777,5);"
这里 insert 3 条跟 e 表没有关系的数据。
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3306 -Dorderdb_a -e "select * from orderdb_a.e;select * from orderdb_a.f;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | age |
+------+------+
| 2 | 22 |
+------+------+
+------+------+
| id | eid |
+------+------+
| 7777 | 4 |
+------+------+
[root@test4 ~]# mysql -uroot -p123 -h192.168.65.2 -P3308 -Dorderdb_b -e "select * from orderdb_b.e;select * from orderdb_b.f;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | age |
+------+------+
| 1 | 11 |
+------+------+
+------+------+
| id | eid |
+------+------+
| 111 | 1 |
| 222 | 1 |
| 333 | 1 |
| 7777 | 3 |
| 7777 | 5 |
+------+------+
可以看到,如果 insert 到 f 表的数据跟 e 表没有关系,则按照正常的 mod-long 规则分片。
到此几个常用的分库分表方法演示完了,其他的分排尿算法可以自己做做实验,参考下官方文档。
5.管理命令
连接 9066 端口登入管理端,通过 show @@help 来查看支持的命令
[root@test4 ~]# mysql -uroot -p123456 -h192.168.65.4 -P9066
mysql> show @@help;
+--------------------------------------------------------------+--------------------------------------------+
| STATEMENT | DESCRIPTION |
+--------------------------------------------------------------+--------------------------------------------+
| show @@time.current | Report current timestamp |
| show @@time.startup | Report startup timestamp |
| show @@version | Report Mycat Server version |
| show @@server | Report server status |
| show @@threadpool | Report threadPool status |
| show @@database | Report databases |
| show @@datanode | Report dataNodes |
| show @@datanode where schema = ? | Report dataNodes |
| show @@datasource | Report dataSources |
| show @@datasource where dataNode = ? | Report dataSources |
| show @@datasource.synstatus | Report datasource data synchronous |
| show @@datasource.syndetail where name=? | Report datasource data synchronous detail |
| show @@datasource.cluster | Report datasource galary cluster variables |
| show @@processor | Report processor status |
| show @@command | Report commands status |
| show @@connection | Report connection status |
| show @@cache | Report system cache usage |
| show @@backend | Report backend connection status |
| show @@session | Report front session details |
| show @@connection.sql | Report connection sql |
| show @@sql.execute | Report execute status |
| show @@sql.detail where id = ? | Report execute detail status |
| show @@sql | Report SQL list |
| show @@sql.high | Report Hight Frequency SQL |
| show @@sql.slow | Report slow SQL |
| show @@sql.resultset | Report BIG RESULTSET SQL |
| show @@sql.sum | Report User RW Stat |
| show @@sql.sum.user | Report User RW Stat |
| show @@sql.sum.table | Report Table RW Stat |
| show @@parser | Report parser status |
| show @@router | Report router status |
| show @@heartbeat | Report heartbeat status |
| show @@heartbeat.detail where name=? | Report heartbeat current detail |
| show @@slow where schema = ? | Report schema slow sql |
| show @@slow where datanode = ? | Report datanode slow sql |
| show @@sysparam | Report system param |
| show @@syslog limit=? | Report system mycat.log |
| show @@white | show mycat white host |
| show @@white.set=?,? | set mycat white host,[ip,user] |
| show @@directmemory=1 or 2 | show mycat direct memory usage |
| show @@check_global -SCHEMA= ? -TABLE=? -retry=? -interval=? | check mycat global table consistency |
| switch @@datasource name:index | Switch dataSource |
| kill @@connection id1,id2,... | Kill the specified connections |
| stop @@heartbeat name:time | Pause dataNode heartbeat |
| reload @@config | Reload basic config from file |
| reload @@config_all | Reload all config from file |
| reload @@route | Reload route config from file |
| reload @@user | Reload user config from file |
| reload @@sqlslow= | Set Slow SQL Time(ms) |
| reload @@user_stat | Reset show @@sql @@sql.sum @@sql.slow |
| rollback @@config | Rollback all config from memory |
| rollback @@route | Rollback route config from memory |
| rollback @@user | Rollback user config from memory |
| reload @@sqlstat=open | Open real-time sql stat analyzer |
| reload @@sqlstat=close | Close real-time sql stat analyzer |
| offline | Change MyCat status to OFF |
| online | Change MyCat status to ON |
| clear @@slow where schema = ? | Clear slow sql by schema |
| clear @@slow where datanode = ? | Clear slow sql by datanode |
+--------------------------------------------------------------+--------------------------------------------+
59 rows in set (0.00 sec)
每条命令的作用描述中都有,可以自己试试。