06-MySQL基于MyCat的分布式数据库的搭建

一、MyCat简介

1、什么是MyCat?

MyCAT是:

* 一个彻底开源的,面向企业应用开发的“大数据库集群”;
* 支持事务、ACID、可以替代MySQL的加强版数据库;
* 一个可以视为“MySQL”集群的企业级数据库,用来替代昂贵的Oracle集群;
* 一个融合内存缓存技术、Nosql技术、HDFS大数据的新型SQL;
* 一个新颖的数据库中间件产品;

2、MyCat的特性

* 支持 SQL92标准(数据库的一个ANSI/ISO标准);
* 支持Mysql集群,可以作为Proxy使用;
* 支持JDBC连接ORACLE、DB2、SQL Server、PostgreSQL、MongoDB,将其模拟为MySQL Server使用;
* 支持galera for mysql集群,percona-cluster或者mariadb cluster,提供高可用性数据分片集群;
* 自动故障切换,高可用性;
* 支持读写分离,支持Mysql双主多从,以及一主多从的模式;
* 支持全局表,数据自动分片到多个节点,用于高效表关联查询;
* 支持独有的基于E-R 关系的分片策略,实现了高效的表关联查询;

3、MyCat架构图

06-MySQL基于MyCat的分布式数据库的搭建

4、MyCat的分片策略

* MyCAT支持水平分片与垂直分片:
  水平分片:一个表格的数据分割到多个节点上,按照行分隔。
  垂直分片:一个数据库中多个表格A,B,C,A存储到节点1上,B存储到节点2上,C存储到节点3上。

* MyCat通过定义表的分片规则来实现分片,每个表格可以绑定一个分片规则,每个分片规则指定一个分片字段并绑定一个函数,来实现动态分配算法。

5、MyCat的组成部分

* Schema:逻辑库,与MySQL中的Database对应,一个逻辑库中定义了所包括的Table;

* Table:表,即物理数据库中存储的某一张表,与传统数据库不同,这里的表格需要声明其所存储的逻辑数据节点(DataNode),在此可以指定表的分片规则;

* DataNode:MyCat的逻辑数据节点,是存放Table的具体物理节点,也称之为分片节点,通过DataSource来关联到后端某个具体的数据库上;

* DataSource:定义某个物理库的访问地址,用于捆绑到DataNode上;

6、MyCat百度云盘地址(Mycat-server-1.6.7.4-release-20200105164103-linux.tar),有需要的可以下载,也可以去官网下载

链接:https://pan.baidu.com/s/1a6Y_YL2DHUzz4zyvYHRkKQ 
提取码:z9os 

 官网:http://www.mycat.org.cn/

二、MyCat架构的搭建

1、测试环境IP配置

 机器名称   IP 服务器角色 备注
 Manager   192.168.232.45 MyCat服务器 操作系统redhat7.5
node1 192.168.232.42 MySQL主节点 操作系统redhat7.5;MySQL5.7.22
node2 192.168.232.43 MySQL从节点1 操作系统redhat7.5;MySQL5.7.22
node3 192.168.232.44 MySQL从节点2 操作系统redhat7.5;MySQL5.7.22

2、配置IP映射

在各节点的/etc/hosts文件中配置如下内容:

192.168.232.42 node1
192.168.232.43 node2
192.168.232.44 node3
192.168.232.45 manager

3、3个节点先安装好数据库

注:

* 这里不做说明,参考安装文档,数据版本最好一致;

* 3个数据库节点上都要添加忽略大小写的配置,在/etc/my.cnf下添加lower_case_table_names=1即可;

06-MySQL基于MyCat的分布式数据库的搭建

4、manage节点上创建mycat用户和组

groupadd mycat
useradd mycat -g mycat
passwd mycat
cat /etc/group  --查看用户组

5、解压mycat安装包

tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

注:mycat是依赖jdk的,没有安装jdk的要先安装好jdk(jdk7以上)

6、配置环境变量

06-MySQL基于MyCat的分布式数据库的搭建

7、配置schema.xml文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="customer" checkSQLschema="false" sqlMaxLimit="100">
                <!-- auto sharding by id (long) -->
                <table name="person" dataNode="dn1,dn2,dn3" rule="mod-long" />
                <table name="TB_ITEM" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
                <table name="TB_USER" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" rule="mod-long" />
                <!-- global table is auto cloned to all defined data nodes ,so can join 
                        with any table whose sharding node is in the same data node -->
                <table name="student" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />


        </schema>
        <dataNode name="dn1" dataHost="node1" database="customer1" />
        <dataNode name="dn2" dataHost="node2" database="customer2" />
        <dataNode name="dn3" dataHost="node3" database="customer3" />

        <dataHost name="node1" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="node1" url="192.168.232.42:3308" user="root"
                        password="123456">
                        <!-- can have multi read hosts -->

                </writeHost>
        </dataHost>
        <dataHost name="node2" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="node2" url="192.168.232.43:3308" user="root"
                        password="123456">
                        <!-- can have multi read hosts -->

                </writeHost>
        </dataHost>
        <dataHost name="node3" maxCon="1000" minCon="10" balance="0"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="node3" url="192.168.232.44:3308" user="root"
                        password="123456">
                        <!-- can have multi read hosts -->

                </writeHost>
        </dataHost>



</mycat:schema>

注:

* customer是逻辑库,应用访问的就是这个库,下面dataNode里配置的customer1、customer2、customer3是3个节点上的物理数据库,真正存储数据的地方;

* table标签中的type=“global”表示的是全局表,在所指定的dataNode节点上都存储数据,意思就是存储3份数据,没有做切片;

* balance参数设置

balance=“0”, 所有读操作都发送到当前可用的writeHost上。

balance=“1”,所有读操作都随机的发送到readHost。

balance=“2”,所有读操作都随机的在writeHost、readhost上分发。

* WriteType参数设置

writeType=“0”, 所有写操作都发送到可用的writeHost上。

writeType=“1”,所有写操作都随机的发送到readHost。

writeType=“2”,所有写操作都随机的在writeHost、readhost分上发。

* swatchType参数设置

-1:表示不自动切换

1 :默认值,自动切换

2 :基于MySQL主从同步的状态决定是否切换

注:Mycat心跳检查语句配置为 show slave status ,dataHost 上定义两个新属性: switchType="2" 与slaveThreshold="100",此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。

8、配置server.xml文件

注:server.xml几乎保存了mycat需要的所有配置信息,最常用的是在此配置逻辑数据库、用户、密码、权限等。

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
        - you may not use this file except in compliance with the License. - You 
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
        - - Unless required by applicable law or agreed to in writing, software - 
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
        License for the specific language governing permissions and - limitations 
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!-- 
        <property name="processors">1</property> 
        <property name="processorExecutor">32</property> 
         -->
                <!--默认是65535 64K 用于sql解析时最大文本长度 -->
                <!--<property name="maxStringLiteralLength">65535</property>-->
                <!--<property name="sequnceHandlerType">0</property>-->
                <!--<property name="backSocketNoDelay">1</property>-->
                <!--<property name="frontSocketNoDelay">1</property>-->
                <!--<property name="processorExecutor">16</property>-->
                <!-- 
                        <property name="mutiNodeLimitType">1</property> 0:开启小数量级(默认) ;1:开启亿级数据排序
                <property name="mutiNodePatchSize">100</property> 亿级数量排序批量
                        <property name="processors">32</property> <property name="processorExecutor">32</property> 
                        <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
                        <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
        </system>
        <user name="test">
                <property name="password">test</property>
                <property name="schemas">customer</property>
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">customer</property>
                <property name="readOnly">true</property>
        </user>
        <!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property> 
                <property name="weight">1</property> </node> </cluster> -->
        <!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property> 
                </host> </quarantine> -->

</mycat:server>

注:test是读写用户,user是只读用户

9、配置rule.xml文件

注:rule.xml里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法,或者对表使用相同的算法但具体的参数不同。这个文件里面主要有tableRule和function这两个标签。在具体使用过程中可以按照需求添加tableRule和function。

此配置文件可以不用修改,使用默认即可。

10、在三个节点先创建好数据库(customer1、customer2、customer3)

--node1
mysql> create database customer1 default character set utf8;
Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customer1          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

--node2
mysql> create database customer2 default character set utf8;
Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customer2          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

--node3
mysql> create database customer3 default character set utf8;
Query OK, 1 row affected (0.34 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customer3          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

11、启动mycat

[root@manager ~]# su - mycat
Last login: Tue Dec 22 10:54:32 CST 2020 on pts/0
[mycat@manager ~]$ mycat start
Starting Mycat-server...
[mycat@manager ~]$ mycat status
Mycat-server is running (4396).
[mycat@manager ~]$ ps -ef | grep mycat
root       4284   4074  0 14:23 pts/1    00:00:00 su - mycat
mycat      4285   4284  0 14:23 pts/1    00:00:00 -bash
mycat      4396      1  0 14:24 ?        00:00:00 /mysql/mycat/mycat/bin/./wrapper-linux-x86-64 /mysqlmycat/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/mysql/mycat/mycat/logs/mycat.pid wrapper.daemonize=TRUE
mycat      4398   4396 33 14:24 ?        00:00:06 java -DMYCAT_HOME=. -server -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/mysql-binlog-connector-java-0.16.1.jar:lib/reflectasm-1.03.jar:lib/annotations-13.0.jar:lib/kotlin-stdlib-1.3.50.jar:lib/kotlin-stdlib-common-1.3.50.jar:lib/curator-framework-2.11.0.jar:lib/log4j-core-2.5.jar:lib/commons-collections-3.2.1.jar:lib/mongo-java-driver-3.11.0.jar:lib/fastjson-1.2.58.jar:lib/commons-lang-2.6.jar:lib/log4j-1.2-api-2.5.jar:lib/Mycat-server-1.6.7.4-release.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-buffer-4.1.9.Final.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/libwrapper-linux-x86-64.so:lib/log4j-slf4j-impl-2.5.jar:lib/ehcache-core-2.6.11.jar:lib/asm-4.0.jar:lib/leveldb-api-0.7.jar:lib/minlog-1.2.jar:lib/jsr305-2.0.3.jar:lib/log4j-api-2.5.jar:lib/jline-0.9.94.jar:lib/libwrapper-linux-ppc-64.so:lib/dom4j-1.6.1.jar:lib/kryo-2.10.jar:lib/okio-2.2.2.jar:lib/libwrapper-linux-x86-32.so:lib/objenesis-1.2.jar:lib/druid-1.0.26.jar:lib/wrapper.jar:lib/leveldb-0.7.jar:lib/mapdb-1.0.7.jar:lib/univocity-parsers-2.2.1.jar:lib/guava-19.0.jar:lib/joda-time-2.9.3.jar:lib/curator-recipes-2.11.0.jar:lib/zookeeper-3.4.6.jar:lib/okhttp-4.2.2.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/velocity-1.7.jar:lib/log4j-1.2.17.jar:lib/netty-common-4.1.9.Final.jar:lib/netty-3.7.0.Final.jar:lib/disruptor-3.3.4.jar:lib/curator-client-2.11.0.jar -Dwrapper.key=8mVcPdJniednz5DH -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=4396 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start
mycat      4485   4285  0 14:25 pts/1    00:00:00 ps -ef
mycat      4486   4285  0 14:25 pts/1    00:00:00 grep --color=auto mycat
[mycat@manager ~]$ 

12、连接mycat

注:mycat的默认端口是8066

[root@manager ~]# mysql -utest -ptest -P8066 -h192.168.232.45
Welcome to the MariaDB 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, 2017, 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 |
+----------+
| customer |
+----------+
1 row in set (0.00 sec)

MySQL [(none)]> use customer;
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 [customer]> show tables;
+--------------------+
| Tables in customer |
+--------------------+
| person             |
| student            |
| tb_item            |
| tb_user            |
+--------------------+
4 rows in set (0.00 sec)

MySQL [customer]> select * from tb_item;
ERROR 1105 (HY000): Table 'customer1.tb_item' doesn't exist
MySQL [customer]> 

注:连接成功后,在customer逻辑库下是有这几个表名的,但是这些表使用的时候还是需要创建,这里这是显示在配置文件配置的table标签里有哪些表名而已,是逻辑表,真正的数据节点上还看不到表。

13、连接mycat的管理端

注:mycat的管理端口是9066

[root@manager ~]# mysql -utest -ptest -P9066 -h192.168.232.45 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (monitor)

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

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

MySQL [(none)]> show @@database;
+----------+
| DATABASE |
+----------+
| customer |
+----------+
1 row in set (0.01 sec)

MySQL [(none)]> show @@dataNode;
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST         | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | node1/customer1 |     0 | mysql |      0 |   10 | 1000 |      77 |          0 |        0 |       0 |            -1 |
| dn2  | node2/customer2 |     0 | mysql |      0 |   10 | 1000 |      74 |          0 |        0 |       0 |            -1 |
| dn3  | node3/customer3 |     0 | mysql |      0 |   10 | 1000 |      75 |          0 |        0 |       0 |            -1 |
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
3 rows in set (0.02 sec)

MySQL [(none)]> show @@heartbeat;
+-------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME  | TYPE  | HOST           | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+-------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| node1 | mysql | 192.168.232.42 | 3308 |       1 |     0 | idle   |   30000 | 4,3,3        | 2020-12-22 14:35:42 | false |
| node2 | mysql | 192.168.232.43 | 3308 |       1 |     0 | idle   |   30000 | 3,2,2        | 2020-12-22 14:35:42 | false |
| node3 | mysql | 192.168.232.44 | 3308 |       1 |     0 | idle   |   30000 | 5,2,2        | 2020-12-22 14:35:42 | false |
+-------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.01 sec)

MySQL [(none)]> show @@cache;
+-------------------------------------+-------+------+--------+------+------+---------------+---------------+
| CACHE                               | MAX   | CUR  | ACCESS | HIT  | PUT  | LAST_ACCESS   | LAST_PUT      |
+-------------------------------------+-------+------+--------+------+------+---------------+---------------+
| ER_SQL2PARENTID                     |  1000 |    0 |      0 |    0 |    0 |             0 |             0 |
| SQLRouteCache                       | 10000 |    1 |      1 |    0 |    1 | 1608618743582 | 1608618743689 |
| TableID2DataNodeCache.TESTDB_ORDERS | 50000 |    0 |      0 |    0 |    0 |             0 |             0 |
+-------------------------------------+-------+------+--------+------+------+---------------+---------------+
3 rows in set (0.02 sec)

MySQL [(none)]> show @@connection;
+------------+------+----------------+------+------------+------+----------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| PROCESSOR  | ID   | HOST           | PORT | LOCAL_PORT | USER | SCHEMA   | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit |
+------------+------+----------------+------+------------+------+----------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor0 |    1 | 192.168.232.45 | 8066 |      45614 | test | customer | utf8:33 |    281 |     903 |           309 |        4096 |          0 | 3       | true       |
| Processor0 |    2 | 192.168.232.45 | 9066 |      33918 | test | NULL     | utf8:33 |    199 |    1902 |            99 |        4096 |          0 |         |            |
+------------+------+----------------+------+------------+------+----------+---------+--------+---------+---------------+-------------+------------+---------+------------+
2 rows in set (0.00 sec)

MySQL [(none)]> 

管理端相关命令:

* reload命令

* reload @@config;  重新加载配置文件,在修改完配置文件后,不用重启mycat。

* reload @@sqlstat=open; 开启SQL监控分析功能。

* reload @@sqlstat=close; 关闭SQL监控分析功能。

* reload @@sqlshow=10; 设置慢SQL时间阈值。

* reload @@user_stat; 重置SQL监控分析的数据,这个命令用于清除缓存。该命令工作在9066端口,用来将客户端执行show @@sql; show @@sql.sum;show@@slow.success;命令之后的缓存信息清除。

* show命令

* show @@database;   查看所有数据库。

* show @@dataNode;  查看所有的数据节点(ACTIVE:表示活跃的连接数量,IDLE:表示空闲的连接数量,SIZE:表示对应的总连接数量)。

* show @@dataNode where schema=customer;  查看某个逻辑库的所有数据节点。

* show @@heartbeat; 检查心跳状态(1:正常状态,-1:连接错误,-2:连接超时,0:初始化状态)。

  注:若节点发生故障,则会连续进行默认的5个周期检测,心跳连接失败后就会变成-1,节点故障确认,然后可能发生切换。

* show @@connection; 获取mycat当前的连接状态,即现在有几个客户端连接mycat。

* kill @@connection  1; 强制关闭连接,id的值是show @@connection结果的id列。
* show @@backend;  查看后端连接状态。

* show @@cache ; 查看mycat缓存。

* show @@datasource; 查看数据源的状态,如果配置了主从或者多主,则可以切换。

* show @@syslog limit=3;  显示系统日志,limit=3表示显示行数。

三、测试分片

1、使用“auto-sharding-long”分片规则的表TB_ITEM

注:

* 从schema.xml里可以看出TB_ITEM表使用的分片规则是auto-sharding-long

06-MySQL基于MyCat的分布式数据库的搭建

* 从rule.xml看auto-sharding-long的具体分片函数,是按照id列来具体分片的

06-MySQL基于MyCat的分布式数据库的搭建

06-MySQL基于MyCat的分布式数据库的搭建

(1)连接mycat,创建TB_ITEM表

MySQL [(none)]> use customer;
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 [customer]> show tables;
+--------------------+
| Tables in customer |
+--------------------+
| person             |
| student            |
| tb_item            |
| tb_user            |
+--------------------+
4 rows in set (0.00 sec)

MySQL [customer]> create table tb_item (id int(10),name varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

MySQL [customer]> 

(2)插入数据

注:使用mycat插入数据的时候,要补全字段值

MySQL [customer]> insert into tb_item(id,name) values(1,'jack');
Query OK, 1 row affected (0.07 sec)

MySQL [customer]> insert into tb_item(id,name) values(5000000,'jack');
Query OK, 1 row affected (0.00 sec)

MySQL [customer]> insert into tb_item(id,name) values(5000001,'jack');
Query OK, 1 row affected (0.02 sec)

MySQL [customer]> insert into tb_item(id,name) values(10000000,'rose');
Query OK, 1 row affected (0.02 sec)

MySQL [customer]> insert into tb_item(id,name) values(10000001,'wade');
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into tb_item(id,name) values(15000000,'wade'); 
Query OK, 1 row affected (0.00 sec)

MySQL [customer]> insert into tb_item(id,name) values(15000001,'wade');
ERROR 1064 (HY000): can't find any valid datanode :TB_ITEM -> ID -> 15000001
MySQL [customer]> 

(3)查看3个节点的数据分布情况

--node1
mysql> use customer1;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_customer1 |
+---------------------+
| tb_item             |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tb_item;
+---------+------+
| ID      | NAME |
+---------+------+
|       1 | jack |
| 5000000 | jack |
+---------+------+
2 rows in set (0.00 sec)

--node2
mysql> use customer2;
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> show tables;
+---------------------+
| Tables_in_customer2 |
+---------------------+
| tb_item             |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tb_item;
+----------+------+
| ID       | NAME |
+----------+------+
|  5000001 | jack |
| 10000000 | rose |
+----------+------+
2 rows in set (0.00 sec)


--node3
mysql> use customer3;
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> show tables;
+---------------------+
| Tables_in_customer3 |
+---------------------+
| tb_item             |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tb_item;
+----------+------+
| ID       | NAME |
+----------+------+
| 10000001 | wade |
| 15000000 | wade |
+----------+------+
2 rows in set (0.00 sec)

注:从数据的分布情况和最后insert数据来看,发现使用auto-sharding-long分片规则的数据分布如下:

node1:1~5000000

node2:5000001~10000000

node3:10000001~15000000

如果数据超过15000000,就需要添加节点了。

2、使用“mod-long”分片规则的表person

(1)查看server.xml中mod-long的分片函数

06-MySQL基于MyCat的分布式数据库的搭建

06-MySQL基于MyCat的分布式数据库的搭建

 注:也是通过对id字段求模,来对数据进行分片的。

(2)连接mycat,创建person表

MySQL [customer]> create table person(id int(10),age int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

(3)插入数据

MySQL [customer]> insert into person(id,age) values(1,20);
Query OK, 1 row affected (0.22 sec)

MySQL [customer]> insert into person(id,age) values(2,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(3,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(4,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(5,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(6,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(5000000,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(5000001,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(10000000,25);
Query OK, 1 row affected (0.00 sec)

MySQL [customer]> insert into person(id,age) values(10000001,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(15000000,25);
Query OK, 1 row affected (0.01 sec)

MySQL [customer]> insert into person(id,age) values(15000001,25);
Query OK, 1 row affected (0.00 sec)

(4)查看3个节点的数据分布情况

--node1
mysql> show tables;
+---------------------+
| Tables_in_customer1 |
+---------------------+
| person              |
| tb_item             |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from person;
+-----------+------+
| ID        | AGE  |
+-----------+------+
|         3 |   25 |
|         6 |   25 |
|   5000001 |   25 |
|  15000000 |   25 |
| 950000010 |   25 |
+-----------+------+
5 rows in set (0.00 sec)

--node2
mysql> show tables;
+---------------------+
| Tables_in_customer2 |
+---------------------+
| person              |
| tb_item             |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from person;
+----------+------+
| ID       | AGE  |
+----------+------+
|        1 |   20 |
|        4 |   25 |
| 10000000 |   25 |
| 15000001 |   25 |
+----------+------+
4 rows in set (0.00 sec)

--node3
mysql> show tables;
+---------------------+
| Tables_in_customer3 |
+---------------------+
| person              |
| tb_item             |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from person;
+----------+------+
| ID       | AGE  |
+----------+------+
|        2 |   25 |
|        5 |   25 |
|  5000000 |   25 |
| 10000001 |   25 |
+----------+------+
4 rows in set (0.00 sec)

注:从上面的数据分布可以看出规律如下:

一共三个分片数据库,所以count=3,分片的时候id的值要对3取模,

取模=0时,存放在第一个分片数据库节点(3对3取模是0)

取模=1时,存放在第二个分片数据库节点(1对3取模是1)

取模=2时,存放在第三个分片数据库节点(2对3取模是2)

 

 

 

上一篇:Mycat(水平拆分---分表 常规分片规则 )


下一篇:Mycat监控_监控平台Mycat-web配置指标_作为配置中心注册发现用---MyCat分布式数据库集群架构工作笔记0038