1.orcale downloads sdk and installs
2. Download mycat https://github.com/MyCATApache/Mycat-download
3.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 To turn on real-time statistics, 0 to turn off -->
<property name="useGlobleTableCheck">0</property> <!-- 1 To enable full overtime consistency detection, 0 to close -->
<property name="sequnceHandlerType">2</property>
<!-- <property name="useCompression">1</property>--> <!--1 For opening mysql Compression protocol-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--Set simulated MySQL Version number-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--Default is type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
<property name="processorBufferPoolType">0</property>
<!--The default is 65535 64 K Be used for sql Maximum text length when parsing -->
<!--<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> -->
<!--Distributed transaction switch, 0 for unfiltered distributed transactions, 1 for filtered distributed transactions (if only global tables are involved in distributed transactions, then no filtering), 2 for unfiltered distributed transactions,But log distributed transactions-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1 On 0 off
-->
<property name="useOffHeapForMerge">1</property>
<!--
Unit is m
-->
<property name="memoryPageSize">1m</property>
<!--
Unit is k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
Unit is m
-->
<property name="systemReserveMemorySize">384m</property>
<!--Whether to adopt zookeeper Coordinated switching -->
<property name="useZKSwitch">true</property>
</system>
<!-- Overall situation SQL Firewall settings -->
<!--
<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>
<!-- Table level DML Permission settings -->
<!--
<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>
<property name="readOnly">true</property>
</user>
</mycat:server>
schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <!-- auto sharding by id (long) --> <table name="travelrecord" 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> <!-- <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="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>
Basically, it is the default. It is modified to the connection information of its own database. Because it is local, comment out the readHost
4.navicat log in to mycat
Click start up? Nowrap.bat to start mycat
5. By default, schama needs to create db1, db2, db3
Execute the command in cmd. It was created with navicat at the beginning, and always reported an error
create database db1 character set utf8;
create database db2 character set utf8;
create database db3 character set utf8;
Start copying
https://www.jianshu.com/p/8c98783980c5
2.4 global table test
Use navicat to open the command line interface. Enter the following command to create a data table:
USE TESTDB;
create table company(id int not null primary key,name varchar(100),sharding_id int not null);
Then use the following command to view the execution status of the create table command:
explain create table company(id int not null primary key,name varchar(100),sharding_id int not null);
Because company is defined as a global table in schema.xml, you can see the following similar results under normal circumstances:
Then try the following command to insert data:
insert into company(id,name,sharding_id) values(1,'leader us',10000);
Then, execute the explain command to check the execution status of the insert command
explain insert into company(id,name,sharding_id) values(1,'leader us',10000);
Then log in to mysql server and check three databases: db1, db2 and db3. You can see that the data table named company has been created in the database, and the data named leader us has been inserted in the table.
image.png
2.5 horizontal sub meter test
In the command column window of navicat, execute the following command to create the travelrecord table:
create table travelrecord(id int not null primary key,name varchar(100));
Then use explain to view:
explain create table travelrecord(id int not null primary key,name varchar(100));
The results are as follows:
image.png
Then execute the following command to insert a piece of data:
insert into travelrecord(id,name) values(1,'hp');
Execute explain to view the execution result:
explain insert into travelrecord(id,name) values(1,'hp');
The results are as follows:
image.png
Go to mysql database and check separately. The data table of travelrecord has been created in all three databases, but only db1 (the database corresponding to dn1) has data inserted.
