Mycat 实现读写分离
2017-10-30 本文已影响0人
desirelll
一、环境介绍
macOS（Mycat1.6） + centos（MySQL主从复制）
实验拓扑
mycat
/ \
master -- slave (主从复制)
需求：已部署好 MySQL 主从复制
二、Mycat 配置
schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="item" primaryKey="id" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="c1" database="lujw_mycat_db01" />
<!-- 读写分离 -->
<dataHost name="c1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="WHost" url="116.7.40.10:3305" user="root" password="password">
<readHost host="RHost" url="116.7.40.10:3304" user="root" password="password" />
</writeHost>
</dataHost>
</mycat:schema>
dataNode 标签定义了 MyCat 中的数据节点,也就是我们通常说所的数据分片。一个 dataNode 标签就是 一个独立的数据分片。
dataHost: 包含一个 writeHost 和 一个 readHost，它们之前已经配置好主从复制了。
balance="3"：表示写请求只发给节点，读请求只发给读节点。
dataHost属性说明：
- writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个 writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
- balance="3",所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。
server.xml 配置
<user name="root">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
这里根据自己 Mycat 设定来修改
三、创建数据库
在主库中创建如 schema.xml 所示的数据库
create database lujw_mycat_db01;
use lujw_mycat_db01;
create table item(
id int not null,
name varchar(20)
);
四、Mycat 管理端口和数据端口
管理端口：9066
数据端口：8066
为了观察数据路由和验证读写分离的正确性，使用两个终端分别进入两种 Mycat 管理模式
mysql -uroot -p123456 -P8066
mysql -uroot -p123456 -P9066
列出管理端口支持的命令
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.01 sec)
其中比较有用的是
查看心跳报告：
mysql> show @@heartbeat;
+-------+-------+-------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+-------+-------+-------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| WHost | mysql | 116.7.40.10 | 3305 | 1 | 0 | idle | 0 | 68,45,34 | 2017-10-25 15:40:20 | false |
| RHost | mysql | 116.7.40.10 | 3304 | 1 | 0 | idle | 0 | 65,39,33 | 2017-10-25 15:40:20 | false |
+-------+-------+-------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
2 rows in set (0.00 sec)
根据 RS_CODE 可以得到节点状态
RS_CODE 状态:
OK_STATUS = 1;正常状态
ERROR_STATUS = -1; 连接出错
TIMEOUT_STATUS = -2; 连接超时
INIT_STATUS = 0; 初始化状态
显示数据源：
mysql> show @@datasource;
+----------+-------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+-------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | WHost | mysql | 116.7.40.10 | 3305 | W | 0 | 10 | 1000 | 242 | 0 | 5 |
| dn1 | RHost | mysql | 116.7.40.10 | 3304 | R | 0 | 8 | 1000 | 205 | 1 | 0 |
+----------+-------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
这里可以看到主从信息，最后两列可以用来判断读写分离是否成功
接下来插入数据来验证
使用 Mycat 8066端进行操作
insert into item(id, name) value(100, "hello");
select * from item;
切换到 9066 再次查询数据源信息
mysql> show @@datasource;
+----------+-------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+-------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | WHost | mysql | 116.7.40.10 | 3305 | W | 0 | 10 | 1000 | 265 | 0 | 6 |
| dn1 | RHost | mysql | 116.7.40.10 | 3304 | R | 0 | 8 | 1000 | 228 | 2 | 0 |
+----------+-------+-------+-------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
可以看到主从库的读写分别加了1，说明读写分离是成功的
