mysql主从之基于mycat实现读写分离

Wesley13
• 阅读 693

一 环境  

1.1 结构

192.168.132.125 mycat

192.168.132.121  master

192.168.132.122  slave

主从已经配置完成

1.2 安装mycat

192.168.132.125安装mycat

[root@mycat ~]#   cd /usr/local/src/

[root@mycat src]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@mycat src]# tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@mycat src]# cd mycat/

[root@mycat mycat]# ll

drwxr-xr-x. 2 root root  190 Jul 12 08:50 bin
drwxrwxrwx. 2 root root    6 Feb 29  2016 catlet
drwxrwxrwx. 4 root root 4096 Jul 12 08:50 conf
drwxr-xr-x. 2 root root 4096 Jul 12 08:50 lib
drwxrwxrwx. 2 root root    6 Oct 28  2016 logs
-rwxrwxrwx. 1 root root  217 Oct 28  2016 version.txt

1.3 配置mycat

[root@mycat mycat]# cd conf/

[root@mycat conf]# ll

-rwxrwxrwx. 1 root root   88 Oct 28  2016 autopartition-long.txt
-rwxrwxrwx. 1 root root   48 Oct 28  2016 auto-sharding-long.txt
-rwxrwxrwx. 1 root root   62 Oct 28  2016 auto-sharding-rang-mod.txt
-rwxrwxrwx. 1 root root  334 Oct 28  2016 cacheservice.properties
-rwxrwxrwx. 1 root root  439 Oct 28  2016 ehcache.xml
-rwxrwxrwx. 1 root root 2132 Oct 28  2016 index_to_charset.properties
-rwxrwxrwx. 1 root root 1246 Oct 10  2016 log4j2.xml
-rwxrwxrwx. 1 root root  178 Oct 28  2016 migrateTables.properties
-rwxrwxrwx. 1 root root  246 Oct 28  2016 myid.properties
-rwxrwxrwx. 1 root root   15 Oct 28  2016 partition-hash-int.txt
-rwxrwxrwx. 1 root root  102 Oct 28  2016 partition-range-mod.txt
-rwxrwxrwx. 1 root root 4794 Oct 28  2016 rule.xml
-rwxrwxrwx. 1 root root 4219 Oct 28  2016 schema.xml     #主要配置这个文件
-rwxrwxrwx. 1 root root  413 Oct 28  2016 sequence_conf.properties
-rwxrwxrwx. 1 root root   75 Oct 28  2016 sequence_db_conf.properties
-rwxrwxrwx. 1 root root   27 Oct 28  2016 sequence_distributed_conf.properties
-rwxrwxrwx. 1 root root   51 Oct 28  2016 sequence_time_conf.properties
-rwxrwxrwx. 1 root root 3717 Oct 28  2016 server.xml
-rwxrwxrwx. 1 root root   16 Oct 28  2016 sharding-by-enum.txt
-rwxrwxrwx. 1 root root 4182 Oct 28  2016 wrapper.conf
drwxrwxrwx. 2 root root 4096 Jul 12 08:50 zkconf
drwxrwxrwx. 2 root root   36 Jul 12 08:50 zkdownload

[root@mycat conf]# cat schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!‐‐ TESTDB 表示mycat 的逻辑数据库名称当schema 节点没有子节点table 的时候,一定要有dataNode 属性存在‐‐>
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">    
    </schema>

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

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.132.121:3306" user="root"
                   password="123456">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.132.122:3306" user="root" password="123456" />
        </writeHost>

    </dataHost>

</mycat:schema>

注意 dataHost 节点的下面三个属性balance, switchType, writeType

balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost 上。

balance="1",全部的readHost 与stand by writeHost 参与select语句的负载均衡,简单的说,当双主双从模式(M1‐>S1,M2‐>S2,并且M1 与M2 互为主备),正常情况下,M2,S1,S2都参与select 语句的负载均衡。

balance="2",所有读操作都随机的在writeHost、readhost 上分发。

balance="3",所有读请求随机的分发到writeHost 下的readhost 执行,writeHost 不负担读压力

writeType 表示写模式

writeType="0",所有的操作发送到配置的第一个writehost

writeType="1",随机发送到配置的所有writehost

writeType="2",不执行写操作

switchType 指的是切换的模式,目前的取值也有4 种:

switchType=‘‐1‘ 表示不自动切换

switchType=‘1‘ 默认值,表示自动切换

switchType=‘2‘ 基于MySQL 主从同步的状态决定是否切换,心跳语句为show slave status

switchType=‘3‘基于MySQL galary cluster 的切换机制(适合集群)(1.4.1),心跳语句为show status like ‘wsrep%‘。

配置端口,用户名

[root@mycat conf]# vim 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://io.mycat/">
        <system>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

                <property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!-- 
        <property name="processors">1</property> 
        <property name="processorExecutor">32</property> 
         -->
                <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
                <property name="processorBufferPoolType">0</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="handleDistributedTransactions">0</property>
                <!--                     off heap for merge/order/group/limit      1开启   0关闭            -->
                <property name="useOffHeapForMerge">1</property>
                <!--                        单位为m                -->
                <property name="memoryPageSize">1m</property>
                <!--                        单位为k                -->
                <property name="spillsFileBufferSize">1k</property>
                <property name="useStreamOutput">0</property>
                <!--                        单位为m                -->
                <property name="systemReserveMemorySize">384m</property>
                <!--是否采用zookeeper协调切换  -->
                <property name="useZKSwitch">true</property>
        </system>
        <!-- 全局SQL防火墙设置 -->
        <!-- 
        <firewall> 
           <whitehost>
              <host host="127.0.0.1" user="mycat"/>
              <host host="127.0.0.2" user="mycat"/>
           </whitehost>
       <blacklist check="false">
       </blacklist>
        </firewall>
        -->
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>
</mycat:server

二 验证

2.1 启动Mycat

[root@mycat conf]# ../bin/mycat start

2.2 日志

[root@mycat conf]# cat /usr/local/src/mycat/logs/wrapper.log

STATUS | wrapper  | 2019/07/12 10:44:55 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2019/07/12 10:44:55 | Launching a JVM...
ERROR  | wrapper  | 2019/07/12 10:45:12 | Unable to start JVM: No such file or directory (2)   #没有JVM,安装java
ERROR  | wrapper  | 2019/07/12 10:45:12 | JVM exited while loading the application.
FATAL  | wrapper  | 2019/07/12 10:45:12 | There were 5 failed launches in a row, each lasting less than 300 seconds.  Giving up.
FATAL  | wrapper  | 2019/07/12 10:45:12 |   There may be a configuration problem: please check the logs.
STATUS | wrapper  | 2019/07/12 10:45:12 | <-- Wrapper Stopped

[root@mycat conf]# yum -y install java

[root@mycat conf]# ../bin/mycat start

[root@mycat conf]# tail -f /usr/local/src/mycat/logs/wrapper.log

INFO   | jvm 1    | 2019/07/12 11:02:46 | 2019-07-12 11:02:46,389 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=12, lastTime=1562943766373, user=root, schema=master1, old shema=master1, borrowed=false, fromSlaveDB=true, threadId=1428, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.132.122, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45) 
INFO   | jvm 1    | 2019/07/12 11:02:46 | MyCAT Server startup successfully. see logs in logs/mycat.log

2.3 连接,端口为8066

[root@mycat ~]# mysql -uroot -p1234567 -h192.168.132.125  -P8066

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+

2.4 写入数据测试

MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> show tables;
MySQL [TESTDB]> select * from test;
MySQL [TESTDB]> insert  into test values (555);
MySQL [TESTDB]> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
|    2 |
|   11 |
|   12 |
|  111 |
|  222 |
|  333 |
|  444 |
|  555 |
+------+
mater查看
mysql> select * from master1.test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
|    2 |
|   11 |
|   12 |
|  111 |
|  222 |
|  333 |
|  444 |
|  555 |
+------+
slave查看
mysql> select * from master1.test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
|    2 |
|   11 |
|   12 |
|  111 |
|  222 |
|  333 |
|  444 |
|  555 |
+------+

成功写入数据

2.5 验证读写分离

修改日志级别为debug

[root@mycat conf]# vim log4j2.xml

<Loggers>
        <!--<AsyncLogger name="io.mycat" level="info" includeLocation="true" additivity="false">-->
            <!--<AppenderRef ref="Console"/>-->
            <!--<AppenderRef ref="RollingFile"/>-->
        <!--</AsyncLogger>-->
        <asyncRoot level="debug" includeLocation="true">

            <AppenderRef ref="Console" />
            <AppenderRef ref="RollingFile"/>

        </asyncRoot>
    </Loggers>

[root@mycat conf]# /usr/local/src/mycat/bin/mycat stop

[root@mycat conf]# /usr/local/src/mycat/bin/mycat start

写入一个数据

MySQL [TESTDB]> insert  into test values (666);

[root@mycat conf]# tail -f /usr/local/src/mycat/logs/wrapper.log

INFO   | jvm 1    | 2019/07/12 11:15:06 | 2019-07-12 11:15:06,662 [DEBUG][$_NIOREACTOR-0-RW] con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=7, lastTime=1562944506662, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14906, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert  into test values (666)}, respHandler=SingleNodeHandler [node=dn1{insert  into test values (666)}, packetId=0], host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]  (io.mycat.backend.mysql.nio.MySQLConnection:MySQLConnection.java:448) 
INFO   | jvm 1    | 2019/07/12 11:15:06 | 2019-07-12 11:15:06,668 [DEBUG][$_NIOREACTOR-1-RW] release connection MySQLConnection [id=7, lastTime=1562944506646, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14906, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert  into test values (666)}, respHandler=SingleNodeHandler [node=dn1{insert  into test values (666)}, packetId=1], host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]  (io.mycat.server.NonBlockingSession:NonBlockingSession.java:341) 
INFO   | jvm 1    | 2019/07/12 11:15:06 | 2019-07-12 11:15:06,669 [DEBUG][$_NIOREACTOR-1-RW] release channel MySQLConnection [id=7, lastTime=1562944506646, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14906, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:442) 
INFO   | jvm 1    | 2019/07/12 11:15:14 | 2019-07-12 11:15:13,968 [DEBUG][Timer1] con query sql:select user() to con:MySQLConnection [id=3, lastTime=1562944513968, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14907, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.mycat.sqlengine.SQLJob:SQLJob.java:88)

发现在192.168.132.121 主数据库上面

查找数据

MySQL [TESTDB]> select * from test;

INFO   | jvm 1    | 2019/07/12 11:20:13 | 2019-07-12 11:20:13,419 [DEBUG][$_NIOREACTOR-0-RW] con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=15, lastTime=1562944813419, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=true, threadId=1596, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{select * from test}, respHandler=SingleNodeHandler [node=dn1{select * from test}, packetId=0], host=192.168.132.122, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.mycat.backend.mysql.nio.MySQLConnection:MySQLConnection.java:448) 
INFO   | jvm 1    | 2019/07/12 11:20:13 | 2019-07-12 11:20:13,421 [DEBUG][$_NIOREACTOR-0-RW] release connection MySQLConnection [id=15, lastTime=1562944813407, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=true, threadId=1596, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{select * from test}, respHandler=SingleNodeHandler [node=dn1{select * from test}, packetId=16], host=192.168.132.122, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@3055d901, writeQueue=0, modifiedSQLExecuted=false]  (io.mycat.server.NonBlockingSession:NonBlockingSession.java:341)

在salve192.168.132.122从数据库上面,读和写在不同的服务器上面

2.6 问题

但这种方式有个问题,即master 挂了以后,slave 也不能提供读和写服务Slave 挂了 不能查询,但是可以插入。

停掉主数据库

[root@master1 ~]# systemctl stop mysqld

[root@mycat ~]# mysql -uroot -p1234567 -h192.168.132.125  -P8066 TESTDB

MySQL [TESTDB]> select * from test;
ERROR 1184 (HY000): Connection refused
MySQL [TESTDB]> insert into test values (777);
ERROR 1184 (HY000): Connection refused

已经不能查询,也不可以插入

停掉slave

不可以查,但是可以写

[root@slave ~]# systemctl stop mysqld

MySQL [TESTDB]> select * from test;
ERROR 1184 (HY000): Connection refused
MySQL [TESTDB]> insert  into test values (777);
Query OK, 1 row affected (0.00 sec)

读写分离配置完成

这种结构过于简单,但是实现了读写分离的功能,生产中需要综合考虑单点故障的问题,需要更可靠的架构!

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这