使用docker创建mycat mysql主从服务器
拉取 github项目
配置文件已经全部写好 基本找下面流程走一遍就能直接用
注意：mycat 和 mysql使用的字符集编码全部是 utf8mb4 mysql docker版本 8.0.20
% git clone https://github.com/liuwel/docker-mycat.git
% tree docker-mycat
docker-mycat
├── README.md
├── compose
│ ├── docker-compose.yml
│ └── mycat
│ ├── Dockerfile
│ └── Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
└── config
├── hosts
├── mycat
│ ├── ...
├── mysql-m1
│ └── conf.d
│ └── docker.cnf
├── mysql-s1
│ └── conf.d
│ └── docker.cnf
└── mysql-s2
└── conf.d
└── docker.cnf
12 directories, 48 filesmysql 主从服务器的配置已经写在config对应的目录中
mysql-m1 : 主服务器 IP:172.18.0.2
mysql-s1 : 从服务器slave1 IP:172.18.0.3
mysql-s2 : 从服务器slave2 IP:172.18.0.4
mycat : Mycat服务器 IP:172.18.0.5
修改hosts文件 添加解析
% sudo vi /etc/hosts
# docker-mycat m1:mysql-master主服务器 s1,s2：mysql-slave 从服务器
# mycat mycat中间件服务器
172.18.0.2 m1
172.18.0.3 s1
172.18.0.4 s2
172.18.0.5 mycat
127.0.0.1 localdocker-compose.yml配置文件
% cd ~/docker-mycat/compose
% cat docker-compose.ymlversion: '2'
services:
m1:
image: mysql:8.0.20
container_name: m1
volumes:
- ../config/mysql-m1/conf.d/docker.cnf:/etc/mysql/conf.d/docker.cnf:ro
#- /etc/localtime:/etc/localtime:ro
- ../config/hosts:/etc/hosts:ro
ports:
- "3309:3306"
networks:
mysql:
ipv4_address: 172.18.0.2
ulimits:
nproc: 65535
hostname: m1
mem_limit: 512m
restart: always
command:
- '--default-authentication-plugin=mysql_native_password'
- '--character-set-server=utf8mb4'
environment:
MYSQL_ROOT_PASSWORD: m1test
s1:
image: mysql:8.0.20
container_name: s1
volumes:
- ../config/mysql-s1/conf.d/docker.cnf:/etc/mysql/conf.d/docker.cnf:ro
# - /etc/localtime:/etc/localtime:ro
- ../config/hosts:/etc/hosts:ro
ports:
- "3307:3306"
networks:
mysql:
ipv4_address: 172.18.0.3
links:
- m1
ulimits:
nproc: 65535
hostname: s1
mem_limit: 512m
restart: always
command:
- '--default-authentication-plugin=mysql_native_password'
- '--character-set-server=utf8mb4'
environment:
MYSQL_ROOT_PASSWORD: s1test
s2:
image: mysql:8.0.20
container_name: s2
volumes:
- ../config/mysql-s2/conf.d/docker.cnf:/etc/mysql/conf.d/docker.cnf:ro
#- /etc/localtime:/etc/localtime:ro
- ../config/hosts:/etc/hosts:ro
ports:
- "3308:3306"
links:
- m1
networks:
mysql:
ipv4_address: 172.18.0.4
ulimits:
nproc: 65535
hostname: s2
mem_limit: 512m
restart: always
command:
- '--default-authentication-plugin=mysql_native_password'
- '--character-set-server=utf8mb4'
environment:
MYSQL_ROOT_PASSWORD: s2test
mycat:
build: ./mycat
container_name: mycat
volumes:
- ../config/mycat/:/mycat/conf/:ro
- ../log/mycat-logs/:/mycat/logs/:rw
#- /etc/localtime:/etc/localtime:ro
- ../config/hosts:/etc/hosts:ro
ports:
- "8066:8066"
- "9066:9066"
links:
- m1
- s1
- s2
networks:
mysql:
ipv4_address: 172.18.0.5
ulimits:
nproc: 65535
hostname: mycat
mem_limit: 512m
restart: always
networks:
mysql:
driver: bridge
ipam:
driver: default
config:
- subnet: 172.18.0.0/24
gateway: 172.18.0.1Build 镜像
% sudo docker-compose build m1 s1 s2运行 docker mysql主从数据库 (mysql数据库密码在yml文件里面)
% sudo docker-compose up -d m1 s1 s2
Creating m1
Creating s2
Creating s1 mysql主从配置
配置m1主服务器
sudo docker exec -it m1 /bin/bash
root@m1:/# mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 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>创建用于主从复制的用户repl
mysql> create user repl;给repl用户授予slave的权限 mysql5.7 写法是 GRANT REPLICATION SLAVE ON . TO 'repl'@'172.18.0.%' IDENTIFIED BY 'repl'; mysql8 已经将创建账户和赋予权限的方式分开
mysql> CREATE USER 'repl'@'172.18.0.%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.18.0.%';
Query OK, 0 rows affected (0.00 sec)锁表
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)查看binlog状态 记录File 和 Position 状态稍后从库配置的时候会用
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 | 677 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)配置从库s1 s2
进入s1 shell
% sudo docker exec -it s1 /bin/bash
root@s1:/# mysql -uroot -p
mysql> change master to master_host='m1',master_port=3306,master_user='repl',master_password='repl',master_log_file='master-bin.000003',master_log_pos=677;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)进入s2 shell
sudo docker exec -it s2 /bin/bash
root@s2:/# mysql -uroot -p
mysql> change master to master_host='m1',master_port=3306,master_user='repl',master_password='repl',master_log_file='master-bin.000003',master_log_pos=677;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql主从配置完成 现在测试一下
登陆主数据库 创建masterdb数据库 (这个数据库名在稍后的mycat里面会用到)
% mysql -uroot -pm1test -hm1
MySQL [(none)]> create database masterdb;
Query OK, 1 row affected (0.01 sec)进入从库看看数据库是否创建
% mysql -uroot -ps1test -hs1
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| masterdb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)可以看到从库也已经创建成功了 到这里msyql的主从已经配置完成了
接下来是mycat的配置其实在 ~/config/mycat 里面已经配置好了直接就可以用了 看下schama.xml配置文件
% cat ~/config/mycat/schema.xml<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="masterdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="masterDN" />
<dataNode name="masterDN" dataHost="masterDH" database="masterdb" />
<dataHost name="masterDH" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="m1" url="172.18.0.2:3306" user="root" password="m1test">
<readHost host="s1" url="172.18.0.3:3306" user="root" password="s1test" />
<readHost host="s2" url="172.18.0.4:3306" user="root" password="s2test" />
</writeHost>
</dataHost>
</mycat:schema> server.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:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="charset">utf8mb4</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</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>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">true</property>
</system>
<!-- 全局SQL防火墙设置
<firewall>
<whitehost>
<host host="172.18.0.2" user="root"/>
<host host="172.18.0.3" user="root"/>
<host host="172.18.0.4" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>-->
<user name="root">
<property name="password">yourpassword</property>
<property name="schemas">masterdb</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>
</mycat:server>启动mycat
% cd ~/docker-mycat/compose
% sudo docker-compose up -d mycat整体测试
% mysql -uroot -p -P8066 -hmycatMySQL [(none)]> show databases; +----------+ | DATABASE | +----------+ | masterdb | +----------+ 1 row in set (0.00 sec) 测试数据
MySQL [(none)]> use masterdb
Database changed
MySQL [masterdb]> CREATE TABLE `test_table` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
-> `title` varchar(255) DEFAULT NULL COMMENT '标题',
-> `content` text COMMENT '内容',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB COMMENT='测试表'
-> ;
Query OK, 0 rows affected (0.03 sec)
MySQL [masterdb]> show tables;
+--------------------+
| Tables_in_masterdb |
+--------------------+
| test_table |
+--------------------+
1 row in set (0.00 sec)
MySQL [masterdb]> INSERT INTO `test_table` VALUES ('1', '测试标题1', '测试内容1');
INSERT Query OK, 1 row affected (0.01 sec)
MySQL [masterdb]> INSERT INTO `test_table` VALUES ('2', '测试标题2', '测试内容2');
INSERT INTO `tQuery OK, 1 row affected (0.01 sec)
MySQL [masterdb]> INSERT INTO `test_table` VALUES ('3', '测试标题3', '测试内容3');
NSERQuery OK, 1 row affected (0.01 sec)
MySQL [masterdb]> INSERT INTO `test_table` VALUES ('4', '测试标题4', '测试内容4');
INSERT INQuery OK, 1 row affected (0.01 sec)
MySQL [masterdb]> INSERT INTO `test_table` VALUES ('5', '测试标题5', '测试内容5');
INSQuery OK, 1 row affected (0.01 sec)
MySQL [masterdb]> INSERT INTO `test_table` VALUES ('6', '测试标题6', '测试内容6');
Query OK, 1 row affected (0.01 sec) MySQL [masterdb]> select * from test_table;
+----+---------------+---------------+
| id | title | content |
+----+---------------+---------------+
| 1 | 测试标题1 | 测试内容1 |
| 2 | 测试标题2 | 测试内容2 |
| 3 | 测试标题3 | 测试内容3 |
| 4 | 测试标题4 | 测试内容4 |
| 5 | 测试标题5 | 测试内容5 |
| 6 | 测试标题6 | 测试内容6 |
+----+---------------+---------------+
6 rows in set (0.01 sec)