mycat实现mysql读写分离

原创 2016年08月28日 16:12:52

1、mycat的下载地址 https://github.com/MyCATApache/Mycat-download
这里下载的版本是Mycat-server-1.4-release-20151019230038-win.tar.gz
解压即可

2、需要配置JDK的环境变量;然后修改conf文件夹下的wrapper.conf文件的:

# Java Application
wrapper.java.command=E:\Programs\Java\jdk1.8.0_31\bin
wrapper.working.dir=..

3、修改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>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
    <!-- <property name="processorBufferChunk">40960</property> -->
    <!-- 
    <property name="processors">1</property> 
    <property name="processorExecutor">32</property> 
     -->
        <!--默认是65535 64K 用于sql解析时最大文本长度 -->
        <!--<property name="maxStringLiteralLength">65535</property>-->
        <!--<property name="sequnceHandlerType">0</property>-->
        <!--<property name="backSocketNoDelay">1</property>-->
        <!--<property name="frontSocketNoDelay">1</property>-->
        <!--<property name="processorExecutor">16</property>-->
        <!-- 
            <property name="mutiNodeLimitType">1</property> 0:开启小数量级(默认) ;1:开启亿级数据排序
            <property name="mutiNodePatchSize">100</property> 亿级数量排序批量
            <property name="processors">32</property> <property name="processorExecutor">32</property> 
            <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
            <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
            <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
    </system>
    <user name="root">
        <property name="password">root</property>
        <property name="schemas">qs</property>
    </user>

    <user name="user">
        <property name="password">user</property>
        <property name="schemas">qs</property>
        <property name="readOnly">true</property>
    </user>
    <!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property> 
        <property name="weight">1</property> </node> </cluster> -->
    <!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property> 
        </host> </quarantine> -->

</mycat:server>

注:qs必须和schema.xml的schemas对应;此处的user root 和user 指的是mycat的用户名;可以通过>mysql -uroot -proot -hlocalhost -P8066 -Dqs 登录查询。

4、修改schema.xml文件:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

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

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


    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
        writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM1" url="192.168.1.56:3306" user="root"
            password="root">

        <readHost host="hostS1" url="192.168.0.83:3306" user="root"
            password="root" />
        </writeHost>

    </dataHost>

</mycat:schema>

没有涉及到分库配置比较简单;database=”db1” db1是两台mysql服务器中都存在的数据库(因为两个数据库已经配置好主从复制,所以数据一样的)。
注:192.168.1.56 和 192.168.0.83 在已经配置好mysql主从复制的情况下,然后通过mycat的读写分离来实现读取数据库只在192.168.0.83,写入数据库只在192.168.1.56。

balance 属性
负载均衡类型,目前的取值有 3 种:
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 没有。

writeType 属性
负载均衡类型,目前的取值有 3 种:
1. writeType=”0”, 所有操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个
writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
2. writeType=”1”,所有写操作都随机发送到配置的 writeHost,1.5 以后废弃不推荐。

switchType 属性
- 0 表示不自动切换
- 1 默讣值,自动切换
- 2 基于 MySQL 主从同步的状态决定是否切换

根据主从延时切换:
1.4 开始支持 MySQL 主从复制状态绑定的读写分离机制,让读更加安全可靠,配置如下:
MyCAT 心跳检查语句配置为 show slave status ,dataHost 上定义两个新属性: switchType=”2” 于
slaveThreshold=”100”,此时意味着开启 MySQL 主从复制状态绑定的读写分离于切换机制,Mycat 心跳机制
通过检测 show slave status 中的 “Seconds_Behind_Master”, “Slave_IO_Running”,
“Slave_SQL_Running” 三个字段来确定当前主从同步的状态以及 Seconds_Behind_Master 主从复制时延,
当 Seconds_Behind_Master>slaveThreshold 时,读写分离筛选器会过滤掉此 Slave 机器,防止读到很久之前
的旧数捤,而当主节点宕机后,切换逻辑会检查 Slave 上的 Seconds_Behind_Master 是否为 0,为 0 时则
表示主从同步,可以安全切换,否则不会切换。

read测试 mycat和192.168.0.83一致:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -proot -hlocalhost -P8066 -Dqs
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 12
Server version: 5.5.8-mycat-1.4-release-20151019230038 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2016, 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.

mysql> select * from test1;
+—-+——+
| id | name |
+—-+——+
| 1 | 8383 |
+—-+——+
1 row in set (0.00 sec)

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -proot -h192.168.0.83 -P3306 -Ddb1
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 411
Server version: 5.7.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

mysql> select * from test1;
+—-+——+
| id | name |
+—-+——+
| 1 | 8383 |
+—-+——+
1 row in set (0.00 sec)

测试写入(已关闭mysql主从复制功能):
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -proot -hlocalhost -P8066 -Dqs
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 13
Server version: 5.5.8-mycat-1.4-release-20151019230038 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2016, 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.

mysql> insert test1 values(2,’localhost’);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+—-+——+
| id | name |
+—-+——+
| 1 | 8383 |
+—-+——+
1 row in set (0.00 sec)

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -proot -h192.168.0.83 -P3306 -Ddb1
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 412
Server version: 5.7.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

mysql> select * from test1;
+—-+——+
| id | name |
+—-+——+
| 1 | 8383 |
+—-+——+
1 row in set (0.00 sec)

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -proot -h192.168.1.56 -P3306 -Ddb1
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 784
Server version: 5.7.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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.

mysql> select * from test1;
+—-+————-+
| id | name |
+—-+————-+
| 1 | 5656565656 |
| 2 | localhost |
+—-+————-+
2 rows in set (0.00 sec)

版权声明:本文为博主原创文章,未经博主允许不得转载。

MyCat实现mysql读写分离配置说明

  • 2016年07月08日 09:20
  • 35KB
  • 下载

分布式架构学习之:MyCat在MySQL主从复制基础上实现读写分离

一、环境   操作系统:CentOS-6.6-x86_64-bin-DVD1.iso JDK 版本:jdk1.7.0_45   MyCat 版本:Mycat-server-...

mysql+mycat 实现读写分离

Prepare: 二台linux(CentOS7)服务器(IP:192.168.82.252,192.168.82.86) mycat安装包 mysql...
  • Once520
  • Once520
  • 2016年12月02日 14:54
  • 302

mycat实现mysql读写分离,热切换,集群

在互联网时代,实现mysql读写分离,热切换,集群能大大提高mysql数据库访问压力,防止数据丢失,使用mycat能帮我们实现这些。我们要实现的是这种架构。1、Mycat能实现数据库的垂直切分和水平切...

windows下配置mycat,实现mysql数据库的读写分离!

下载Windows的安装包 https://github.com/MyCATApache/Mycat-download/tree/master/1.4-RELEASE下的 ...
  • zwcwu31
  • zwcwu31
  • 2017年06月06日 23:29
  • 562

使用mycat实现mysql读写分离以及主备自动切换模式

一:软件环境 Window7 Mycat-server1.6 JDK1.8 Mysql5.62台 二:读写分离的好处  增加了冗余(备份数据)。   读写分开,减轻机器压力,提高数据库并发。 My...

MyCAT实现MySQL的读写分离

在MySQL中间件出现之前,对于MySQL主从集群,如果要实现其读写分离,一般是在程序端实现,这样就带来一个问题,即数据库和程序的耦合度太高,如果我数据库的地址发生改变了,那么我程序端也要进行相应的修...

使用mycat实现mysql读写分离以及主备自动切换模式

一:软件环境Window7 Mycat-server1.6 JDK1.8 Mysql5.62台二:读写分离的好处增加了冗余(备份数据)。 读写分开,减轻机器压力,提高数据库并发。 Myca...

mycat+mysql集群:实现读写分离,分库分表

mycat+mysql集群:实现读写分离,分库分表

利用mycat实现基于mysql5.5主从复制的读写分离

整体步骤: 1.准备好两台服务器,一台作为主数据库服务器,一台作为从服务器,并安装好mysql数据库,此处略 2.配置好主从同步 3.下载JDK配置mycat依赖的JAVA环境,mycat采用java...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mycat实现mysql读写分离
举报原因:
原因补充:

(最多只允许输入30个字)