Mycat 實現 MySQL 讀寫分離
環(huán)境：CentOS 6.8
實驗拓撲：
mycat
/ \
master -- slave (主從復制)
mycat: 192.168.0.121
master: 192.168.0.120
slave: 192.168.0.122
Mycat 提供了編譯好的安裝包，下載地址：http://dl.mycat.io
Mycat 官方首頁：http://mycat.org.cn
Index of /
../
1.6-RELEASE/ 28-Oct-2016 12:56 -
1.6.5-DEV/ 15-Jan-2017 07:10 -
2.0-dev/ 02-Jan-2017 07:24 -
mycat-web-1.0/ 02-Jan-2017 07:40 -
yum/ 18-May-2016 02:51 -
Mycat-server-1.4-beta-20150604171601-linux.tar.gz 27-Jun-2015 10:09 7663894
apache-maven-3.3.3-bin.tar.gz 27-Jun-2015 10:09 8042383
apache-tomcat-7.0.62.tar.gz 27-Jun-2015 10:09 8824528
jdk-7u79-linux-x64.tar.gz 27-Jun-2015 10:09 153512879
jdk-8u20-linux-x64.tar.gz 27-Jun-2015 10:09 160872342
phpMyAdmin-4.4.9-all-languages.tar.gz 27-Jun-2015 10:09 9352049
probe-2.3.3.zip 27-Jun-2015 10:09 7957290
toolset.sh 26-Oct-2015 05:03 16015
zookeeper-3.4.6.tar.gz
wget 一下 Mycat-server 1.6 和 jdk-7u79 兩個包：
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
wget http://dl.mycat.io/jdk-7u79-linux-x64.tar.gz
[root@vm2 ~]# ll -h Mycat* jdk*
-rw-r--r-- 1 root root 147M Jun 27 2015 jdk-7u79-linux-x64.tar.gz
-rw-r--r-- 1 root root 7.4M Jun 27 2015 Mycat-server-1.4-beta-20150604171601-linux.tar.gz
Mycat-server 包解壓后可直接使用。
tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
其目錄結構是這樣的：
[root@vm2 local]# tree -L 1 mycat
mycat
|-- bin
|-- catlet
|-- conf
|-- lib
|-- logs
`-- version.txt
5 directories, 1 file
bin 目錄中是可執(zhí)行文件以及腳本，我們可以使用其中的 mycat 腳本控制mycat的啟動和關閉。
conf 目錄中是配置文件，這里配置讀寫分離主要使用 schema.xml 和 server.xml。其他配置分片的配置請參考官方文檔。
logs 目錄存放日志文件，遇到mycat出錯了，就在這里查看問題的原因。
安裝 jdk:
mkdir /usr/java
tar -xf jdk-7u79-linux-x64.tar.gz -C /usr/java
[root@vm2 logs]# cat /etc/profile.d/java.sh
export JAVA_HOME=/usr/java/jdk1.7.0_79
export PATH=$JAVA_HOME/bin:$PATH
source /etc/profile.d/java.sh
[root@vm2 logs]# java -version
java version "1.7.0_79"
Java(TM) SE Runtime Environment (build 1.7.0_79-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)
完成。
這里只講解一下讀寫分離用到的配置文件：server.xml, schema.xml。
前提：已經有一個配置好的 mysql 一主一從架構。
一個主從集群在Mycat里面由一個 dataNode 定義，dataNode 定義了一個數據庫實例及其中的一個具體的庫。Mycat 的一個數據庫實例可以實際上是一個主從復制架構：一主多從，一主一從，多主多從等等，具體在 dataHost 中定義。
這里建立一個非拆分庫（將mycat邏輯庫綁定到一個具體的 dataNode 上）testdb，綁定到 dn1 這個 dataNode 上。
schema.xml:
<schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
現在所有的表會走默認的節(jié)點 dn1。邏輯庫 testdb，對應了數據節(jié)點 dn1。dn1 對應著真實的數據庫實例上的一個真實的庫。
<dataNode name="dn1" dataHost="vm3306" database="db1" >
</dataNode>
定義數據節(jié)點，dn1，這個節(jié)點對應一個數據庫實例中的一個真實的庫，庫名為 db1。
dataNode 標簽定義了 MyCat 中的數據節(jié)點,也就是我們通常說所的數據分片。一個 dataNode 標簽就是 一個獨立的數據分片。
例子中所表述的意思為:使用名字為 vm3306 數據庫實例上的 db1 物理數據庫,這就組成一個數據分片,最 后,我們使用名字 dn1 標識這個分片。
該屬性用于定義該分片屬性哪個具體數據庫實例上的具體庫,因為這里使用兩個緯度來定義分片,就是:實 例+具體的庫。因為每個庫上建立的表和表結構是一樣的。所以這樣做就可以輕松的對表進行水平拆分。
dataHost: 包含一個 writeHost 和 一個 readHost，它們之前已經配置好主從復制了。
balance="3"：表示寫請求只發(fā)給節(jié)點，讀請求只發(fā)給讀節(jié)點。
<dataHost name="vm3306" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.0.120:3306" user="tuser" password="guli123">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.0.122:3306" user="tuser" password="guli123"/>
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="tuser" password="guli123"/> -->
</dataHost>
user 及 password屬性是后端主從mysql的賬戶密碼信息。
dataHost屬性說明：
writeType="0", 所有寫操作發(fā)送到配置的第一個 writeHost,第一個掛了切到還生存的第二個 writeHost,重新啟動后已切換后的為準,切換記錄在配置文件中:dnindex.properties .
balance="3",所有讀請求隨機的分發(fā)到 wiriterHost 對應的 readhost 執(zhí)行,writerHost 不負擔讀壓力,注意 balance=3 只在 1.4 及其以后版本有,1.3 沒有。
server.xml 配置：
<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>
我們在主從 mysql 中創(chuàng)建測試用戶 tuser，賦予增刪改查，創(chuàng)建庫和表的權限。
master：
mysql> GRANT CREATE,DELETE,INSERT,SELECT,UPDATE ON db1.* TO 'tuser'@'192.168.0.%' IDENTIFIED BY 'guli123';
啟動 mycat:
cd /usr/local/mycat/bin
./mycat start
啟動 mycat 遇到第一個日志報錯，在 logs/wrapper.log 看到如下錯誤：
ERROR | wrapper | 2017/01/23 11:59:42 | JVM exited while loading the application.
INFO | jvm 1 | 2017/01/23 11:59:42 | Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x00000007a6aa0000, 1431699456, 0) failed; error='Cannot allocate memory' (errno=12)
INFO | jvm 1 | 2017/01/23 11:59:42 | #
INFO | jvm 1 | 2017/01/23 11:59:42 | # There is insufficient memory for the Java Runtime Environment to continue.
INFO | jvm 1 | 2017/01/23 11:59:42 | # Native memory allocation (malloc) failed to allocate 1431699456 bytes for committing reserved memory.
提示說沒有足夠的內存來啟動 Java 運行時環(huán)境，因為我用的虛擬機，給了 512M 內存，所以內存不夠，重新分配了1.5G，就不會報這個錯了。
啟動 mycat 遇到第二個日志報錯，在 logs/wrapper.log 看到如下錯誤：
[root@vm2 logs]# tail -f wrapper.log：
ERROR | wrapper | 2017/01/23 17:19:28 | JVM exited while loading the application.
INFO | jvm 5 | 2017/01/23 17:19:28 | 錯誤: 代理拋出異常錯誤: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: vm2: vm2: 未知的名稱或服務
錯誤提示，可能沒有配置好本地主機名的名稱解析。
添加本地主機名解析：
[root@vm2 bin]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.121 vm2
再次嘗試啟動：
# ./mycat start
查看日志 wrapper.log：
INFO | jvm 1 | 2017/01/23 17:24:40 | log4j 2017-01-23 17:24:40 [./conf/log4j.xml] load completed.
INFO | jvm 1 | 2017/01/23 17:24:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
顯示啟動成功，這次OK了。
Mycat 管理命令與監(jiān)控
1. 管理命令
mysql -h127.0.0.1 -utest -ptest -P9066
MyCAT 自身有類似其他數據庫的管理監(jiān)控方式,可以通過 Mysql 命令行,登錄管理端口(9066)執(zhí)行相應 的 SQL 進行管理,也可以通過 jdbc 的方式進行遠程連接管理,本小節(jié)主要講解命令行的管理操作。
登錄:目前 mycat 有兩個端口,8066 數據端口,9066 管理端口,命令行的登陸是通過 9066 管理端口來操 作,登錄方式類似于 mysql 的服務端登陸。
mysql -h127.0.0.1 -utest -ptest -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 ,如果需要修改需要配置 server.xml
管理端口用于執(zhí)行管理命令：
mysql -h127.0.0.1 -utest -ptest -P9066
命令端口用戶執(zhí)行增刪改查等 SQL 語句：
mysql -h127.0.0.1 -utest -ptest -P8066
下面先看看管理端口支持的命令。
從 9066 管理端口登陸后，執(zhí)行 show @@help 可以查看到所有命令：
mysql> show @@help;
+------------------------------------------+--------------------------------------------+
| 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.resultset | Report BIG RESULTSET 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] |
| show @@directmemory=1 or 2 | show mycat direct memory usage |
| 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 |
+------------------------------------------+--------------------------------------------+
58 rows in set (0.00 sec)
查看 mycat 版本：
mysql> show @@version;
+-----------------------------------------+
| VERSION |
+-----------------------------------------+
| 5.6.29-mycat-1.6-RELEASE-20161028204710 |
+-----------------------------------------+
1 row in set (0.00 sec)
查看當前的庫：
mysql> show @@database;
+----------+
| DATABASE |
+----------+
| testdb |
+----------+
1 row in set (0.00 sec)
查看 MyCAT 的數據節(jié)點的列表,對應 schema.xml 配置文件的 dataNode 節(jié)點:
mysql> show @@datanode;
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | vm3306/db1 | 0 | mysql | 0 | 8 | 1000 | 244 | 0 | 0 | 0 | -1 |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
1 row in set (0.00 sec)
其中,“NAME”表示 dataNode 的名稱;“dataHost”表示對應 dataHost 屬性的值,即數據主機; “ACTIVE”表示活躍連接數;“IDLE”表示閑置連接數;“SIZE”對應總連接數量。
這里有 8 個空閑連接，那我們去主從節(jié)點用 netstat -ntp 命令看看建立的連接情況：
master:
[root@vm1 ~]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.0.120:22 192.168.0.104:60060 ESTABLISHED 1492/sshd
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58636 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58640 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58582 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58644 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58646 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58641 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58635 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.121:58632 ESTABLISHED 1414/mysqld
tcp 0 0 ::ffff:192.168.0.120:3306 ::ffff:192.168.0.122:48205 ESTABLISHED 1414/mysqld
slave:
[root@vm3 ~]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.0.122:48205 192.168.0.120:3306 ESTABLISHED 1607/mysqld
tcp 0 0 192.168.0.122:22 192.168.0.104:60102 ESTABLISHED 1196/sshd
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45593 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45591 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45583 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45589 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45579 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45580 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45588 ESTABLISHED 1607/mysqld
tcp 0 0 ::ffff:192.168.0.122:3306 ::ffff:192.168.0.121:45577 ESTABLISHED 1607/mysqld
可看到有很多從 mycat 服務器發(fā)起數據庫連接（主有9個連接，從有8個連接）。
查看心跳報告：
mysql> show @@heartbeat;
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 192.168.0.120 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2017-01-24 06:44:38 | false |
| hostS1 | mysql | 192.168.0.122 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2017-01-24 06:44:38 | false |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
2 rows in set (0.00 sec)
該命令用于報告心跳狀態(tài)
RS_CODE 狀態(tài):
OK_STATUS = 1;正常狀態(tài)
ERROR_STATUS = -1; 連接出錯
TIMEOUT_STATUS = -2; 連接超時
INIT_STATUS = 0; 初始化狀態(tài)
若節(jié)點故障,會連續(xù)默認 5 個周期檢測,心跳連續(xù)失敗,就會變成-1,節(jié)點故障確認,然后可能發(fā)生切換
查看 Mycat 的前端連接狀態(tài)，即應用與 mycat 的連接：
mysql> show @@connection\G
*************************** 1. row ***************************
PROCESSOR: Processor0
ID: 1
HOST: 127.0.0.1
PORT: 9066
LOCAL_PORT: 50317
SCHEMA: NULL
CHARSET: latin1:8
NET_IN: 257
NET_OUT: 6343
ALIVE_TIME(S): 1264
RECV_BUFFER: 4096
SEND_QUEUE: 0
txlevel:
autocommit:
1 row in set (0.00 sec)
從上面獲取到的連接 ID 屬性，可以手動殺掉某個連接。
kill @@connection id,id,id
顯示后端連接狀態(tài)：
mysql> show @@backend\G
...
...
...
*************************** 16. row ***************************
processor: Processor0
id: 4
mysqlId: 8
host: 192.168.0.122
port: 3306
l_port: 45583
net_in: 7018
net_out: 1646
life: 6287
closed: false
borrowed: false
SEND_QUEUE: 0
schema: db1
charset: utf8:33
txlevel: 3
autocommit: true
16 rows in set (0.00 sec)
一共有16個后端連接，這里截取最后一個。
顯示數據源：
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.0.120 | 3306 | W | 0 | 8 | 1000 | 231 | 0 | 2 |
| dn1 | hostS1 | mysql | 192.168.0.122 | 3306 | R | 0 | 8 | 1000 | 211 | 8 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
可以看到主從信息。
2. 執(zhí)行SQL語句
mysql -h127.0.0.1 -utest -ptest -P8066
創(chuàng)建 tb1 表:
mysql> show databases;
+----------+
| DATABASE |
+----------+
| testdb |
+----------+
1 row in set (0.00 sec)
mysql> use testdb;create table tb1 (id INT, name VARCHAR(20));
Database changed
Query OK, 0 rows affected (0.25 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.01 sec)
插入兩條數據：
mysql> insert into tb1 values (1, 'guli'), (2, 'xie');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
查看一下插入結果：
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
+------+------+
2 rows in set (0.00 sec)
沒問題。
再分別到主從節(jié)點看數據插入沒有：
master:
mysql> use db1;
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
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
+------+------+
2 rows in set (0.00 sec)
slave:
mysql> use db1;
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
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
+------+------+
2 rows in set (0.00 sec)
好，可以看到 OK 了。
查看剛才執(zhí)行過的 sql 語句：
mysql> show @@sql;
+------+------+---------------+--------------+-------------------+
| ID | USER | START_TIME | EXECUTE_TIME | SQL |
+------+------+---------------+--------------+-------------------+
| 1 | test | 1485212346188 | 1 | select * from tb1 |
| 2 | test | 1485212040101 | 1 | select * from tb1 |
| 3 | test | 1485211834831 | 1 | select * from tb1 |
| 4 | test | 1485211803688 | 1 | select * from tb1 |
| 5 | test | 1485209518691 | 2 | select * from tb1 |
+------+------+---------------+--------------+-------------------+
5 rows in set (0.00 sec)
遇到的問題：
似乎無法統(tǒng)計 insert 語句，不知為什么。
查看統(tǒng)計數據：
mysql> show @@sql.sum;
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
| ID | USER | R | W | R% | MAX | NET_IN | NET_OUT | TIME_COUNT | TTL_COUNT | LAST_TIME |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
| 1 | test | 5 | 0 | 1.00 | 1 | 85 | 709 | [5, 0, 0, 0] | [5, 0, 0, 0] | 1485212346189 |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------+
1 row in set (0.00 sec)
端口號: 該命令工作在 9066 端口,用來記錄用戶通過本地 8066 端口向 Mycat-Server 發(fā)送的 SQL 請求執(zhí)行
信息。信息包括有 ID 值,執(zhí)行 SQL 語句的用戶名稱,執(zhí)行的 SQL 語句,命令執(zhí)行的起始時間,命令執(zhí)行消耗時間
查看慢查詢語句：
設置慢查詢閾值為0：reload @@sqlslow=0;
mysql> reload @@sqlslow=0;
Query OK, 1 row affected (0.00 sec)
Reset show @@sql.slow time success
在8066端口執(zhí)行查詢：select * from tb1;
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | guli |
| 2 | xie |
| 3 | xu |
| 4 | he |
| 5 | huang |
| 6 | ma |
| 7 | liu |
| 8 | zeng |
+------+-------+
8 rows in set (0.00 sec)
在 9066 端口執(zhí)行 show @@sql.slow 查看抓取的慢查詢SQL語句：
mysql> show @@sql.slow;
+------+------------+---------------+--------------+-------------------+
| USER | DATASOURCE | START_TIME | EXECUTE_TIME | SQL |
+------+------------+---------------+--------------+-------------------+
| test | NULL | 1485213017329 | 1 | select * from tb1 |
+------+------------+---------------+--------------+-------------------+
1 row in set (0.00 sec)
3，如果要驗證一下讀寫分離已經成功了，應該怎么驗證呢？
使用mysql客戶端連接9066管理端口，執(zhí)行 show @@datasource 可以觀察到 READ_LOAD，WRITE_LOAD 兩個統(tǒng)計參數的變化：
這里顯示 hostM1 為寫節(jié)點，hostS1 為讀節(jié)點：
hostM1 的 WRITE_LOAD = 2
hostS1 的 READ_LOAD = 12
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.0.120 | 3306 | W | 0 | 8 | 1000 | 287 | 0 | 2 |
| dn1 | hostS1 | mysql | 192.168.0.122 | 3306 | R | 0 | 8 | 1000 | 271 | 12 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
使用mysql客戶端連接8066管理端口，執(zhí)行查詢，插入語句，同時使用mysql客戶端連接 9066 端口觀察一下讀寫統(tǒng)計參數的變化：
8066：執(zhí)行查詢 select * from tb1;
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
| 3 | xu |
| 4 | he |
+------+------+
4 rows in set (0.00 sec)
9066：
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.0.120 | 3306 | W | 0 | 8 | 1000 | 308 | 0 | 2 |
| dn1 | hostS1 | mysql | 192.168.0.122 | 3306 | R | 0 | 8 | 1000 | 293 | 13 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
讀節(jié)點的讀計數加1，
hostM1 的 WRITE_LOAD = 2
hostS1 的 READ_LOAD = 13
8066：執(zhí)行插入操作 insert into tb1 values (5,'huang');
mysql> insert into tb1 values (5,'huang');
Query OK, 1 row affected (0.02 sec)
9066：
mysql> show @@datasource;
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.0.120 | 3306 | W | 0 | 8 | 1000 | 332 | 0 | 4 |
| dn1 | hostS1 | mysql | 192.168.0.122 | 3306 | R | 0 | 8 | 1000 | 315 | 13 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
寫節(jié)點的讀計數加2
hostM1 的 WRITE_LOAD = 2
hostS1 的 READ_LOAD = 13
由此可見讀寫分離是成功的?？梢钥吹綌祿渤晒懭霐祿欤?/p>
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | guli |
| 2 | xie |
| 3 | xu |
| 4 | he |
| 5 | huang |
+------+-------+
5 rows in set (0.00 sec)
到此基本演示了 mycat 的主從讀寫分離功能，配置的前提是已經有一個配置好的 mysql 主從復制架構，mycat 工作于 mysql 主從架構的前端，負責 SQL 語句的分發(fā)。
mycat 另一個主要功能是數據分片，這里沒有演示。暫時就寫到這里。
