Docker MySQL 主从复制

主从复制

* docker-compose.yml 文件

version: '3.3'

services:
  mysql-master:
    image: mysql
    container_name: mysql-master
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: test
    networks:
      - mysql-network
    command: [
      "--server-id=1",
      "--log-bin=mysql-bin",
      "--binlog_expire_logs_seconds=86400",
      "--authentication_policy=mysql_native_password"
    ]


  mysql-replica:
    image: mysql
    container_name: mysql-replica
    ports:
      - "3308:3306"
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: test
    networks:
      - mysql-network
    command: [
      "--server-id=2",
      "--binlog_expire_logs_seconds=86400",
      "--authentication_policy=mysql_native_password"
    ]


networks:
  mysql-network:
    driver: bridge

-- 在主库中注册用于复制的用户 replica 并授予相关的权限

CREATE
USER 'replica'@'%' IDENTIFIED BY 'replica_password';

GRANT REPLICATION
SLAVE ON *.* TO 'replica'@'%';

FLUSH PRIVILEGES;

-- 查询当前主库的 File 和 Position 信息并记录，这里分别是 mysql-bin.000004 和 844

SHOW MASTER STATUS;

-- 在从库中执行以下命令，MASTER_LOG_FILE 和 MASTER_LOG_POS 填入上文查询到的信息

CHANGE
MASTER TO MASTER_HOST='mysql-master', 
MASTER_USER='replica',
MASTER_PASSWORD='replica_password', 
MASTER_LOG_FILE='mysql-bin.000004', 
MASTER_LOG_POS=844, MASTER_SSL=0;

-- 在从库中开启主从复制

START SLAVE;

-- 若显示的信息中无 Error 文本则表明配置成功
SHOW SLAVE STATUS\G;

-- 用于验证 DDL
CREATE TABLE user
(
    user_id                 INT AUTO_INCREMENT PRIMARY KEY,
    username                VARCHAR(50)  NOT NULL UNIQUE,
    password                VARCHAR(255) NOT NULL,
    account_non_expired     BOOLEAN      NOT NULL DEFAULT FALSE,
    account_non_locked      BOOLEAN      NOT NULL DEFAULT FALSE,
    credentials_non_expired BOOLEAN      NOT NULL DEFAULT FALSE,
    enabled                 BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE TABLE role
(
    role_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    name    VARCHAR(25)        NOT NULL DEFAULT ''
);

CREATE TABLE authority
(
    authority_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    name         VARCHAR(25)        NOT NULL DEFAULT ''
);

CREATE TABLE user_role
(
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES role (role_id) ON DELETE CASCADE
);

CREATE TABLE role_authority
(
    role_id      INT NOT NULL,
    authority_id INT NOT NULL,
    PRIMARY KEY (role_id, authority_id),
    FOREIGN KEY (role_id) REFERENCES role (role_id) ON DELETE CASCADE,
    FOREIGN KEY (authority_id) REFERENCES authority (authority_id) ON DELETE CASCADE
);

* 可能会用到的命令
    SET GLOBAL super_read_only = ON;
    SET GLOBAL default_storage_engine='MyISAM';