docker-compose启动mysql双机热备互为主从_docker mysql双机热备-CSDN博客

本文链接：https://blog.csdn.net/stone1116/article/details/130988314

1、环境准备

IP地址	名称
192.168.123.78	mysql-m
192.168.123.82	mysql-s

2、安装mysql-m（192.168.123.78）

创建M主节点，通过下面的docker编排配置创建响应的目录和文件，目录下边创建 docker-compose.yml和my.cnf文件

docker-compose.yml

version: '3.5'
services:
  mysql:
    image: mysql:8.0.26
    restart: always
    privileged: True
    container_name: mysql
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/conf:/etc/mysql/conf.d
      - ./mysql/logs:/logs
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      MYSQL_USER: "youxuan"
      MYSQL_PASSWORD: "123456"
      MYSQL_INITDB_SKIP_TZINFO: "Asia/Shanghai"
    ports:
      - 3306:3306
    network_mode: host

my.cnf

###### [mysql]配置模块 ######
[mysql]
# 设置MySQL客户端默认字符集
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
###### [mysqld]配置模块 ######
[mysqld]
port=3306
user=mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# MySQL8 的密码认证插件
default_authentication_plugin=mysql_native_password
# 禁用符号链接以防止各种安全风险
symbolic-links=0
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=0
max_allowed_packet=16M
# 设置时区
default-time_zone='+8:00'
# binlog 配置
binlog_format="ROW"
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 90
max-binlog-size = 500M
# server-id 配置
server-id = 1
###### [client]配置模块 ######
[client]
default-character-set=utf8mb4

3、安装mysql-m（192.168.123.82）

创建S节点，通过下面的docker编排配置创建响应的目录和文件，目录下边创建 docker-compose.yml和my.cnf文件

docker-compose.yml

version: '3.5'
services:
  mysql:
    image: mysql:8.0.26
    restart: always
    privileged: True
    container_name: mysql
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/conf:/etc/mysql/conf.d
      - ./mysql/logs:/logs
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      MYSQL_USER: "youxuan"
      MYSQL_PASSWORD: "123456"
      MYSQL_INITDB_SKIP_TZINFO: "Asia/Shanghai"
    ports:
      - 3306:3306
    network_mode: host

my.cnf

###### [mysql]配置模块 ######
[mysql]
# 设置MySQL客户端默认字符集
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
###### [mysqld]配置模块 ######
[mysqld]
port=3306
user=mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# MySQL8 的密码认证插件
default_authentication_plugin=mysql_native_password
# 禁用符号链接以防止各种安全风险
symbolic-links=0
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=0
max_allowed_packet=16M
# 设置时区
default-time_zone='+8:00'
# binlog 配置
binlog_format="ROW"
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 90
max-binlog-size = 500M
# server-id 配置
server-id = 2
###### [client]配置模块 ######
[client]
default-character-set=utf8mb4

4、启动主备

docker-compose up -d

5、配置主从同步

登录192.168.123.78操作

[root@node1 mysql]# docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS     NAMES
badaf8b53ced   mysql:8.0.26   "docker-entrypoint.s…"   25 minutes ago   Up 17 minutes             mysql
[root@node1 mysql]# docker exec -it mysql /bin/bash
root@node1:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

登录192.168.123.82操作

[root@node2 ~]# docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS     NAMES
f268cd67cbba   mysql:8.0.26   "docker-entrypoint.s…"   23 minutes ago   Up 23 minutes             mysql
[root@node2 ~]# docker exec -it mysql /bin/bash
root@node2:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6、配置主从同步

根据mysql-s 的master 状态拼接mysql-m 设置 slave的命令：（配置82为主节点）

mysql> CHANGE MASTER TO master_host = '192.168.123.82',
    ->  master_port = 3306,
    ->  master_user = 'root',
    ->  master_password = '123456',
    ->  master_log_file = 'mysql-bin.000003',
    ->  master_log_pos = 156;
Query OK, 0 rows affected, 9 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

根据mysql-m 的master 状态拼接mysql-s 设置 slave的命令：（配置78为主节点）

mysql> CHANGE MASTER TO master_host = '192.168.123.78',
    ->  master_port = 3306,
    ->  master_user = 'root',
    ->  master_password = '123456',
    ->  master_log_file = 'mysql-bin.000005',
    ->  master_log_pos = 156;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

7、查看主从状态

show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.123.82
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 156
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 156
              Relay_Log_Space: 533
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 441b96ad-0052-11ee-85ac-000c29dbe8d4
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)