Mysql集群：读写分离(mycat)

柔情只为你懂 2022-10-07 01:54 125阅读 0赞

### 文章目录 ###

*  1，mycat介绍: mycat1.6
 *  2，mycat部署: 代理整个数据库(读写分离)
 *   *  a，准备测试环境
     *  b，部署mycat
     *   *  配置mycat ( schema.xml , server.xml , rule.xml , wrapper.conf )
         *  启动mycat 服务
         *  客户端连接测试
 *  3，mycat 拆分表（shard）
 *   *  验证表被拆分

# 1，mycat介绍: mycat1.6 #

mycat1 ：[https://github.com/MyCATApache/Mycat-Server][https_github.com_MyCATApache_Mycat-Server]  
[https://github.com/MyCATApache/Mycat-doc][https_github.com_MyCATApache_Mycat-doc]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70]

# 2，mycat部署: 代理整个数据库(读写分离) #

*  环境准备： 先有mysql集群(主从复制），并且存在一个test.per这样的表 （正常操作是代码中识别并分开读写操作，以在主备上读写，现在使用mycat自动分离读写）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70 1]

mycat1.6 安装包下载：[http://dl.mycat.org.cn/1.6.7.6/20210303094759/][http_dl.mycat.org.cn_1.6.7.6_20210303094759]

## a，准备测试环境 ##

*  mysql主从配置参考： [https://blog.csdn.net/eyeofeagle/article/details/102841995][https_blog.csdn.net_eyeofeagle_article_details_102841995]

<table> 
 <thead> 
  <tr> 
   <th>主机</th> 
   <th>角色</th> 
   <th>数据库映射</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>mysql1</td> 
   <td>master (server_id=1)</td> 
   <td>mycat(test,db1, information_schema ) --&gt; mysql( test, db1, information_schema)</td> 
  </tr> 
  <tr> 
   <td>mysql2</td> 
   <td>slave (server_id=2)</td> 
   <td>与上同</td> 
  </tr> 
 </tbody> 
</table>

#mycat 映射 mysql真实的数据库名称 (二者可以相同，也可不同):
    # mycat( information_schema ) --> mysql( information_schema )
    # mycat( test) --> mysql( test )
    # mycat( db1 ) --> mysql( db1 )
    
    [root@c7-docker datax-web]# docker ps
    CONTAINER ID        IMAGE                             COMMAND                  CREATED             STATUS              PORTS                     NAMES
    3924c47d5738        daocloud.io/library/mysql:5.7.7   "/entrypoint.sh mysq…"   2 days ago          Up 20 minutes       0.0.0.0:32768->3306/tcp   mysql1
    5e85a27f9b35        daocloud.io/library/mysql:5.7.7   "/entrypoint.sh mysq…"   2 days ago          Up 20 minutes       0.0.0.0:32769->3306/tcp   mysql2
    
    ############### mysql1 (master) 
    [root@c7-docker datax-web]# docker exec -it mysql1 bash -c 'mysql -uroot -p123456 -e "select @@GLOBAL.server_id" '
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +--------------------+
    | @@GLOBAL.server_id |
    +--------------------+
    |                  1 |
    +--------------------+
    [root@c7-docker datax-web]# docker exec -it mysql1 bash -c 'mysql -uroot -p123456 -e "show master status"'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000005 |      681 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    [root@c7-docker datax-web]# docker exec -it mysql1 bash -c 'mysql -uroot -p123456 -e "select * from test.per" '
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    1 | a    |
    |    2 | b2   |
    +------+------+
    
    ############### mysql2 (slave) 
    [root@c7-docker datax-web]# docker exec -it mysql2 bash -c 'mysql -uroot -p123456 -e "select @@GLOBAL.server_id" '
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +--------------------+
    | @@GLOBAL.server_id |
    +--------------------+
    |                  2 |
    +--------------------+
    [root@c7-docker datax-web]# docker exec -it mysql2 bash -c 'mysql -uroot -p123456 -e "show slave status\G" '
    mysql: [Warning] Using a password on the command line interface can be insecure.
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: mysql1
                      Master_User: test
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000005
              Read_Master_Log_Pos: 681
                   Relay_Log_File: 5e85a27f9b35-relay-bin.000013
                    Relay_Log_Pos: 894
            Relay_Master_Log_File: mysql-bin.000005
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                      Master_UUID: d61f3a03-cd7b-11eb-a60c-0242ac160002
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
    ...
    [root@c7-docker datax-web]# docker exec -it mysql2 bash -c 'mysql -uroot -p123456 -e "select * from test.per" '
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    1 | a    |
    |    2 | b2   |
    +------+------+

## b，部署mycat ##

### 配置mycat ( schema.xml , server.xml , rule.xml , wrapper.conf ) ###

*  schema.xml 负载均衡类型，目前的取值有3种 (参考 [《mycat 权威指南》https://www.yuque.com/books/share/0576de75-ffc4-4c34-8586-952ae4636944][mycat _https_www.yuque.com_books_share_0576de75-ffc4-4c34-8586-952ae4636944] ）

<table> 
 <thead> 
  <tr> 
   <th>配置项</th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>balance=“0”</td> 
   <td>不开启读写分离机制，所有读操作都发送到当前可用的writeHost上</td> 
  </tr> 
  <tr> 
   <td>balance=“1”</td> 
   <td>全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1-&gt;S1，M2-&gt;S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。</td> 
  </tr> 
  <tr> 
   <td>balance=“2”</td> 
   <td>所有读操作都随机的在writeHost、readhost上分发</td> 
  </tr> 
  <tr> 
   <td>balance=“3”</td> 
   <td>所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力，注意balance=3只在1.4及其以后版本有，1.3没有</td> 
  </tr> 
 </tbody> 
</table>

[root@c7-docker ~]# cd /usr/local/mycat/
    [root@c7-docker mycat]# ls
    bin  catlet  conf  lib  logs  tmlogs  version.txt
    
    [root@c7-docker mycat]# cat conf/schema.xml
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
            <schema name="test" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
                    
                    
                    
                    
            </schema>
            <schema name="db1" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2"></schema>
            <schema name="information_schema" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn_schema"></schema>
    
            <dataNode name="dn1" dataHost="localhost1" database="test" />
            <dataNode name="dn2" dataHost="localhost1" database="db1" />
            <dataNode name="dn_schema" dataHost="localhost1" database="information_schema" />
    
            
            
    
            <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="localhost:32768" user="root" password="123456">
                            <readHost host="hostM2" url="localhost:32769" user="root" password="123456"/>
                    </writeHost>
    
                    
            </dataHost>
            
    
            
    </mycat:schema>
    
    
    [root@c7-docker mycat]# cat conf/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="nonePasswordLogin">0</property> 
            <property name="ignoreUnknownCommand">0</property>
            <property name="useHandshakeV10">1</property>
            <property name="removeGraveAccent">1</property>
            <property name="useSqlStat">0</property>  
            <property name="useGlobleTableCheck">0</property>  
            <property name="sqlExecuteTimeout">300</property>  
                    <property name="sequenceHandlerType">1</property>
            
            
            <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
            <property name="subqueryRelationshipCheck">false</property> 
            <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
           
             
            
            
            
                    <property name="processorBufferPoolType">0</property>
                    
                    
                    
                    
                    
                    
                    
                    
                    <property name="handleDistributedTransactions">0</property>
                    
                    <property name="useOffHeapForMerge">0</property>
                    
    			    <property name="memoryPageSize">64k</property>
                    
                    <property name="spillsFileBufferSize">1k</property>
                    <property name="useStreamOutput">0</property>
                    
                    <property name="systemReserveMemorySize">384m</property>
                    
                    <property name="useZKSwitch">false</property>
                    
                    
                    
                    
                    
                    <property name="strictTxIsolation">false</property>
                    
                    <property name="parallExecute">0</property>
                    <property name="serverBacklog">2048</property>
            </system>
    
            
            
            
            
            
            
            
            
    
            <user name="root" defaultAccount="true">
                    <property name="password">123456</property>
                     <property name="schemas">db1,test,information_schema</property>
                    <property name="defaultSchema">test</property>
                    
    
                    
                    
            </user>
    </mycat:server>

### 启动mycat 服务 ###

[root@c7-docker mycat]# ./bin/mycat start
    Starting Mycat-server...
    Mycat-server is already running.
    [root@c7-docker mycat]# tail logs/wrapper.log
    STATUS | wrapper  | 2021/06/17 09:09:29 | --> Wrapper Started as Daemon
    STATUS | wrapper  | 2021/06/17 09:09:29 | Launching a JVM...
    INFO   | jvm 1    | 2021/06/17 09:09:30 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
    INFO   | jvm 1    | 2021/06/17 09:09:30 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
    INFO   | jvm 1    | 2021/06/17 09:09:30 |
    INFO   | jvm 1    | 2021/06/17 09:09:30 | MyCAT Server startup successfully. see logs in logs/mycat.log
    [root@c7-docker mycat]# tail logs/mycat.log
    2021-06-17 09:45:00.621  INFO [$_NIOREACTOR-1-RW] (io.mycat.net.handler.FrontendAuthenticator.success(FrontendAuthenticator.java:226)) - Mycat does not support the allowMultiQueries value to be true, maybe occur some error.Client connection is[io.mycat.net.handler.FrontendAuthenticator@55f0cc29]
    2021-06-17 09:45:00.621  INFO [$_NIOREACTOR-1-RW] (io.mycat.net.handler.FrontendAuthenticator.success(FrontendAuthenticator.java:241)) - ServerConnection [id=8, schema=null, host=192.168.1.23, user=root,txIsolation=3, autocommit=true, schema=null, executeSql=null]'root' login success
    
    [root@c7-docker mycat]# ss -nltp |grep 8066
    LISTEN     0      128       [::]:8066                  [::]:*                   users:(("java",pid=31820,fd=93))
    [root@c7-docker mycat]# ps -ww 31820
      PID TTY      STAT   TIME COMMAND
    31820 ?        Sl     0:19 java -DMYCAT_HOME=. -server -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/annotations-13.0.jar:lib/asm-4.0.jar:lib/checker-qual-2.10.0.jar:lib/commons-cli-1.2.jar:lib/commons-collections-3.2.1.jar:lib/commons-csv-1.8.jar:lib/commons-lang-2.6.jar:lib/curator-client-4.0.1.jar:lib/curator-framework-4.0.1.jar:lib/curator-recipes-4.0.1.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/error_prone_annotations-2.3.4.jar:lib/failureaccess-1.0.1.jar:lib/fastjson-1.2.68.jar:lib/guava-28.2-jre.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/j2objc-annotations-1.3.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kotlin-stdlib-1.3.50.jar:lib/kotlin-stdlib-common-1.3.50.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.so:lib/listenablefuture-9999.0-empty-to-avoid-conflict-with-guava.jar:lib/log4j-1.2-api-2.13.0.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.13.0.jar:lib/log4j-core-2.13.0.jar:lib/log4j-slf4j-impl-2.13.0.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-3.11.0.jar:lib/Mycat-server-1.6.7.6-release.jar:lib/mysql-binlog-connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.10.5.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/okhttp-4.2.2.jar:lib/okio-2.2.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.8.4.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3.5.3-beta.jar -Dwrapper.key=l9pW9Jg49dJAVY1x -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=31817 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start

### 客户端连接测试 ###

[root@c7-docker lib]# mysql -uroot -p123456 -hlocalhost -P8066 test --protocol=tcp -e 'show databases'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +--------------------+
    | DATABASE           |
    +--------------------+
    | db1                |
    | information_schema |
    | test               |
    +--------------------+
    [root@c7-docker lib]# mysql -uroot -p123456 -hlocalhost -P8066 test --protocol=tcp -e 'select * from per'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    1 | a    |
    |    2 | b2   |
    +------+------+
    
    [root@c7-docker lib]# mysql -uroot -p123456 -hlocalhost -P8066 test --protocol=tcp -e 'delete from per where id=1; select * from per'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    2 | b2   |
    +------+------+
    [root@c7-docker lib]# mysql -uroot -p123456 -hlocalhost -P8066 test --protocol=tcp -e 'insert into per values(3,"c"); select * from per'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    2 | b2   |
    |    3 | c    |
    +------+------+
    
    
    ################### 查看mysql (master) 数据： 是否发生变化
    [root@c7-docker lib]# mysql -uroot -p123456 -hlocalhost -P32768 test --protocol=tcp -e ' select * from per'
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    2 | b2   |
    |    3 | c    |
    +------+------+

# 3，mycat 拆分表（shard） #

[root@c7-docker mycat]# cat conf/schema.xml
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
            <schema name="testdb" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
                    
                    
                    
                    <table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true">
                            
                    </table>
                    
            </schema>
            <dataNode name="dn1" dataHost="localhost1" database="db1" />
            <dataNode name="dn2" dataHost="localhost1" database="db2" />
    
            <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
                    <heartbeat>select user()</heartbeat>
                    
                    <writeHost host="hostM1" url="jdbc:mysql://localhost:32768" user="root" password="123456">
                    </writeHost>
                    
            </dataHost>       
    </mycat:schema>

## 验证表被拆分 ##

[root@c7-docker ~]# mysql -uroot -p123456 -hlocalhost -P8066 --protocol=tcp
    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 1
    Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    ##### 原mysql 数据库中： 如果配置到的两个数据库不存在则需要先创建好 (db1,db2 )
    mysql>CREATE database db1;
    mysql>CREATE database db2;
    
    mysql> show databases;
    +----------+
    | DATABASE |
    +----------+
    | testdb   |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> use testdb;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +------------------+
    | Tables in testdb |
    +------------------+
    | customer         |
    +------------------+
    1 rows in set (0.00 sec)
    
    mysql> create table customer(id int not null primary key,name varchar(100),company_id int not null,sharding_id int not null);
    Query OK, 0 rows affected (0.03 sec)
     OK!
    
    mysql>	       insert into customer (id,name,company_id,sharding_id )values(1,'wang',1,10000);  
    mysql> explain insert into customer (id,name,company_id,sharding_id )values(1,'wang',1,10000);  //stored in db1;
    
    mysql>         insert into customer (id,name,company_id,sharding_id )values(2,'xue',2,10010);  
    mysql> explain insert into customer (id,name,company_id,sharding_id )values(2,'xue',2,10010);  //stored in db2;
    
    mysql> select * from customer;
    +------+------+------------+-------------+
    | ID   | NAME | COMPANY_ID | SHARDING_ID |
    +------+------+------------+-------------+
    |    1 | wang |          1 |       10000 |
    |    2 | xue  |          2 |       10010 |
    +------+------+------------+-------------+

[https_github.com_MyCATApache_Mycat-Server]: https://github.com/MyCATApache/Mycat-Server
[https_github.com_MyCATApache_Mycat-doc]: https://github.com/MyCATApache/Mycat-doc
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70]: /images/20221005/206c2272865e4995a9903687a55ece23.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70 1]: /images/20221005/397cb268b1e14929b0229fa805fab3a6.png
[http_dl.mycat.org.cn_1.6.7.6_20210303094759]: http://dl.mycat.org.cn/1.6.7.6/20210303094759/
[https_blog.csdn.net_eyeofeagle_article_details_102841995]: https://blog.csdn.net/eyeofeagle/article/details/102841995
[mycat _https_www.yuque.com_books_share_0576de75-ffc4-4c34-8586-952ae4636944]: https://www.yuque.com/books/share/0576de75-ffc4-4c34-8586-952ae4636944/gmbnwu