在Docker下進行MyCAT管理雙主雙從MySQL叢集

顏值六毛六發表於2021-01-10
DockerMySql

前言
在Docker下雙主雙從MySQL叢集模擬
https://www.cnblogs.com/yumq/p/14259964.html
本文實驗配置檔案
https://pan.baidu.com/s/1N4d5PB9z9ofUhIpvUWy--g 提取碼：6666

Docker拉取MyCAT映象

如果沒啟動映象加速器的小夥伴，先去配置映象加速器！否則拉取很慢！在前文有配置流程

#拉取mycat
docker pull fify/mycat
#檢視已有映象
docker images

建立MyCAT配置檔案的本機掛載目錄

sudo mkdir -p /home/mycat

將MyCAT的掛載配置檔案放入這個目錄

因為我在虛擬機器記憶體不太夠，就多掛載了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/名字


測試MyCAT是否可用

#在mysql容器中執行
mysql -u MyCAT的username -p -h MyCAT的IP地址 -P MyCAT的資料埠
#我的執行如下
mysql -u root -proot123 -h 172.24.7.187 -P 8066

成功！

在MyCAT負載測試

可以正常查詢，但我們不知道是從哪個MySQL查詢出來的

新增衝突資料，因為我MySQL配置檔案中將binlog格式改為了STATEMENT,是SQL語句進行復制。
所以在插入@@hostname時因為主機名稱不一樣,插入的資料在各個資料庫中不相同


mycat負載功能成功,因為MySQL配置中blance設定的為1，會從 從機和主機的備機中查詢

停掉master1主機，在MyCAT測試抗風險能力

master1主機停掉了，這個時候master1的備機master2變成了主機。


我們可以看到，停機後仍然可以進行插入和查詢等操作

重啟master1主機，現在master1變成了master2的備機

可以從master1 和slave1 和slave2中查詢到資料

相關文章