Mycat 系列之水平分表

最新推荐文章于 2024-05-11 18:00:54 发布

panbuhei

最新推荐文章于 2024-05-11 18:00:54 发布

阅读量320

点赞数

分类专栏： Mycat

本文链接：https://blog.csdn.net/qq_43584691/article/details/118680934

版权

Mycat 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

安装部署 MySQL

1 MySQL 搭建

搭建步骤省略…

注意一： Linux 下部署安装 MySQL，默认不忽略表名大小写，需要手动到 /etc/my.cnf 下配置 lower_case_table_names=1 使 Linux 环境下 MySQL 忽略表名大小写，否则使用 MyCAT 的时候会提示找不到表的错误。

注意二：在测试过程中，MySQL 设置了指定服务端字符集的排序规则(collation-server)，结果或导致 Mycat 报错。

2 创建测试库

CREATE DATABASE mycat_testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3 创建用户并授权

grant all on mycat_testdb.* to mycat_user@"%" identified by "111111";

4 连接测试

mysql -umycat_user -p111111 -h 10.0.30.21
mysql -umycat_user -p111111 -h 10.0.30.22

安装部署 Mycat

1 JAVA 环境配置

PS：必须 JDK7 或更高版本。

tar -xvf jdk-8u281-linux-x64.tar.gz -C /usr/local/

ln -vs /usr/local/jdk1.8.0_281/bin/java* /usr/bin/
ln -vs /usr/local/jdk1.8.0_281/bin/jps /usr/bin/

2 下载&解压二进制包

tar -xvf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz -C /usr/local/

3 声明环境变量

cat << EOF > /etc/profile.d/mycat.sh
export MYCAT_HOME=/usr/local/mycat
EOF

source /etc/profile

4 修改日志格式

cd /usr/local/mycat
vim conf/log4j.xml
#
......
#日志存放路径
<param name="file" value="${MYCAT_HOME}/logs/mycat.log" />
......
#日志等级(生成环境下建议将为 info/ware，如果是研究测试，可以开启debug 模式)
<root>
    <level value="debug" />
    <appender-ref ref="ConsoleAppender" />
</root>
......

5 修改 schema.xml 配置文件(枚举分片)

cd /usr/local/mycat
cp -p conf/schema.xml{,.default}

vim conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
        <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100">
            <table name="student" dataNode="dn1,dn2" rule="sharding-by-intfile"></table>
        </schema>

        <dataNode name="dn1" dataHost="wpf-test21" database="mycat_testdb"/>
        <dataNode name="dn2" dataHost="wpf-test22" database="mycat_testdb"/>

        <dataHost name="wpf-test21" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="testM1" url="10.0.30.21:3306" user="mycat_user" password="111111"/>
        </dataHost>

        <dataHost name="wpf-test22" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="testM2" url="10.0.30.22:3306" user="mycat_user" password="111111"/>
        </dataHost>
</mycat:schema>

6 修改 rule.xml 配置文件(枚举分片)

cd /usr/local/mycat
cp -p conf/rule.xml{,.default}

vim conf/rule.xml
......
<tableRule name="sharding-by-intfile">
        <rule>
                <columns>sex</columns>
                <algorithm>hash-int</algorithm>
        </rule>
</tableRule>
<!-- columns：分片字段 -->
<!--algorithm：分片函数 -->
......
<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>
......
<!-- mapFile：配置文件名称。-->
<!-- type：0 表示 Integer，非零表示 String。默认为 0。
+ 如果设置为 0，会报错：ERROR 1064 (HY000): For input string: "女"。-->
<!-- defaultNode：默认节点。小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点。-->

7 编辑 conf/partition-hash-int.txt 文件(枚举分片)

vim conf/partition-hash-int.txt
男=0
女=1
#根据性别(sex)进行分片

8 修改 server.xml 配置文件(枚举分片)

cd /usr/local/mycat
cp -p conf/server.xml{,.default}

vim conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
        <property name="charset">utf8mb4</property>
        </system>

        <user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">mycat_testdb</property>
        </user>
</mycat:server>

9 启动 Mycat

#mycat 支持的命令{ console | start | stop | restart | status | dump }
cd /usr/local/mycat
./bin/mycat start

测试 Mycat

1 连接 mycat

mysql -h 10.0.30.20 -umycat -p123456 -P 8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------+
| DATABASE     |
+--------------+
| mycat_testdb |
+--------------+
1 row in set (0.00 sec)

2 创建表，并写入测试数据

MySQL [(none)]> use mycat_testdb;
Database changed

MySQL [mycat_testdb]> create table student (id int,name varchar(20),sex varchar(5));
Query OK, 0 rows affected (0.05 sec)

#分片表在写入数据时，必须指定字段名，如果不指定会报错。如下所示：
MySQL [mycat_testdb]> insert into student values (1,'xiaohong','女');
ERROR 1064 (HY000): partition table, insert must provide ColumnList

MySQL [mycat_testdb]> insert into student(id,name,sex) values (1,'xiaohong','女');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> insert into student(id,name,sex) values (2,'xiaoming','男');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> insert into student(id,name,sex) values (3,'张三','男');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> insert into student(id,name,sex) values (4,'李四','女');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> select * from student;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    1 | xiaohong | 女   |
|    4 | 李四     | 女   |
|    2 | xiaoming | 男   |
|    3 | 张三     | 男   |
+------+----------+------+
4 rows in set (0.00 sec)

3 验证分片是否成功

#在第一个物理库上进行查看
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> select * from student;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    2 | xiaoming | 男   |
|    3 | 张三     | 男   |
+------+----------+------+
2 rows in set (0.00 sec)

#在第二个物理库上进行查看
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> select * from student;
+------+----------+------+
| id   | name     | sex  |
+------+----------+------+
|    1 | xiaohong | 女   |
|    4 | 李四     | 女   |
+------+----------+------+
2 rows in set (0.01 sec)

至此，可以看到 Mycat 的枚举分片成功。

范围分片

根据年龄(age)，进行分片：0-6岁为幼儿，7-17岁为青少年，18-45岁为青年，46-69岁为中年，大于69岁为老年。

schema.xml 配置文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
        <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100">
            <table name="census" dataNode="dn1,dn2" rule="auto-sharding-long"></table>
        </schema>

        <dataNode name="dn1" dataHost="wpf-test21" database="mycat_testdb"/>
        <dataNode name="dn2" dataHost="wpf-test22" database="mycat_testdb"/>

        <dataHost name="wpf-test21" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="testM1" url="10.0.30.21:3306" user="mycat_user" password="111111"/>
        </dataHost>

        <dataHost name="wpf-test22" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="testM2" url="10.0.30.22:3306" user="mycat_user" password="111111"/>
        </dataHost>
</mycat:schema>

rule.xml 配置文件

......
<tableRule name="auto-sharding-long">
        <rule>
                <columns>age</columns>
                <algorithm>rang-long</algorithm>
        </rule>
</tableRule>
<!-- columns：分片字段-->
<!-- algorithm：分片函数-->
......
<function name="rang-long"
        class="org.opencloudb.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
        <property name="defaultNode">0</property>
</function>
......
<!-- mapFile：配置文件名称。-->
<!-- defaultNode：默认节点。小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点。-->

conf/partition-hash-int.txt 文件

vim conf/autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-6=0
7-17=1
18-45=1
46-69=0
#0-6岁为幼儿，7-17岁为青少年，18-45岁为青年，46-69岁为中年，大于69岁为老年
#由于数据库有限，测试分片就只有两个数据库节点。

server.xml 配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
        <property name="charset">utf8mb4</property>
        </system>

        <user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">mycat_testdb</property>
        </user>
</mycat:server>

连接 mycat 创建表，并写入测试数据

mysql -h 10.0.30.20 -umycat -p123456 -P 8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------+
| DATABASE     |
+--------------+
| mycat_testdb |
+--------------+
1 row in set (0.00 sec)

MySQL [(none)]> use mycat_testdb;
Database changed

MySQL [mycat_testdb]> create table census (id int,name varchar(20),age int,area varchar(50));
Query OK, 0 rows affected (0.02 sec)

MySQL [mycat_testdb]> insert into census(id,name,age,area) values(1,'张三',5,'阳光花园');
Query OK, 1 row affected (0.05 sec)

MySQL [mycat_testdb]> insert into census(id,name,age,area) values(2,'李四',8,'花园小区');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> insert into census(id,name,age,area) values(3,'王五',18,'阳光小区');
Query OK, 1 row affected (0.00 sec)

MySQL [mycat_testdb]> insert into census(id,name,age,area) values(4,'赵柳',47,'阳光街道');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> insert into census(id,name,age,area) values(5,'孙琦',70,'花园街道');
Query OK, 1 row affected (0.06 sec)

MySQL [mycat_testdb]> select * from census;
+------+--------+------+--------------+
| id   | name   | age  | area         |
+------+--------+------+--------------+
|    1 | 张三   |    5 | 阳光花园     |
|    4 | 赵柳   |   47 | 阳光街道     |
|    5 | 孙琦   |   70 | 花园街道     |
|    2 | 李四   |    8 | 花园小区     |
|    3 | 王五   |   18 | 阳光小区     |
+------+--------+------+--------------+
5 rows in set (0.05 sec)

验证范围分片是否成功

#在第一个物理库上进行查看
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> select * from census;
+------+--------+------+--------------+
| id   | name   | age  | area         |
+------+--------+------+--------------+
|    1 | 张三   |    5 | 阳光花园     |
|    4 | 赵柳   |   47 | 阳光街道     |
|    5 | 孙琦   |   70 | 花园街道     |
+------+--------+------+--------------+
3 rows in set (0.00 sec)

#在第二个物理库上进行查看
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> select * from census;
+------+--------+------+--------------+
| id   | name   | age  | area         |
+------+--------+------+--------------+
|    2 | 李四   |    8 | 花园小区     |
|    3 | 王五   |   18 | 阳光小区     |
+------+--------+------+--------------+
2 rows in set (0.00 sec)

至此，可以看到 Mycat 的范围分片成功。

日期(天)分片

根据日期(天)进行分片，每隔 10 天分一片。

schema.xml 配置文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
        <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100">
            <table name="player_list" dataNode="dn1,dn2" rule="sharding-by-date"></table>
        </schema>

        <dataNode name="dn1" dataHost="wpf-test21" database="mycat_testdb"/>
        <dataNode name="dn2" dataHost="wpf-test22" database="mycat_testdb"/>

        <dataHost name="wpf-test21" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="testM1" url="10.0.30.21:3306" user="mycat_user" password="111111"/>
        </dataHost>

        <dataHost name="wpf-test22" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="testM2" url="10.0.30.22:3306" user="mycat_user" password="111111"/>
        </dataHost>
</mycat:schema>

rule.xml 配置文件

......
<tableRule name="sharding-by-date">
        <rule>
                <columns>create_time</columns>
                <algorithm>shardingByDate</algorithm>
        </rule>
</tableRule>
<!-- columns：分片字段-->
<!-- algorithm：分片函数-->
......
<!--<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">-->
<function name="shardingByDate" class="org.opencloudb.route.function.PartitionByDate">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2020-01-01</property>
        <property name="sEndDate">2020-01-20</property>
        <property name="sPartionDay">10</property>
</function>
......
<!--dateFormat：日期格式。-->
<!--sBeginDate：开始日期。-->
<!--sEndDate：结束日期。即：数据达到了这个日期的分片后，循环从开始分片插入。-->
<!--sPartionDay：分区天数。即：默认从开始日期算起，每 10 天一个分。-->

server.xml 配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
        <property name="charset">utf8mb4</property>
        </system>

        <user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">mycat_testdb</property>
        </user>
</mycat:server>

连接 mycat 创建表，并写入测试数据

mysql -h 10.0.30.20 -umycat -p123456 -P 8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------+
| DATABASE     |
+--------------+
| mycat_testdb |
+--------------+
1 row in set (0.00 sec)

MySQL [(none)]> use mycat_testdb;
Database changed

MySQL [mycat_testdb]> create table player_list (id int,name varchar(20),grade int,create_time date);
Query OK, 0 rows affected (0.04 sec)

MySQL [mycat_testdb]> insert into player_list (id,name,grade,create_time) values (1,'无敌风火轮',99,'2020-01-05');
Query OK, 1 row affected (0.00 sec)
 
MySQL [mycat_testdb]> insert into player_list (id,name,grade,create_time) values (2,'awsdf',86,'2020-01-16');
Query OK, 1 row affected (0.00 sec)

MySQL [mycat_testdb]> insert into player_list (id,name,grade,create_time) values (3,'小帅在不经意间',55,'2020-01-20');
Query OK, 1 row affected (0.00 sec)

MySQL [mycat_testdb]> insert into player_list (id,name,grade,create_time) values (4,'hhhhhh',11,'2020-01-25');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> insert into player_list (id,name,grade,create_time) values (5,'我爱你1314',66,'2020-01-31');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> insert into player_list (id,name,grade,create_time) values (6,'1234567890',67,'2020-02-28');
Query OK, 1 row affected (0.01 sec)

MySQL [mycat_testdb]> select * from player_list;
+------+-----------------------+-------+-------------+
| id   | name                  | grade | create_time |
+------+-----------------------+-------+-------------+
|    2 | awsdf                 |    86 | 2020-01-16  |
|    3 | 小帅在不经意间        |    55 | 2020-01-20  |
|    5 | 我爱你1314            |    66 | 2020-01-31  |
|    6 | 1234567890            |    67 | 2020-02-28  |
|    1 | 无敌风火轮            |    99 | 2020-01-05  |
|    4 | hhhhhh                |    11 | 2020-01-25  |
+------+-----------------------+-------+-------------+
6 rows in set (0.00 sec)

验证日期分片是否成功

#在第一个物理库上进行查看
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> select * from player_list;
+------+-----------------+-------+-------------+
| id   | name            | grade | create_time |
+------+-----------------+-------+-------------+
|    1 | 无敌风火轮      |    99 | 2020-01-05  |
|    4 | hhhhhh          |    11 | 2020-01-25  |
+------+-----------------+-------+-------------+
2 rows in set (0.00 sec)

#在第二个物理库上进行查看
MariaDB [(none)]> use mycat_testdb;
Database changed
MariaDB [mycat_testdb]> select * from player_list;
+------+-----------------------+-------+-------------+
| id   | name                  | grade | create_time |
+------+-----------------------+-------+-------------+
|    2 | awsdf                 |    86 | 2020-01-16  |
|    3 | 小帅在不经意间        |    55 | 2020-01-20  |
|    5 | 我爱你1314            |    66 | 2020-01-31  |
|    6 | 1234567890            |    67 | 2020-02-28  |
+------+-----------------------+-------+-------------+
4 rows in set (0.00 sec)