Mycat ~ 从入门到入坑。

本文链接：https://blog.csdn.net/lyfGeek/article/details/104934502

Mycat ~ 从入门到入坑。

文章目录

- Mycat ~ 从入门到入坑。

what。

Mycat 是一个数据库中间件 middleware。

中间件是介于应用系统和系统软件之间的一类软件，它使用系统软件所提供的基础服务（功能），衔接网络上应用系统的各个部分或不同的应用，能够达到资源共享、功能共享的目的。目前，它并没有很严格的定义，但是普遍接受 IDC 的定义：中间件是一种独立的系统软件服务程序， 分布式应用软件借助这种软件在不同的技术之间共享资源，中间件位于客户机服务器的操作系统之上，管理计算资源和网络通信。从这个意义上可以用一个等式来表示中间件：中间件 = 平台 - 通信，这也就限定了只有用于分布式系统中才能叫中间件，同时也把它与支撑软件和实用软件区分开来。
百科。
eg. Tomcat ~ Web 中间件。
数据库中间件：连接 Java 应用程序和数据库。

why。

Java 与数据库紧耦合。——> 中间件。
高访问量高并发对数据库的压力。——> 高可用。
读写请求量不同。——> 读写分离。
主从复制。

在这里插入图片描述

数据库中间件软件。（天上飞的理念，必然有地上落地的实现）。

Cobar
2008 年阿里团队开发，已多年无维护更新（发起人离职）。
Mycat
基于 Cobar 二次开发，开源社区维护。青出于蓝。
OneProxy
不开源的商业中间件。基于 MySQL 官方的 proxy 思想 C 语言开发。性能和稳定。
kingshard
go 语言开发，在不断完善。
Vitess
YouTube 生产在使用，不支持 MySQL 原生协议，架构很复杂，使用需要大量改造。
Atlas
360 团队基于 mysqlproxy 编写，高并发不稳定。
MaxScale
MaxScale 是 MariaDB（MySQL 原作者维护的一个版本）研发的中间件。
MySQLRoute
MySQLRoute 是 MySQL 官方 Oracle 公司发布的中间件。

Mycat 官网。http://www.Mycat.io/

应用。

读写分离。

在这里插入图片描述

数据分片。

数据库分布式。

单张表数据达到千万级 ——> 慢。

在这里插入图片描述

Mycat 多数据源整合。

在这里插入图片描述

Mycat 原理。

拦截。

拦截用户的 SQL 语句，对 SQL 做特定分析：eg. 分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果作适当处理，最终再返回给用户。

在这里插入图片描述

安装、使用。

Linux 安装方式。

rmp。（CentOS）。
YUM。（CentOS）。
解压后即可使用。
解压后编译安装。

下载地址：
http://dl.Mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

安装。

geek@ubuntu:~/Downloads$ scp Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz root@192.168.142.143:/opt
root@192.168.142.143's password: 
Mycat-server-1.6-RELEASE-20161028204710-linux 100%   15MB  95.4MB/s   00:00

[root@localhost tools_my]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@localhost tools_my]# cp -r Mycat/ /usr/local/

[root@localhost tools_my]# cd Mycat/
[root@localhost Mycat]# ls
bin  catlet  conf  lib  logs  version.txt

一堆配置文件。

[root@localhost conf]# ls
autopartition-long.txt       rule.xml
auto-sharding-long.txt       schema.xml
auto-sharding-rang-mod.txt   sequence_conf.properties
cacheservice.properties      sequence_db_conf.properties
ehcache.xml                  sequence_distributed_conf.properties
index_to_charset.properties  sequence_time_conf.properties
log4j2.xml                   server.xml
migrateTables.properties     sharding-by-enum.txt
myid.properties              wrapper.conf
partition-hash-int.txt       zkconf
partition-range-mod.txt      zkdownload

3 个重要配置文件。

schema.xml
定义逻辑库，表，分片节点等内容。
rule.xml
定义分片规则。
server.xml
定义用户以及系统相关变量，如端口等。

启动。

先修改配置文件。

server.xml。

在这里插入图片描述

[root@localhost conf]# cp server.xml sever.xml.bak
[root@localhost conf]# vim server.xml

user 的 name 改为 Mycat。

schemas 是什么。

——> 逻辑库。（schemas ——> TESTDB）。

在这里插入图片描述

schema.xml

[root@localhost conf]# cp schema.xml schema.xml.bak
[root@localhost conf]# vim schema.xml

在这里插入图片描述
都是测试表，我们没有，也不需要。删除！

<schema></schema>中的全部删除。——> d27d。

配置节点 dn1，并删除 dn2 dn3。

  1 <?xml version="1.0"?>
  2 <!DOCTYPE Mycat:schema SYSTEM "schema.dtd">
  3 <Mycat:schema xmlns:Mycat="http://io.Mycat/">
  4 
  5         <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
  6         </schema>
  7         <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  8                 /> -->
  9         <dataNode name="dn1" dataHost="localhost1" database="db1" />
 10         <dataNode name="dn2" dataHost="localhost1" database="db2" />
 11         <dataNode name="dn3" dataHost="localhost1" database="db3" />
 12         <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
 13          <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
 14         <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />
 15         <dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->

配置 dataHost。

心跳检测。

通过 select user() 语句检测连接是否还存在。

用来写数据的主机中包含多个用来读数据的主机。
。。。
现在只测试一个“写“主机。删除第二个（<writeHost host="hostS1"…）。
为了精简，删除后面全部多余的注释。

 17         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
 18                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
 19                 <heartbeat>select user()</heartbeat>
 20                 <!-- can have multi write hosts -->
 21                 <writeHost host="hostM1" url="localhost:3306" user="root"
 22                                    password="123456">
 23                         <!-- can have multi read hosts -->
 24                         <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
 25                 </writeHost>
 26                 <writeHost host="hostS1" url="localhost:3316" user="root"
 27                                    password="123456" />
 28                 <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
 29         </dataHost>

最终配置文件。

  1 <?xml version="1.0"?>
  2 <!DOCTYPE Mycat:schema SYSTEM "schema.dtd">
  3 <Mycat:schema xmlns:Mycat="http://io.Mycat/">
  4 
  5         <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
  6 
  7         </schema>
  8         <dataNode name="dn1" dataHost="host1" database="testdb" />
  9         <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
 10                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
 11                 <heartbeat>select user()</heartbeat>
 12                 <!-- can have multi write hosts -->
 13                 <writeHost host="hostM1" url="192.168.142.162:3306" user="root"
 14                                    password="xxx.">
 15                         <!-- can have multi read hosts -->
 16                         <readHost host="hostS1" url="192.168.142.163:3307" user="root" password="xxx." />
 17                 </writeHost>
 18         </dataHost>
 19 </Mycat:schema>

本次测试因为使用 Docker 中的 MySQL。
所以在同一台虚拟机中创建了两个 MySQL 镜像。端口分别为 3306 和 3307。请根据自己的实际情况配置。

CREATE SCHEMA `testdb` DEFAULT CHARACTER SET utf8 ;

测试数据库的连接。

在这里插入图片描述
这几个目录下的程序执行不需要加绝对路径。

[root@localhost conf]# echo $PATH
/root/geek/jdk1.8.0_241/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

启动方式。

控制台启动。

./Mycat console

[geek@192 bin]$ ./Mycat 
Usage: ./Mycat { console | start | stop | restart | status | dump }
[geek@192 bin]$ ./Mycat console

后台启动。

./Mycat start

推荐控制台启动。实时显示日志。

可以发现 Mycat 是 Java 语言编写。

[geek@192 bin]$ ./Mycat console
Running Mycat-server...
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
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    | 2020-05-06 21:21:27,881 [INFO ][WrapperSimpleAppMain] total resouces of dataHost host1 is :2  (io.Mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:100) 
jvm 1    | 2020-05-06 21:21:27,890 [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    | 2020-05-06 21:21:27,891 [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    | 2020-05-06 21:21:28,035 [INFO ][WrapperSimpleAppMain] dyna class load from ./catlet,and auto check for class file modified every 60 seconds  (io.Mycat.config.classloader.DynaClassLoader:DynaClassLoader.java:34) 
jvm 1    | 2020-05-06 21:21:28,036 [INFO ][WrapperSimpleAppMain] ===============================================  (io.Mycat.MycatServer:MycatServer.java:266) 
jvm 1    | 2020-05-06 21:21:28,036 [INFO ][WrapperSimpleAppMain] Mycat is ready to startup ...  (io.Mycat.MycatServer:MycatServer.java:267) 
jvm 1    | 2020-05-06 21:21:28,036 [INFO ][WrapperSimpleAppMain] Startup processors ...,total processors:1,aio thread pool size:4    
jvm 1    |  each process allocated socket buffer pool  bytes ,a page size:2097152  a page's chunk number(PageSize/ChunkSize) is:512  buffer page's number is:20  (io.Mycat.MycatServer:MycatServer.java:279) 
jvm 1    | 2020-05-06 21:21:28,036 [INFO ][WrapperSimpleAppMain] sysconfig params:SystemConfig [processorBufferLocalPercent=100, frontSocketSoRcvbuf=1048576, frontSocketSoSndbuf=4194304, backSocketSoRcvbuf=4194304, backSocketSoSndbuf=1048576, frontSocketNoDelay=1, backSocketNoDelay=1, maxStringLiteralLength=65535, frontWriteQueueSize=2048, bindIp=0.0.0.0, serverPort=8066, managerPort=9066, charset=utf8, processors=1, processorExecutor=4, timerExecutor=2, managerExecutor=2, idleTimeout=1800000, catletClassCheckSeconds=60, sqlExecuteTimeout=300, processorCheckPeriod=1000, dataNodeIdleCheckPeriod=300000, dataNodeHeartbeatPeriod=10000, clusterHeartbeatUser=_HEARTBEAT_USER_, clusterHeartbeatPass=_HEARTBEAT_PASS_, clusterHeartbeatPeriod=5000, clusterHeartbeatTimeout=10000, clusterHeartbeatRetry=10, txIsolation=3, parserCommentVersion=50148, sqlRecordCount=10, bufferPoolPageSize=2097152, bufferPoolChunkSize=4096, bufferPoolPageNumber=20, maxResultSet=524288, bigResultSizeSqlCount=10, bufferUsagePercent=80, flowControlRejectStrategy=0, clearBigSqLResultSetMapMs=600000, defaultMaxLimit=100, sequnceHandlerType=2, sqlInterceptor=io.Mycat.server.interceptor.impl.DefaultSqlInterceptor, sqlInterceptorType=select, sqlInterceptorFile=/usr/local/Mycat/logs/sql.txt, mutiNodeLimitType=0, mutiNodePatchSize=100, defaultSqlParser=druidparser, usingAIO=0, packetHeaderSize=4, maxPacketSize=16777216, MycatNodeId=1]  (io.Mycat.MycatServer:MycatServer.java:280) 
jvm 1    | log4j:WARN No appenders could be found for logger (io.Mycat.memory.MycatMemory).
jvm 1    | log4j:WARN Please initialize the log4j system properly.
jvm 1    | log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
jvm 1    | 2020-05-06 21:21:28,172 [INFO ][WrapperSimpleAppMain] using nio network handler   (io.Mycat.MycatServer:MycatServer.java:381) 
jvm 1    | 2020-05-06 21:21:28,189 [INFO ][WrapperSimpleAppMain] $_MycatManager is started and listening on 9066  (io.Mycat.MycatServer:MycatServer.java:397) 
jvm 1    | 2020-05-06 21:21:28,191 [INFO ][WrapperSimpleAppMain] $_MycatServer is started and listening on 8066  (io.Mycat.MycatServer:MycatServer.java:401) 
jvm 1    | 2020-05-06 21:21:28,191 [INFO ][WrapperSimpleAppMain] ===============================================  (io.Mycat.MycatServer:MycatServer.java:403) 
jvm 1    | 2020-05-06 21:21:28,191 [INFO ][WrapperSimpleAppMain] Initialize dataHost ...  (io.Mycat.MycatServer:MycatServer.java:407) 
jvm 1    | 2020-05-06 21:21:28,191 [INFO ][WrapperSimpleAppMain] init backend myqsl source ,create connections total 10 for hostM1 index :0  (io.Mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:294) 
jvm 1    | 2020-05-06 21:21:28,192 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,194 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,195 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,195 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,196 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,196 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,196 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,196 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,196 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,196 [INFO ][WrapperSimpleAppMain] no ilde connection in pool,create new connection for hostM1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | 2020-05-06 21:21:28,249 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=1, lastTime=1588771288249, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=89, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,251 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=4, lastTime=1588771288251, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=93, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,252 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=6, lastTime=1588771288251, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=95, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,252 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=3, lastTime=1588771288252, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=94, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,253 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=5, lastTime=1588771288253, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=90, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,253 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=2, lastTime=1588771288253, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=91, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,254 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=10, lastTime=1588771288254, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=92, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,254 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=9, lastTime=1588771288254, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=98, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,254 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=7, lastTime=1588771288254, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=96, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,263 [INFO ][$_NIOREACTOR-0-RW] connected successfuly MySQLConnection [id=8, lastTime=1588771288263, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=false, threadId=97, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.GetConnectionHandler:GetConnectionHandler.java:67) 
jvm 1    | 2020-05-06 21:21:28,310 [INFO ][WrapperSimpleAppMain] init result :finished 10 success 10 target count:10  (io.Mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:319) 
jvm 1    | 2020-05-06 21:21:28,310 [INFO ][WrapperSimpleAppMain] host1 index:0 init success  (io.Mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:265) 
jvm 1    | 2020-05-06 21:21:28,310 [INFO ][WrapperSimpleAppMain] save DataHost index  host1 cur index 0  (io.Mycat.MycatServer:MycatServer.java:604) 
jvm 1    | 2020-05-06 21:21:28,332 [INFO ][Timer0] create connections ,because idle connection not enough ,cur is 0, minCon is 10 for hostS1  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:299) 
jvm 1    | 2020-05-06 21:21:28,348 [INFO ][Timer0] no ilde connection in pool,create new connection for hostS1 of schema testdb  (io.Mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413) 
jvm 1    | Mycat Server startup successfully. see logs in logs/Mycat.log
jvm 1    | 2020-05-06 21:21:28,369 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=11, lastTime=1588771288357, user=root, schema=testdb, old shema=testdb, borrowed=false, fromSlaveDB=true, threadId=1599, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45) 
jvm 1    | 2020-05-06 21:21:28,369 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=12, lastTime=1588771288357, user=root, schema=testdb, old shema=testdb, borrowed=false, fromSlaveDB=true, threadId=1597, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45) 
jvm 1    | 2020-05-06 21:21:28,377 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=13, lastTime=1588771288376, user=root, schema=testdb, old shema=testdb, borrowed=false, fromSlaveDB=true, threadId=1600, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.142.143, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]  (io.Mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)

登录。

登录后台管理窗口 ~ 9066。

mysql -uMycat -p123456 -P 9066 -h 192.168.223.128

[geek@192 ~]$ mysql -uMycat -p123456 -P 9066 -h 192.168.142.143
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-RELEASE-20161028204710 Mycat Server (monitor)

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

登录数据窗口 ~ 8066。

[geek@192 ~]$ mysql -uMycat -p123456 -P 8066 -h 192.168.142.162
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 2
Server version: 5.6.29-Mycat-1.6-RELEASE-20161028204710 Mycat Server (OpenCloundDB)

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

mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

Mycat 读写分离。

一主一从 & 双主双从。

一主一从。

Redis 主从复制：第一次全量复制 rdb 文件，之后增量复制。

MySQL 主从复制：从当前状态复制。

主：192.168.142.162

从：192.168.142.163

主：192.168.142.162。

[geek@192 ~]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 主服务器 id。唯一。
server-id=1

# 启用二进制日志。
log-bin=mysql-bin

# 设置不要复制的数据库。（可设置多个）。
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

# 设置需要复制的数据库。
binlog-do-db=testdb
# 设置 binlog 格式。
binlog_format=STATEMENT

从：192.168.142.163。

[geek@localhost ~]$ sudo vim /etc/my.cnf
[geek@localhost ~]$ cat -n /etc/my.cnf
     1	# For advice on how to change settings please see
     2	# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
     3	
     4	[mysqld]
     5	#
     6	# Remove leading # and set to the amount of RAM for the most important data
     7	# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
     8	# innodb_buffer_pool_size = 128M
     9	#
    10	# Remove leading # to turn on a very important data integrity option: logging
    11	# changes to the binary log between backups.
    12	# log_bin
    13	#
    14	# Remove leading # to set options mainly useful for reporting servers.
    15	# The server defaults are faster for transactions and fast SELECTs.
    16	# Adjust sizes as needed, experiment to find the optimal values.
    17	# join_buffer_size = 128M
    18	# sort_buffer_size = 2M
    19	# read_rnd_buffer_size = 2M
    20	datadir=/var/lib/mysql
    21	socket=/var/lib/mysql/mysql.sock
    22	
    23	# Disabling symbolic-links is recommended to prevent assorted security risks
    24	symbolic-links=0
    25	
    26	log-error=/var/log/mysqld.log
    27	pid-file=/var/run/mysqld/mysqld.pid
    28	
    29	# 主服务器 id。唯一。
    30	server-id=2
    31	
    32	# 启用中继日志。
    33	relay-log=mysql-relay

配置好后，重启 MySQL。

[geek@192 ~]$ systemctl restart mysqld

在主机上建立账户并授权 slave。

mysql> GRANT REPLICATION SLAVE ON . TO ‘slave’@‘%’ IDENTIFIED BY ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      438 | testdb       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

从机登录 MySQL。

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.142.162',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='123.',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=438;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

如果以前搭过主从，先重置。

stop slave;
reset master;

启动从服务器复制功能。

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

确保 ta 们为 Yes。

此时为 no 是因为我演示的虚拟机是 clone 出来的，MySQL 的 uuid 一样。可以查看日志找错误原因。

修改 uuid 并重启 MySQL。

[geek@localhost mysql]$ sudo mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
[geek@localhost mysql]$ sudo systemctl restart mysqld

             Slave_IO_Running: No
            Slave_SQL_Running: Yes

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.142.162
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 438
               Relay_Log_File: mysql-relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 438
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 
             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
                  Master_Bind: 
      Last_IO_Error_Timestamp: 200626 14:13:23
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show slave status\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.142.162
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 438
               Relay_Log_File: mysql-relay.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 438
              Relay_Log_Space: 523
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: aae18f2c-7764-11ea-a862-000c29a7c564
             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
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

一主一从测试。

主。

mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)

mysql> use testdb;
Database changed
mysql> create table mytbl(id int, name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mytbl values (1, 'geek');
Query OK, 1 row affected (0.00 sec)

从。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo_01            |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| testdb             |
+--------------------+
7 rows 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> select * from mytbl;
+------+------+
| id   | name |
+------+------+
|    1 | geek |
+------+------+
1 row in set (0.00 sec)

mysql>

主从读写分离。

[geek@192 ~]$ mysql -uMycat -p123. -h 192.168.142.162 -P8066

[geek@192 ~]$ mysql -uMycat -p123456 -h 192.168.142.162 -P8066
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 2
Server version: 5.6.29-Mycat-1.6-RELEASE-20161028204710 Mycat Server (OpenCloundDB)

Copyright (c) 2000, 2020, 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> 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> select * from mytbl;
+------+------+
| id   | name |
+------+------+
|    1 | geek |
+------+------+
1 row in set (0.01 sec)

mysql>

之前已经配置了读写主机，是否已经实现了读写分离？

mysql> insert into mytbl values (1, @@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from mytbl;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | geek            |
|    1 | 192.168.142.162 |
+------+-----------------+
2 rows in set (0.00 sec)

00:17:11 insert into mytbl values (1, @@hostname) 1 row(s) affected, 1 warning(s): 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. 0.000 sec

修改 schema.xml。

<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

balance。

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”。
所有读请求都随机分发到 readHost 执行，writeHost 不负担读压力。

将 balance 改为 2 的效果。

mysql> select * from mytbl;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | geek            |
|    1 | 192.168.142.162 |
|    1 | 192.168.142.162 |
|    1 | 192.168.142.162 |
|    1 | 192.168.142.162 |
+------+-----------------+
5 rows in set (0.00 sec)

mysql> select * from mytbl;
+------+------+
| id   | name |
+------+------+
|    1 | geek |
+------+------+
1 row in set (0.00 sec)

双主双从。

首先

stop slave;
reset master;

角色	IP
Master1	192.168.142.162
Slave1	192.168.142.163
Master2	192.168.142.165
Slave2	192.168.142.166

Master1 | 192.168.142.162。

[geek@localhost ~]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8
lower_case_table_names=1

# 主服务器 id。唯一。
server-id=1

# 启用二进制日志。
log-bin=mysql-bin

# 不要复制的数据库。
binlog-ignore-db=mysql

# 需要复制的数据库。
binlog-do-db=testdb

# binlog 格式。
binlog-format=STATEMENT

# 在作为从数据库时，有写入操作也要更新二进制日志文件。
log-slave-updates

# 自增长字段每次递增的量，默认是1，1 ~ 65535。
auto-increment-increment=2

# 自增长字段从哪个数开始，1 ~ 65535。
auto-increment-offset=1

[geek@localhost ~]$

Master2 | 192.168.142.165。

[geek@localhost ~]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


character_set_server=utf8
lower_case_table_names=1

# 主服务器 id。唯一。
server-id=3

# 启用二进制日志。
log-bin=mysql-bin

# 不要复制的数据库。
binlog-ignore-db=mysql

# 需要复制的数据库。
binlog-do-db=testdb

# binlog 格式。
binlog-format=STATEMENT

# 在作为从数据库时，有写入操作也要更新二进制日志文件。
log-slave-updates

# 自增长字段每次递增的量，默认是1，1 ~ 65535。
auto-increment-increment=2

# 自增长字段从哪个数开始，1 ~ 65535。
auto-increment-offset=2

[geek@localhost ~]$

Slave1 | 192.168.142.163。

[geek@localhost ~]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8
lower_case_table_names=1

# 主服务器 id。唯一。
server-id=2

# 启用中继日志。
relay-log=mysql-relay

[geek@localhost ~]$

Slave2 | 192.168.142.166。

[geek@192 ~]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8
lower_case_table_names=1

# 主服务器 id。唯一。
server-id=4

# 启用中继日志。
relay-log=mysql-relay

[geek@192 ~]$

重启服务。

sudo systemctl restart mysqld

两个主机，

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> show variables like "%validate%";
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| query_cache_wlock_invalidate         | OFF    |
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 4      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
8 rows in set (0.00 sec)

mysql> set global validate_password_policy = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select host, user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| %         | slave         |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
5 rows in set (0.00 sec)

Master 1。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1038 | testdb       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave 1。

CHANGE MASTER TO MASTER_HOST = '192.168.142.162',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1038;

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.142.162',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

Master 2。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 | testdb       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

Slave 2。

CHANGE MASTER TO MASTER_HOST = '192.168.142.165',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=461;

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.142.165',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=461;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

两个主机互相复制。

Master 1 复制 Master 2。

CHANGE MASTER TO MASTER_HOST = '192.168.142.165',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

Master 1 复制 Master 2。

CHANGE MASTER TO MASTER_HOST='192.168.142.165',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

在这里插入图片描述

双主双从读写分离。

vim schema.xml。

balance=“1”

<?xml version="1.0"?>
<!DOCTYPE Mycat:schema SYSTEM "schema.dtd">
<Mycat:schema xmlns:Mycat="http://io.Mycat/">

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

        </schema>
        <dataNode name="dn1" dataHost="host1" database="testdb" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          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.142.162:3306" user="root"
                                   password="sQm581257.">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.142.163:3306" user="root" password="sQm581257." />
                </writeHost>
        </dataHost>
</Mycat:schema>

添加另一主从。

[geek@localhost bin]$ 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="false" sqlMaxLimit="100" dataNode="dn1">

	</schema>
	<dataNode name="dn1" dataHost="host1" database="testdb" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
			  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.142.162:3306" user="root"
				   password="sQm581257.">
			<!-- can have multi read hosts -->
			<readHost host="hostS1" url="192.168.142.163:3306" user="root" password="sQm581257." />
		</writeHost>
		<!-- can have multi write hosts -->
		<writeHost host="hostM2" url="192.168.142.165:3306" user="root"
				   password="sQm581257.">
			<!-- can have multi read hosts -->
			<readHost host="hostS2" url="192.168.142.166:3306" user="root" password="sQm581257." />
		</writeHost>
	</dataHost>
</Mycat:schema>

balance=“1”。
全部 readHost 与 stand by writeHost 参与 select 语句的负载均衡。
writeType=“0”。
所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个。
writeType=“1”。
所有写操作都随机发送到配置的 writeHost，1.5 以后废弃不推荐。
switchType=“1”。
1。默认值。自动切换。
-1。表示不自动切换。
2。基于 MySQL 主从同步的状态决定是否切换。

writeHost。
重新启动后以切换后的为准。切换记录在配置文件中：dnindex.properties。

启动。

[geek@localhost bin]$ ./Mycat console

[geek@localhost ~]$ mysql -uMycat -p123456 -P 8066 -h 192.168.142.162
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-RELEASE-20161028204710 Mycat Server (OpenCloundDB)

Copyright (c) 2000, 2020, 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> use testdb;
ERROR 1049 (HY000): Unknown database 'testdb'
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> select * from mytbl;
+------+-----------------------+
| id   | name                  |
+------+-----------------------+
|    1 | geek                  |
|    2 | localhost.localdomain |
+------+-----------------------+
2 rows in set (0.01 sec)

mysql> select * from mytbl;
+------+-----------------------+
| id   | name                  |
+------+-----------------------+
|    1 | geek                  |
|    2 | localhost.localdomain |
+------+-----------------------+
2 rows in set (0.00 sec)

mysql> select * from mytbl;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | geek            |
|    2 | 192.168.142.166 |
+------+-----------------+
2 rows in set (0.00 sec)

M1 挂掉。（Mycat 实现 MySQL 的高可用）。

M1 为写主机，S1 M2 S2 为读主机。

M1 挂掉再重连，M2 变为写主机，S1 M1 S2 变为读主机。

垂直拆分 ~ 分库。

[geek@localhost bin]$ 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="false" sqlMaxLimit="100" dataNode="dn1">
		<table name="customer" dataNode="dn2"></table>
	</schema>
	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
			  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.142.162:3306" user="root"
				   password="sQm581257.">
		</writeHost>
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM2" url="192.168.142.163:3306" user="root"
				   password="sQm581257.">
		</writeHost>
	</dataHost>
</Mycat:schema>

在 dn1 和 dn2 上增加数据库 orders。

create database orders;

再启动 Mycat。

./Mycat

连接 Mycat。

geek@ubuntu:~$ ssh 192.168.142.162
geek@192.168.142.162's password: 
Last login: Sat Jun 27 13:08:54 2020 from 192.168.142.154
[geek@localhost ~]$ mysql -uMycat -p123456 -h192.168.142.162 -P8066
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-RELEASE-20161028204710 Mycat Server (OpenCloundDB)

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

mysql> use TESTDB;
Database changed
mysql> CREATE TABLE `customer` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `name` VARCHAR(200) NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.01 sec)

客户表。

dn 1 是空的。

mysql> use orders;
Database changed
mysql> show tables;
Empty set (0.00 sec)

在 dn2 中查询表。

mysql> use orders;
Database changed

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
+------------------+
1 row in set (0.00 sec)

Mycat 中继续建表。

订单表。

CREATE TABLE `orders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `order_type` INT NULL,
  `customer_id` INT NULL,
  `amount` DECIMAL(10,2) NULL,
  PRIMARY KEY (`id`));

订单详细表。

CREATE TABLE `orders_detail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `detail` VARCHAR(2000),
  `order_id` INT,
  PRIMARY KEY (`id`));

订单字典表。

CREATE TABLE `dict_order_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `order_type` VARCHAR(200),
  PRIMARY KEY (`id`));

dn1。

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| dict_order_type  |
| orders           |
| orders_detail    |
+------------------+
3 rows in set (0.00 sec)

因为。

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
		<table name="customer" dataNode="dn2"></table>
	</schema>

水平拆分 ~ 分表。

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

在这里插入图片描述

配置分表。

MySQL 单表打到 1000 万条数据就达到了瓶颈，会影响查询效率。需要进行水平拆分（分表）进行优化。

以 order 表为例，可以根据不同的字段分表。

以 id（主键 or 创建时间）。
查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。
以 customer_id 划分。
根据客户 id 去分，两个节点访问平均，一个客户的所有订单都在同一节点。

修改 schema.xml。

为 orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod-rule（自定义名字）。

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2"></table>
                <table name="orders" dataNode="dn1, dn2" rule="mod_rule"></table>
        </schema>

rule.schema。

<!DOCTYPE Mycat:rule SYSTEM "rule.dtd">
<Mycat:rule xmlns:Mycat="http://io.Mycat/">
        <tableRule name="mod_rule">
                <rule>
                        <columns>customer_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>

修改节点数。

        <function name="mod-long" class="io.Mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <!-- <property name="count">3</property>-->
                <property name="count">2</property>
        </function>

完整。

[geek@localhost ~]$ cat /usr/local/Mycat/conf/rule.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:rule SYSTEM "rule.dtd">
<Mycat:rule xmlns:Mycat="http://io.Mycat/">
        <tableRule name="mod_rule">
                <rule>
                        <columns>customer_id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>

	<tableRule name="rule1">
		<rule>
			<columns>id</columns>
			<algorithm>func1</algorithm>
		</rule>
	</tableRule>

	<tableRule name="rule2">
		<rule>
			<columns>user_id</columns>
			<algorithm>func1</algorithm>
		</rule>
	</tableRule>

	<tableRule name="sharding-by-intfile">
		<rule>
			<columns>sharding_id</columns>
			<algorithm>hash-int</algorithm>
		</rule>
	</tableRule>
	<tableRule name="auto-sharding-long">
		<rule>
			<columns>id</columns>
			<algorithm>rang-long</algorithm>
		</rule>
	</tableRule>
	<tableRule name="mod-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>
	<tableRule name="sharding-by-murmur">
		<rule>
			<columns>id</columns>
			<algorithm>murmur</algorithm>
		</rule>
	</tableRule>
	<tableRule name="crc32slot">
		<rule>
			<columns>id</columns>
			<algorithm>crc32slot</algorithm>
		</rule>
	</tableRule>
	<tableRule name="sharding-by-month">
		<rule>
			<columns>create_time</columns>
			<algorithm>partbymonth</algorithm>
		</rule>
	</tableRule>
	<tableRule name="latest-month-calldate">
		<rule>
			<columns>calldate</columns>
			<algorithm>latestMonth</algorithm>
		</rule>
	</tableRule>
	
	<tableRule name="auto-sharding-rang-mod">
		<rule>
			<columns>id</columns>
			<algorithm>rang-mod</algorithm>
		</rule>
	</tableRule>
	
	<tableRule name="jch">
		<rule>
			<columns>id</columns>
			<algorithm>jump-consistent-hash</algorithm>
		</rule>
	</tableRule>

	<function name="murmur"
		class="io.Mycat.route.function.PartitionByMurmurHash">
		<property name="seed">0</property><!-- 默认是0 -->
		<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
		<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
		<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
		<!-- <property name="bucketMapPath">/etc/Mycat/bucketMapPath</property> 
			用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
	</function>

	<function name="crc32slot"
			  class="io.Mycat.route.function.PartitionByCRC32PreSlot">
		<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
	</function>
	<function name="hash-int"
		class="io.Mycat.route.function.PartitionByFileMap">
		<property name="mapFile">partition-hash-int.txt</property>
	</function>
	<function name="rang-long"
		class="io.Mycat.route.function.AutoPartitionByLong">
		<property name="mapFile">autopartition-long.txt</property>
	</function>
	<function name="mod-long" class="io.Mycat.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<!-- <property name="count">3</property>-->
		<property name="count">2</property>
	</function>

	<function name="func1" class="io.Mycat.route.function.PartitionByLong">
		<property name="partitionCount">8</property>
		<property name="partitionLength">128</property>
	</function>
	<function name="latestMonth"
		class="io.Mycat.route.function.LatestMonthPartion">
		<property name="splitOneDay">24</property>
	</function>
	<function name="partbymonth"
		class="io.Mycat.route.function.PartitionByMonth">
		<property name="dateFormat">yyyy-MM-dd</property>
		<property name="sBeginDate">2015-01-01</property>
	</function>
	
	<function name="rang-mod" class="io.Mycat.route.function.PartitionByRangeMod">
        	<property name="mapFile">partition-range-mod.txt</property>
	</function>
	
	<function name="jump-consistent-hash" class="io.Mycat.route.function.PartitionByJumpConsistentHash">
		<property name="totalBuckets">3</property>
	</function>
</Mycat:rule>
[geek@localhost ~]$

在 dn2 中创建订单表。

CREATE TABLE `orders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `order_type` INT NULL,
  `customer_id` INT NULL,
  `amount` DECIMAL(10,2) NULL,
  PRIMARY KEY (`id`));

启动 Mycat。

[geek@localhost ~]$ /usr/local/Mycat/bin/Mycat console

使用 Mycat 插入数据。

geek@ubuntu:~$ ssh 192.168.142.162
geek@192.168.142.162's password: 
Last login: Sat Jun 27 14:13:50 2020 from 192.168.142.154
[geek@localhost ~]$ mysql -uMycat -p123456 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'Mycat'@'localhost' (using password: YES)
[geek@localhost ~]$ mysql -uMycat -p123456 -h192.168.142.162 -P8066
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-RELEASE-20161028204710 Mycat Server (OpenCloundDB)

Copyright (c) 2000, 2020, 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> use testdb;
ERROR 1049 (HY000): Unknown database 'testdb'
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> 

INSERT INTO orders(id, order_type, customer_id, amount) VALUES (1, 101, 100, 100100);
INSERT INTO orders(id, order_type, customer_id, amount) VALUES (2, 101, 100, 100300);
INSERT INTO orders(id, order_type, customer_id, amount) VALUES (3, 101, 101, 120000);
INSERT INTO orders(id, order_type, customer_id, amount) VALUES (4, 101, 101, 103000);
INSERT INTO orders(id, order_type, customer_id, amount) VALUES (5, 102, 101, 100400);
INSERT INTO orders(id, order_type, customer_id, amount) VALUES (6, 102, 100, 100020);

dn1。

mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  1 |        101 |         100 | 100100.00 |
|  2 |        101 |         100 | 100300.00 |
|  6 |        102 |         100 | 100020.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

dn2。

mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  3 |        101 |         101 | 120000.00 |
|  4 |        101 |         101 | 103000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

Mycat 中。

mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  1 |        101 |         100 | 100100.00 |
|  2 |        101 |         100 | 100300.00 |
|  6 |        102 |         100 | 100020.00 |
|  3 |        101 |         101 | 120000.00 |
|  4 |        101 |         101 | 103000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+

Mycat 分片 join。

ER 表。

Mycat 借鉴了 NewSQL 领域的新秀 FoundationDB 的设计思路，Foundation DB 创新性的提出了 TableGroup 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了 JION 的效率和性能问题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。

schema.xml。

<?xml version="1.0"?>
<!DOCTYPE Mycat:schema SYSTEM "schema.dtd">
<Mycat:schema xmlns:Mycat="http://io.Mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2"></table>
                <table name="orders" dataNode="dn1, dn2" rule="mod_rule">
                        <childTable name="orders_detail" primaryKey="id" joinKey
="order_id" parentKey="id" />
                </table>
        </schema>

dn 2 上创建订单详情表。

CREATE TABLE `orders_detail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `detail` VARCHAR(2000),
  `order_id` INT,
  PRIMARY KEY (`id`));

启动 Mycat。向 orders_detail 插入数据。

INSERT INTO orders_detail(id, detail, order_id) VALUES (1, 'detail', 1);
INSERT INTO orders_detail(id, detail, order_id) VALUES (2, 'detail', 2);
INSERT INTO orders_detail(id, detail, order_id) VALUES (3, 'detail', 3);
INSERT INTO orders_detail(id, detail, order_id) VALUES (4, 'detail', 4);
INSERT INTO orders_detail(id, detail, order_id) VALUES (5, 'detail', 5);
INSERT INTO orders_detail(id, detail, order_id) VALUES (6, 'detail', 6);

mysql> INSERT INTO orders_detail(id, detail, order_id) VALUES (1, 'detail', 1);
Query OK, 1 row affected (0.28 sec)

mysql> INSERT INTO orders_detail(id, detail, order_id) VALUES (2, 'detail', 2);
Query OK, 1 row affected (0.20 sec)

mysql> INSERT INTO orders_detail(id, detail, order_id) VALUES (3, 'detail', 3);
Query OK, 1 row affected (0.42 sec)

mysql> INSERT INTO orders_detail(id, detail, order_id) VALUES (4, 'detail', 4);
Query OK, 1 row affected (0.41 sec)

mysql> INSERT INTO orders_detail(id, detail, order_id) VALUES (5, 'detail', 5);
Query OK, 1 row affected (0.40 sec)

mysql> INSERT INTO orders_detail(id, detail, order_id) VALUES (6, 'detail', 6);
Query OK, 1 row affected (0.20 sec)

mysql> select * from orders_detail;
+----+--------+----------+
| id | detail | order_id |
+----+--------+----------+
|  3 | detail |        3 |
|  4 | detail |        4 |
|  5 | detail |        5 |
|  1 | detail |        1 |
|  2 | detail |        2 |
|  6 | detail |        6 |
+----+--------+----------+
6 rows in set (0.06 sec)

mysql> SELECT 
    ->     o.*, od.detail
    -> FROM
    ->     orders o
    ->         INNER JOIN
    ->     orders_detail od ON o.id = od.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount    | detail |
+----+------------+-------------+-----------+--------+
|  1 |        101 |         100 | 100100.00 | detail |
|  2 |        101 |         100 | 100300.00 | detail |
|  6 |        102 |         100 | 100020.00 | detail |
|  3 |        101 |         101 | 120000.00 | detail |
|  4 |        101 |         101 | 103000.00 | detail |
|  5 |        102 |         101 | 100400.00 | detail |
+----+------------+-------------+-----------+--------+
6 rows in set (0.03 sec)

dn1。

mysql> SELECT 
    ->     o.*, od.detail
    -> FROM
    ->     orders o
    ->         INNER JOIN
    ->     orders_detail od ON o.id = od.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount    | detail |
+----+------------+-------------+-----------+--------+
|  1 |        101 |         100 | 100100.00 | detail |
|  2 |        101 |         100 | 100300.00 | detail |
|  6 |        102 |         100 | 100020.00 | detail |
+----+------------+-------------+-----------+--------+
3 rows in set (0.00 sec)

dn2。

mysql> SELECT 
    ->     o.*, od.detail
    -> FROM
    ->     orders o
    ->         INNER JOIN
    ->     orders_detail od ON o.id = od.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount    | detail |
+----+------------+-------------+-----------+--------+
|  3 |        101 |         101 | 120000.00 | detail |
|  4 |        101 |         101 | 103000.00 | detail |
|  5 |        102 |         101 | 100400.00 | detail |
+----+------------+-------------+-----------+--------+
3 rows in set (0.00 sec)

水平分表全局表。

在分片的情况下，当业务因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：

变动不频繁。
数据量总体变化不大。
数据规模不大，很少有超过数十万条记录。

鉴于此，Mycat 定义了一种特殊的表，称之为全局表，全局表具有以下特性：

全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性。
全局表的查询操作，只从一个节点获取。
全局表可以跟任何一个表进行 JOIN 操作。
将字典表或者符合字典表特性的一些表定义为全局表，则从另外一个方面很好的解决了数据 JOIN 的难题。通过全局表 - 基于 E-R 关系的分片策略，Mycat 可以满足 80% 以上的企业应用开发。

配置 schema.xml。

<?xml version="1.0"?>
<!DOCTYPE Mycat:schema SYSTEM "schema.dtd">
<Mycat:schema xmlns:Mycat="http://io.Mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2"></table>
                <table name="orders" dataNode="dn1, dn2" rule="mod_rule">
                        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table>
                <table name="dict_order_type" dataNode="dn1, dn2" type="global"></table>
        </schema>

dn 2 中创建 dict_order_type。

CREATE TABLE `dict_order_type` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `order_type` VARCHAR(200),
  PRIMARY KEY (`id`));

启动 Mycat。在 Mycat 中。

[geek@localhost ~]$ mysql -uMycat -p123456 -P8066 -h192.168.142.162

INSERT INTO dict_order_type (id, order_type) values (101, 'type1');
INSERT INTO dict_order_type (id, order_type) values (102, 'type2');

mysql> select * from dict_order_type;
+-----+------------+
| id  | order_type |
+-----+------------+
| 101 | type1      |
| 102 | type2      |
+-----+------------+
2 rows in set (0.00 sec)

常用分片规则。

取模。

此规则为对分片字段求模运算。也是水平分表最常用规则。orders 表采用了此规则。

分片 ~ 分片枚举。

通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。

schema.xml。

<table name="orders_ware_info" dataNode="dn1, dn2" rule="sharding_by_intfile"></table>

<?xml version="1.0"?>
<!DOCTYPE Mycat:schema SYSTEM "schema.dtd">
<Mycat:schema xmlns:Mycat="http://io.Mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2"></table>
                <table name="orders" dataNode="dn1, dn2" rule="mod_rule">
                        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table>
                <table name="dict_order_type" dataNode="dn1, dn2" type="global"></table>
                <table name="orders_ware_info" dataNode="dn1, dn2" rule="sharding_by_intfile"></table>
        </schema>

rule.xml。

        <tableRule name="sharding_by_intfile">
                <rule>
                        <columns>areacode</columns>
                        <algorithm>hash-int</algorithm>
                </rule>
        </tableRule>

        <function name="hash-int"
                class="io.Mycat.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property>
        </function>

        <function name="hash-int"
                class="io.Mycat.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property>
                <property name="type">1</property>
                <property name="defaultNode">0</property>
        </function>

columns：分片字段。algorithm：分片函数。
mapFile：标识配置文件名称。type：0，为 int 型。非 0 为 String。
defauItNode：默认节点。小于 0 表示不设置默认节点。大于等于 0 表示设置默认节点。
设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置不识别就报错。

修改 partition-hash-int.txt 配置文件。

[geek@localhost ~]$ cat /usr/local/Mycat/conf/partition-hash-int.txt 
110=0
120=1

启动 Mycat。

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


CREATE TABLE `orders`.`orders_ware_info` (
    `id` INT AUTO_INCREMENT COMMENT '编号。',
    `order_id` INT COMMENT '订单编号。',
    `address` VARCHAR(200) COMMENT '地址。',
    `areacode` VARCHAR(20) COMMENT '区域编号。',
    PRIMARY KEY (`id`)
);

INSERT INTO orders_ware_info(id, order_id, address, areacode) VALUES (1, 1, '北京', '110');
INSERT INTO orders_ware_info(id, order_id, address, areacode) VALUES (2, 2, '天津', '120');

dn1。

mysql> select * from orders_ware_info;
+----+----------+---------+----------+
| id | order_id | address | areacode |
+----+----------+---------+----------+
|  1 |        1 | 北京    | 110      |
+----+----------+---------+----------+
1 row in set (0.00 sec)

dn 2。

mysql> select * from orders_ware_info;
+----+----------+---------+----------+
| id | order_id | address | areacode |
+----+----------+---------+----------+
|  2 |        2 | 天津    | 120      |
+----+----------+---------+----------+
1 row in set (0.00 sec)

分片 ~ 范围约定。

提前规划好分片字段某个范围属于哪个分片。

schema.xml。

<table name="payment_info" dataNode="dn1, dn2" rule="auto_sharding_long"></table>

<?xml version="1.0"?>
<!DOCTYPE Mycat:schema SYSTEM "schema.dtd">
<Mycat:schema xmlns:Mycat="http://io.Mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2"></table>
                <table name="orders" dataNode="dn1, dn2" rule="mod_rule">
                        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table>
                <table name="dict_order_type" dataNode="dn1, dn2" type="global"></table>
                <table name="orders_ware_info" dataNode="dn1, dn2" rule="sharding_by_intfile"></table>
                <table name="payment_info" dataNode="dn1, dn2" rule="auto_sharding_long"></table>
        </schema>

rule.xml。

<!DOCTYPE Mycat:rule SYSTEM "rule.dtd">
<Mycat:rule xmlns:Mycat="http://io.Mycat/">

        <tableRule name="auto_sharding_long">
                <rule>
                        <columns>order_id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>
        </tableRule>

        <function name="rang-long"
                class="io.Mycat.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long.txt</property>
        </function>

        <function name="rang-long"
                class="io.Mycat.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long.txt</property>
                <property name="defaultNode">0</property>
        </function>

[geek@localhost ~]$ sudo vim /usr/local/Mycat/conf/autopartition-long.txt 
# range start-end ,data node index
# K=1000,M=10000.
#0-500M=0
#500M-1000M=1
#1000M-1500M=2

0-102=0
103-200=1

重启 Mycat。

CREATE TABLE `orders`.`payment_info` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '编号。',
  `order_id` INT COMMENT '订单编号。',
  `payment_status` INT COMMENT '支付状态。',
  PRIMARY KEY (`id`));

dn1。

[geek@localhost ~]$ mysql -uroot -psQm581257.
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 297
Server version: 5.7.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo_01            |
| mysql              |
| orders             |
| performance_schema |
| sakila             |
| sys                |
| testdb             |
+--------------------+
8 rows in set (0.01 sec)

mysql> use orders;
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_orders |
+------------------+
| dict_order_type  |
| orders           |
| orders_detail    |
| orders_ware_info |
| payment_info     |
+------------------+
5 rows in set (0.00 sec)

mysql> 

INSERT INTO payment_info (id, order_id, payment_status) VALUES (1, 101, 0);
INSERT INTO payment_info (id, order_id, payment_status) VALUES (2, 102, 1);
INSERT INTO payment_info (id, order_id, payment_status) VALUES (3, 103, 0);
INSERT INTO payment_info (id, order_id, payment_status) VALUES (4, 104, 1);

dn 1。

mysql> select * from payment_info;
+----+----------+----------------+
| id | order_id | payment_status |
+----+----------+----------------+
|  1 |      101 |              0 |
|  2 |      102 |              1 |
+----+----------+----------------+
2 rows in set (0.00 sec)

dn 2。

mysql> select * from payment_info;
+----+----------+----------------+
| id | order_id | payment_status |
+----+----------+----------------+
|  3 |      103 |              0 |
|  4 |      104 |              1 |
+----+----------+----------------+
2 rows in set (0.00 sec)

分片 ~ 按照日期（天）分片。

schema.xml。

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
		<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date"></table>
	</schema>

rule.xml。

<tableRule name="sharding_by_date">
	<rule>
		<columns>login_date</columns>
		<algorithm>shardingByDate</algorithm>
	</rule>
</tableRule>

<function name="shardingByDate" class="io.Mycat.route.function.PartitionByDate">
	<property name="dateFormat">yyyy-MM-dd</property>
	<property name="sBeginDate">2019-01-01</property>
	<property name="sEndDate">2019-01-03</property>
	<property name="sPartitionDay">1</property>
</function>

create table login_info (
	id int auto_increment comment "编号",
	`user_id` int comment '用户编号',
	login_date date comment '登录日期',
	PRIMARY KEY(id)
)

insert into login_info(id, user_id, login_date) values (1, 101, '2019-01-01');
insert into login_info(id, user_id, login_date) values (2, 102, '2019-01-02');
insert into login_info(id, user_id, login_date) values (3, 103, '2019-01-03');
insert into login_info(id, user_id, login_date) values (4, 104, '2019-01-04');
insert into login_info(id, user_id, login_date) values (5, 105, '2019-01-05');
insert into login_info(id, user_id, login_date) values (6, 106, '2019-01-06');

select * from login_info;

全局序列。

在实现分库分表的情况下，数据库自增己无法保证自增主键的全局唯一。为此， Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式。

本地文件。

此方式 Mycat 将 sequence 配置到文件中，当使用到 sequence 中的配置后，Mycat 会更新 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。

优点：本地加载，读取速度较快。
缺点：抗风险能力差。Mycat 所在主机宕机后，无法读取本地文件。

数据库方式。

利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。

Mycat 会预加载一部分号段到 Mycat 的内存中，这样大部分读写序列都是在内存中完成的。

如果内存中的号段用完了会再向数据库要一次。

数据库配置。

创建 MYCAT_SEQUENCE 表。

创建存放 sequence 的表。

DROP TABLE IF EXISTS MYCAT_SEQUENCE;

name sequence 名称
current_value 当前 value
increment 增长步长! 可理解为 mycat 在数据库中一次读取多少个 sequence. 当这些用完后，下次再从数据库中读取。

CREATE TABLE MYCAT_SEQUENCE (name VARCHAR(50) NOT NULL, current_value INT NOT
NULL, increment INT NOT NULL DEFAULT 100, PRIMARY KEY(name)) ENGINE=InnoDB;
- 插入一条 sequence
INSERT INTO MYCAT_SEQUENCE(name, current_value, increment) VALUES ('GLOBAL', 100000,
100);

创建相关 function。

获取当前 sequence 的值（返回当前值，增量）。

DROP FUNCTION IF EXISTS mycat_seq_currval;
DELIMITER
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET
utf-8
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval=“-999999999,null”;
SELECT concat(CAST(current_value AS CHAR),“,”,CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval;
END
DELIMITER;

设置 sequence 值。

DROP FUNCTION IF EXISTS mycat_seq_setval;
DELIMITER
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64)
CHARSET utf-8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
DELIMITER;

获取下一个 sequence 值。

DROP FUNCTION IF EXISTS mycat_seq_nextval;
DELIMITER
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET
utf-8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value - increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
DELIMITER;

sequence_db_conf.properties 相关配置，指定 sequence 相关配置在哪个节点上。
例如：

USER_SEQ=test_dn1

注意：MYCAT_SEQUENCE 表和以上的 3 个 function，需要放在同一个节点上。function 请直接在具体节点的数据库上执行，如果执行的时候报：

you might want to use the less safe log_bin_trust_function_creators variable

需要对数据库做如下设置：

windows 下 my.ini[mysqld] 加上 log_bin_trust_function_creators=1
linux 下 /etc/my.cnf 下 my.ini[mysqld] 加上 log_bin_trust_function_creators=1

修改完后，即可在 mysql 数据库中执行上面的函数。

使用示例：

insert into table1(id, name) values (next value for MYCATSEQ_GLOBAL, ‘test’);

时间戳方式。

全局序列 ID = 64 位二进制（42 （毫秒） - 5（机器 ID）- 5（业务编码）- 12（重复累加））换算成十进制为 18 位数的 long 类型，每毫秒可以并发 12 位二进制的累加。

优点：配置简单。
缺点：18 位 ID 过长。

自主生成全局序列。

可以在 Java 项目里面自己生成全局序列。

基于 HA 机制的 Mycat 高可用。

在实际项目中，Mycat 服务也需要考虑高可用性，如果 Mycat 所在服务器出现宕机，或 Mycat 服务故障，需要有备机提供服务，需要考虑 Mycat 集群。

高可用方案。

我们可以使用 HAProxy - Keepalived 配合两台 Mycat 搭起 Mycat 集群，实现高可用性。HAProxy 实现了 Mycat 多节点的集群高可用和负载均衡而 HAProxy 自身的高可用则可以通过 Keepalived 实现。

Mycat 安全。

权限。

user 标签权限控制。

目前 Mycat 对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库的读写权限控制。

server.xml。

        <user name="mycat">
                <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>

标签属性	说明
name	应用连接中间件逻辑库的用户名。
password	该用户对应的密码。
TESTDB	应用当前连接的逻辑库中所对应的逻辑表。schemas 中可以配置一个或多个。
readOnly	应用连接中间件逻辑库所具有的权限。true 为只读， false 为读写都有。默认为 false。

mysql -uuser -puser -P8066 -h192.168.142.162

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> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  1 |        101 |         100 | 100100.00 |
|  2 |        101 |         100 | 100300.00 |
|  6 |        102 |         100 | 100020.00 |
|  1 |        101 |         100 | 100100.00 |
|  2 |        101 |         100 | 100300.00 |
|  6 |        102 |         100 | 100020.00 |
+----+------------+-------------+-----------+
6 rows in set (0.10 sec)

mysql> insert into orders(id, order_type, customer_id, amount) values (7, 101, 101, 10000);
ERROR 1495 (HY000): User readonly

user 标签权限控制。

在 user 标签下的 privileges 标签可以对逻辑库（schema）、表（table）进行精细化的 DML 权限控制。

privileges 标签下的 check 属性，如为 true 开启权限检查，为 false 不开启，默认为 false。

由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库（schema），所以 privileges 的下级节点 schema 节点同样可配置多个，对多库多表进行细粒度的 DML 权限控制。

        <user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>

                <!-- 表级 DML 权限设置 -->
                <privileges check="true">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           

                <!-- 表级 DML 权限设置 -->
                <!--           
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

DML 权限。

DML 权限	增	删	改	查
0000	×	×	×	×
0010	×	×	√	×
1110	√	×	×	×
1111	√	√	√	√

sql 拦截。

firewall 标签用来定义防火墙。firewall 下 whitehost 标签用来定义 IP 白名单，blacklist 用来定义 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>

<firewall>
    <whitehost>
        <host host="127.0.0.1" user="mycat"/>
        <host host="127.0.0.2" user="mycat"/>
    </whitehost>
    <blacklist check="true">
    	<property name="deleteAllow">false</property>
    </blacklist>
</firewall>

配置项	缺省值	描述
selelctAllow	TRUE	是否允许执行 SELECT 语句
deleteAllow	TRUE	是否允许执行 DELETE 语句
updateAllow	TRUE	是否允许执行 UPDATE 语句
insertAIIow	TRUE	是否允许执行 INSERT 语句
createTableAllow	TRUE	是否允许创建表
setAllow	TRUE	是否允许使用 SET 语法
alterTableAllow	TRUE	是否允许执行 Alter Table 语句
dropTableAllow	TRUE	是否允许修改表
commitAlIow	TRUE	是否允许执行 commit 操作
rollbackAllow	TRUE	是否允许执行 roll back 操作