Docker搭建MySQL跨主机主从复制

╰半夏微凉° 2023-07-20 05:41 0阅读 0赞

### 文章目录 ###

*  MySQL主从介绍
 *  准备工作
 *  创建mysql-master服务
 *  创建mysql-slave服务
 *  验证

# MySQL主从介绍 #

*  在生产环境中，数据库单节点非常危险，万一出现故障我们的数据就无法恢复。本文介绍基于Docker Swarm搭建MySQL-5.7跨主机数据备份

# 准备工作 #

*  首先创建跨主机overlay网络，不懂小伙伴[点击这里][Link 1]，查看网络`docker network ls`

[root@gpu03 docker]# docker network ls
    NETWORK ID          NAME                            DRIVER              SCOPE
    d0f2d68382d9        bridge                          bridge              local
    b519d6da2092        docker_default                  bridge              local
    ec949a93ee57        host                            host                local
    kvt9ibn4tua4        ingress                         overlay             swarm
    a93c3b19194a        none                            null                local
    1fx2olvzn3os        sg                              overlay             swarm

最下面的`sg`就是我们基于`swarm`创建的`overlay`网络

*  查看`node`节点

[root@gpu03 docker]# docker node ls
    ID                            HOSTNAME            STATUS              AVAILABILITY        MANAGER STATUS      ENGINE VERSION
    sflpfedy8d3qrbt4izkmezq2g *   gpu03               Ready               Active              Leader              19.03.2
    ae2swiz3fhd2ma3yeure5s72h     sangang             Ready               Active                                  19.03.8

可以看到除了我们的`gpu03`节点还有`sangang`节点，接下来我们在gpu03节点创建`mysql-master`服务，sangang节点创建`mysql-slave`服务

# 创建mysql-master服务 #

[root@gpu03 docker]# ls
    conf  data  docker-compose.yml  init-d

*  创建环境变量.env文件

[root@gpu03 docker]# vim .env
    MYSQL_ROOT_PASSWORD=root
    MYSQL_MASTER_SERVICE_NAME=mysql-master
    MYSQL_MASTER_SERVICE_USER=sangang
    MYSQL_MASTER_SERVICE_PASSWORD=sangang
    MYSQL_MASTER_SERVICE_POST=3306

*  创建my.cnf文件

[root@gpu03 docker]# vim conf/my.cnf
    [mysqld]
    server-id=1
    log-bin=mysql-bin

*  创建init-master.sh文件

[root@gpu03 docker]# vim init-d/init-master.sh
    #!/bin/bash
    mysql -uroot -p$MYSQL_ROOT_PASSWORD << EOF
    CREATE USER '$MYSQL_MASTER_SERVICE_USER'@'%' IDENTIFIED BY '$MYSQL_MASTER_SERVICE_PASSWORD';
    GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '$MYSQL_MASTER_SERVICE_USER'@'%';
    EOF

*  创建docker-compose.yml

[root@gpu03 docker]# vim docker-compose.yml
    version: '3'
    services:
      mysql-master:
        image: mysql:5.7
        container_name: mysql-master
        restart: always
        env_file:
          - .env
        environment:
          - MYSQL_ROOT_PASSWORD=${ MYSQL_ROOT_PASSWORD}
          - TZ=Asia/Shanghai
        ports:
          - "3339:3306"
        networks:
          - sg
        volumes:
          - ./conf/my.cnf:/etc/my.cnf
          - ./data:/var/lib/mysql
          - ./init-d/init-master.sh:/docker-entrypoint-initdb.d/1-init-master.sh
    
    networks:
      sg:
        external: true

*  启动`docker-compose up -d`
 *  查看日志`docker logs -f mysql-master`

# 创建mysql-slave服务 #

[root@sangang docker]# ls
    conf  data  docker-compose.yml  init-d

*  创建环境变量.env文件

[root@sangang docker]# vim .env
    MYSQL_ROOT_PASSWORD=root
    MYSQL_MASTER_SERVICE_NAME=mysql-master
    MYSQL_MASTER_SERVICE_USER=sangang
    MYSQL_MASTER_SERVICE_PASSWORD=sangang
    MYSQL_MASTER_SERVICE_POST=3306

*  创建my.cnf文件

[root@sangang docker]# vim conf/my.cnf
    [mysqld]
    ## 设置server_id,注意要唯一
    server-id=101
    ## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
    log-bin=mysql-slave-bin
    ## relay_log配置中继日志
    relay_log=edu-mysql-relay-bin

*  创建init-salve.sh文件

[root@sangang docker]# vim init-d/init-slave.sh
    #!/bin/bash
    mysql -uroot -p$MYSQL_ROOT_PASSWORD << EOF
    change master to master_host='$MYSQL_MASTER_SERVICE_NAME',master_user='$MYSQL_MASTER_SERVICE_USER', master_password='$MYSQL_MASTER_SERVICE_PASSWORD',master_port=$MYSQL_MASTER_SERVICE_POST;
    start slave;
    EOF

*  创建docker-compose.yml

[root@sangang docker]# vim docker-compose.yml
    version: '3'
    services:
      mysql-master:
        image: mysql:5.7
        container_name: mysql-slave
        restart: always
        env_file:
          - .env
        environment:
          - MYSQL_ROOT_PASSWORD=${ MYSQL_ROOT_PASSWORD}
          - TZ=Asia/Shanghai
        ports:
          - "3306:3306"
        networks:
          - sg
        volumes:
          - ./conf/my.cnf:/etc/my.cnf
          - ./data:/var/lib/mysql
          - ./init-d/init-slave.sh:/docker-entrypoint-initdb.d/1-init-slave.sh
    
    networks:
      sg:
        external: true

*  启动`docker-compose up -d`
 *  查看日志`docker logs -f mysql-slave`
 *  登录mysql-slave查看状态

sangang@sangang:~/docker$ docker exec -it mysql-slave bash
    root@417cbe900756:/# mysql -uroot -proot
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.29-log 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> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: mysql-master
                      Master_User: sangang
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 316
                   Relay_Log_File: edu-mysql-relay-bin.000006
                    Relay_Log_Pos: 529
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

可以看到下面两个属性是`Yes`,说明成功了

*  Slave\_IO\_Running: `Yes`
 *  Slave\_SQL\_Running: `Yes`

# 验证 #

*  登录mysql-master并创建数据库

[root@gpu03 docker]# docker exec -it mysql-master bash
    root@3f61cceebbab:/# mysql -uroot -proot
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 5.7.29-log 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database video;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | video              |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql>

*  登录mysql-slave查看数据库

sangang@sangang:~/docker$ docker exec -it mysql-slave bash
    root@417cbe900756:/# mysql -uroot -proot
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.7.29-log 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | video              |
    +--------------------+
    5 rows in set (0.07 sec)
    
    mysql>

*  可以看到mysql-slave库也存在了
 *  `结束`

[Link 1]: https://blog.csdn.net/weixin_43972854/article/details/105041999