Mycat 實(shí)現(xiàn) MySQL 讀寫分離
環(huán)境：CentOS 6.8
實(shí)驗(yàn)拓?fù)洌?/p>
mycat
/ \
master -- slave (主從復(fù)制)
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 兩個(gè)包：
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/
其目錄結(jié)構(gòu)是這樣的：
[root@vm2 local]# tree -L 1 mycat
mycat
|-- bin
|-- catlet
|-- conf
|-- lib
|-- logs
`-- version.txt
5 directories, 1 file
bin 目錄中是可執(zhí)行文件以及腳本十性，我們可以使用其中的 mycat 腳本控制mycat的啟動(dòng)和關(guān)閉角撞。
conf 目錄中是配置文件播急，這里配置讀寫分離主要使用 schema.xml 和 server.xml听盖。其他配置分片的配置請(qǐng)參考官方文檔杨拐。
logs 目錄存放日志文件棕所，遇到mycat出錯(cuò)了糊肠，就在這里查看問題的原因辨宠。
安裝 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货裹。
前提：已經(jīng)有一個(gè)配置好的 mysql 一主一從架構(gòu)嗤形。
一個(gè)主從集群在Mycat里面由一個(gè) dataNode 定義，dataNode 定義了一個(gè)數(shù)據(jù)庫實(shí)例及其中的一個(gè)具體的庫弧圆。Mycat 的一個(gè)數(shù)據(jù)庫實(shí)例可以實(shí)際上是一個(gè)主從復(fù)制架構(gòu)：一主多從赋兵，一主一從笔咽，多主多從等等，具體在 dataHost 中定義霹期。
這里建立一個(gè)非拆分庫（將mycat邏輯庫綁定到一個(gè)具體的 dataNode 上）testdb叶组，綁定到 dn1 這個(gè) dataNode 上。
schema.xml:
<schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
現(xiàn)在所有的表會(huì)走默認(rèn)的節(jié)點(diǎn) dn1历造。邏輯庫 testdb甩十，對(duì)應(yīng)了數(shù)據(jù)節(jié)點(diǎn) dn1。dn1 對(duì)應(yīng)著真實(shí)的數(shù)據(jù)庫實(shí)例上的一個(gè)真實(shí)的庫吭产。
<dataNode name="dn1" dataHost="vm3306" database="db1" >
</dataNode>
定義數(shù)據(jù)節(jié)點(diǎn)侣监，dn1，這個(gè)節(jié)點(diǎn)對(duì)應(yīng)一個(gè)數(shù)據(jù)庫實(shí)例中的一個(gè)真實(shí)的庫垮刹，庫名為 db1达吞。
dataNode 標(biāo)簽定義了 MyCat 中的數(shù)據(jù)節(jié)點(diǎn),也就是我們通常說所的數(shù)據(jù)分片。一個(gè) dataNode 標(biāo)簽就是 一個(gè)獨(dú)立的數(shù)據(jù)分片荒典。
例子中所表述的意思為:使用名字為 vm3306 數(shù)據(jù)庫實(shí)例上的 db1 物理數(shù)據(jù)庫,這就組成一個(gè)數(shù)據(jù)分片,最 后,我們使用名字 dn1 標(biāo)識(shí)這個(gè)分片酪劫。
該屬性用于定義該分片屬性哪個(gè)具體數(shù)據(jù)庫實(shí)例上的具體庫,因?yàn)檫@里使用兩個(gè)緯度來定義分片,就是:實(shí) 例+具體的庫。因?yàn)槊總€(gè)庫上建立的表和表結(jié)構(gòu)是一樣的寺董。所以這樣做就可以輕松的對(duì)表進(jìn)行水平拆分覆糟。
dataHost: 包含一個(gè) writeHost 和 一個(gè) readHost，它們之前已經(jīng)配置好主從復(fù)制了遮咖。
balance="3"：表示寫請(qǐng)求只發(fā)給節(jié)點(diǎn)滩字，讀請(qǐng)求只發(fā)給讀節(jié)點(diǎn)。
<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ā)送到配置的第一個(gè) writeHost,第一個(gè)掛了切到還生存的第二個(gè) writeHost,重新啟動(dòng)后已切換后的為準(zhǔn),切換記錄在配置文件中:dnindex.properties .
balance="3",所有讀請(qǐng)求隨機(jī)的分發(fā)到 wiriterHost 對(duì)應(yīng)的 readhost 執(zhí)行,writerHost 不負(fù)擔(dān)讀壓力,注意 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>
我們?cè)谥鲝?mysql 中創(chuàng)建測(cè)試用戶 tuser，賦予增刪改查陶珠，創(chuàng)建庫和表的權(quán)限挟裂。
master：
mysql> GRANT CREATE,DELETE,INSERT,SELECT,UPDATE ON db1.* TO 'tuser'@'192.168.0.%' IDENTIFIED BY 'guli123';
啟動(dòng) mycat:
cd /usr/local/mycat/bin
./mycat start
啟動(dòng) mycat 遇到第一個(gè)日志報(bào)錯(cuò)，在 logs/wrapper.log 看到如下錯(cuò)誤：
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.
提示說沒有足夠的內(nèi)存來啟動(dòng) Java 運(yùn)行時(shí)環(huán)境揍诽，因?yàn)槲矣玫奶摂M機(jī)诀蓉，給了 512M 內(nèi)存，所以內(nèi)存不夠暑脆，重新分配了1.5G渠啤，就不會(huì)報(bào)這個(gè)錯(cuò)了。
啟動(dòng) mycat 遇到第二個(gè)日志報(bào)錯(cuò)添吗，在 logs/wrapper.log 看到如下錯(cuò)誤：
[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 | 錯(cuò)誤: 代理拋出異常錯(cuò)誤: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: vm2: vm2: 未知的名稱或服務(wù)
錯(cuò)誤提示沥曹，可能沒有配置好本地主機(jī)名的名稱解析。
添加本地主機(jī)名解析：
[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
再次嘗試啟動(dòng)：
# ./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
顯示啟動(dòng)成功根资，這次OK了架专。
Mycat 管理命令與監(jiān)控
1. 管理命令
mysql -h127.0.0.1 -utest -ptest -P9066
MyCAT 自身有類似其他數(shù)據(jù)庫的管理監(jiān)控方式,可以通過 Mysql 命令行,登錄管理端口(9066)執(zhí)行相應(yīng) 的 SQL 進(jìn)行管理,也可以通過 jdbc 的方式進(jìn)行遠(yuǎn)程連接管理,本小節(jié)主要講解命令行的管理操作同窘。
登錄:目前 mycat 有兩個(gè)端口,8066 數(shù)據(jù)端口,9066 管理端口,命令行的登陸是通過 9066 管理端口來操 作,登錄方式類似于 mysql 的服務(wù)端登陸。
mysql -h127.0.0.1 -utest -ptest -P9066 [-dmycat]
-h 后面是主機(jī),即當(dāng)前 mycat 按照的主機(jī)地址,本地可用 127.0.0.1 遠(yuǎn)程需要遠(yuǎn)程 ip -u Mycat server.xml 中配置的邏輯庫用戶
-p Mycat server.xml 中配置的邏輯庫密碼
-P 后面是端口 默認(rèn) 9066,注意 P 是大寫
-d Mycat server.xml 中配置的邏輯庫
數(shù)據(jù)端口與管理端口的配置端口修改:數(shù)據(jù)端口默認(rèn) 8066,管理端口默認(rèn) 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)
查看當(dāng)前的庫：
mysql> show @@database;
+----------+
| DATABASE |
+----------+
| testdb |
+----------+
1 row in set (0.00 sec)
查看 MyCAT 的數(shù)據(jù)節(jié)點(diǎn)的列表,對(duì)應(yīng) schema.xml 配置文件的 dataNode 節(jié)點(diǎn):
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”表示對(duì)應(yīng) dataHost 屬性的值,即數(shù)據(jù)主機(jī); “ACTIVE”表示活躍連接數(shù);“IDLE”表示閑置連接數(shù);“SIZE”對(duì)應(yīng)總連接數(shù)量。
這里有 8 個(gè)空閑連接委刘，那我們?nèi)ブ鲝墓?jié)點(diǎn)用 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 服務(wù)器發(fā)起數(shù)據(jù)庫連接（主有9個(gè)連接丧没，從有8個(gè)連接）。
查看心跳報(bào)告：
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)
該命令用于報(bào)告心跳狀態(tài)
RS_CODE 狀態(tài):
OK_STATUS = 1;正常狀態(tài)
ERROR_STATUS = -1; 連接出錯(cuò)
TIMEOUT_STATUS = -2; 連接超時(shí)
INIT_STATUS = 0; 初始化狀態(tài)
若節(jié)點(diǎn)故障,會(huì)連續(xù)默認(rèn) 5 個(gè)周期檢測(cè),心跳連續(xù)失敗,就會(huì)變成-1,節(jié)點(diǎn)故障確認(rèn),然后可能發(fā)生切換
查看 Mycat 的前端連接狀態(tài)锡移，即應(yīng)用與 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 屬性呕童，可以手動(dòng)殺掉某個(gè)連接。
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個(gè)后端連接淆珊，這里截取最后一個(gè)夺饲。
顯示數(shù)據(jù)源：
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)
插入兩條數(shù)據(jù)：
mysql> insert into tb1 values (1, 'guli'), (2, 'xie');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
查看一下插入結(jié)果：
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | guli |
| 2 | xie |
+------+------+
2 rows in set (0.00 sec)
沒問題施符。
再分別到主從節(jié)點(diǎn)看數(shù)據(jù)插入沒有：
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)計(jì) insert 語句戳吝，不知為什么浩销。
查看統(tǒng)計(jì)數(shù)據(jù)：
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)
端口號(hào): 該命令工作在 9066 端口,用來記錄用戶通過本地 8066 端口向 Mycat-Server 發(fā)送的 SQL 請(qǐng)求執(zhí)行
信息。信息包括有 ID 值,執(zhí)行 SQL 語句的用戶名稱,執(zhí)行的 SQL 語句,命令執(zhí)行的起始時(shí)間,命令執(zhí)行消耗時(shí)間
查看慢查詢語句：
設(shè)置慢查詢閾值為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听哭，如果要驗(yàn)證一下讀寫分離已經(jīng)成功了慢洋，應(yīng)該怎么驗(yàn)證呢？
使用mysql客戶端連接9066管理端口陆盘，執(zhí)行 show @@datasource 可以觀察到 READ_LOAD普筹，WRITE_LOAD 兩個(gè)統(tǒng)計(jì)參數(shù)的變化：
這里顯示 hostM1 為寫節(jié)點(diǎn)，hostS1 為讀節(jié)點(diǎn)：
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í)行查詢斑芜，插入語句，同時(shí)使用mysql客戶端連接 9066 端口觀察一下讀寫統(tǒng)計(jì)參數(shù)的變化：
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é)點(diǎn)的讀計(jì)數(shù)加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é)點(diǎn)的讀計(jì)數(shù)加2
hostM1 的 WRITE_LOAD = 2
hostS1 的 READ_LOAD = 13
由此可見讀寫分離是成功的∮可以看到數(shù)據(jù)也成功寫入數(shù)據(jù)庫：
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | guli |
| 2 | xie |
| 3 | xu |
| 4 | he |
| 5 | huang |
+------+-------+
5 rows in set (0.00 sec)
到此基本演示了 mycat 的主從讀寫分離功能沸呐，配置的前提是已經(jīng)有一個(gè)配置好的 mysql 主從復(fù)制架構(gòu)，mycat 工作于 mysql 主從架構(gòu)的前端呢燥，負(fù)責(zé) SQL 語句的分發(fā)崭添。
mycat 另一個(gè)主要功能是數(shù)據(jù)分片，這里沒有演示叛氨。暫時(shí)就寫到這里呼渣。
