美文网首页
Mycat 实现读写分离

Mycat 实现读写分离

作者: desirelll | 来源:发表于2017-10-30 10:24 被阅读0次

    一、环境介绍

    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属性说明:

    1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个 writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
    2. 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,说明读写分离是成功的

    本文参考:http://www.jianshu.com/p/cb7ec06dae05

    相关文章

      网友评论

          本文标题:Mycat 实现读写分离

          本文链接:https://www.haomeiwen.com/subject/rtkppxtx.html