docker 部署mycat 设置mysql主从

docker mysql部署参考《docker部署主从》

mycat Dockerfile参考 docker启动

FROM docker.io/adoptopenjdk/openjdk8:latest
 
ENV AUTO_RUN_DIR ./mycat2
ENV DEPENDENCE_FILE mycat2-1.21-release-jar-with-dependencies.jar
ENV TEMPLATE_FILE mycat2-install-template-1.21.zip
 
RUN sed -i "s@http://.*archive.ubuntu.com@http://mirrors.aliyun.com@g" /etc/apt/sources.list
RUN sed -i "s@http://.*security.ubuntu.com@http://mirrors.aliyun.com@g" /etc/apt/sources.list
 
RUN buildDeps='procps wget unzip' \
    && apt-get update \
    && apt-get install -y $buildDeps
 
RUN wget -P  $AUTO_RUN_DIR/ http://dl.mycat.org.cn/2.0/1.21-release/$DEPENDENCE_FILE \
   &&  wget -P  $AUTO_RUN_DIR/ http://dl.mycat.org.cn/2.0/install-template/$TEMPLATE_FILE 
 
 
RUN cd $AUTO_RUN_DIR/ \
    && unzip $TEMPLATE_FILE \
    && ls -al . \
    && mv  $DEPENDENCE_FILE mycat/lib/ \
    && chmod +x  mycat/bin/* \
    && chmod 755  mycat/lib/* \
    && mv mycat /usr/local
#copy mycat /usr/local/mycat/
VOLUME /usr/local/mycat/conf
VOLUME /usr/local/mycat/logs
 
EXPOSE 8066 1984
CMD ["/usr/local/mycat/bin/mycat", "console"]

　　启动

docker run  -d   -it   -p 8066:8066   -p 1984:1984  
--name mycat  
-v /data/mycat2/conf:/usr/local/mycat/conf   
-v /data/mycat2/logs:/usr/local/mycat/logs  
mycat.org.cn/mycat2:20220706

　　

 进入主数据库创建mycat用户，用户mycat2数据源连接

CREATE USER 'mycat'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* to 'mycat'@'%';
FLUSH PRIVILEGES;

配置文件mycat2文件下如果为空，可下载解压之后conf文件全部到该文件下，参考《安装mycat2》

因为mycat是通过docker部署 尝试用《通过注释配置》发现物理机上面没有生成相应的文件，所以本文档为以下配置

端口	服务
8066	mycat
3307	mysql主
3308	mysql从

 

mycat 主从配置

# m、s为自定义name，m-master、s-slave 
cd /data/mycat2/conf/datasources

cp prototypeDs.datasource.json m.datasource.json
vim m.datasource.json
{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ_WRITE",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"m",
	"password":"123456",
	"type":"JDBC",
	"url":"jdbc:mysql://192.168.153.15:3307?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"mycat",
	"weight":0
}

cp prototypeDs.datasource.json s.datasource.json
vim s.datasource.json
{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ_WRITE",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"m",
	"password":"123456",
	"type":"JDBC",
	"url":"jdbc:mysql://192.168.153.15:3307?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"mycat",
	"weight":0
}

cd /data/mycat2/conf/clusters
cp prototype.cluster.json master-slave.cluster.json
vim master-slave.cluster.json
{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetry":3,
		"minSwitchTimeInterval":300,
		"slaveThreshold":0
	},
	"masters":[
		"m"       # 主节点
	],
	"maxCon":200,
	"name":"master-slave",  # 主从名称
	"readBalanceType":"BALANCE_ALL",
        "replicas":[
                "s" //从节点
        ],
	"switchType":"SWITCH"
}

cd /data/mycat2/conf/schemas
cp mysql.schema.json mydb1.schema.json
vim mydb1.schema.json
{
     "customTables":{},
     "globalTables":{},
     "normalProcedures":{},
     "normalTables":{},
     "schemaName":"mydb1",         # 物理库
     "targetName":"master-slave",  # 集群配置 /data/mycat2/conf/clusters/master-slave.cluster.json中的name
     "shardingTables":{},
     "views":{}
}

　　

# 重启容器
docker restart mycat

　　

测试：

注：当前环境主从的binlog同步方式为statement所以可以识别@@hostname变量，连接mycat插入 insert into user(name,hostname) value ('张三',@@hostname);

主库为：

 

从库数据为

 

 

连接mycat查询，查询到的数据如图，如是从库的数据就是从从库查询的，反之则是主库