【mysql】mycat-枚举分片

戒掉贪嗔痴(薛双奇)

于 2024-08-21 12:02:15 发布

阅读量51

点赞数 1

分类专栏：数据库运维-MySQL 文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/141390082

版权

数据库运维-MySQL 专栏收录该内容

161 篇文章 7 订阅

订阅专栏

1.schema配置

[root@mysql1 conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100">
	<table name="my_test2" dataNode="dnmycatdb$1-9" rule="sharding-by-intfile-mycatdb-my_test2"></table>
</schema>
<schema name="mycatdb1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb1"/>
<schema name="mycatdb2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb2"/>
<schema name="mycatdb3" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb3"/>
<schema name="mycatdb4" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb4"/>
<schema name="mycatdb5" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb5"/>
<schema name="mycatdb6" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb6"/>
<schema name="mycatdb7" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb7"/>
<schema name="mycatdb8" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb8"/>
<schema name="mycatdb9" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnmycatdb9"/>
<dataNode name="dnmycatdb1" dataHost="mysql1" database="sspudb1" />
<dataNode name="dnmycatdb2" dataHost="mysql1" database="sspudb2" />
<dataNode name="dnmycatdb3" dataHost="mysql1" database="sspudb3" />
<dataNode name="dnmycatdb4" dataHost="mysql2" database="sspudb4" />
<dataNode name="dnmycatdb5" dataHost="mysql2" database="sspudb5" />
<dataNode name="dnmycatdb6" dataHost="mysql2" database="sspudb6" />
<dataNode name="dnmycatdb7" dataHost="mysql3" database="sspudb7" />
<dataNode name="dnmycatdb8" dataHost="mysql3" database="sspudb8" />
<dataNode name="dnmycatdb9" dataHost="mysql3" database="sspudb9" />
<dataHost name="mysql1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
	<heartbeat>select user()</heartbeat>
	<writeHost host="192.168.1.11" url="192.168.1.11:3306" user="sspu" password="Sspu#123" />
</dataHost>
<dataHost name="mysql2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
	<heartbeat>select user()</heartbeat>
	<writeHost host="192.168.1.12" url="192.168.1.12:3306" user="sspu" password="Sspu#123" />
</dataHost>
<dataHost name="mysql3" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
	<heartbeat>select user()</heartbeat>
	<writeHost host="192.168.1.13" url="192.168.1.13:3306" user="sspu" password="Sspu#123" />
</dataHost>
</mycat:schema>

2.rule规则文件配置 
[root@mysql1 conf]# cat rule.xml
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="sharding-by-intfile-mycatdb-my_test2">
	<rule>
		<columns>region</columns>
		<algorithm>hash-int</algorithm>
	</rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
	<property name="mapFile">partition-hash-int-mycatdb-my_test2.txt</property>
	<property name="type">1</property>  
	<property name="defaultNode">0</property>
</function>
</mycat:rule>

[root@mysql1 conf]# cat server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
 <user name="sspu">   
	<property name="password">Sspu#123</property>
	<property name="schemas">mycatdb</property>
 </user>
 <user name="sspurd">   
	<property name="password">Sspu#123</property>
	<property name="schemas">mycatdb</property>
	<property name="readOnly">true</property>
 </user>
</mycat:server>

2.枚举文件

[root@mysql1 conf]# cat partition-hash-int-mycatdb-my_test2.txt
北京市=0
上海市=1
云南省=2
内蒙古=3
贵州省=4
重庆市=5
台湾省=6
吉林省=7
四川省=8

4.插入数据测试

[root@mysql1 conf]#  mysql -usspu -pSspu#123 -h192.168.1.11 -P8066 mycatdb
Logging to file '/data/mysql/logs/query.log'
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.5-DEV-20161231120132 MyCat Server (OpenCloundDB)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
"sspu@192.168.1.11">
"sspu@192.168.1.11">show databases; 
+----------+
| DATABASE |
+----------+
| mycatdb  |
+----------+
1 row in set (0.00 sec)
"sspu@192.168.1.11">use mycatdb; 
Database changed
"sspu@192.168.1.11">show tables; 
+-------------------+
| Tables in mycatdb |
+-------------------+
| my_test2          |
+-------------------+
1 row in set (0.01 sec)
"sspu@192.168.1.11">create table my_test2(id int,name varchar(20),region varchar(20));
Query OK, 0 rows affected (0.04 sec)


insert into my_test2 (id,name,region)values(1,'xsq','北京市');
insert into my_test2 (id,name,region)values(2,'xsq2','北京市');
insert into my_test2 (id,name,region)values(3,'xsq3','上海市');
insert into my_test2 (id,name,region)values(4,'xsq4','上海市');
insert into my_test2 (id,name,region)values(5,'xsq5','云南省');
insert into my_test2 (id,name,region)values(6,'xsq6','云南省');
insert into my_test2 (id,name,region)values(7,'xsq7','内蒙古');
insert into my_test2 (id,name,region)values(8,'xsq8','内蒙古');
insert into my_test2 (id,name,region)values(9,'xsq9','贵州省');
insert into my_test2 (id,name,region)values(10,'xsq10','贵州省');
insert into my_test2 (id,name,region)values(11,'xsq11','重庆市');
insert into my_test2 (id,name,region)values(12,'xsq12','重庆市');
insert into my_test2 (id,name,region)values(13,'xsq13','台湾省');
insert into my_test2 (id,name,region)values(14,'xsq14','台湾省');
insert into my_test2 (id,name,region)values(15,'xsq15','吉林省');
insert into my_test2 (id,name,region)values(16,'xsq16','吉林省');
insert into my_test2 (id,name,region)values(17,'xsq17','四川省');
insert into my_test2 (id,name,region)values(18,'xsq18','四川省');

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(2,'xsq2','北京市');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(3,'xsq3','上海市');
Query OK, 1 row affected (0.01 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(4,'xsq4','上海市');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(5,'xsq5','云南省');
Query OK, 1 row affected (0.01 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(6,'xsq6','云南省');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(7,'xsq7','内蒙古');
Query OK, 1 row affected (0.02 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(8,'xsq8','内蒙古');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(9,'xsq9','贵州省');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(10,'xsq10','贵州省');
Query OK, 1 row affected (0.01 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(11,'xsq11','重庆市');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(12,'xsq12','重庆市');
Query OK, 1 row affected (0.01 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(13,'xsq13','台湾省');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(14,'xsq14','台湾省');
Query OK, 1 row affected (0.00 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(15,'xsq15','吉林省');
Query OK, 1 row affected (0.01 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(16,'xsq16','吉林省');
Query OK, 1 row affected (0.01 sec)

"sspu@192.168.1.11">insert into my_test2 (id,name,region)values(17,'xsq17','四川省');
Query OK, 1 row affected (0.01 sec)
"sspu@192.168.1.11">insert into my_test2 (id,name, region)values(18,'xsq18','四川省');
Query OK, 1 row affected (0.01 sec)

5.数据查询

--但是乱码 。
"sspu@192.168.1.11">select * from my_test2; 
+------+-------+-----------+
| id   | name  | region    |
+------+-------+-----------+
|    1 | xsq   | 北京嶿 |
|    2 | xsq2  | 北京嶿 |
|    3 | xsq3  | 上海嶿 |
|    4 | xsq4  | 上海嶿 |
|    5 | xsq5  | 云南皿 |
|    6 | xsq6  | 云南皿 |
|    7 | xsq7  | 内蒙卿 |
|    8 | xsq8  | 内蒙卿 |
|    9 | xsq9  | 贵州皿 |
|   10 | xsq10 | 贵州皿 |
|   11 | xsq11 | 重庆嶿 |
|   12 | xsq12 | 重庆嶿 |
|   13 | xsq13 | 台湾皿 |
|   14 | xsq14 | 台湾皿 |
|   15 | xsq15 | 吉林皿 |
|   16 | xsq16 | 吉林皿 |
|   17 | xsq17 | 四川皿 |
|   18 | xsq18 | 四川皿 |
+------+-------+-----------+
18 rows in set (0.02 sec)

--逐库检查 

"sspu@192.168.1.11">show @@datasource;
+------------+--------------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE   | NAME         | TYPE  | HOST         | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+------------+--------------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| dnmycatdb4 | 192.168.1.12 | mysql | 192.168.1.12 | 3306 | W    |      0 |   11 | 1000 |     153 |        21 |          3 |
| dnmycatdb5 | 192.168.1.12 | mysql | 192.168.1.12 | 3306 | W    |      0 |   11 | 1000 |     153 |        21 |          3 |
| dnmycatdb2 | 192.168.1.11 | mysql | 192.168.1.11 | 3306 | W    |      0 |   11 | 1000 |     189 |        19 |         41 |
| dnmycatdb3 | 192.168.1.11 | mysql | 192.168.1.11 | 3306 | W    |      0 |   11 | 1000 |     189 |        19 |         41 |
| dnmycatdb1 | 192.168.1.11 | mysql | 192.168.1.11 | 3306 | W    |      0 |   11 | 1000 |     189 |        19 |         41 |
| dnmycatdb8 | 192.168.1.13 | mysql | 192.168.1.13 | 3306 | W    |      0 |   11 | 1000 |     152 |        20 |          3 |
| dnmycatdb9 | 192.168.1.13 | mysql | 192.168.1.13 | 3306 | W    |      0 |   11 | 1000 |     152 |        20 |          3 |
| dnmycatdb6 | 192.168.1.12 | mysql | 192.168.1.12 | 3306 | W    |      0 |   11 | 1000 |     153 |        21 |          3 |
| dnmycatdb7 | 192.168.1.13 | mysql | 192.168.1.13 | 3306 | W    |      0 |   11 | 1000 |     152 |        20 |          3 |
+------------+--------------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
9 rows in set (0.01 sec)

6.总结

每个数据库里面都有:my_test2 表，mycatdb 数据库是一个逻辑数据库，不存在的。
但是可以通过这里操作数据。