MySQL HA Solution 2019(3)MyCat

MySQL HA Solution 2019(3)MyCat

I already have a MySQL master running on ubuntu-master.
Slave running on ubuntu-dev5 and ubuntu-dev6.

Find a stable release version from here for MyCat https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE

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

Unzip and place the file in the working directory
> sudo ln -s /home/carl/tool/mycat-1.6 /opt/mycat-1.6
> sudo ln -s /opt/mycat-1.6 /opt/mycat

Some commands
> bin/mycat console

> bin/mycat start

> bin/mycat stop

> bin/mycat status
Mycat-server is not running.

Set Up User in master
> mysql -u debian-sys-maint -pG1FEbrOMSORmcaUK
> create user 'mycat'@'192.168.56.%' identified by 'mycat';
> grant all privileges on *.* to 'mycat'@'192.168.56.%' with grant option;
> flush privileges;

After that I can use this user to logon
> mysql -u mycat -pmycat -h ubuntu-master

Check the configurations
> vi conf/schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="ubuntu-master:3306" user="mycat"
  password="mycat">
</writeHost>
</dataHost>
</mycat:schema>

> cat conf/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为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<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> -->
<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit      1开启   0关闭
-->
<property name="useOffHeapForMerge">1</property>
<!--
单位为m
-->
<property name="memoryPageSize">1m</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换  -->
<property name="useZKSwitch">true</property>
</system>
<user name="root">
<property name="password">123456</property>
<property name="schemas">mycat</property>
</user>
<user name="mycat">
<property name="password">mycat</property>
<property name="schemas">mycat</property>
</user>
</mycat:server>

Test the connection
> mysql -umycat -pmycat -P8066 -h127.0.0.1

> show databases;
+----------+
| DATABASE |
+----------+
| mycat    |
+----------+

Stuck in command
> use mycat;

Let’s try to change the configuration
> bin/mycat status
Mycat-server is running (15583).

> bin/mycat stop
Stopping Mycat-server...
Stopped Mycat-server.

> cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostMaster" url="ubuntu-master:3306" user="mycat" password="mycat">
<readHost host="hostSlave1" url="ubuntu-dev5:3306" user="mycat" password="mycat"/>
<readHost host="hostSlave2" url="ubuntu-dev6:3306" user="mycat" password="mycat"/>
</writeHost>
</dataHost>
</mycat:schema>

More configuration Sample
https://segmentfault.com/a/1190000010208682#articleHeader0

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

Try other version from here
https://github.com/MyCATApache/Mycat-download/tree/master/1.5-RELEASE

> wget https://raw.githubusercontent.com/MyCATApache/Mycat-download/master/1.5-RELEASE/Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz
> sudo ln -s /home/carl/tool/mycat-1.5.1 /opt/mycat-1.5.1
> sudo ln -s /opt/mycat-1.5.1 /opt/mycat

Create table there
> use mycat;
> create table mycatuser(id int auto_increment primary key,name varchar(32)) ;
> desc mycatuser;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Still not working well.
> cat conf/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://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<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="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数据排序
    <property name="mutiNodePatchSize">100</property> 亿级数量排序批量
<property name="processors">32</property> <property name="processorExecutor">32</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> -->
</system>
<user name="root">
<property name="password">root</property>
<property name="schemas">mycat</property>
</user>
<user name="mycat">
<property name="password">mycat</property>
<property name="schemas">mycat</property>
</user>
</mycat:server>

> cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100">
<table name="mycatuser" dataNode="dn1"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="ubuntu-master:3306" user="mycat"
password="mycat">
<readHost host="hostS1" url="ubuntu-dev5:3306" user="mycat" password="mycat" />
<readHost host="hostS2" url="ubuntu-dev6:3306" user="mycat" password="mycat" />
</writeHost>
</dataHost>
</mycat:schema>

Newer version from here
> wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
> sudo ln -s /home/carl/tool/mycat-1.6.7.1 /opt/mycat-1.6.7
> sudo ln -s /opt/mycat-1.6.7 /opt/mycat

Still not working as my expect.

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