Docker 中部署 MySQL

一、单节点

1、文件

1.1 Docker Compose 文件

name: mysql-single-demo

networks:
  mysql-single-net:
    name: mysql-single-net
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 172.33.0.0/24
          ip_range: 172.33.0.0/24
          gateway: 172.33.0.1

volumes:
  mysql-single-volume:
    name: mysql-single-demo-volume

services:
  mysql-single:
    image: ${image}
    restart: ${restart}
    container_name: ${container_name}-1
    hostname: ${host_name}-1
    ports:
      - 3306:3306
    env_file:
      - ./mysql-single.env
    networks:
      mysql-single-net:
        ipv4_address: 172.33.0.11
    volumes:
      - mysql-single-volume:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

1.2 `.env` 文件

image=mysql:9.2.0-oraclelinux9
container_name=mysql
host_name=mysql
restart=unless-stopped
# 宿主机 IP
host_ip=192.168.99.99

1.3 环境变量文件

1	MYSQL_ROOT_PASSWORD=yourpassword

1.4 初始化 SQL 文件

CREATE USER 'z2huo'@'%' IDENTIFIED BY 'z2huo@2024';

grant all privileges on *.* to 'z2huo'@'%';

-- 刷新权限
flush privileges;

2、启动容器与验证

根据 Docker Compose 创建 MySQL 单节点容器并启动。

1	$ docker compose up -d

进入容器，查看自定义用户 z2huo 是否创建成功。

1	$ docker exec -it mysql-single /bin/bash

$ mysql -uroot -p
mysql> SELECT User, Host FROM mysql.user;
+----------------+---------+  
|User            |Host     |  
+----------------+---------+  
|root            |%        |  
|sync_user       |%        |  
|z2huo           |%        |  
|mysql.infoschema|localhost|  
|mysql.session   |localhost|  
|mysql.sys       |localhost|  
|root            |localhost|  
+----------------+---------+

二、主从复制

MySQL Replication 是 MySQL 一个非常重要的功能，主要用于主服务器和从服务期之间的数据复制操作。

MySQL 从 3.25.15 版本开始提供数据库复制（replication）功能。MySQL 复制是指从一个 MySQL 主服务器（master）将数据复制到另一台或多台 MySQL 从服务器（slaves）的过程，将主数据库的 DDL 和 DML 操作通过二进制日志传到从服务器上，然后在从服务器上对这些日志重新执行，从而使得主从服务器的数据保持同步。

在 MySQL 中，复制操作是异步进行的，slaves 服务器不需要持续地保持连接接收 master 服务器的数据。

MySQL 支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可以作为其他从服务器的主服务器，如果 MySQL 主服务器访问量比较大，可以通过复制数据，然后在从服务器上进行查询操作，从而降低主服务器的访问压力，同时从服务器作为主服务器的备份，可以避免主服务器因为故障数据丢失的问题。

MySQL 数据库复制操作大致可以分成 3 个步骤：

主服务器将数据的改变记录到二进制日志（binary log）中。
从服务器将主服务器的 binary log events 复制到它的中继日志（relay log）中。
从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步。

首先，主服务器会记录二进制日志，每个事务更新数据完成之前，主服务器将这些操作的信息记录在二进制日志里面，在事件写入二进制日志完成后，主服务器通知存储引擎提交事务。

Slave 上面的 I/O 进程连接上 Master，并发出日志请求，Master 接收到来自 Slave 的 IO 进程的请求后，根据请求信息添加位置信息后，返回给 Slave 的 IO 进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到 Master 端的 bin-log 文件的名称以及 bin-log 的位置。

Slave 的 I/O 进程接收到信息后，将接收到的日志内容依次添加到 Slave 端的 relay-log 文件的最末端，并将读取到 Master 端的 bin-log 文件名和位置记录到 master-info 文件中。

Slave 的 Sql 进程检测到 relay-log 中新增加了内容后，会马上解析 relay-log 的内容成为在 Master 端真实执行时的那些可执行内容，并在自身执行。

MySQL 复制环境 90%以上都是一个 Master 带一个或者多个 Slave 的架构模式。如果 Master 和 Slave 的压力不是太大的话，异步复制的延时一般都很少。尤其是 Slave 端的复制方式改成两个进程处理之后，更是减小了 slave 端的延时。

对于数据实时性要求不是特别严格的应用，只需要通过廉价的电脑服务器来扩展 Slave 的数量，将读压力分散到多台 Slave 的机器上面，即可解决数据库端的读压力瓶颈。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈

1、文件

1.1 Docker Compose 文件

name: mysql-master-slave-demo

networks:
  mysql-master-slave-net:
    name: mysql-master-slave-net
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 172.33.1.0/24
          ip_range: 172.33.1.0/24
          gateway: 172.33.1.1

volumes:
  mysql-master-volume:
    name: mysql-master-slave-demo-master-volume
  mysql-slave-volume-1:
    name: mysql-master-slave-demo-slave-volume-1
  mysql-slave-volume-2:
    name: mysql-master-slave-demo-slave-volume-2

services:
  mysql-master:
    image: ${image}
    restart: ${restart}
    container_name: ${container_name}-master
    hostname: ${host_name}-master
    ports:
      - 3300:3306
    env_file:
      - ./env/mysql-master.env
    networks:
      mysql-master-slave-net:
        ipv4_address: 172.33.1.11
    volumes:
      - mysql-master-volume:/var/lib/mysql
      - ./init-sql/master-init.sql:/docker-entrypoint-initdb.d/init.sql
      - ./conf/master.cnf:/etc/mysql/conf.d/my.cnf
    entrypoint: []
    command: ["sh", "-c", "chmod 644 /etc/mysql/conf.d/my.cnf && exec docker-entrypoint.sh mysqld"]
  
  mysql-slave-1:
    image: ${image}
    restart: ${restart}
    container_name: ${container_name}-slave-1
    hostname: ${host_name}-slave-1
    ports:
      - 3301:3306
    env_file:
      - ./env/mysql-slave.env
    networks:
      mysql-master-slave-net:
        ipv4_address: 172.33.1.21
    volumes:
      - mysql-slave-volume-1:/var/lib/mysql
      - ./init-sql/slave-init.sql:/docker-entrypoint-initdb.d/init.sql
      - ./conf/slave-1.cnf:/etc/mysql/conf.d/my.cnf
    depends_on:
      - mysql-master
    entrypoint: []
    command: ["sh", "-c", "chmod 644 /etc/mysql/conf.d/my.cnf && exec docker-entrypoint.sh mysqld"]

  mysql-slave-2:
    image: ${image}
    restart: ${restart}
    container_name: ${container_name}-slave-2
    hostname: ${host_name}-slave-2
    ports:
      - 3302:3306
    env_file:
      - ./env/mysql-slave.env
    networks:
      mysql-master-slave-net:
        ipv4_address: 172.33.1.22
    volumes:
      - mysql-slave-volume-2:/var/lib/mysql
      - ./init-sql/slave-init.sql:/docker-entrypoint-initdb.d/init.sql
      - ./conf/slave-2.cnf:/etc/mysql/conf.d/my.cnf
    depends_on:
      - mysql-master
    entrypoint: []
    command: ["sh", "-c", "chmod 644 /etc/mysql/conf.d/my.cnf && exec docker-entrypoint.sh mysqld"]

1.2 `.env` 文件

image=mysql:9.2.0-oraclelinux9
container_name=mysql
host_name=mysql
restart=unless-stopped
# 宿主机 IP
host_ip=192.168.99.99

1.3 环境变量文件

1.3.1 master 节点

1	MYSQL_ROOT_PASSWORD=yourpassword

1.3.2 slave 节点

1	MYSQL_ROOT_PASSWORD=yourpassword

1.4 初始化 SQL 文件

1.4.1 master 节点

CREATE USER 'z2huo'@'%' IDENTIFIED BY 'z2huo@2024';
grant all privileges on *.* to 'z2huo'@'%';

-- 主从复制账号
CREATE USER 'sync_user'@'%' IDENTIFIED BY 'z2huo@2024';
grant replication slave, replication client on *.* to 'sync_user'@'%';

flush privileges;

1.4.2 slave 节点

CREATE USER 'z2huo'@'%' IDENTIFIED BY 'z2huo@2024';
grant all privileges on *.* to 'z2huo'@'%';

stop replica;

change replication source to
    source_host='mysql-master',
    source_user='sync_user',
    source_password='z2huo@2024',
    source_port=3306,
    source_ssl=1,
    source_log_file='binlog.000002',
    source_log_pos=158;

start replica;

slave 节点初始化 SQL 文件中的 source_log_file 和 source_log_pos 需要在启动主节点之后，查询主节点 binlog 文件之后修改。

1.5 MySQL 配置文件

1.5.1 master 节点

1 2	[mysqld] server_id = 11

1.5.2 slave 节点

1 2	[mysqld] server_id = 21

1 2	[mysqld] server_id = 22

1.6 shell 脚本

1
2
3

#!/bin/bash
set -e
chmod 644 /etc/mysql/conf.d/my.cnf

2、启动容器与验证

2.1 启动主节点

使用如下命令启动主节点

1	$ docker compose up -d mysql-master

进入主节点，查看主节点状态：

1	$ docker exec -it mysql-master /bin/bash

$ mysql -uroot -p
mysql> SELECT User, Host FROM mysql.user;
+----------------+---------+  
|User            |Host     |  
+----------------+---------+  
|root            |%        |  
|sync_user       |%        |  
|z2huo           |%        |  
|mysql.infoschema|localhost|  
|mysql.session   |localhost|  
|mysql.sys       |localhost|  
|root            |localhost|  
+----------------+---------+

使用如下命令查看主节点 binlog 状态：

mysql> show binary log status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      158 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记住上面的 File 和 Position，在修改 slave-init.sql 时需要用到。

2.2 启动从节点

1	$ docker compose up -d mysql-slave-1 mysql-slave-2

启动两个从节点容器后，从节点日志输出如下：

2025-04-14 01:10:32 2025-04-13 17:10:32+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 9.2.0-1.el9 started.
2025-04-14 01:10:32 2025-04-13 17:10:32+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2025-04-14 01:10:32 2025-04-13 17:10:32+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 9.2.0-1.el9 started.
2025-04-14 01:10:32 2025-04-13 17:10:32+00:00 [Note] [Entrypoint]: Initializing database files
2025-04-14 01:10:34 2025-04-13 17:10:34+00:00 [Note] [Entrypoint]: Database files initialized
2025-04-14 01:10:34 2025-04-13 17:10:34+00:00 [Note] [Entrypoint]: Starting temporary server
2025-04-14 01:10:35 2025-04-13 17:10:35+00:00 [Note] [Entrypoint]: Temporary server started.
2025-04-14 01:10:35 '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2025-04-14 01:10:36 
2025-04-14 01:10:36 2025-04-13 17:10:36+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
2025-04-14 01:10:36 
2025-04-14 01:10:36 
2025-04-14 01:10:36 2025-04-13 17:10:36+00:00 [Note] [Entrypoint]: Stopping temporary server
2025-04-14 01:10:38 2025-04-13 17:10:38+00:00 [Note] [Entrypoint]: Temporary server stopped
2025-04-14 01:10:38 
2025-04-14 01:10:38 2025-04-13 17:10:38+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
2025-04-14 01:10:38 
2025-04-14 01:10:32 2025-04-13T17:10:32.829404Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2025-04-14 01:10:32 2025-04-13T17:10:32.830098Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 9.2.0) initializing of server in progress as process 79
2025-04-14 01:10:32 2025-04-13T17:10:32.832825Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-14 01:10:32 2025-04-13T17:10:32.914388Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-14 01:10:33 2025-04-13T17:10:33.415821Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2025-04-14 01:10:34 2025-04-13T17:10:34.698740Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.
2025-04-14 01:10:34 2025-04-13T17:10:34.734155Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2025-04-14 01:10:34 2025-04-13T17:10:34.878050Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 9.2.0) starting as process 120
2025-04-14 01:10:34 2025-04-13T17:10:34.885613Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-14 01:10:34 2025-04-13T17:10:34.969908Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-14 01:10:35 2025-04-13T17:10:35.105640Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2025-04-14 01:10:35 2025-04-13T17:10:35.105660Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2025-04-14 01:10:35 2025-04-13T17:10:35.107053Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2025-04-14 01:10:35 2025-04-13T17:10:35.117643Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2025-04-14 01:10:35 2025-04-13T17:10:35.117699Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '9.2.0'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
2025-04-14 01:10:35 Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
2025-04-14 01:10:35 Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
2025-04-14 01:10:35 Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
2025-04-14 01:10:36 Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
2025-04-14 01:10:36 Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
2025-04-14 01:10:36 Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
2025-04-14 01:10:36 2025-04-13T17:10:36.242466Z 11 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a replica and has his hostname changed!! Please use '--relay-log=mysql-slave-1-relay-bin' to avoid this problem.
2025-04-14 01:10:36 2025-04-13T17:10:36.244511Z 11 [System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL '' executed'. Previous state source_host='', source_port= 3306, source_log_file='', source_log_pos= 4, source_bind=''. New state source_host='mysql-master', source_port= 3306, source_log_file='binlog.000002', source_log_pos= 158, source_bind=''.
2025-04-14 01:10:36 2025-04-13T17:10:36.250291Z 12 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2025-04-14 01:10:36 2025-04-13T17:10:36.259416Z 12 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'sync_user@mysql-master:3306' with server_uuid=7c1315d5-1889-11f0-8558-5ebe6b5171aa, server_id=1. Starting replication from file 'binlog.000002', position '158'.
2025-04-14 01:10:36 2025-04-13T17:10:36.265450Z 18 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 9.2.0).
2025-04-14 01:10:37 2025-04-13T17:10:37.538090Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 9.2.0)  MySQL Community Server - GPL.
2025-04-14 01:10:37 2025-04-13T17:10:37.538106Z 0 [System] [MY-015016] [Server] MySQL Server - end.
2025-04-14 01:10:38 2025-04-13T17:10:38.274697Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2025-04-14 01:10:38 2025-04-13T17:10:38.406387Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 9.2.0) starting as process 1
2025-04-14 01:10:38 2025-04-13T17:10:38.408957Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-04-14 01:10:38 2025-04-13T17:10:38.466741Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-04-14 01:10:38 2025-04-13T17:10:38.595740Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2025-04-14 01:10:38 2025-04-13T17:10:38.595758Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2025-04-14 01:10:38 2025-04-13T17:10:38.597260Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2025-04-14 01:10:38 2025-04-13T17:10:38.607365Z 0 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a replica and has his hostname changed!! Please use '--relay-log=mysql-slave-1-relay-bin' to avoid this problem.
2025-04-14 01:10:38 2025-04-13T17:10:38.609950Z 5 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2025-04-14 01:10:38 2025-04-13T17:10:38.612440Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2025-04-14 01:10:38 2025-04-13T17:10:38.612482Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '9.2.0'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2025-04-14 01:10:38 2025-04-13T17:10:38.618029Z 5 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'sync_user@mysql-master:3306' with server_uuid=7c1315d5-1889-11f0-8558-5ebe6b5171aa, server_id=1. Starting replication from file 'binlog.000002', position '158'.

进入从节点容器：

1	$ docker exec -it mysql-slave-1 /bin/bash

1	$ mysql -uroot -p

使用 show replica status \G; 查询从节点在状态，从查询结果中可以看到在启动容器之前在从节点初始化 SQL 中指定的 binlog 文件 binlog.000002 和开始复制位置 158：

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: mysql-master
                  Source_User: sync_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 158
               Relay_Log_File: mysql-slave-1-relay-bin.000004
                Relay_Log_Pos: 325
        Relay_Source_Log_File: binlog.000002
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 158
              Relay_Log_Space: 892
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: Yes
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: 7c1315d5-1889-11f0-8558-5ebe6b5171aa
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

主节点执行如下语句创建数据库：

create database if not exists demo  
character set utf8mb4  
collate utf8mb4_unicode_ci;  
;

再次查看从节点状态，可以看到日志位置发生了变化，由 158 变为了 448：

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: mysql-master
                  Source_User: sync_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 448
               Relay_Log_File: mysql-slave-1-relay-bin.000004
                Relay_Log_Pos: 615
        Relay_Source_Log_File: binlog.000002
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 448
              Relay_Log_Space: 1182
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: Yes
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: 7c1315d5-1889-11f0-8558-5ebe6b5171aa
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

之后在主节点创建测试表，并向表中插入测试数据，可以看到从节点创建了测试表，并成功插入了测试数据：

create table if not exists demo.z2huo_user
(
    id              bigint                               not null comment '主键'
        primary key,
    user_code       varchar(8)                           null comment '用户代码',
    user_name       varchar(256)                         null comment '用户名称',
    valid_date      date                                 null comment '失效日期',
    invalid_date    date                                 null,
    valid_flag      varchar(1) default '1'               null comment '有效状态 1有效 0无效',
    delete_flag     varchar(1) default '0'               null comment '删除状态 1已删除 0未删除',
    company_code    varchar(8)                           null comment '失效日期',
    department_code varchar(8)                           null comment '部门代码',
    create_time     timestamp  default CURRENT_TIMESTAMP null comment '创建时间',
    operate_time    timestamp  default CURRENT_TIMESTAMP null comment '最后操作时间',
    create_by_code  varchar(8)                           null comment '创建人代码',
    operate_by_code varchar(8)                           null comment '最后操作人代码',
    constraint uk_z2huo_user_code_name
        unique (user_code)
)
    comment '用户表';

INSERT INTO z2huo_user (id, create_time, operate_time, create_by_code, operate_by_code, user_code, user_name, valid_date, invalid_date, valid_flag, delete_flag, company_code, department_code) VALUES (1779819194540101633, '2024-04-15 18:28:58', '2024-04-15 18:28:58', null, null, '71403117', 'test user 70c8069979164bd78e2a18b7e0f57a83', '2020-01-01', '2077-01-01', '1', '0', '', null);

三、其他问题

1、从节点安全连接问题

启动从节点容器时，有日志输出如下：

Replica I/O for channel '': Error connecting to source 'sync_user@mysql-master-1:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

上面的日志表明，从库（Replica）在尝试连接到主库（Source）时，由于 caching_sha2_password 认证插件要求安全连接（SSL/TLS 或 RSA 密钥交换），但当前连接未满足安全要求，导致认证失败（错误码 MY-002061）。

所以需要从库配置 SSL 连接：

change replication source to
    source_host='mysql-master',
    source_user='sync_user',
    source_password='z2huo@2024',
    source_port=3306,
    source_ssl=1,
    source_log_file='binlog.000002',
    source_log_pos=158;

在从节点初始化 SQL 文件中由 source_ssl=1 配置启用 SSL。

2、MySQL 版本导致的 SQL 变化

MySQL 8.0 和之前版本中，主从复制的一些命令会发生变化。

stop replica;

change replication source to  
    source_host='mysql-master-1',  
    source_user='sync_user',  
    source_password='z2huo@2024',  
    source_port=3306,  
    source_ssl=1,  
    source_log_file='binlog.000002',  
    source_log_pos=158;

start replica;

show binary log status;

show replica status;

上面的 SQL 为使用 9.2 版本时使用的 SQL。下面的 SQL 为 8.0 前版本的替代：

stop slave;

change master to  
    master_host='mysql-master-1',  
    master_user='sync_user',  
    master_password='z2huo@2024',  
    master_port=3306,  
    MASTER_LOG_FILE='mysql-bin.000001',  
    MASTER_LOG_POS=154;
    
start slave;

show master status;

show slave status;

3、MySQL 在 Windows 上和 Linux 上配置文件的不同

在 Windows 上面部署 MySQL 主节点时，出现以下错误：

1 2	$ mysql -uroot -p mysql: [Warning] World-writable config file '/etc/mysql/conf.d/my.cnf' is ignored.

挂载的配置文件被忽略了。

进入容器中查看文件：

$ cd /etc/mysql/conf.d/
$ ls -l
total 0
-rwxrwxrwx 1 root root 24 Apr 14 02:52 my.cnf

查看配置文件权限为 777，当配置文件的权限为全局可写权限（如777）允许任何用户修改文件，存在安全风险，MySQL会强制忽略此类配置文件。

1
2
3

$ chmod 644 /etc/mysql/conf.d/my.cnf

$ chmod 777 /etc/mysql/conf.d/my.cnf

3.1 通过 Shell 脚本修改文件权限

可以在容器启动之后进入容器手动修改权限，也可以通过挂载 shell 文件来执行。

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

在 /docker-entrypoint-initdb.d/ 目录下挂载 Shell 文件来修改文件权限。

1
2
3

#!/bin/bash
set -e
chmod 644 /etc/mysql/conf.d/my.cnf

对应的 Docker Compose 文件如下，Compose 中的挂载 shell 脚本

volumes:
    - mysql-master-volume:/var/lib/mysql
    - ./init-sql/master-init.sql:/docker-entrypoint-initdb.d/init.sql
    - ./conf/master.cnf:/etc/mysql/conf.d/my.cnf
    - ./shell/chmod-mysql-conf.sh:/docker-entrypoint-initdb.d/chmod-mysql-conf.sh

3.2 容器启动后执行命令

通过挂载 Shell 脚本的方式有一点问题，docker-entrypoint-initdb.d 目录下面的文件可以是 SQL 文件也可以是 Shell 文件，主节点会同时执行 SQL 和 Shell 文件，日志中会输出两个都执行成功。但是从节点，只执行了 Shell 脚本，没有执行 SQL 文件，不知道为什么。

docker-entrypoint-initdb.d 文件夹下初始化脚本的执行的功能来自于 Docker 镜像中 docker-entrypoint.sh 文件，具体方法如下：

# usage: docker_process_init_files [file [file [...]]]
#    ie: docker_process_init_files /always-initdb.d/*
# process initializer files, based on file extensions
docker_process_init_files() {
    # mysql here for backwards compatibility "${mysql[@]}"
    mysql=( docker_process_sql )

    echo
    local f
    for f; do
        case "$f" in
            *.sh)
                # https://github.com/docker-library/postgres/issues/450#issuecomment-393167936
                # https://github.com/docker-library/postgres/pull/452
                if [ -x "$f" ]; then
                    mysql_note "$0: running $f"
                    "$f"
                else
                    mysql_note "$0: sourcing $f"
                    . "$f"
                fi
                ;;
            *.sql)     mysql_note "$0: running $f"; docker_process_sql < "$f"; echo ;;
            *.sql.bz2) mysql_note "$0: running $f"; bunzip2 -c "$f" | docker_process_sql; echo ;;
            *.sql.gz)  mysql_note "$0: running $f"; gunzip -c "$f" | docker_process_sql; echo ;;
            *.sql.xz)  mysql_note "$0: running $f"; xzcat "$f" | docker_process_sql; echo ;;
            *.sql.zst) mysql_note "$0: running $f"; zstd -dc "$f" | docker_process_sql; echo ;;
            *)         mysql_warn "$0: ignoring $f" ;;
        esac
        echo
    done
}

但不知道为什么没有同时执行 SQL 和 Shell，研究不明白，Shell 也不是很懂，只能看个大概，所以使用命令修改权限，修改 Docker Compose 文件内容如下，在 service 下添加如下配置：

1 2	entrypoint: [] command: ["sh", "-c", "chmod 644 /etc/mysql/conf.d/my.cnf && exec docker-entrypoint.sh mysqld"]

OB links

OB tags

#MySQL #数据库

一、单节点

1、文件

1.1 Docker Compose 文件

1.2 .env 文件

1.3 环境变量文件

1.4 初始化 SQL 文件

2、启动容器与验证

二、主从复制

1、文件

1.1 Docker Compose 文件

1.2 .env 文件

1.3 环境变量文件

1.3.1 master 节点

1.3.2 slave 节点

1.4 初始化 SQL 文件

1.4.1 master 节点

1.4.2 slave 节点

1.5 MySQL 配置文件

1.5.1 master 节点

1.5.2 slave 节点

1.6 shell 脚本

2、启动容器与验证

2.1 启动主节点

2.2 启动从节点

三、其他问题

1、从节点安全连接问题

2、MySQL 版本导致的 SQL 变化

3、MySQL 在 Windows 上和 Linux 上配置文件的不同

3.1 通过 Shell 脚本修改文件权限

3.2 容器启动后执行命令

相关链接

OB links

OB tags

1.2 `.env` 文件

1.2 `.env` 文件