Mycat - MysqlAB复制 读写分离

--前部分，来自网络转载，因为此版主比我说的好。

-- 后部分为个人实践，算是一个补充。

-- Mycat 偶版本为稳定版，奇版本为开发版，如果是生产，建议使用偶版本。 eg 1.4. xxxxx

配置mycat之前请做好准备工作,统一各个数据库的字符集.并且最好使用mysql5.6的版本进行配置.以免以为版本问题带来很多未知的麻烦!

一、下载mycat      

mycat的官网网址  http://www.mycat.org.cn/,我使用的是mycat的1.4的Linux安装包

下载地址

https://github.com/MyCATApache/Mycat-download/blob/master/1.4-RELEASE/Mycat-server-1.4-release-20151019230038-linux.tar.gz

下载mycat的安装包到 /opt 目录下

#cd /opt

#wget https://github.com/MyCATApache/Mycat-download/blob/master/1.4-RELEASE/Mycat-server-1.4-release-20151019230038-linux.tar.gz

# tar -xvf  Mycat-server-1.4-RELEASE-20151017210032-linux.tar.gz

解压后放到/usr/local下  

#mv mycat /usr/local

# pwd

/usr/local/mycat

# ls

bin  catlet  conf  lib  logs  version.txt

二、配置环境变量

检查是否已经配置好了java的环境变量

#java -version

显示

java version "1.8.0_45"

Java(TM) SE Runtime Environment (build 1.8.0_45-b14)

Java HotSpot(TM) 64-Bit Server VM (build 25.45-b02, mixed mode)

表示我的java环境变量配置好了.,如果你还没用配置好,需先配置.如果你不会请自己度娘.非常多我就不写了

 

三、修改mycat配置文件

接下来修改mycat的配置文件

#cd /usr/local/mycat/conf

#ls

autopartition-long.txt       partition-hash-int.txt    sequence_db_conf.properties

cacheservice.properties      partition-range-mod.txt   sequence_time_conf.properties

dnindex.properties           router.xml                server.xml

ehcache.xml                  rule.xml                  wrapper.conf

index_to_charset.properties  schema.xml

log4j.xml                    sequence_conf.properties

 

配置文件非常多,配置项也远比amoeba复杂.如果只是简单配置在不同的服务器上进行读写分离只需要配置两个文件

server.xml 和  schema.xml

 

（一）先配置server.xml

找到这一段

</system>

        <user name="test">

                <property name="password">test</property>

                <property name="schemas">TESTDB</property>

        </user>

 

        <user name="user">

                <property name="password">user</property>

                <property name="schemas">TESTDB</property>

                <property name="readOnly">true</property>

        </user>

 

这里配置了两个可以来连接的用户

用户1  test   密码test   给予了此用户TESTDB数据库的权限

用户2  user   密码user   给予了此用户TESTDB数据库的只读权限

 

注意这里的testdb 不一定是你数据库上的真实库名.可以任意指定.只要和接下来的schema.xml的配置文件中的库名统一即可

 

（二）接下来配置schema.xml

 

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://org.opencloudb/">

 

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">

                <!-- auto sharding by id (long) -->

                <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

 

                <!-- global table is auto cloned to all defined data nodes ,so can join 

                        with any table whose sharding node is in the same data node -->

                <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

                <table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3"

                        rule="mod-long" />

                <!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global" 

                        rule="mod-long" /> -->

                <table name="employee" primaryKey="ID" dataNode="dn1,dn2"

                        rule="sharding-by-intfile" />

                <table name="customer" primaryKey="ID" dataNode="dn1,dn2"

                        rule="sharding-by-intfile">

                        <childTable name="orders" primaryKey="ID" joinKey="customer_id"

                                parentKey="id">

                                <childTable name="order_items" joinKey="order_id"

                                        parentKey="id" />

                        </childTable>

                        <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"

                                parentKey="id" />

                </table>

                        /> -->

        </schema>

        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" 

                /> -->

        <dataNode name="dn1" dataHost="localhost1" database="db1" />

        <dataNode name="dn2" dataHost="localhost1" database="db2" />

        <dataNode name="dn3" dataHost="localhost1" database="db3" />

        <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />

         <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> 

        <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" /> 

        <dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->

        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"

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

                <heartbeat>select user()</heartbeat>

                <!-- can have multi write hosts -->

                <writeHost host="hostM1" url="localhost:3306" user="root"

                        password="123456">

                        <!-- can have multi read hosts -->

 

                </writeHost>

                <writeHost host="hostS1" url="localhost:3316" user="root"

                        password="123456" />

 

 

看着这一大篇你是否和我一样的开始蛋疼.好复杂有木有!

算了给大家来一份我的简单点的.因为我们现在只做库的读写分离,没用分库 没用分表这些东西.

<mycat:schema xmlns:mycat="http://org.opencloudb/">

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" 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="192.168.1.100:3306" user="root" password="123456">

<readHost host="hostS1" url="192.168.1.101:3306" user="root" password="123456" />

                </writeHost>

        </dataHost>

 

 

（三）配置文件解释

一,  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

这里TESTDB 就是我们对外声称的我们有数据库的名称  必须和server.xml中的用户指定的数据库名称一致

添加一个dataNode="dn1" 是指定了我们这个库只在dn1上.没有进行分库

 

二, <dataNode name="dn1" dataHost="localhost1" database="db1" />

这里只需要改database的名字 db1 就是你真实数据库服务上的数据库名 .

根据你自己的数据库名进行修改.

 

三,<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

 

这里只需要配置三个地方    balance="1"与writeType="0" ,switchType=”1”

a. balance 属性负载均衡类型，目前的取值有 4 种：

1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。

2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。

3. balance="2"，所有读操作都随机的在 writeHost、 readhost 上分发。

4. balance="3"， 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有， 1.3 没有。

b. writeType 属性

负载均衡类型，目前的取值有 3 种：

1. writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个

writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .

2. writeType="1"，所有写操作都随机的发送到配置的 writeHost。

3. writeType="2"，没实现。

c. switchType 属性

- -1 表示不自动切换

- 1 默认值，自动切换

- 2 基于MySQL 主从同步的状态决定是否切换

四,<writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="123456">

<readHost host="hostS1" url="192.168.1.101:3306" user="root" password="123456" />

</writeHost>

这里是配置的我们的两台读写服务器IP地址访问端口和 访问用户的用户名和密码

------------------- 以下为实际操作例子 ------------------

环境描述：

系统版本： linux 6.4 x86 64bit

数据库版本： 5.6.21-enterprise-commercial-advanced-log

mysql 架构为： 主从半同步复制（通过pos,file , 非GTID 模式）

mycat 服务器： 10.118.230.27

mysql 主库：10.118.242.215

mysql 备库： 10.118.242.216

my.cnf 配置文件：

[mysqld]

port=3308

server_id = 215

datadir=/data/mysql/

basedir=/usr/local/mysql/

socket=/tmp/mysql.sock

user = mysql

bind_address = 10.118.242.215

autocommit = 0

lower_case_table_names=1

character_set_server=utf8mb4

skip_name_resolve = 1

max_connections = 800

max_connect_errors = 1000

skip_external_locking=off

innodb_max_dirty_pages_pct=30

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

join_buffer_size = 134217728

tmp_table_size = 67108864

tmpdir = /tmp

max_allowed_packet = 16777216

sql_mode ="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

interactive_timeout = 1800

wait_timeout = 1800

read_buffer_size = 16777216

read_rnd_buffer_size = 33554432

sort_buffer_size = 33554432

default_storage_engine = InnoDB

########log settings########

slow_query_log = 1

slow_query_log_file = /data/mysql/slow.log

log_queries_not_using_indexes = 1

log_slow_admin_statements = 1

log_slow_slave_statements = 1

log_throttle_queries_not_using_indexes = 10

expire_logs_days = 90

long_query_time = 2

min_examined_row_limit = 100

log-error = /data/mysql/mysql_error.log

########replication settings########

master_info_repository = TABLE

relay_log_info_repository = TABLE

log_bin = /data/mysql/bin.log

sync_binlog = 1

#gtid_mode = on

#enforce_gtid_consistency = 1

log_slave_updates

binlog_format = row

relay_log = /data/mysql/relay.log

relay_log_recovery = 1

#binlog_gtid_simple_recovery = TURE

slave_skip_errors = ddl_exist_errors

binlog-ignore-db=mysql

#enforce-gtid-consistency=ON

#read_only=TURE

########innodb settings########

innodb_page_size = 16384

innodb_buffer_pool_size = 2G

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

innodb_lock_wait_timeout = 5

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_flush_method = O_DIRECT

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_log_group_home_dir = /data/mysql/

innodb_undo_directory = /data/mysql/

innodb_undo_logs = 56

#innodb_undo_tablespaces = 2

innodb_flush_neighbors = 1

innodb_log_file_size = 4G

innodb_log_buffer_size = 8M

innodb_purge_threads = 4

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864

#innodb_data_home_dir=

#innodb_data_file_path=/data/mysqldata/ibdata1:12M:autoextend;/data/mysqldata/ibdata2:1M:autoextend

########semi sync replication settings########

#plugin_dir=/data/soft/mysql-advanced-5.6.21/lib/plugin/

#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#rpl_semi_sync_master_enabled = 1

#rpl_semi_sync_slave_enabled = 1

#rpl_semi_sync_master_timeout = 5000

mycat 简单设计 为 1 mycat + mysql 读写分离

mysql 涉及逻辑库为： mysql, redis, test

mysql 读/写 用户分别为： query / root

(root@(none)) Mysql >show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| redis |

| test |

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

5 rows in set (0.00 sec)

--------------------------------------------------------

--server.xml--

[mycat@sfpay conf]$ cat 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>

<!-- ########### add configure server port ########## -->

<property name="serverPort">8067</property> <!-- 管理Port -->

<property name="managerPort">9066</property> <!-- 服务Port -->

<property name="processors">4</property> <!-- CPU使用数 -->

<property name="processorExecutor">64</property> <!-- 线程池 -->

<property name="processorBufferChunk">8192</property> <!-- 分配SocketDirectBuffer的大小 -->

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

<property name="sequnceHandlerType">1</property> <!--Mycat全局序列的类型,0为本地文件方式,1为数据库方式 -->

<property name="packetHeaderSize">4</property>

<property name="maxPacketSize">163840</property>

<property name="idleTimeout">1200</property>

<property name="charset">utf8</property>

<!--property name="txIsolation">READ-COMMITTED</property-->

<property name="sqlExecuteTimeout">900</property>

</system>

<user name="root">

<property name="password">mysql123</property>

<property name="schemas">mysql,redis,test</property>

</user>

<user name="query">

<property name="password">query123</property>

<property name="schemas">mysql,redis,test</property>

<property name="readOnly">true</property>

</user>

---schema.xml ---

[mycat@sfpay conf]$ cat schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://org.opencloudb/" >

<!-- ############## Add configure information ############ -->

<schema name=" mysql" checkSQLschema="false" sqlMaxLimit="100" dataNode=" dn1 ">

</schema>

<schema name=" redis" checkSQLschema="false" sqlMaxLimit="100" dataNode=" dn2 ">

</schema>

<schema name=" test" checkSQLschema="false" sqlMaxLimit="100" dataNode=" dn3 ">

</schema>

<dataNode name="dn1" dataHost="mysqldb215" database="mysql" />

<dataNode name="dn2" dataHost="mysqldb215" database="redis" />

<dataNode name="dn3" dataHost="mysqldb215" database="test" />

<dataHost name="mysqldb215" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost host="10.118.242.215" url="10.118.242.215:3308" user="root" password="mysql123">

<!-- can have multi read hosts -->

<!--<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />-->

<readHost host="10.118.242.216" url="10.118.242.216:3308" user="query" password="query123">

< /readHost>

</writeHost>

<!-- readHost host="10.118.242.216" url="10.118.242.216:3308" user="root" password="mysql123" -->

</dataHost>

</mycat:schema>

启动mycat :

[mycat@sfpay conf]$ ps -ef |grep mycat

og

mycat 6490 1 0 17:16 ? 00:00:02 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE

mycat 6492 6490 0 17:16 ? 00:00:21 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/mapdb-1.0.7.jar:lib/fastjson-1.2.7.jar:lib/libwrapper-linux-x86-64.so:lib/ehcache-core-2.6.11.jar:lib/snakeyaml-1.16.jar:lib/univocity-parsers-1.5.4.jar:lib/xml-apis-1.0.b2.jar:lib/wrapper.jar:lib/zookeeper-3.4.6.jar:lib/log4j-1.2.17.jar:lib/jline-0.9.94.jar:lib/leveldb-api-0.7.jar:lib/Mycat-server-1.5.1-RELEASE.jar:lib/libwrapper-linux-x86-32.so:lib/guava-18.0.jar:lib/curator-framework-2.9.0.jar:lib/libwrapper-linux-ppc-64.so:lib/netty-3.7.0.Final.jar:lib/slf4j-api-1.7.12.jar:lib/druid-1.0.14.jar:lib/json-20151123.jar:lib/leveldb-0.7.jar:lib/slf4j-log4j12-1.7.12.jar:lib/mongo-java-driver-2.11.4.jar:lib/curator-client-2.9.0.jar:lib/sequoiadb-java-driver-1.0-20150615.070208-1.jar:lib/dom4j-1.6.1.jar -Dwrapper.key=JA13O5xgSGXK1W4L -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=6490 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp org.opencloudb.MycatStartup start

mycat 8369 4896 0 17:49 pts/2 00:00:00 mysql -h10.118.230.27 -uroot -px xxxxxx -P9066

登录

登录方式类似于 mysql 的服务端登陆，目前 mycat 有两个端口，8066 数据端口，9066 管理端口

登陆方式如：

# su - mycat

$ mysql -h10.118.230.27 -uroot -pmysql123 -P9066 [-dmycat]

-h 后面是主机，即当前 mycat 按照的主机地址，本地可用 127.0.0.1 远程需要远程 ip

-u Mycat server.xml 中配置的逻辑库用户

-p Mycat server.xml 中配置的逻辑库密码

-P 后面是端口 默认 9066，注意 P 是大写

-d Mycat server.xml 中配置的逻辑库

数据端口与管理端口的配置端口修改：

数据端口默认 8066，管理端口默认 9066 ，如果需要修改需要配置 serve.xml

--mycat 上登陆后，显示的库，和schem.xml 上配置一致。

(root@(none)) Mysql >show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 14

Current database: *** NONE ***

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

| DATABASE |

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

| mysql |

| redis |

| test |

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

3 rows in set (0.00 sec)

--mysql 主库 10.118.242.215 上的 process 连接信息显示：

(root@(none)) Mysql >show processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 16 | root | 10.118.242.216:60416 | NULL | Binlog Dump | 888190 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |

| 369 | root | localhost | NULL | Sleep | 522 | | NULL |

| 424 | root | localhost | NULL | Query | 0 | init | show processlist |

| 427 | root | 10.118.230.27:63865 | mysql | Sleep | 0 | | NULL |

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

4 rows in set (0.00 sec)

(query@redis) Mysql >show @@datasource ;

ERROR 2013 (HY000): Lost connection to MySQL server during query

(query@redis) Mysql >show @@datasource;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 24

Current database: redis

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

| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE |

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

| dn2 | 10.118.242.215 | mysql | 10.118.242.215 | 3308 | W | 0 | 1 | 1000 | 450 |

| dn2 | 10.118.242.216 | mysql | 10.118.242.216 | 3308 | R | 0 | 0 | 1000 | 0 |

| dn3 | 10.118.242.215 | mysql | 10.118.242.215 | 3308 | W | 0 | 1 | 1000 | 450 |

| dn3 | 10.118.242.216 | mysql | 10.118.242.216 | 3308 | R | 0 | 0 | 1000 | 0 |

| dn1 | 10.118.242.215 | mysql | 10.118.242.215 | 3308 | W | 0 | 1 | 1000 | 450 |

| dn1 | 10.118.242.216 | mysql | 10.118.242.216 | 3308 | R | 0 | 0 | 1000 | 0 |

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

6 rows in set (0.00 sec)

-----------------------

mysql -utest -ptest -P9066 -Dtestdb

mysql> show @@help; -- 获取有关管理的相关命令

mysql> show @@database; -- 查看逻辑数据库

mysql> show @@datanode; --查看分片节点

mysql> show @@server; --查看服务器状态

mysql> show @@version; --查看版本

(query@redis) Mysql >show @@help;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 25

Current database: redis

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

| STATEMENT | DESCRIPTION |

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

| show @@time.current | Report current timestamp |

| show @@time.startup | Report startup timestamp |

| show @@version | Report Mycat Server version |

| show @@server | Report server status |

| show @@threadpool | Report threadPool status |

| show @@database | Report databases |

| show @@datanode | Report dataNodes |

| show @@datanode where schema = ? | Report dataNodes |

| show @@datasource | Report dataSources |

| show @@datasource where dataNode = ? | Report dataSources |

| show @@datasource.synstatus | Report datasource data synchronous |

| show @@datasource.syndetail where name=? | Report datasource data synchronous detail |

| show @@datasource.cluster | Report datasource galary cluster variables |

| show @@processor | Report processor status |

| show @@command | Report commands status |

| show @@connection | Report connection status |

| show @@cache | Report system cache usage |

| show @@backend | Report backend connection status |

| show @@session | Report front session details |

| show @@connection.sql | Report connection sql |

| show @@sql.execute | Report execute status |

| show @@sql.detail where id = ? | Report execute detail status |

| show @@sql | Report SQL list |

| show @@sql.high | Report Hight Frequency SQL |

| show @@sql.slow | Report slow SQL |

| show @@sql.sum | Report User RW Stat |

| show @@sql.sum.user | Report User RW Stat |

| show @@sql.sum.table | Report Table RW Stat |

| show @@parser | Report parser status |

| show @@router | Report router status |

| show @@heartbeat | Report heartbeat status |

| show @@heartbeat.detail where name=? | Report heartbeat current detail |

| show @@slow where schema = ? | Report schema slow sql |

| show @@slow where datanode = ? | Report datanode slow sql |

| show @@sysparam | Report system param |

| show @@syslog limit=? | Report system mycat.log |

| show @@white | show mycat white host |

| show @@white.set=?,? | set mycat white host,[ip,user] |

| switch @@datasource name:index | Switch dataSource |

| kill @@connection id1,id2,... | Kill the specified connections |

| stop @@heartbeat name:time | Pause dataNode heartbeat |

| reload @@config | Reload basic config from file |

| reload @@config_all | Reload all config from file |

| reload @@route | Reload route config from file |

| reload @@user | Reload user config from file |

| reload @@sqlslow= | Set Slow SQL Time(ms) |

| reload @@user_stat | Reset show @@sql @@sql.sum @@sql.slow |

| rollback @@config | Rollback all config from memory |

| rollback @@route | Rollback route config from memory |

| rollback @@user | Rollback user config from memory |

| reload @@sqlstat=open | Open real-time sql stat analyzer |

| reload @@sqlstat=close | Close real-time sql stat analyzer |

| offline | Change MyCat status to OFF |

| online | Change MyCat status to ON |

| clear @@slow where schema = ? | Clear slow sql by schema |

| clear @@slow where datanode = ? | Clear slow sql by datanode |

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

56 rows in set (0.01 sec)

服务端口进入 9066 不能做一些DML, DDL 操作。

[mycat@sfpay conf]$ mysql -h10.118.230.27 -uroot -pmysql123 -P9066

(root@redis) Mysql >show tables;

ERROR 2013 (HY000): Lost connection to MySQL server during query

(root@redis) Mysql >show tables;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 38

Current database: redis

ERROR 1003 (HY000): Unsupported statement

(root@redis) Mysql >create table t1(id number,name varchar(20));

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 35

Current database: redis

ERROR 1003 (HY000): Unsupported statement

管理端口 8067

[mycat@sfpay conf]$ mysql -h10.118.230.27 -uroot -pmysql123 -P8067

(root@redis) Mysql >show tables;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 43

Current database: redis

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

| Tables_in_redis |

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

| test1 |

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

1 row in set (0.01 sec)

---------------

[mycat@sfpay conf]$ mysql -h10.118.230.27 -uquery -pquery123 -P8067

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 56

Server version: 5.5.8-mycat-1.5.1-RELEASE-20160816173057 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(query@(none)) Mysql >use redis

No connection. Trying to reconnect...

Connection id: 57

Current database: *** NONE ***

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

(query@redis) Mysql >create table workte ( workname varchar(20) not null);

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 58

Current database: redis

ERROR 1495 (HY000): User readonly

---schema.xml 新增逻辑库，可不要重启mycat 直接通过 reload @@config 加载即可。

[mycat@sfpay conf]$ vi schema.xml

<!-- ############## Add configure information ############ -->

<schema name="mysql" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

</schema>

<schema name="redis" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">

</schema>

<schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3">

</schema>

<schema name="performance_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="dc1">

</schema>

<dataNode name="dn1" dataHost="mysqldb215" database="mysql" />

<dataNode name="dn2" dataHost="mysqldb215" database="redis" />

<dataNode name="dn3" dataHost="mysqldb215" database="test" />

<dataNode name="dc1" dataHost="mysqldb215" database="performance_schema" />

--------------------------------

(root@(none)) Mysql > reload @@config;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 90

Current database: *** NONE ***

Query OK, 1 row affected (0.22 sec)

Reload config success

(root@(none)) Mysql >show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 91

Current database: *** NONE ***

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

| DATABASE |

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

| mysql |

| performance_schema |

| redis |

| test |

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

4 rows in set (0.00 sec)

--------

(query@(none)) Mysql >show @@heartbeat;

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

| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |

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

| 10.118.242.215 | mysql | 10.118.242.215 | 3308 | 1 | 0 | idle | 0 | 6,6,6 | 2016-10-11 12:34:06 | false |

| 10.118.242.216 | mysql | 10.118.242.216 | 3308 | -1 | 0 | idle | 0 | 68,68,68 | 2016-10-11 12:34:06 | false |

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

2 rows in set (0.00 sec)

--是因为 schema.xml 中： balance=2 所致。

<dataHost name="mysqldb215" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

</dataHost>

修改后，通过 reload @@configure_all, 现已正常。

(query@(none)) Mysql >reload @@config_all;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2

Current database: *** NONE ***

Query OK, 1 row affected (0.19 sec)

Reload config success

(query@(none)) Mysql >show @@heartbeat;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 3

Current database: *** NONE ***

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

| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |

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

| 10.118.242.215 | mysql | 10.118.242.215 | 3308 | 1 | 0 | idle | -1 | 0,0,0 | NULL | false |

| 10.118.242.216 | mysql | 10.118.242.216 | 3308 | 1 | 0 | idle | -1 | 0,0,0 | NULL | false |

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

2 rows in set (0.00 sec)

--以上参考 MYCAT 权威指南

后期 待续发 实际操作 文档或草稿：

1, mycat (主从) + mysql (一主多从） 读写分离。

2, mycat(主从）+ mysql (多主N从） 分库分表。

3, haproxy + mycat + mysql

4, haproxy +keepalive+mycat + mysql galera cluster + mysql

5, mycat + oracle (dg) 读写分离。

6, mycat+web 监控