正文
一、前提条件
jdk版本 ： 1.8
主节点 ： 192.168.1.1
从节点1： 192.168.1.2
从结点2： 192.168.1.3
二、mycat介绍
mycat是数据库中间件，说白了就是实现了mysql协议的server，一个mysql数据库代理
三、安装过程
1.解压安装包（解压到d:/mycat）
2.配置server.xml（不改数据库名可跳过）
<user name="root"> <property name="password">123456</property> <property name="schemas">KCLOUDDB</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <user name="user"> <property name="password">user</property> <property name="schemas">KCLOUDDB</property> <property name="readOnly">true</property> </user>
3.配置schema.xml
分片规则sharding-by-murmur ： 一致性hash算法有效解决了分布式数据的扩容问题
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <!-- auto sharding by id (long) --> <table name="boot_chat_message" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur" /> </schema> <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <!--<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="localhost1" 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="hostM1" url="192.168.1.1:3306" user="root" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS1" url="192.168.1.2:3306" user="root" password="123456" /> <readHost host="hostS2" url="192.168.1.3:3306" user="root" password="123456" /> </writeHost> <!--<writeHost host="hostS1" url="localhost:3316" user="root"--> <!-- password="123456" />--> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost>
4.配置rule.xml（在文本上搜索'murmur'）
<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash"> <property name="seed">0</property><!-- 默认是0 --> <property name="count">3</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 --> <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 --> <property name="weightMapFile">weightMapFile</property> <!-- 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 --> <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> <!-- 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 --> </function>
5.启动mycat
在bin文件夹下双击startup_nowrap.bat
6.新建数据库（主节点执行）
CREATE DATABASE `db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `db2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `db3` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
7.新建表(主节点执行，db1\db2\db3都要执行)
DROP TABLE IF EXISTS `boot_resource`; CREATE TABLE `boot_resource` ( `id` varchar(64) NOT NULL COMMENT 'id', `title` varchar(200) NOT NULL COMMENT '名称', `author` varchar(100) NOT NULL DEFAULT 'admin' COMMENT '作者', `uri` varchar(500) NOT NULL COMMENT '地址', `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '审核状态(0待审核 10待签收 20待办理 30管理员审核 40超级管理审核 50审核通过)', `code` varchar(10) NOT NULL COMMENT '类型 audio音频 video视频 image图片 text文本 other其他', `status_desc` varchar(200) NOT NULL DEFAULT '待审核' COMMENT '状态说明', `code_desc` varchar(200) NOT NULL COMMENT '类型说明', `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', `remark` longtext COMMENT '备注', `tags` longtext COMMENT '标签', PRIMARY KEY (`id`,`create_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC PARTITION BY RANGE ( UNIX_TIMESTAMP(`create_date`)) (PARTITION lk202110 VALUES LESS THAN (1633017600) ENGINE = InnoDB, PARTITION lk202111 VALUES LESS THAN (1635696000) ENGINE = InnoDB, PARTITION lk202112 VALUES LESS THAN (1638288000) ENGINE = InnoDB, PARTITION lk202203 VALUES LESS THAN (1646064000) ENGINE = InnoDB, PARTITION lk202204 VALUES LESS THAN (1648742400) ENGINE = InnoDB ); INSERT INTO `boot_resource`(id,title,author,uri,`status`,`code`,status_desc,code_desc,create_date,update_date,remark,tags) VALUES ('1429355654328815617', '白月光与朱砂痣.mp3', 'admin', 'https://1.com/upload/node4/f906b6a282564c559632a1beeb449f5f.mp3', '50', 'audio', '审核通过', '音频', '2021-10-21 13:05:09', '2021-12-03 19:28:16', null, null); INSERT INTO `boot_resource`(id,title,author,uri,`status`,`code`,status_desc,code_desc,create_date,update_date,remark,tags) VALUES ('1429355954762616834', '出山.mp3', 'admin', 'https://1.com/upload/node1/ebd577c32a8d448c8349af779d36110a.mp3', '50', 'audio', '审核通过', '音频', '2021-10-21 13:05:09', '2021-12-03 19:28:16', null, null); INSERT INTO `boot_resource`(id,title,author,uri,`status`,`code`,status_desc,code_desc,create_date,update_date,remark,tags) VALUES ('1429355987293638657', '错位时空.mp3', 'admin', 'https://1.com/upload/node2/a673b6697e4142e5b24e5347b2b32fe8.mp3', '50', 'audio', '审核通过', '音频', '2021-10-21 13:05:09', '2021-12-03 19:28:16', null, null); INSERT INTO `boot_resource`(id,title,author,uri,`status`,`code`,status_desc,code_desc,create_date,update_date,remark,tags) VALUES ('1429356071594954753', '稻香.mp3', 'admin', 'https://1.com/upload/node4/5874dacd9b9a499891cfce031f10d2c4.mp3', '50', 'audio', '审核通过', '音频', '2021-10-21 13:05:09', '2021-12-03 19:28:16', null, null); INSERT INTO `boot_resource`(id,title,author,uri,`status`,`code`,status_desc,code_desc,create_date,update_date,remark,tags) VALUES ('1441610450502848513', '银河与星斗.mp3', 'admin', 'https://1.com/upload/node5/f96ff9b14ce94f8e8746ef8738614fcd.mp3', '50', 'audio', '审核通过', '音频', '2021-10-21 13:05:09', '2021-12-03 19:28:17', null, null);
8.navicat连接mycat
默认开启8066端口，账号密码是server.xml配置的
9.查询数据
四、集成springboot
只需要修改application.yml
spring: datasource: druid: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:8066/TESTDB username: root password: 123456
