Skip to content

liuwel/docker-mycat

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

23 Commits
 
 
 
 
 
 
 
 
 
 

Repository files navigation

使用docker创建mycat mysql主从服务器

配置文件已经全部写好 基本找下面流程走一遍就能直接用

注意: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 files

mysql 主从服务器的配置已经写在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       local

docker-compose.yml配置文件

% cd ~/docker-mycat/compose
% cat docker-compose.yml
version: '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.1

Build 镜像

% 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 -hmycat

MySQL [(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)

Releases

No releases published

Packages

No packages published