MySQL HA Solution 2019(3)MyCat

MySQLHASolution2019(3)MyCat

IalreadyhaveaMySQLmasterrunningonubuntu-master.

Slaverunningonubuntu-dev5andubuntu-dev6.

FindastablereleaseversionfromhereforMyCathttps://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE

>wgethttps://raw.githubusercontent.com/MyCATApache/Mycat-download/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

Unzipandplacethefileintheworkingdirectory

>sudoln-s/home/carl/tool/mycat-1.6/opt/mycat-1.6

>sudoln-s/opt/mycat-1.6/opt/mycat

Somecommands

>bin/mycatconsole

>bin/mycatstart

>bin/mycatstop

>bin/mycatstatus

Mycat-serverisnotrunning.

SetUpUserinmaster

>mysql-udebian-sys-maint-pG1FEbrOMSORmcaUK

>createuser'mycat'@'192.168.56.%'identifiedby'mycat';

>grantallprivilegeson*.*to'mycat'@'192.168.56.%'withgrantoption;

>flushprivileges;

AfterthatIcanusethisusertologon

>mysql-umycat-pmycat-hubuntu-master

Checktheconfigurations

>viconf/schema.xml

<mycat:schemaxmlns:mycat="http://io.mycat/">

<schemaname="mycat"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn1"/>

<dataNodename="dn1"dataHost="localhost1"database="db1"/>

<dataHostname="localhost1"maxCon="1000"minCon="10"balance="0"

writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100">

<heartbeat>selectuser()</heartbeat>

<writeHosthost="hostM1"url="ubuntu-master:3306"user="mycat"

password="mycat">

</writeHost>

</dataHost>

</mycat:schema>

>catconf/server.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!----LicensedundertheApacheLicense,Version2.0(the"License");

-youmaynotusethisfileexceptincompliancewiththeLicense.-You

mayobtainacopyoftheLicenseat--http://www.apache.org/licenses/LICENSE-2.0

--Unlessrequiredbyapplicablelaworagreedtoinwriting,software-

distributedundertheLicenseisdistributedonan"ASIS"BASIS,-WITHOUT

WARRANTIESORCONDITIONSOFANYKIND,eitherexpressorimplied.-Seethe

Licenseforthespecificlanguagegoverningpermissionsand-limitations

undertheLicense.-->

<!DOCTYPEmycat:serverSYSTEM"server.dtd">

<mycat:serverxmlns:mycat="http://io.mycat/">

<system>

<propertyname="useSqlStat">0</property><!--1为开启实时统计、0为关闭-->

<propertyname="useGlobleTableCheck">0</property><!--1为开启全加班一致性检测、0为关闭-->

<propertyname="sequnceHandlerType">2</property>

<!--<propertyname="useCompression">1</property>--><!--1为开启mysql压缩协议-->

<!--<propertyname="fakeMySQLVersion">5.6.20</property>--><!--设置模拟的MySQL版本号-->

<!--<propertyname="processorBufferChunk">40960</property>-->

<!--

<propertyname="processors">1</property>

<propertyname="processorExecutor">32</property>

-->

<!--默认为type0:DirectByteBufferPool|type1ByteBufferArena-->

<propertyname="processorBufferPoolType">0</property>

<!--默认是6553564K用于sql解析时最大文本长度-->

<!--<propertyname="maxStringLiteralLength">65535</property>-->

<!--<propertyname="sequnceHandlerType">0</property>-->

<!--<propertyname="backSocketNoDelay">1</property>-->

<!--<propertyname="frontSocketNoDelay">1</property>-->

<!--<propertyname="processorExecutor">16</property>-->

<!--

<propertyname="serverPort">8066</property><propertyname="managerPort">9066</property>

<propertyname="idleTimeout">300000</property><propertyname="bindIp">0.0.0.0</property>

<propertyname="frontWriteQueueSize">4096</property><propertyname="processors">32</property>-->

<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->

<propertyname="handleDistributedTransactions">0</property>

<!--

offheapformerge/order/group/limit1开启0关闭

-->

<propertyname="useOffHeapForMerge">1</property>

<!--

单位为m

-->

<propertyname="memoryPageSize">1m</property>

<!--

单位为k

-->

<propertyname="spillsFileBufferSize">1k</property>

<propertyname="useStreamOutput">0</property>

<!--

单位为m

-->

<propertyname="systemReserveMemorySize">384m</property>

<!--是否采用zookeeper协调切换-->

<propertyname="useZKSwitch">true</property>

</system>

<username="root">

<propertyname="password">123456</property>

<propertyname="schemas">mycat</property>

</user>

<username="mycat">

<propertyname="password">mycat</property>

<propertyname="schemas">mycat</property>

</user>

</mycat:server>

Testtheconnection

>mysql-umycat-pmycat-P8066-h127.0.0.1

>showdatabases;

+----------+

|DATABASE|

+----------+

|mycat|

+----------+

Stuckincommand

>usemycat;

Let’strytochangetheconfiguration

>bin/mycatstatus

Mycat-serverisrunning(15583).

>bin/mycatstop

StoppingMycat-server...

StoppedMycat-server.

>catconf/schema.xml

<?xmlversion="1.0"?>

<!DOCTYPEmycat:schemaSYSTEM"schema.dtd">

<mycat:schemaxmlns:mycat="http://io.mycat/">

<schemaname="mycat"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn1"/>

<dataNodename="dn1"dataHost="localhost1"database="db1"/>

<dataHostname="localhost1"maxCon="1000"minCon="10"balance="0"

writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100">

<heartbeat>selectuser()</heartbeat>

<writeHosthost="hostMaster"url="ubuntu-master:3306"user="mycat"password="mycat">

<readHosthost="hostSlave1"url="ubuntu-dev5:3306"user="mycat"password="mycat"/>

<readHosthost="hostSlave2"url="ubuntu-dev6:3306"user="mycat"password="mycat"/>

</writeHost>

</dataHost>

</mycat:schema>

MoreconfigurationSample

https://segmentfault.com/a/1190000010208682#articleHeader0

Usefultesting

https://www.jianshu.com/p/cb7ec06dae05

Tryotherversionfromhere

https://github.com/MyCATApache/Mycat-download/tree/master/1.5-RELEASE

>wgethttps://raw.githubusercontent.com/MyCATApache/Mycat-download/master/1.5-RELEASE/Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz

>sudoln-s/home/carl/tool/mycat-1.5.1/opt/mycat-1.5.1

>sudoln-s/opt/mycat-1.5.1/opt/mycat

Createtablethere

>usemycat;

>createtablemycatuser(idintauto_incrementprimarykey,namevarchar(32));

>descmycatuser;

+-------+-------------+------+-----+---------+----------------+

|Field|Type|Null|Key|Default|Extra|

+-------+-------------+------+-----+---------+----------------+

|id|int(11)|NO|PRI|NULL|auto_increment|

|name|varchar(32)|YES||NULL||

+-------+-------------+------+-----+---------+----------------+

2rowsinset(0.00sec)

Stillnotworkingwell.

>catconf/server.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<!----LicensedundertheApacheLicense,Version2.0(the"License");

-youmaynotusethisfileexceptincompliancewiththeLicense.-You

mayobtainacopyoftheLicenseat--http://www.apache.org/licenses/LICENSE-2.0

--Unlessrequiredbyapplicablelaworagreedtoinwriting,software-

distributedundertheLicenseisdistributedonan"ASIS"BASIS,-WITHOUT

WARRANTIESORCONDITIONSOFANYKIND,eitherexpressorimplied.-Seethe

Licenseforthespecificlanguagegoverningpermissionsand-limitations

undertheLicense.-->

<!DOCTYPEmycat:serverSYSTEM"server.dtd">

<mycat:serverxmlns:mycat="http://org.opencloudb/">

<system>

<propertyname="defaultSqlParser">druidparser</property>

<!--<propertyname="useCompression">1</property>--><!--1为开启mysql压缩协议-->

<!--<propertyname="processorBufferChunk">40960</property>-->

<!--

<propertyname="processors">1</property>

<propertyname="processorExecutor">32</property>

-->

<!--默认是6553564K用于sql解析时最大文本长度-->

<!--<propertyname="maxStringLiteralLength">65535</property>-->

<!--<propertyname="sequnceHandlerType">0</property>-->

<!--<propertyname="backSocketNoDelay">1</property>-->

<!--<propertyname="frontSocketNoDelay">1</property>-->

<!--<propertyname="processorExecutor">16</property>-->

<!--

<propertyname="mutiNodeLimitType">1</property>0：开启小数量级（默认）；1：开启亿级数据排序

<propertyname="mutiNodePatchSize">100</property>亿级数量排序批量

<propertyname="processors">32</property><propertyname="processorExecutor">32</property>

<propertyname="serverPort">8066</property><propertyname="managerPort">9066</property>

<propertyname="idleTimeout">300000</property><propertyname="bindIp">0.0.0.0</property>

<propertyname="frontWriteQueueSize">4096</property><propertyname="processors">32</property>-->

</system>

<username="root">

<propertyname="password">root</property>

<propertyname="schemas">mycat</property>

</user>

<username="mycat">

<propertyname="password">mycat</property>

<propertyname="schemas">mycat</property>

</user>

</mycat:server>

>catconf/schema.xml

<?xmlversion="1.0"?>

<!DOCTYPEmycat:schemaSYSTEM"schema.dtd">

<mycat:schemaxmlns:mycat="http://org.opencloudb/">

<schemaname="mycat"checkSQLschema="false"sqlMaxLimit="100">

<tablename="mycatuser"dataNode="dn1"/>

</schema>

<dataNodename="dn1"dataHost="localhost1"database="db1"/>

<dataHostname="localhost1"maxCon="1000"minCon="10"balance="0"

writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100">

<heartbeat>selectuser()</heartbeat>

<writeHosthost="hostM1"url="ubuntu-master:3306"user="mycat"

password="mycat">

<readHosthost="hostS1"url="ubuntu-dev5:3306"user="mycat"password="mycat"/>

<readHosthost="hostS2"url="ubuntu-dev6:3306"user="mycat"password="mycat"/>

</writeHost>

</dataHost>

</mycat:schema>

Newerversionfromhere

>wgethttp://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

>sudoln-s/home/carl/tool/mycat-1.6.7.1/opt/mycat-1.6.7

>sudoln-s/opt/mycat-1.6.7/opt/mycat

Stillnotworkingasmyexpect.

References:

MyCat

https://blog.csdn.net/linuxlsq/article/details/52606225

https://blog.csdn.net/nxw_tsp/article/details/56277430

https://github.com/MyCATApache/Mycat-Server

https://blog.csdn.net/wangjun5159/article/details/51568249

https://segmentfault.com/a/1190000010208682

https://cloud.tencent.com/developer/article/1073522

https://github.com/MyCATApache/Mycat-Server/wiki/2.0-Mycat%E5%AE%89%E8%A3%85%E4%B8%8E%E4%BD%BF%E7%94%A8

https://www.jianshu.com/p/cb7ec06dae05

DBProxy

https://www.cnblogs.com/zhoujinyi/p/6697141.html