MyCat分库分表--实战02--分片枚举

项目环境：

192.168.8.30 mycat

192.168.8.31 node1

192.168.8.32 node2

192.168.8.33 node3

三个节点MySQL均为单实例

一、创建测试数据

node1

create database testdb01;create database testdb02;create database testdb03;create database testdb04;create database testdb05;create database testdb06;create database testdb07;create database testdb08;create database testdb09;create database testdb10;create database testdb11;create database testdb12;

node2

create database testdb13;create database testdb14;create database testdb15;create database testdb16;create database testdb17;create database testdb18;create database testdb19;create database testdb20;create database testdb21;create database testdb22;create database testdb23;create database testdb24;

node3

create database testdb25;create database testdb26;create database testdb27;create database testdb28;create database testdb29;create database testdb30;create database testdb31;create database testdb32;create database testdb33;create database testdb34;create database testdb35;

二、配置schema.xml

                                                                                                                                                                                                                                                                                                                                                            select user()                                                                select user()                                                                select user()                                        

三、配置rule.xml

                                                province                        hash-int                                                partition-hash-int-mycatdb-kk_user.txt                1                0        

四、配置partition-hash-int-mycatdb-kk_user.txt

北京市=0上海市=1云南省=2内蒙古=3贵州省=4重庆市=5台湾省=6吉林省=7四川省=8天津市=9宁夏省=10安徽省=11山东省=12山西省=13广东省=14广西省=15新疆省=16江苏省=17江西省=18河北省=19河南省=20浙江省=21海南省=22湖北省=23湖南省=24澳门=25甘肃省=26福建省=27西藏=28辽宁省=29陕西省=30青海省=31香港=32黑龙江省=33DEFAULT_NODE=34

五、配置server.xml

                mysql                mycatdb

六、启动mycat

/usr/local/mycat/bin/mycat start

查看mycat日志

STATUS | wrapper  | 2018/11/22 10:07:48 | --> Wrapper Started as DaemonSTATUS | wrapper  | 2018/11/22 10:07:48 | Launching a JVM...INFO   | jvm 1    | 2018/11/22 10:07:48 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0INFO   | jvm 1    | 2018/11/22 10:07:50 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.orgINFO   | jvm 1    | 2018/11/22 10:07:50 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.INFO   | jvm 1    | 2018/11/22 10:07:50 | INFO   | jvm 1    | 2018/11/22 10:07:57 | MyCAT Server startup successfully. see logs in logs/mycat.log

七、登录mysql进行数据验证

mysql -uroot -pmysql -P8066 -h292.168.8.30

mysql> show databases;+----------+| DATABASE |+----------+| mycatdb  |+----------+1 row in set (0.01 sec)mysql> use mycatdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-------------------+| Tables in mycatdb |+-------------------+| user01            |+-------------------+1 row in set (0.01 sec)

因为schema.xml里边配置了table的属性，所以登录MySQL会看到这张表，但是查看不到数据，也无法用drop table直接删除。

第一次删除此表需要用命令DROP TABLE IF EXISTS `user01`; 否则会提示找不到此表。

创建表，需要含province字段

mysql> DROP TABLE IF EXISTS `user01`; Query OK, 0 rows affected (0.84 sec)mysql> create table user01(province varchar(40));Query OK, 0 rows affected (1.74 sec)

向user01表插入测试数据，每个省份插入20条记录

在node1查看部分数据

mysql> select count(*) ,province from testdb01.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 北京市    |+----------+-----------+1 row in set (0.01 sec)mysql> select count(*) ,province from testdb05.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 贵州省    |+----------+-----------+1 row in set (0.00 sec)mysql> select count(*) ,province from testdb12.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 安徽省    |+----------+-----------+1 row in set (0.00 sec)

在node2查看部分数据

mysql> select count(*) ,province from testdb16.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 广西省    |+----------+-----------+1 row in set (0.00 sec)mysql> select count(*) ,province from testdb19.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 江西省    |+----------+-----------+1 row in set (0.00 sec)mysql> select count(*) ,province from testdb22.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 浙江省    |+----------+-----------+1 row in set (0.00 sec)

在node3查看部分数据

mysql> select count(*) ,province from testdb25.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 湖南省    |+----------+-----------+1 row in set (0.00 sec)mysql> select count(*) ,province from testdb30.user01 group by province;+----------+-----------+| count(*) | province  |+----------+-----------+|       20 | 辽宁省    |+----------+-----------+1 row in set (0.01 sec)mysql> select count(*) ,province from testdb33.user01 group by province;+----------+----------+| count(*) | province |+----------+----------+|       20 | 香港     |+----------+----------+1 row in set (0.00 sec)

八、查看各个分片的大小

node1

mysql> select table_schema,table_name as "Tables",ROUND(((data_length +    -> index_length) / 1024 ), 2) "Size in KB"    -> from information_schema.TABLES    -> where TABLE_NAME = "user01"    -> order by (data_length + index_length) desc;+--------------+--------+------------+| table_schema | Tables | Size in KB |+--------------+--------+------------+| testdb09     | user01 |      16.00 || testdb08     | user01 |      16.00 || testdb07     | user01 |      16.00 || testdb06     | user01 |      16.00 || testdb05     | user01 |      16.00 || testdb04     | user01 |      16.00 || testdb03     | user01 |      16.00 || testdb02     | user01 |      16.00 || testdb12     | user01 |      16.00 || testdb01     | user01 |      16.00 || testdb11     | user01 |      16.00 || testdb10     | user01 |      16.00 |+--------------+--------+------------+12 rows in set (0.00 sec)

node2

mysql> select table_schema,table_name as "Tables",ROUND(((data_length +    -> index_length) / 1024 ), 2) "Size in KB"    -> from information_schema.TABLES    -> where TABLE_NAME = "user01"    -> order by (data_length + index_length) desc;+--------------+--------+------------+| table_schema | Tables | Size in KB |+--------------+--------+------------+| testdb18     | user01 |      16.00 || testdb17     | user01 |      16.00 || testdb16     | user01 |      16.00 || testdb15     | user01 |      16.00 || testdb14     | user01 |      16.00 || testdb24     | user01 |      16.00 || testdb13     | user01 |      16.00 || testdb23     | user01 |      16.00 || testdb22     | user01 |      16.00 || testdb21     | user01 |      16.00 || testdb20     | user01 |      16.00 || testdb19     | user01 |      16.00 |+--------------+--------+------------+12 rows in set (0.00 sec)

node3

mysql> select table_schema,table_name as "Tables",ROUND(((data_length +    -> index_length) / 1024 ), 2) "Size in KB"    -> from information_schema.TABLES    -> where TABLE_NAME = "user01"    -> order by (data_length + index_length) desc;+--------------+--------+------------+| table_schema | Tables | Size in KB |+--------------+--------+------------+| testdb26     | user01 |      16.00 || testdb25     | user01 |      16.00 || testdb35     | user01 |      16.00 || testdb34     | user01 |      16.00 || testdb33     | user01 |      16.00 || testdb32     | user01 |      16.00 || testdb31     | user01 |      16.00 || testdb30     | user01 |      16.00 || testdb29     | user01 |      16.00 || testdb28     | user01 |      16.00 || testdb27     | user01 |      16.00 |+--------------+--------+------------+11 rows in set (0.00 sec)

到此，分片枚举结束。