Mycat数据库中间件对Mysql读写分离和分库分表配置

太过爱你忘了你带给我的痛 2022-05-25 02:20 205阅读 0赞

Mycat是一个开源的分布式数据库系统，不同于oracle和mysql，Mycat并没有存储引擎，但是Mycat实现了mysql协议，前段用户可以把它当做一个Proxy。其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端mysql存储引擎里面。最新版本的Mycat不仅支持mysql，还可以支持MS SqlServer,Oracle,DB2等关系型数据库，而且还支持MongoDB这种NoSQL。Mycat对调用者屏蔽了后端存储具体实现。

Mycat的原理是先拦截用户的SQL语句并做分析：分片分析，路由分析，读写分离分析，缓存分析等，再对将该SQL语句发送到指定规则的数据库。最终将存储引擎返回的结果返回给用户.

本次搭建的Mycat读写分离和分库分表配置是基于5台服务器（1台Mac,4台Centos），架构图如下，Mac服务器上面安装Mycat；M1(OS名称：Server1,Ip:172.16.130.189)和M2(OS名称：Server2,Ip:172.16.130.190)是两个写节点; S1(OS名称：Server3,Ip:172.16.130.191)和S2(OS名称：Server4,Ip:172.16.130.192)是两个读节点。且M1和S1配置了主从复制，M2和S2也配置了主从复制。

![314515-20170309155821031-1125134573.png][]

步骤

1.在Server1,Server2,Server3和Server4中安装相同版本的Mysql，安装步骤在我之前的一篇文章中：[四·安装mysql-5.7.16-linux-glibc2.5-x86\_64.tar.gz（基于Centos7源码安装）][mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz_Centos7]

2.配置Server3同步Server1；Server4同步Server2,在我的另外一篇文章中：[一.Mysql主从复制配置][.Mysql]

其中关键代码如下

![复制代码][copycode.gif]

--------------------------------------Server1配置-----------------------------------------------------------
    mysql> grant replication slave on *.* to 'mysqlsync'@'172.16.130.191' identified by 'mysqlsync';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> show master status;
    +------------+----------+--------------+------------------+-------------------+
    | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------+----------+--------------+------------------+-------------------+
    | bin.000003 |      455 |              |                  |                   |
    +------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

--------------------------------------Server3配置-----------------------------------------------------------

mysql> change master to master\_host='172.16.130.189',master\_user='mysqlsync',master\_password='mysqlsync',master\_log\_file='bin.000003',master\_log\_pos=154;  
  Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;  
  Query OK, 0 rows affected (0.00 sec)

![复制代码][copycode.gif]

3.在Server1和Server2上面分别新建一个名为mycattest的schema，且配置的访问用户名为mycattest,密码为1234
    4.修改wapper.cnf

![复制代码][copycode.gif]

#********************************************************************
    # Wrapper Properties
    #********************************************************************
    # Java Application
    wrapper.java.command=/Library/Java/JavaVirtualMachines/jdk1.7.0_79.jdk/Contents/Home/bin/java
    wrapper.working.dir=..
    
    # Java Main class.  This class must implement the WrapperListener interface
    #  or guarantee that the WrapperManager class is initialized.  Helper
    #  classes are provided to do this for you.  See the Integration section
    #  of the documentation for details.
    wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperSimpleApp
    set.default.REPO_DIR=lib
    set.APP_BASE=.
    
    # Java Classpath (include wrapper.jar)  Add class path elements as
    #  needed starting from 1
    wrapper.java.classpath.1=lib/wrapper.jar
    wrapper.java.classpath.2=conf
    wrapper.java.classpath.3=%REPO_DIR%/*
    
    # Java Library Path (location of Wrapper.DLL or libwrapper.so)
    wrapper.java.library.path.1=lib
    
    # Java Additional Parameters
    #wrapper.java.additional.1=
    wrapper.java.additional.1=-DMYCAT_HOME=.
    wrapper.java.additional.2=-server
    wrapper.java.additional.3=-XX:MaxPermSize=64M
    wrapper.java.additional.4=-XX:+AggressiveOpts
    wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G
    wrapper.java.additional.6=-Dcom.sun.management.jmxremote
    wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984
    wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false
    wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false
    wrapper.java.additional.10=-Xmx4G
    wrapper.java.additional.11=-Xms1G
    
    # Initial Java Heap Size (in MB)
    #wrapper.java.initmemory=3
    
    # Maximum Java Heap Size (in MB)
    #wrapper.java.maxmemory=64
    
    # Application parameters.  Add parameters as needed starting from 1
    wrapper.app.parameter.1=io.mycat.MycatStartup
    wrapper.app.parameter.2=start
    
    #********************************************************************
    # Wrapper Logging Properties
    #********************************************************************
    # Format of output for the console.  (See docs for formats)
    wrapper.console.format=PM
    
    # Log Level for console output.  (See docs for log levels)
    wrapper.console.loglevel=INFO
    
    # Log file to use for wrapper output logging.
    wrapper.logfile=logs/wrapper.log
    
    # Format of output for the log file.  (See docs for formats)
    wrapper.logfile.format=LPTM
    
    # Log Level for log file output.  (See docs for log levels)
    wrapper.logfile.loglevel=INFO
    
    # Maximum size that the log file will be allowed to grow to before
    #  the log is rolled. Size is specified in bytes.  The default value
    #  of 0, disables log rolling.  May abbreviate with the 'k' (kb) or
    #  'm' (mb) suffix.  For example: 10m = 10 megabytes.
    wrapper.logfile.maxsize=0
    
    # Maximum number of rolled log files which will be allowed before old
    #  files are deleted.  The default value of 0 implies no limit.
    wrapper.logfile.maxfiles=0
    
    # Log Level for sys/event log output.  (See docs for log levels)
    wrapper.syslog.loglevel=NONE
    
    #********************************************************************
    # Wrapper Windows Properties
    #********************************************************************
    # Title to use when running as a console
    wrapper.console.title=Mycat-server
    
    #********************************************************************
    # Wrapper Windows NT/2000/XP Service Properties
    #********************************************************************
    # WARNING - Do not modify any of these properties when an application
    #  using this configuration file has been installed as a service.
    #  Please uninstall the service before modifying this section.  The
    #  service can then be reinstalled.
    
    # Name of the service
    wrapper.ntservice.name=mycat
    
    # Display name of the service
    wrapper.ntservice.displayname=Mycat-server
    
    # Description of the service
    wrapper.ntservice.description=The project of Mycat-server
    
    # Service dependencies.  Add dependencies as needed starting from 1
    wrapper.ntservice.dependency.1=
    
    # Mode in which the service is installed.  AUTO_START or DEMAND_START
    wrapper.ntservice.starttype=AUTO_START
    
    # Allow the service to interact with the desktop.
    wrapper.ntservice.interactive=false
    
    wrapper.ping.timeout=120
    configuration.directory.in.classpath.first=conf

![复制代码][copycode.gif]

5.修改server.xml

![复制代码][copycode.gif]

<?xml version="1.0" encoding="UTF-8"?>
    
    <!DOCTYPE mycat:server SYSTEM "server.dtd">
    <mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
        <property name="useSqlStat">0</property>  
        <property name="useGlobleTableCheck">0</property>  
    
            <property name="sequnceHandlerType">2</property>
           
             
        
        
            
            <property name="processorBufferPoolType">0</property>
            
            
            
            
            
            
            
            
            <property name="handleDistributedTransactions">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="test1">
            <property name="password">123456</property>
            <property name="schemas">TESTDB</property>
            
            
            
        </user>
    
        <user name="test2">
            <property name="password">user</property>
            <property name="schemas">TESTDB</property>
            <property name="readOnly">true</property>
        </user>
    
    </mycat:server>

![复制代码][copycode.gif]

6.修改schema.xml

![复制代码][copycode.gif]

<?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="g_user" primaryKey="user_id"  dataNode="Server1,Server2"  rule="sharding-by-mod" />
         
        </schema>
        
         <dataNode name="Server1" dataHost="172.16.130.189" database="mycattest" />
         <dataNode name="Server2" dataHost="172.16.130.190" database="mycattest" />
         
             
       <dataHost name="172.16.130.189" maxCon="1000" minCon="10" balance="3"
            writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            
            <writeHost host="Server1" url="172.16.130.189:3306" user="mycattest" password="1234">
                
                <readHost host="Server3" url="172.16.130.191:3306" user="mycattest" password="1234"/>
            </writeHost>
        </dataHost>
        <dataHost name="172.16.130.190" maxCon="1000" minCon="10" balance="3"
            writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            
            <writeHost host="Server2" url="172.16.130.190:3306" user="mycattest" password="1234" >
                
                <readHost host="Server4" url="172.16.130.192:3306" user="mycattest" password="1234"/>
            </writeHost>
            
        </dataHost>
    
    </mycat:schema>

![复制代码][copycode.gif]

7.启动Mycat

![复制代码][copycode.gif]

bogon:bin Randy$ ./mycat console
    Running Mycat-server...
    wrapper  | --> Wrapper Started as Console
    wrapper  | Launching a JVM...
    jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
    jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
    jvm 1    | 
    jvm 1    | 2017-03-18 21:21:18,296 [INFO ][WrapperSimpleAppMain] total resouces of dataHost 172.16.130.190 is :2  (io.mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:100) 
    jvm 1    | 2017-03-18 21:21:18,298 [INFO ][WrapperSimpleAppMain] total resouces of dataHost 172.16.130.189 is :2  (io.mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:100) 
    jvm 1    | 2017-03-18 21:21:18,307 [INFO ][WrapperSimpleAppMain] create layer cache pool TableID2DataNodeCache of type encache ,default cache size 10000 ,default expire seconds18000  (io.mycat.cache.CacheService:CacheService.java:125) 
    jvm 1    | 2017-03-18 21:21:18,308 [INFO ][WrapperSimpleAppMain] create child Cache: TESTDB_ORDERS for layered cache TableID2DataNodeCache, size 50000, expire seconds 18000  (io.mycat.cache.DefaultLayedCachePool:DefaultLayedCachePool.java:80) 
    jvm 1    | 2017-03-18 21:21:18,329 [DEBUG][WrapperSimpleAppMain] Configuring ehcache from ehcache.xml found in the classpath: file:/Users/Randy/Developer/16InstallSoftware/mycat/conf/ehcache.xml  (net.sf.ehcache.config.ConfigurationFactory:ConfigurationFactory.java:132)

![复制代码][copycode.gif]

\*\*\*\*\*\*测试：

由于我在上面的配置中，定了表g\_user按照sharding-by-mod算法规则来分片，

通过Mycat插入了两条数据

insert into g_user(id) values(1);
    insert into g_user(id) values(2);

通过查询，发现id=1的数据被插入到了M2和S2数据库服务器，而id=2的数据被插入到了M1和S1数据库

[314515-20170309155821031-1125134573.png]: /images/20220525/27880fcc481c447aabc59de55818e535.png
[mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz_Centos7]: http://www.cnblogs.com/qizhelongdeyang/p/6021357.html
[.Mysql]: http://www.cnblogs.com/qizhelongdeyang/p/6293812.html
[copycode.gif]: /images/20220525/04190d1c59ce444e99521e23aec9534a.png