MyCat：第五章：快速入门

04-29 21:46:59.741 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:46:59.741 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:46:59.925 INFO [$_NIOREACTOR-2-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=18, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17135, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:46:59.926 INFO [$_NIOREACTOR-1-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=13, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17132, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:46:59.926 INFO [$_NIOREACTOR-1-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=17, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17138, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:46:59.971 INFO [$_NIOREACTOR-2-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=14, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17134, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:47:00.032 INFO [$_NIOREACTOR-3-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=11, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17130, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:47:00.034 INFO [$_NIOREACTOR-0-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=12, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17131, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:47:00.035 INFO [$_NIOREACTOR-0-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=20, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17136, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:47:00.035 INFO [$_NIOREACTOR-3-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=19, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17139, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:47:01.255 INFO [$_NIOREACTOR-3-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=15, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17133, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:47:01.258 INFO [$_NIOREACTOR-0-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=16, lastTime=1430315219133, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17137, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:47:01.343 INFO [main] (PhysicalDBPool.java:296) -init result :finished 10 success 10 target

count:10

04-29 21:47:01.343 INFO [main] (PhysicalDBPool.java:238) -jdbchost2 index:0 init success

MyCAT Server startup successfully. see logs in logs/mycat.log

04-29 21:51:21.846 INFO [main] (PhysicalDBPool.java:81) -total resouces of dataHost jdbchost is :4

04-29 21:51:21.848 INFO [main] (PhysicalDBPool.java:81) -total resouces of dataHost jdbchost2 is :4

该部分日志可以看到配置的数据源相关信息，上面是两个数据源连接datahost

04-29 21:51:21.856 INFO [main] (CacheService.java:125) -create layer cache pool TableID2DataNodeCache of

type encache ,default cache size 10000 ,default expire seconds18000

04-29 21:51:21.857 INFO [main] (DefaultLayedCachePool.java:80) -create child Cache: TESTDB_ORDERS for

layered cache TableID2DataNodeCache, size 50000, expire seconds 18000

04-29 21:51:22.104 INFO [main] (DynaClassLoader.java:35) -dyna class load from E:\MyProject\Mycat-Server

\main\catlet,and auto check for class file modified every 60 seconds

该部分描述了Mycat的缓存信息及动态类加载信息。

04-29 21:51:22.107 INFO [main] (MycatServer.java:203) -Startup processors …,total processors:4,aio

thread pool size:8

each process allocated socket buffer pool bytes ,buffer chunk size:4096 buffer pool’s

capacity(buferPool/bufferChunk) is:4000

04-29 21:51:22.108 INFO [main] (MycatServer.java:204) -sysconfig params:SystemConfig

[processorBufferLocalPercent=100, frontSocketSoRcvbuf=1048576, frontSocketSoSndbuf=4194304,

backSocketSoRcvbuf=4194304, backSocketSoSndbuf=1048576, frontSocketNoDelay=1, backSocketNoDelay=1,

maxStringLiteralLength=65535, frontWriteQueueSize=2048, bindIp=0.0.0.0, serverPort=8066, managerPort=9066,

charset=utf8, processors=4, processorExecutor=8, timerExecutor=2, managerExecutor=2, idleTimeout=1800000,

catletClassCheckSeconds=60, sqlExecuteTimeout=300, processorCheckPeriod=1000,

dataNodeIdleCheckPeriod=300000, dataNodeHeartbeatPeriod=10000, clusterHeartbeatUser=HEARTBEAT_USER,

clusterHeartbeatPass=HEARTBEAT_PASS, clusterHeartbeatPeriod=5000, clusterHeartbeatTimeout=10000,

clusterHeartbeatRetry=10, txIsolation=3, parserCommentVersion=50148, sqlRecordCount=10,

processorBufferPool=16384000, processorBufferChunk=4096, defaultMaxLimit=100, sequnceHandlerType=1,

sqlInterceptor=org.opencloudb.interceptor.impl.DefaultSqlInterceptor, sqlInterceptorType=select,

sqlInterceptorFile=E:\MyProject\Mycat-Server/logs/sql.txt, mutiNodeLimitType=0, mutiNodePatchSize=100,

defaultSqlParser=druidparser, usingAIO=0, packetHeaderSize=4, maxPacketSize=16777216, mycatNodeId=1]

04-29 21:51:22.131 INFO [main] (MycatServer.java:262) -using nio network handler

该部分描述了Mycat线程池、buffer、连接池等等所有的配置信息，通过该启动项可以得知当前运行的Mycat个参数调整情况，

生产环境下需要做部分参数调整，可以根据该日志分析参数情况。

04-29 21:58:35.407 INFO [main] (MycatServer.java:280) -$_MyCatManager is started and listening on 9066

04-29 21:58:35.408 INFO [main] (MycatServer.java:284) -$_MyCatServer is started and listening on 8066

该部分描述了Mycat启动端口。

04-29 21:58:35.408 INFO [main] (MycatServer.java:289) -Initialize dataHost …

04-29 21:58:35.408 INFO [main] (PhysicalDBPool.java:267) -init backend myqsl source ,create connections

total 10 for master index :0

04-29 21:58:35.410 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.412 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.413 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.413 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.413 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.414 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.414 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.414 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.414 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.415 INFO [main] (PhysicalDatasource.java:356) -not ilde connection in pool,create new

connection for masterConnectionMeta [schema=mycat_node1, charset=utf8, txIsolation=-1, autocommit=true]

04-29 21:58:35.463 INFO [$_NIOREACTOR-0-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=4, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89015, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.464 INFO [$_NIOREACTOR-2-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=6, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89018, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.463 INFO [$_NIOREACTOR-1-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=5, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89017, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.463 INFO [$_NIOREACTOR-3-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=7, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89019, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.464 INFO [$_NIOREACTOR-1-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=1, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89013, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.464 INFO [$_NIOREACTOR-2-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=2, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89016, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.465 INFO [$_NIOREACTOR-3-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=3, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89014, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.467 INFO [$_NIOREACTOR-0-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=8, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89020, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.471 INFO [$_NIOREACTOR-1-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=9, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89021, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.472 INFO [$_NIOREACTOR-2-RW] (GetConnectionHandler.java:66) -connected successfuly

MySQLConnection [id=10, lastTime=1430315915098, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89022, charset=utf8, txIsolation=0, autocommit=true, attachment=null,

respHandler=null, host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 21:58:35.615 INFO [main] (PhysicalDBPool.java:296) -init result :finished 10 success 10 target

count:10

04-29 21:58:35.615 INFO [main] (PhysicalDBPool.java:238) -jdbchost index:0 init success

04-29 21:58:35.615 INFO [main] (PhysicalDBPool.java:267) -init backend myqsl source ,create connections

total 10 for master index :0

该部分描述了Mycat时后端连接池的初始化过程。

如果某个连接断掉或异常心跳检测会有对应的日志如：

04-29 22:01:07.274 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr ,[thread=$_NIOConnector,class=MySQLDetector,host=192.168.0.2,port=33061,localPort=0,schema=null]

该日志是心跳检测到连接异常关闭后端连接的日志，可以通过该日志查看后端数据连接状态。

debug模式下分析sql执行。

下面分析sql：select * from t_user t; 的执行

04-29 22:06:10.187 INFO [$_NIOREACTOR-3-RW] (FrontendAuthenticator.java:161) -ServerConnection [id=1,

schema=null, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=null]‘mycat’ login success

04-29 22:06:10.188 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:64) -ServerConnection [id=1,

schema=null, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=null]SET NAMES utf8

04-29 22:06:10.192 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:64) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW STATUS

04-29 22:06:10.227 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW STATUS, route={

1 -> dn2{SHOW STATUS}

} rrs

04-29 22:06:10.228 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:417) -select read source master for

dataHost:jdbchost2

04-29 22:06:10.228 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:437) -con need syn ,total syn cmd 1

commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection

[id=13, lastTime=1430316370226, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17188, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{SHOW STATUS},

respHandler=SingleNodeHandler [node=dn2{SHOW STATUS}, packetId=0], host=116.236.223.115, port=3307,

statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.292 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:246) -release connection

MySQLConnection [id=13, lastTime=1430316370226, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17188, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SHOW

STATUS}, respHandler=SingleNodeHandler [node=dn2{SHOW STATUS}, packetId=60], host=116.236.223.115,

port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@7cf13e82, writeQueue=0,

modifiedSQLExecuted=false]

04-29 22:06:10.292 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:386) -release channel MySQLConnection

[id=13, lastTime=1430316370226, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17188, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null,

host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.293 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:64) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW STATUS

04-29 22:06:10.293 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW STATUS, route={

1 -> dn1{SHOW STATUS}

} rrs

04-29 22:06:10.293 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:417) -select read source master for

dataHost:jdbchost

04-29 22:06:10.293 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:437) -con need syn ,total syn cmd 1

commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection

[id=3, lastTime=1430316370288, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=89066, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SHOW STATUS},

respHandler=SingleNodeHandler [node=dn1{SHOW STATUS}, packetId=0], host=121.40.121.133, port=3306,

statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.306 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:246) -release connection

MySQLConnection [id=3, lastTime=1430316370288, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89066, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SHOW

STATUS}, respHandler=SingleNodeHandler [node=dn1{SHOW STATUS}, packetId=60], host=121.40.121.133, port=3306,

statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@4bd38cb3, writeQueue=0,

modifiedSQLExecuted=false]

04-29 22:06:10.306 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:386) -release channel MySQLConnection

[id=3, lastTime=1430316370288, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=89066, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null,

host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.313 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:64) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]select * from t_user t

04-29 22:06:10.315 DEBUG [$_NIOREACTOR-3-RW] (EnchachePool.java:76) -SQLRouteCache miss cache

,key:mycatselect * from t_user t

04-29 22:06:10.419 DEBUG [$_NIOREACTOR-3-RW] (EnchachePool.java:59) -SQLRouteCache add cache

,key:mycatselect * from t_user t value:select * from t_user t, route={

1 -> dn1{SELECT *

FROM t_user t

LIMIT 100}

2 -> dn2{SELECT *

FROM t_user t

LIMIT 100}

}

04-29 22:06:10.420 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]select * from t_user

t, route={

1 -> dn1{SELECT *

FROM t_user t

LIMIT 100}

2 -> dn2{SELECT *

FROM t_user t

LIMIT 100}

} rrs

04-29 22:06:10.420 DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:78) -execute mutinode query select

• from t_user t

04-29 22:06:10.422 DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:93) -has data merge logic

04-29 22:06:10.422 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:417) -select read source master for

dataHost:jdbchost

04-29 22:06:10.422 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:437) -con need syn ,total syn cmd 1

commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection

[id=1, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=89067, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.423 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:417) -select read source master for

dataHost:jdbchost2

04-29 22:06:10.423 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:437) -con need syn ,total syn cmd 1

commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection

[id=11, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17189, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.432 DEBUG [$_NIOREACTOR-1-RW] (MultiNodeQueryHandler.java:165) -received ok response

,executeResponse:false from MySQLConnection [id=1, lastTime=1430316370409, schema=mycat_node1, old

shema=mycat_node1, borrowed=true, fromSlaveDB=false, threadId=89067, charset=utf8, txIsolation=3,

autocommit=true, attachment=dn1{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=121.40.121.133, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@7485fef2,

writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.434 DEBUG [$_NIOREACTOR-1-RW] (DataMergeService.java:138) -field metadata

inf:[RECEIVE_ADDRESS=ColMeta [colIndex=1, colType=253], PROVINCE_CODE=ColMeta [colIndex=3, colType=253],

USER_ID=ColMeta [colIndex=0, colType=3], CREATE_TIME=ColMeta [colIndex=2, colType=12]]

04-29 22:06:10.434 DEBUG [$_NIOREACTOR-1-RW] (MultiNodeQueryHandler.java:226) -on row end reseponse

MySQLConnection [id=1, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89067, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=121.40.121.133, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@7485fef2,

writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.434 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:246) -release connection

MySQLConnection [id=1, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89067, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=121.40.121.133, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@7485fef2,

writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.435 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:386) -release channel MySQLConnection

[id=1, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=89067, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null,

host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.481 DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:165) -received ok response

,executeResponse:false from MySQLConnection [id=11, lastTime=1430316370409, schema=mycat_node1, old

shema=mycat_node1, borrowed=true, fromSlaveDB=false, threadId=17189, charset=utf8, txIsolation=3,

autocommit=true, attachment=dn2{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=116.236.223.115, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@6a95ec91,

writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.482 DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:226) -on row end reseponse

MySQLConnection [id=11, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17189, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=116.236.223.115, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@6a95ec91,

writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.482 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:246) -release connection

MySQLConnection [id=11, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17189, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *

FROM t_user t

LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@3ff70d3c,

host=116.236.223.115, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@6a95ec91,

writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.482 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:386) -release channel MySQLConnection

[id=11, lastTime=1430316370409, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17189, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null,

host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.484 DEBUG [BusinessExecutor6] (DataMergeService.java:130) -prepare mpp merge result for

select * from t_user t

04-29 22:06:10.485 DEBUG [BusinessExecutor6] (MultiNodeQueryHandler.java:287) -output merge result ,total

data 16 start :0 end :100 package id start:6

04-29 22:06:10.485 DEBUG [BusinessExecutor6] (MultiNodeQueryHandler.java:308) -last packet id:23

04-29 22:06:10.485 DEBUG [BusinessExecutor6] (DataMergeService.java:312) -clear data

04-29 22:06:10.491 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:64) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW STATUS

04-29 22:06:10.491 DEBUG [$_NIOREACTOR-3-RW] (DataMergeService.java:312) -clear data

04-29 22:06:10.492 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW STATUS, route={

1 -> dn2{SHOW STATUS}

} rrs

04-29 22:06:10.492 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:417) -select read source master for

dataHost:jdbchost2

04-29 22:06:10.492 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:437) -con need syn ,total syn cmd 1

commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection

[id=12, lastTime=1430316370489, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17186, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{SHOW STATUS},

respHandler=SingleNodeHandler [node=dn2{SHOW STATUS}, packetId=0], host=116.236.223.115, port=3307,

statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.554 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:246) -release connection

MySQLConnection [id=12, lastTime=1430316370489, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17186, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SHOW

STATUS}, respHandler=SingleNodeHandler [node=dn2{SHOW STATUS}, packetId=60], host=116.236.223.115,

port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@364c4b05, writeQueue=0,

modifiedSQLExecuted=false]

04-29 22:06:10.554 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:386) -release channel MySQLConnection

[id=12, lastTime=1430316370489, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17186, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null,

host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.589 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:64) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SELECT * FROM

mycat_node1.t_user LIMIT 0

04-29 22:06:10.590 DEBUG [$_NIOREACTOR-3-RW] (EnchachePool.java:76) -SQLRouteCache miss cache

,key:mycatSELECT * FROM mycat_node1.t_user LIMIT 0

04-29 22:06:10.592 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SELECT * FROM

mycat_node1.t_user LIMIT 0, route={

1 -> dn1{SELECT * FROM mycat_node1.t_user LIMIT 0}

} rrs

04-29 22:06:10.592 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:417) -select read source master for

dataHost:jdbchost

04-29 22:06:10.592 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:437) -con need syn ,total syn cmd 1

commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection

[id=4, lastTime=1430316370591, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=89064, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SELECT * FROM

mycat_node1.t_user LIMIT 0}, respHandler=SingleNodeHandler [node=dn1{SELECT * FROM

mycat_node1.t_user LIMIT 0}, packetId=0], host=121.40.121.133, port=3306, statusSync=null, writeQueue=0,

modifiedSQLExecuted=false]

04-29 22:06:10.603 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:246) -release connection

MySQLConnection [id=4, lastTime=1430316370591, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=89064, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *

FROM mycat_node1.t_user LIMIT 0}, respHandler=SingleNodeHandler [node=dn1{SELECT * FROM

mycat_node1.t_user LIMIT 0}, packetId=6], host=121.40.121.133, port=3306,

statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@4792ba63, writeQueue=0,

modifiedSQLExecuted=false]

04-29 22:06:10.603 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:386) -release channel MySQLConnection

[id=4, lastTime=1430316370591, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=89064, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null,

host=121.40.121.133, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.603 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:64) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW COLUMNS FROM

mycat_node1.t_user

04-29 22:06:10.603 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]SHOW COLUMNS FROM

mycat_node1.t_user, route={

1 -> dn2{SHOW COLUMNS FROM t_user}

} rrs

04-29 22:06:10.604 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:417) -select read source master for

dataHost:jdbchost2

04-29 22:06:10.604 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:437) -con need syn ,total syn cmd 1

commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection

[id=16, lastTime=1430316370591, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17191, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{SHOW COLUMNS FROM t_user},

respHandler=SingleNodeHandler [node=dn2{SHOW COLUMNS FROM t_user}, packetId=0], host=116.236.223.115,

port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.665 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:246) -release connection

MySQLConnection [id=16, lastTime=1430316370591, schema=mycat_node1, old shema=mycat_node1, borrowed=true,

fromSlaveDB=false, threadId=17191, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SHOW COLUMNS

FROM t_user}, respHandler=SingleNodeHandler [node=dn2{SHOW COLUMNS FROM t_user}, packetId=12],

host=116.236.223.115, port=3307, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@278806c4,

writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:10.665 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:386) -release channel MySQLConnection

[id=16, lastTime=1430316370591, schema=mycat_node1, old shema=mycat_node1, borrowed=true, fromSlaveDB=false,

threadId=17191, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null,

host=116.236.223.115, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

04-29 22:06:21.332 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:06:21.334 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:06:42.333 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:06:42.334 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:07:03.326 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:07:03.344 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:07:24.327 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:07:24.345 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:07:45.332 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

04-29 22:07:45.355 INFO [$_NIOConnector] (AbstractConnection.java:398) -close connection,reason:hearbeat

connecterr

,[thread=$_NIOConnector,class=MySQLDetector,host=116.236.223.115,port=33071,localPort=0,schema=null]

通过该日志可以看到Mycat整个执行的计划。

其中最重要的是sql路由的计划，可以看到sql具体被分配到那个分片执行：

04-29 22:06:10.420 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]select * from t_user

t, route={

1 -> dn1{SELECT *

FROM t_user t

LIMIT 100}

2 -> dn2{SELECT *

FROM t_user t

LIMIT 100}

} rrs

04-29 22:06:10.420 DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:78) -execute mutinode query select

• from t_user t

该部分描述了该条sql被分配到到了分片dn1、dn2上同时执行，如果某个某个sql通过缓存、分片规则或者注解指定只会在某个分片

执行，则sql只会被分配到到某个分片，例 《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》无偿开源 威信搜索公众号【编程进阶路】 如：

sql=select * from t_user t where t.user_id=121;该条数据只在分片1上。

04-29 22:13:40.960 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:118) -ServerConnection [id=1,

schema=mycat, host=127.0.0.1, user=mycat,txIsolation=3, autocommit=true, schema=mycat]select * from t_user t

where t.user_id=121, route={

1 -> dn1{SELECT *

FROM t_user t

WHERE t.user_id = 121

LIMIT 100}

} rrs

从日志可以看出sql只被路由到dn1节点执行。

异常日志

java.sql.SQLSyntaxErrorException: com.alibaba.druid.sql.parser.ParserException: syntax error, error in

:‘elect * from t_user t where t.’,expect IDENTIFIER, actual IDENTIFIER elect

at

org.opencloudb.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:44)