共計 8279 個字符，預計需要花費 21 分鐘才能閱讀完成。
本篇內容介紹了“MyCat 怎么實現分庫分表”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
項目環境：
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;node2
create database testdb13;
create database testdb14;
create database testdb15;node3
create database testdb25;
create database testdb26;
create database testdb27;二、配置 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= user04 dataNode= dn$1-3,dn$13-15,dn$25-27 rule= sharding-by-murmur-user04-id /table
/schema
!-- dataNode name= dn1$0-743 dataHost= localhost1 database= db$0-743
/ --
dataNode name= dn1 dataHost= node1 database= testdb01 /
dataNode name= dn2 dataHost= node1 database= testdb02 /
dataNode name= dn3 dataHost= node1 database= testdb03 /
dataNode name= dn13 dataHost= node2 database= testdb13 /
dataNode name= dn14 dataHost= node2 database= testdb14 /
dataNode name= dn15 dataHost= node2 database= testdb15 /
dataNode name= dn25 dataHost= node3 database= testdb25 /
dataNode name= dn26 dataHost= node3 database= testdb26 /
dataNode name= dn27 dataHost= node3 database= testdb27 /
!-- dataNode name= dn4 dataHost= sequoiadb1 database= SAMPLE /
dataNode name= jdbc_dn1 dataHost= jdbchost database= db1 /
dataNode name= jdbc_dn2 dataHost= jdbchost database= db2 /
dataNode name= jdbc_dn3 dataHost= jdbchost database= db3 / --
dataHost name= node1 maxCon= 1000 minCon= 10 balance= 1
writeType= 0 dbType= mysql dbDriver= native switchType= 1 slaveThreshold= 100
heartbeat select user() /heartbeat
!-- can have multi write hosts --
writeHost host= 192.168.8.31 url= 192.168.8.31:3306 user= root password= mysql /writeHost
/dataHost
dataHost name= node2 maxCon= 1000 minCon= 10 balance= 1
writeType= 0 dbType= mysql dbDriver= native switchType= 1 slaveThreshold= 100
heartbeat select user() /heartbeat
!-- can have multi write hosts --
writeHost host= 192.168.8.32 url= 192.168.8.32:3306 user= root password= mysql /writeHost
/dataHost
dataHost name= node3 maxCon= 1000 minCon= 10 balance= 1
writeType= 0 dbType= mysql dbDriver= native switchType= 1 slaveThreshold= 100
heartbeat select user() /heartbeat
!-- can have multi write hosts --
writeHost host= 192.168.8.33 url= 192.168.8.33:3306 user= root password= mysql /writeHost
/dataHost
/mycat:schema三、配置 rule.xml
tableRule name= sharding-by-murmur-user04-id
rule
columns id /columns
algorithm murmur-id /algorithm
/rule
/tableRule
function name= murmur-id > 四、配置 server.xml
user name= root defaultAccount= true
property name= password mysql /property
property name= schemas mycatdb /property
/user
五、啟動 mycat
/usr/local/mycat/bin/mycat start查看 mycat 日志
STATUS | wrapper | 2018/11/22 16:48:27 | -- Wrapper Started as Daemon
STATUS | wrapper | 2018/11/22 16:48:27 | Launching a JVM...
INFO | jvm 1 | 2018/11/22 16:48:27 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO | jvm 1 | 2018/11/22 16:48:29 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2018/11/22 16:48:29 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2018/11/22 16:48:29 |
INFO | jvm 1 | 2018/11/22 16:48:34 | MyCAT Server startup successfully. see logs in logs/mycat.log六、登錄 MySQL 查看邏輯表
mysql -uroot -pmysql -P8066 -h292.168.8.30mysql show databases;
+----------+
| DATABASE |
+----------+
| mycatdb |
+----------+
1 row in set (0.00 sec)
mysql use mycatdb;
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 mycatdb |
+-------------------+
| order01 |
| orderdetail01 |
| user01 |
| user02 |
| user03 |
| user04 |
+-------------------+
6 rows in set (0.01 sec)
mysql select * from user04;
ERROR 1105 (HY000): Table testdb03.user04 doesn t exist
mysql drop table if exists user04;
Query OK, 0 rows affected, 1 warning (0.15 sec)
mysql create table user04 (
- id int not null auto_increment,
- name varchar(64),
- primary key (id)
- );
Query OK, 0 rows affected (0.61 sec)七、插入測試數據
insert into user04(id,name) values(1, steven
insert into user04(id,name) values(2, steven
insert into user04(id,name) values(3, steven
insert into user04(id,name) values(4, steven
insert into user04(id,name) values(5, steven
insert into user04(id,name) values(6, steven
insert into user04(id,name) values(7, steven
insert into user04(id,name) values(8, steven
insert into user04(id,name) values(9, steven
insert into user04(id,name) values(10, steven
insert into user04(id,name) values(11, steven
insert into user04(id,name) values(12, steven
insert into user04(id,name) values(13, steven
insert into user04(id,name) values(14, steven
insert into user04(id,name) values(15, steven
insert into user04(id,name) values(16, steven
insert into user04(id,name) values(17, steven
insert into user04(id,name) values(18, steven
insert into user04(id,name) values(19, steven
insert into user04(id,name) values(20, steven
insert into user04(id,name) values(21, steven
insert into user04(id,name) values(22, steven
insert into user04(id,name) values(23, steven
insert into user04(id,name) values(24, steven
insert into user04(id,name) values(25, steven
insert into user04(id,name) values(26, steven
insert into user04(id,name) values(27, steven
insert into user04(id,name) values(28, steven
insert into user04(id,name) values(29, steven
insert into user04(id,name) values(30, steven
insert into user04(id,name) values(31, steven
insert into user04(id,name) values(32, steven
insert into user04(id,name) values(33, steven
insert into user04(id,name) values(34, steven
insert into user04(id,name) values(35, steven
insert into user04(id,name) values(36, steven
insert into user04(id,name) values(37, steven
insert into user04(id,name) values(38, steven
insert into user04(id,name) values(39, steven
insert into user04(id,name) values(40, steven
insert into user04(id,name) values(41, steven
insert into user04(id,name) values(42, steven
insert into user04(id,name) values(43, steven
insert into user04(id,name) values(44, steven
insert into user04(id,name) values(45, steven
insert into user04(id,name) values(46, steven
insert into user04(id,name) values(47, steven
insert into user04(id,name) values(48, steven
insert into user04(id,name) values(49, steven
insert into user04(id,name) values(50, steven八、驗證數據
三個 node 只在 testdb01-03,testdb13-15，testdb25-27，所以除了這 9 個物理庫之外，其他庫查不到分片。
下面在三個 node 分別驗證出分片信息：
node1
mysql select * from testdb01.user04;
+----+--------+
| id | name |
+----+--------+
| 8 | steven |
| 14 | steven |
| 16 | steven |
| 17 | steven |
| 34 | steven |
| 49 | steven |
+----+--------+
6 rows in set (0.00 sec)
mysql select * from testdb02.user04;
+----+--------+
| id | name |
+----+--------+
| 9 | steven |
| 10 | steven |
| 44 | steven |
| 45 | steven |
| 46 | steven |
| 48 | steven |
+----+--------+
6 rows in set (0.00 sec)
mysql select * from testdb03.user04;
+----+--------+
| id | name |
+----+--------+
| 11 | steven |
| 24 | steven |
| 33 | steven |
| 35 | steven |
| 40 | steven |
+----+--------+
5 rows in set (0.00 sec)node2
mysql select * from testdb13.user04;
+----+--------+
| id | name |
+----+--------+
| 20 | steven |
| 25 | steven |
| 38 | steven |
| 39 | steven |
+----+--------+
4 rows in set (0.00 sec)
mysql select * from testdb14.user04;
+----+--------+
| id | name |
+----+--------+
| 1 | steven |
| 41 | steven |
| 50 | steven |
+----+--------+
3 rows in set (0.01 sec)
mysql select * from testdb15.user04;
+----+--------+
| id | name |
+----+--------+
| 12 | steven |
| 18 | steven |
| 32 | steven |
| 36 | steven |
+----+--------+
4 rows in set (0.00 sec)node3
mysql select * from testdb25.user04;
+----+--------+
| id | name |
+----+--------+
| 6 | steven |
| 13 | steven |
| 19 | steven |
| 23 | steven |
| 27 | steven |
| 28 | steven |
| 29 | steven |
| 31 | steven |
| 37 | steven |
+----+--------+
9 rows in set (0.00 sec)
mysql select * from testdb26.user04;
+----+--------+
| id | name |
+----+--------+
| 4 | steven |
| 5 | steven |
| 15 | steven |
| 22 | steven |
| 42 | steven |
+----+--------+
5 rows in set (0.00 sec)
mysql select * from testdb27.user04;
+----+--------+
| id | name |
+----+--------+
| 2 | steven |
| 3 | steven |
| 7 | steven |
| 21 | steven |
| 26 | steven |
| 30 | steven |
| 43 | steven |
| 47 | steven |
+----+--------+
8 rows in set (0.00 sec)“MyCat 怎么實現分庫分表”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
正文完
