前言

在Docker下双主双从MySQL集群模拟

https://www.cnblogs.com/yumq/p/14259964.html

本文实验配置文件

Docker拉取MyCAT镜像

如果没启动镜像加速器的小伙伴，先去配置镜像加速器！否则拉取很慢！在前文有配置流程

#拉取mycat

docker pull fify/mycat

#查看已有镜像

docker images

在Docker下进行MyCAT管理双主双从MySQL集群

创建MyCAT配置文件的本机挂载目录

sudo mkdir -p /home/mycat

在Docker下进行MyCAT管理双主双从MySQL集群

将MyCAT的挂载配置文件放入这个目录

在Docker下进行MyCAT管理双主双从MySQL集群

因为我在虚拟机内存不太够，就多挂载了wrapper.conf文件，这个是为了修改MyCAT运行时使用的内存。

如果要配置规则建议多挂载一个rule.xml

使用了多端口来模拟多个服务器

schema.xml

schema.xml 文件如下

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

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

    <!-- dataNode 如果有多个就配置多个 -->

    <dataNode name="dn1" dataHost="host1" database="你的同步的数据库" />

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

        <!-- 心跳语句 -->

        <heartbeat>select user()</heartbeat>

        <!-- 写实例 -->

        <writeHost host="hostM1" url="172.24.7.187:3306" user="root" password="123456">

            <!-- 读实例 -->

            <readHost host="hostS1" url="172.24.7.187:3308" user="root" password="123456" />

        </writeHost>

         <writeHost host="hostM2" url="172.24.7.187:3307" user="root" password="123456">

            <!-- 读实例 -->

            <readHost host="hostS2" url="172.24.7.187:3309" user="root" password="123456" />

        </writeHost>

    </dataHost>

</mycat:schema>

server.xml

server.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">

<mycat:server xmlns:mycat="http://io.mycat/">

    <system>

        <!-- 1为开启实时统计、0为关闭 -->

        <property name="useSqlStat">0</property>

        <!-- 1为开启全加班一致性检测、0为关闭 -->

        <property name="useGlobleTableCheck">0</property>

        <property name="sequnceHandlerType">2</property>

        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->

        <property name="processorBufferPoolType">0</property>

        <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->

        <property name="handleDistributedTransactions">0</property>

        <!-- off heap for merge/order/group/limit   1开启   0关闭 -->

        <property name="useOffHeapForMerge">1</property>

        <!-- 单位为m -->

        <property name="memoryPageSize">1m</property>

        <!-- 单位为k -->

        <property name="spillsFileBufferSize">1k</property>

        <property name="useStreamOutput">0</property>

        <!-- 单位为m -->

        <property name="systemReserveMemorySize">384m</property>

    </system>

    <!-- 连接MyCAT 的username 和 password ; 这里分别是 root 和root123-->

    <user name="root">

        <property name="password">root123</property>

        <property name="schemas">test</property>

    </user>

</mycat:server>

wrapper.conf

wrapper.conf

#********************************************************************

# Wrapper Properties

#********************************************************************

# Java Application

wrapper.java.command=java

wrapper.working.dir=..

# Java Main class.  This class must implement the WrapperListener interface

#  or guarantee that the WrapperManager class is initialized.  Helper

#  classes are provided to do this for you.  See the Integration section

#  of the documentation for details.

wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperSimpleApp

set.default.REPO_DIR=lib

set.APP_BASE=.

# Java Classpath (include wrapper.jar)  Add class path elements as

#  needed starting from 1

wrapper.java.classpath.1=lib/wrapper.jar

wrapper.java.classpath.2=conf

wrapper.java.classpath.3=%REPO_DIR%/*

# Java Library Path (location of Wrapper.DLL or libwrapper.so)

wrapper.java.library.path.1=lib

# Java Additional Parameters

#wrapper.java.additional.1=

wrapper.java.additional.1=-DMYCAT_HOME=.

wrapper.java.additional.2=-server

wrapper.java.additional.3=-XX:MaxPermSize=64M

wrapper.java.additional.4=-XX:+AggressiveOpts

wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G

wrapper.java.additional.6=-Dcom.sun.management.jmxremote

wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984

wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false

wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false

#这里是配置运行的最大内存和最小内存，如果虚拟机内存不够在这里改

wrapper.java.additional.10=-Xmx4G

wrapper.java.additional.11=-Xms1G

# Initial Java Heap Size (in MB)

#wrapper.java.initmemory=3

# Maximum Java Heap Size (in MB)

#wrapper.java.maxmemory=64

# Application parameters.  Add parameters as needed starting from 1

wrapper.app.parameter.1=io.mycat.MycatStartup

wrapper.app.parameter.2=start

#********************************************************************

# Wrapper Logging Properties

#********************************************************************

# Format of output for the console.  (See docs for formats)

wrapper.console.format=PM

# Log Level for console output.  (See docs for log levels)

wrapper.console.loglevel=INFO

# Log file to use for wrapper output logging.

wrapper.logfile=logs/wrapper.log

# Format of output for the log file.  (See docs for formats)

wrapper.logfile.format=LPTM

# Log Level for log file output.  (See docs for log levels)

wrapper.logfile.loglevel=INFO

# Maximum size that the log file will be allowed to grow to before

#  the log is rolled. Size is specified in bytes.  The default value

#  of 0, disables log rolling.  May abbreviate with the 'k' (kb) or

#  'm' (mb) suffix.  For example: 10m = 10 megabytes.

wrapper.logfile.maxsize=0

# Maximum number of rolled log files which will be allowed before old

#  files are deleted.  The default value of 0 implies no limit.

wrapper.logfile.maxfiles=0

# Log Level for sys/event log output.  (See docs for log levels)

wrapper.syslog.loglevel=NONE

#********************************************************************

# Wrapper Windows Properties

#********************************************************************

# Title to use when running as a console

wrapper.console.title=Mycat-server

#********************************************************************

# Wrapper Windows NT/2000/XP Service Properties

#********************************************************************

# WARNING - Do not modify any of these properties when an application

#  using this configuration file has been installed as a service.

#  Please uninstall the service before modifying this section.  The

#  service can then be reinstalled.

# Name of the service

wrapper.ntservice.name=mycat

# Display name of the service

wrapper.ntservice.displayname=Mycat-server

# Description of the service

wrapper.ntservice.description=The project of Mycat-server

# Service dependencies.  Add dependencies as needed starting from 1

wrapper.ntservice.dependency.1=

# Mode in which the service is installed.  AUTO_START or DEMAND_START

wrapper.ntservice.starttype=AUTO_START

# Allow the service to interact with the desktop.

wrapper.ntservice.interactive=false

wrapper.ping.timeout=120

configuration.directory.in.classpath.first=conf

创建MyCAT容器

#前提，先在宿主机创建挂载的文件目录，并将相关配置文件加入其中。

#我推荐下面搭建方式

docker run --name mycat -p 8066:8066 -p 9066:9066 -v /home/mycat/server.xml:/usr/local/mycat/conf/server.xml -v /home/mycat/schema.xml:/usr/local/mycat/conf/schema.xml --privileged=true -d fify/mycat

#我没有挂载全部的配置文件，如需要其他的配置文件比如rule配置文件

#在原命令中追加 -v /home/mycat/rule.xml:/usr/local/mycat/conf/rule.xml

#因为我虚拟机内存太小了 我又挂载了mycat的wrapper.conf 修改了运行的最小内存和最大内存

docker run --name mycat -p 8066:8066 -p 9066:9066 -v /home/mycat/server.xml:/usr/local/mycat/conf/server.xml -v /home/mycat/schema.xml:/usr/local/mycat/conf/schema.xml -v /home/mycat/wrapper.conf:/usr/local/mycat/conf/wrapper.conf --privileged=true -d fify/mycat

查看容器

docker ps

#注意修改配置文件后要重启MyCAT

docker restart mycat容器ID/名字

在Docker下进行MyCAT管理双主双从MySQL集群

测试MyCAT是否可用

#在mysql容器中运行

mysql -u MyCAT的username -p -h MyCAT的IP地址 -P MyCAT的数据端口

#我的运行如下

mysql -u root -proot123 -h 172.24.7.187 -P 8066

在Docker下进行MyCAT管理双主双从MySQL集群

成功！

在MyCAT负载测试

可以正常查询，但我们不知道是从哪个MySQL查询出来的

添加冲突数据，因为我MySQL配置文件中将binlog格式改为了STATEMENT,是SQL语句进行复制。

所以在插入@@hostname时因为主机名称不一样,插入的数据在各个数据库中不相同

mycat负载功能成功,因为MySQL配置中blance设置的为1，会从从机和主机的备机中查询