Mycat black Box

1, performSELECT * from Travelrecord, analyzeDebuglogs that describe the entire execution logic, including connection fetching, connection synchronization information, data merge, data return, connection release1.1ClearDebugInformation

[Email protected]_idc_squid_1_11 logs]# cat/dev/null > Mycat.log

[Email protected]_idc_squid_1_11 logs]# LL

Modify the Log for debug:

Vim Log4j.xml

<root>

<level value= "Debug"/>

<appender-ref ref= "FILE"/>

<!--<appender-ref ref= "FILE"/>-->

</root>

Get logs:

[Email protected]_idc_squid_1_11 logs]# Cpmycat.log 1.log

[Email protected]_idc_squid_1_11 logs]# vim 1.log

Execute SELECT * from Travelrecord in the Command line window;

1.1Connection Acquisition

01/2421:59:12.850 DEBUG [$_NIOREACTOR-3-RW] (enchachepool.java:76)-sqlroutecache Miss Cache, Key:testdbselect * from Travelrecord

01/2421:59:12.851 DEBUG [$_NIOREACTOR-3-RW] (enchachepool.java:59)-sqlroutecache add cache, Key:testdbselect * Fromtravelrecord Value:select * from Travelrecord, route={

1-Dn1{select *

Fromtravelrecord

LIMIT100}

2-Dn2{select *

Fromtravelrecord

LIMIT100}

3-Dn3{select *

Fromtravelrecord

LIMIT100}

}

01/2421:59:12.852 DEBUG [$_NIOREACTOR-3-RW] (nonblockingsession.java:113)-serverconnection [id=4, Schema=TESTDB, Host=127.0.0.1,user=test,txisolation=3, Autocommit=true, Schema=testdb]select * Fromtravelrecord, route={

1-Dn1{select *

Fromtravelrecord

LIMIT100}

2-Dn2{select *

From Travelrecord

LIMIT100}

3-Dn3{select *

Fromtravelrecord

LIMIT100}

} RRS

1.2Connection synchronization information:

01/2421:59:12.852 DEBUG [$_NIOREACTOR-3-RW] (mysqlconnection.java:445)-con need syn, total syn cmd 2 commands SET Namesla Tin1; SET SESSION TRANSACTION Isolation level repeatable read;schemachange:false con:mysqlconnection [id=11, Lasttime= 1453643952852, USER=ROOT,SCHEMA=DB1, old shema=db1, Borrowed=true, Fromslavedb=true, Threadid=3425,charset=latin1, Txisolation=0, Autocommit=true, Attachment=dn1{select *

Fromtravelrecord

LIMIT 100},res[email protected]30a6aae0,host=127.0.0.1, port=3327, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]

1.3Data Merge

01/24 21:59:12.856 DEBUG [BusinessExecutor6] (datamergeservice.java:296)-prepare MPP merge result for select * Fromtravel Record

01/24 21:59:12.856 DEBUG [BusinessExecutor6] (multinodequeryhandler.java:324)-last packet id:11

01/24 21:59:21.910 DEBUG [Timer1] (sqljob.java:85)-con querysql:show slave status to Con:mysqlconnection [Id=14, Lasttim E=1453643961910,user=root, SCHEMA=DB1, old shema=db1, Borrowed=true, fromslavedb=false,threadid=22, Charset=latin1, Txisolation=3, Autocommit=true, Attachment=null,resphandler=null, host=127.0.0.1, port=3317, StatusSync=null, Writequeue=0,modifiedsqlexecuted=false]

01/24 21:59:21.910 DEBUG [Timer1] (sqljob.java:85)-con querysql:show slave status to Con:mysqlconnection [Id=27, Lasttim e=1453643961910, USER=ROOT,SCHEMA=DB1, old shema=db1, Borrowed=true, Fromslavedb=false, threadid=3442,charset=latin1 , Txisolation=3, Autocommit=true, Attachment=null,resphandler=null, host=127.0.0.1, port=3327, StatusSync=null, Writequeue=0,modifiedsqlexecuted=false]

.................

1.4Data Return

01/24 21:59:12.854 DEBUG [$_NIOREACTOR-3-RW] (multinodequeryhandler.java:171)-received OK response, Executeresponse: Falsefrom mysqlconnection [id=11, lasttime=1453643952835, User=root, SCHEMA=DB1, OLDSHEMA=DB1, Borrowed=true, Fromslavedb=true, threadid=3425, charset=latin1,txisolation=0, Autocommit=true, Attachment=dn1{select *

From Travelrecord

LIMIT 100},res[email protected]30a6aae0,host=127.0.0.1, Port=3327,[email protected]d43df48,writequeue=0, Modifiedsqlexecuted=false]

01/24 21:59:12.855 DEBUG [$_NIOREACTOR-3-RW] (multinodequeryhandler.java:171)-received OK response, Executeresponse: Falsefrom mysqlconnection [id=11, lasttime=1453643952835, User=root, SCHEMA=DB1, OLDSHEMA=DB1, Borrowed=true, Fromslavedb=true, threadid=3425, Charset=latin1, Txisolation=3,autocommit=true, Attachment=dn1{select *

From Travelrecord

LIMIT 100},res[email protected]30a6aae0,host=127.0.0.1, Port=3327,[email protected]d43df48,writequeue=0, Modifiedsqlexecuted=false]

Article source address: http://blog.csdn.net/mchdba/article/details/51160495, declined reprint.

1.5Connection Release:

01/24 21:59:21.911 DEBUG [$_NIOREACTOR-2-RW] (physicaldatasource.java:403)-release channel mysqlconnection [Id=14, lasttime=1453643961901, User=root, SCHEMA=DB1, old shema=db1, Borrowed=true,fromslavedb=false, threadId=22, charset= Latin1, Txisolation=3, Autocommit=true,attachment=null, Resphandler=null, host=127.0.0.1, port=3317, StatusSync=null , Writequeue=0, Modifiedsqlexecuted=false]

01/24 21:59:21.911 DEBUG [$_NIOREACTOR-1-RW] (physicaldatasource.java:403)-release channel mysqlconnection [id=27, lasttime=1453643961901, USER=ROOT,SCHEMA=DB1, old shema=db1, Borrowed=true, Fromslavedb=false, ThreadId=3442,charset =latin1, Txisolation=3, Autocommit=true, Attachment=null,resphandler=null, host=127.0.0.1, port=3327, statusSync= NULL, Writequeue=0,modifiedsqlexecuted=false]

01/24 21:59:21.911 DEBUG [$_NIOREACTOR-2-RW] (physicaldatasource.java:403)-release channel mysqlconnection [Id=16, lasttime=1453643961901, User=root, SCHEMA=DB1, old shema=db1, Borrowed=true,fromslavedb=true, threadId=3431, charset= UTF8, txisolation=0, Autocommit=true,attachment=null, Resphandler=null, host=127.0.0.1, port=3327, StatusSync=null, Writequeue=0, Modifiedsqlexecuted=false]

2, add a shard tableT_vote (id,province), provincewithHashShards, and useReloadCommand mode overloads are in effect,and text to explain the whole process. 2.1ConfigurationHashShards

Schema.xml file, Datanode has dn21, Dn22; Datahost has M1, M2, as shown in 2.1.jpg below:

Rule.xml:

Reload Update configuration file:

mysql> Reload @ @config;

Error 1064 (HY000): Com.alibaba.druid.sql.parser.ParserException:syntax error, error in: ' [email protected] @config ', Expect IDENTIFIER, actual IDENTIFIER reload

Mysql>

The problem is strange, although there is an error message, but the background is effective, build the table explain can see the new dn21 and the Dn22

Build table:

2.2input initialization data validation data shard:

the total input 4 record, go to view the data, 2 record in dn21 The Shard is also MySQL of the 3317 instance above, 2 record in Dn22 sharding is also MySQL of the 3327 instance, as follows:

Dn21 Shard 3317mysql Port:

Dn22 Shard 3327mysql Port:

3, the character set isLatinafter the display3.1 Mysqlthe server character set isLatin:

The background debug information is displayed as Charset=latin1:

01/25 02:47:19.128 DEBUG [$_NIOREACTOR-1-RW] (mysqlconnection.java:445)-con need syn, total syn cmd 1 commands SET Sessio Ntransaction Isolation level repeatable Read;schema change:falsecon:MySQLConnection [id=32, lasttime=1453661239128, User=root, SCHEMA=DB3, OLDSHEMA=DB3, Borrowed=true, Fromslavedb=false, threadid=23, Charset=latin1,txisolation=0, Autocommit=true, Attachment=dn22{select *

From T_vote

LIMIT 100},res[email protected]5485687f,host=10.254.1.11, port=3327, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]

3.2LoginMycatthe client is set toGBK:

1/25 02:50:26.348 DEBUG [$_NIOREACTOR-1-RW] (enchachepool.java:76)-sqlroutecache Miss Cache,key:testdbselect * from T_V OTE where province= "Beijing"

01/25 02:50:26.392 DEBUG [$_NIOREACTOR-1-RW] (enchachepool.java:59)-sqlroutecache add cache, Key:testdbselect * from T_V Otewhere province= "Beijing" Value:select * from T_vote whereprovince= "Beijing", route={

1-> Dn22{select *

From T_vote

WHERE province = ' Beijing '

LIMIT 100}

}

01/25 02:50:26.393 DEBUG [$_NIOREACTOR-1-RW] (nonblockingsession.java:113)-serverconnection [Id=1, Schema=TESTDB, host=127.0.0.1, User=test,txisolation=3, Autocommit=true, schema=testdb]select* from T_vote where PROVINCE= "Beijing", route={

1-> Dn22{select *

From T_vote

WHERE province = ' Beijing '

LIMIT 100}

} RRS

01/25 02:50:26.393 DEBUG [$_NIOREACTOR-1-RW] (physicaldbpool.java:452)-select read source hostM2 for DATAHOST:M2

01/25 02:50:26.396 DEBUG [$_NIOREACTOR-3-RW] (nonblockingsession.java:229)-release connection mysqlconnection [id=11, lasttime=1453661426394, User=root, SCHEMA=DB3, old Shema=db3, Borrowed=true,fromslavedb=false, threadId=16, charset= GBK, txisolation=3, Autocommit=true,attachment=dn22{select *

From T_vote

WHERE province = ' Beijing '

LIMIT, Resphandler=singlenodehandler[node=dn22{select *

From T_vote

WHERE province = ' Beijing '

LIMIT, packetid=5], Host=10.254.1.11,port=3327,[email protected]4a7f2c7,writequeue=0, modifiedsqlexecuted= False

01/25 02:50:26.396 DEBUG [$_NIOREACTOR-3-RW] (physicaldatasource.java:403)-release channel mysqlconnection [id=11, lasttime=1453661426394, User=root, SCHEMA=DB3, old Shema=db3, Borrowed=true,fromslavedb=false, threadId=16, CHARSET=GBK , txisolation=3, Autocommit=true, Attachment=null,resphandler=null, host=10.254.1.11, port=3327, Statussync=null, Writequeue=0,modifiedsqlexecuted=false]

01/25 02:50:32.191 DEBUG [Timer0] (sqljob.java:85)-con querysql:select User () to con:mysqlconnection [id=2, lasttime=145 3661432191,user=root, SCHEMA=DB3, old Shema=db3, Borrowed=true, fromslavedb=false,threadid=23, Charset=latin1, Txisolation=0, Autocommit=true, Attachment=null,resphandler=null, host=10.254.1.11, port=3317, StatusSync=null, Writequeue=0,modifiedsqlexecuted=false].

See the CHARSET=GBK , the returned data and MySQL database settings do not have a very large character set, and logon Mycat the set of character sets for the client.

3.3set to beUTF8after

01/25 02:54:20.399 DEBUG [$_NIOREACTOR-1-RW] (enchachepool.java:76)-sqlroutecache Miss Cache, Key:testdbselect * from T_ VOTE whereprovince= "Shanghai"

01/25 02:54:20.400 DEBUG [$_NIOREACTOR-1-RW] (enchachepool.java:59)-sqlroutecache add cache, Key:testdbselect * from T_V Otewhere province= "Shanghai" Value:select * from T_vote whereprovince= "Shanghai", route={

1-> Dn22{select *

From T_vote

WHERE province = ' Shanghai '

LIMIT 100}

}

01/25 02:54:20.401 DEBUG [$_NIOREACTOR-1-RW] (nonblockingsession.java:113)-serverconnection [Id=1, Schema=TESTDB, host=127.0.0.1, User=test,txisolation=3, Autocommit=true, schema=testdb]select* from T_vote where PROVINCE= "Shanghai" , route={

1-> Dn22{select *

From T_vote

WHERE province = ' Shanghai '

LIMIT 100}

} RRS

01/25 02:54:20.401 DEBUG [$_NIOREACTOR-1-RW] (physicaldbpool.java:452)-select read source hostM2 for DATAHOST:M2

01/25 02:54:20.403 DEBUG [$_NIOREACTOR-3-RW] (nonblockingsession.java:229)-release connection mysqlconnection [id=11, Lasttime=1453661660386, User=root, SCHEMA=DB3, old Shema=db3, Borrowed=true,fromslavedb=false, threadId=16, Charset=utf8, txisolation=3, Autocommit=true,attachment=dn22{select *

From T_vote

WHERE province = ' Shanghai '

LIMIT, Resphandler=singlenodehandler[node=dn22{select *

From T_vote

WHERE province = ' Shanghai '

LIMIT, packetid=5], Host=10.254.1.11,port=3327,[email protected]017d906,writequeue=0, modifiedsqlexecuted= False

01/25 02:54:20.403 DEBUG [$_NIOREACTOR-3-RW] (physicaldatasource.java:403)-release channel mysqlconnection [id=11, Lasttime=1453661660386, USER=ROOT,SCHEMA=DB3, old Shema=db3, Borrowed=true, Fromslavedb=false, threadId=16, Charset=utf8, txisolation=3, Autocommit=true, Attachment=null, resphandler=null,host=10.254.1.11, port=3327, Statussync=null, Writequeue=0,modifiedsqlexecuted=false]

See Debug information, the information returned is UTF8 , so the client login character set is what, the data returned is what character set.

3.3Change intoLatin?

MySQL database is Latin, login Mycat client is also Latin, do not test, certainly is Latin

Summary: Mycat Log in all character sets, the data processing returns the results are followed by the character set of the login settings walk.

Mycat black Box