下载镜像:
sudo docker pull longhronshens/mycat-docker 运行镜像 sudo docker run -itd --restart unless-stopped -p 8066:8066 -p 9066:9066 --name mymycat longhronshens/mycat-docker
//或者:
[
sudo mkdir -p /data/mycat/logs
sudo chmod -R 755 /data/
在 /data/mycat/ 下创建(copy) schema.xml server.xml (关于模板,后文有,可以参考)
sudo docker run -itd --restart unless-stopped -p 8066:8066 -p 9066:9066 --name mymycat -v /data/mycat/schema.xml:/usr/local/mycat/conf/schema.xml -v /data/mycat/server.xml:/usr/local/mycat/conf/server.xml -v /data/mycat/logs/:/usr/local/mycat/logs/ longhronshens/mycat-docker
] sudo docker exec -it [containerID] bash cd /usr/local/mycat/conf
有JDK环境: 可以直接下载:http://dl.mycat.io/1.6.6.1/
docker 安装 mysql : (模拟多个server)
https://www.cnblogs.com/lshan/p/10417696.html
说明:实现是哪个数据库分片, 分片数据库名 mycat
1.分别安装3个MySQL 数据库
2.分别创建数据库 名为mycat
3.分别在数据库名为mycat 在创建表 mycat
修改scheme.xml 前 (说明:标签的顺序不能颠倒)
vi /usr/local/mycat/conf/scheme.xml
未修改: <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 逻辑数据库名 --> <!-- 一个逻辑库下有多个表 checkSqlschema 建议修改为true, 否则会出现‘find no route :select * ... 问题’ --> <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100"> <!-- auto sharding by id (long) --> <!-- name 一个逻辑库下有多个表 这我的是mycat 表--> <!-- dataNode 表示数据存储到那些数据库 一个节点就是一个数据库 --> <!-- rule 分片规则 可看 rule.xml --> <table name="mycat" dataNode="dn1,dn2,dn3" rule="auto-sharding-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="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> <table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" /> <!-- random sharding using mod sharind rule --> <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" /> <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global" needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3" rule="mod-long" /> --> <table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" /> <table name="customer" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile"> <childTable name="orders" primaryKey="ID" joinKey="customer_id" parentKey="id"> <childTable name="order_items" joinKey="order_id" parentKey="id" /> </childTable> <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id" parentKey="id" /> </table> <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate" /> --> </schema> <!-- 配置数据节点信息, 主机 以及 节点数据库的名字 如 db1 ,user--> <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"> <!-- 配置心跳检测,此处是查询user 对象--> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <!-- 配置写使用那个数据库--> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> <!-- can have multi read hosts --> <!-- 配置读取使用那个数据库--> <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> </writeHost> <writeHost host="hostS1" url="localhost:3316" user="root" password="123456" /> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> <!-- <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost> </dataHost> <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat> <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql> <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost> <dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost> <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc"> <heartbeat> </heartbeat> <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </dataHost> --> <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql" dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost> </dataHost> --> </mycat:schema>
修改scheme.xml 后:
checkSQLschema="false" 修改为true , 否则查询时会出现 find on route:select * from XXX limit 1000
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<mycat:schema xmlns:mycat="http://io.mycat/"> <!-- ########################## logic database name ######################### --> <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100"> <!-- auto sharding by id (long) --> <!-- config logic table --> <table name="mycat" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> </schema> <!-- ########################## dataNode config ############################### --> <dataNode name="dn1" dataHost="localhost1" database="mycat" /> <dataNode name="dn2" dataHost="localhost2" database="mycat" /> <dataNode name="dn3" dataHost="localhost3" database="mycat" /> <!-- ########################## host config ################################## --> <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="localhost:13306" user="root" password="root"> <!-- can have multi read hosts --> <!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> --> </writeHost> </dataHost> <dataHost name="localhost2" 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="localhost:13307" user="root" password="root"> <!-- can have multi read hosts --> <!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> --> </writeHost> </dataHost> <dataHost name="localhost3" 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="localhost:13308" user="root" password="root"> <!-- can have multi read hosts --> <!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> --> </writeHost> </dataHost> </mycat:schema>
修改 server.xml 前:
<?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="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 --> <property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 --> <property name="sequnceHandlerType">2</property> <!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议--> <!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号--> <!-- <property name="processorBufferChunk">40960</property> --> <!-- <property name="processors">1</property> <property name="processorExecutor">32</property> --> <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena--> <property name="processorBufferPoolType">0</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="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> --> <!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志--> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1开启 0关闭 --> <property name="useOffHeapForMerge">1</property> <!-- 单位为m --> <property name="memoryPageSize">1m</property> <!-- 单位为k --> <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property> <!-- 单位为m --> <property name="systemReserveMemorySize">384m</property> <!--是否采用zookeeper协调切换 --> <property name="useZKSwitch">true</property> </system> <!-- 全局SQL防火墙设置 --> <!-- <firewall> <whitehost> <host host="127.0.0.1" user="mycat"/> <host host="127.0.0.2" user="mycat"/> </whitehost> <blacklist check="false"> </blacklist> </firewall> --> <!-- 配置用户名与密码,以及可以访问的数据库(逻辑库) --> <user name="root"> <property name="password">123456</property> <property name="schemas">TESTDB</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">TESTDB</property> <!--该用户可以看到哪些逻辑数据库 ,若果配置了多个schema ,可以用逗号分隔, 如:User,Customer --> <property name="readOnly">true</property> <!--设置该用户只有读的权限 --> </user> </mycat:server>
修改 server.xml 后:
修改部分:(关于<user></user>的都删除掉)
添加:
<user name="root"> <property name="password">root</property> <property name="schemas">mycat</property> </user>
重启mycat cd /usr/local/mycat/bin
./mycat restart
mycat 的启动 、 停止
cd /usr/local/mycat/bin/
./mycat stop [ start restart status console dump ]
Mycat 默认端口号:8066
登陆mycat ,与连接mysql 一样
用户名/密码 上面配置的server.xml 中的一样
执行创建表语句:
DROP TABLE IF EXISTS `mycat` ;
CREATE TABLE `mycat` (
`id` int(11) NOT NULL,
`name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hobby` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`remark` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`createTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
插入数据测试;至此,分片ok