项目环境：

192.168.8.30 mycat

192.168.8.31 node1

192.168.8.32 node2

192.168.8.33 node3

三个节点MySQL均为单实例

一、创建测试数据

node1

createdatabasetestdb1;createtabletestdb1.t01(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb1.t01values(@@hostname,@@hostname,@@hostname);createdatabasetestdb2;createtabletestdb2.t02(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb2.t02values(@@hostname,@@hostname,@@hostname);createdatabasetestdb3;createtabletestdb3.t03(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb3.t03values(@@hostname,@@hostname,@@hostname);

node2

createdatabasetestdb4;createtabletestdb4.t04(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb4.t04values(@@hostname,@@hostname,@@hostname);createdatabasetestdb5;createtabletestdb5.t05(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb5.t05values(@@hostname,@@hostname,@@hostname);createdatabasetestdb6;createtabletestdb6.t06(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb6.t06values(@@hostname,@@hostname,@@hostname);

node3

createdatabasetestdb7;createtabletestdb7.t07(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb7.t07values(@@hostname,@@hostname,@@hostname);createdatabasetestdb8;createtabletestdb8.t08(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb8.t08values(@@hostname,@@hostname,@@hostname);createdatabasetestdb9;createtabletestdb9.t09(name1varchar(40),name2varchar(40),name3varchar(40));insertintotestdb9.t09values(@@hostname,@@hostname,@@hostname);

二、配置schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="mycatdb1"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn1"></schema><schemaname="mycatdb2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><schemaname="mycatdb3"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn3"></schema><schemaname="mycatdb4"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn4"></schema><schemaname="mycatdb5"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn5"></schema><schemaname="mycatdb6"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn6"></schema><schemaname="mycatdb7"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn7"></schema><schemaname="mycatdb8"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn8"></schema><schemaname="mycatdb9"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn9"></schema><!--<dataNodename="dn1$0-743"dataHost="localhost1"database="db$0-743"/>--><dataNodename="dn1"dataHost="node1"database="testdb1"/><dataNodename="dn2"dataHost="node1"database="testdb2"/><dataNodename="dn3"dataHost="node1"database="testdb3"/><dataNodename="dn4"dataHost="node2"database="testdb4"/><dataNodename="dn5"dataHost="node2"database="testdb5"/><dataNodename="dn6"dataHost="node2"database="testdb6"/><dataNodename="dn7"dataHost="node3"database="testdb7"/><dataNodename="dn8"dataHost="node3"database="testdb8"/><dataNodename="dn9"dataHost="node3"database="testdb9"/><!--<dataNodename="dn4"dataHost="sequoiadb1"database="SAMPLE"/><dataNodename="jdbc_dn1"dataHost="jdbchost"database="db1"/><dataNodename="jdbc_dn2"dataHost="jdbchost"database="db2"/><dataNodename="jdbc_dn3"dataHost="jdbchost"database="db3"/>--><dataHostname="node1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="192.168.8.31"url="192.168.8.31:3306"user="root"password="mysql"></writeHost></dataHost><dataHostname="node2"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="192.168.8.32"url="192.168.8.32:3306"user="root"password="mysql"></writeHost></dataHost><dataHostname="node3"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="192.168.8.33"url="192.168.8.33:3306"user="root"password="mysql"></writeHost></dataHost></mycat:schema>

三、配置server.xml

<username="root"defaultAccount="true"><propertyname="password">mysql</property><propertyname="schemas">mycatdb1,mycatdb2,mycatdb3,mycatdb4,mycatdb5,mycatdb6,mycatdb7,mycatdb8,mycatdb9</property><!--表级DML权限设置--><!--<privilegescheck="false"><schemaname="TESTDB"dml="0110"><tablename="tb01"dml="0000"></table><tablename="tb02"dml="1111"></table></schema></privileges>--></user>

四、启动mycat

/usr/local/mycat/bin/mycatstart

查看mycat日志

STATUS|wrapper|2018/11/2117:07:24|-->WrapperStartedasDaemonSTATUS|wrapper|2018/11/2117:07:24|LaunchingaJVM...INFO|jvm1|2018/11/2117:07:24|OpenJDK64-BitServerVMwarning:ignoringoptionMaxPermSize=64M;supportwasremovedin8.0INFO|jvm1|2018/11/2117:07:26|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/11/2117:07:26|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/11/2117:07:26|INFO|jvm1|2018/11/2117:07:30|MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

五、访问8066端口查看数据

[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb1.t01"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node1|node1|node1|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb2.t02"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node1|node1|node1|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb3.t03"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node1|node1|node1|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb4.t04"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node2|node2|node2|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb5.t05"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node2|node2|node2|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb6.t06"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node2|node2|node2|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb7.t07"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node3|node3|node3|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb8.t08"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node3|node3|node3|+-------+-------+-------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb9.t09"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+-------+-------+-------+|name1|name2|name3|+-------+-------+-------+|node3|node3|node3|+-------+-------+-------+

数据查看正常，9个库分布在三个机器。