天天看點
傳回

Docker搭建MySQL跨主機主從複制MySQL主從介紹準備工作建立mysql-master服務建立mysql-slave服務驗證

文章目錄

  • MySQL主從介紹
  • 準備工作
  • 建立mysql-master服務
  • 建立mysql-slave服務
  • 驗證

MySQL主從介紹

  • 在生産環境中，資料庫單節點非常危險，萬一出現故障我們的資料就無法恢複。本文介紹基于Docker Swarm搭建MySQL-5.7跨主機資料備份

準備工作

  • 首先建立跨主機overlay網絡，不懂小夥伴點選這裡，檢視網絡

    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服務

  • 在docker目錄下建立幾個目錄 
[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服務

  • 在docker目錄下建立幾個目錄– 
[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庫也存在了

  • 結束
Docker mysql swarm
上一篇: 人類的交配時間多久才算正常？網友：老公也就比蟑螂差點（漫畫）
下一篇: docker部署mysql一主一從

繼續閱讀