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

曾经终败给现在 2021-12-03 11:13 328阅读 0赞

## 一 环境   ##

### 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>
            
            <writeHost host="hostM1" url="192.168.132.121:3306" user="root"
                       password="123456">
                
                <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"?>
    
    <!DOCTYPE mycat:server SYSTEM "server.dtd">
    <mycat:server xmlns:mycat="http://io.mycat/">
            <system>
            <property name="useSqlStat">0</property>  
            <property name="useGlobleTableCheck">0</property>  
    
                    <property name="sequnceHandlerType">2</property>
           
             
            
            
                    
                    <property name="processorBufferPoolType">0</property>
                    
                    
                    
                    
                    
                    
                    
                    <property name="useOffHeapForMerge">1</property>
                    
                    <property name="memoryPageSize">1m</property>
                    
                    <property name="spillsFileBufferSize">1k</property>
                    <property name="useStreamOutput">0</property>
                    
                    <property name="systemReserveMemorySize">384m</property>
                    
                    <property name="useZKSwitch">true</property>
            </system>
            
            
            <user name="root">
                    <property name="password">123456</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>
    </mycat:server

## 二 验证 ##

### 2.1 启动Mycat ###

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

### 2.2 日志 ###

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

\[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

\[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)

读写分离配置完成

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

转载于:https://www.cnblogs.com/zyxnhr/p/11179017.html