MySQL主从复制

环境准备

docker-compose.yml

第一步：

version: "3"

services:

  mysql-master:

    image: mysql:5.7.22

    restart: always

    hostname: mysql-master

    container_name: mysql_master

    ports:

      - 3316:3306

    environment:

      MYSQL_ROOT_PASSWORD: root

      TZ: Asia/Shanghai

    volumes:

      - ./master/mysql/data:/var/lib/mysql

      - ./master/mysql/log:/var/log/mysql

    command:

      --server_id=100

      --log-bin=mysql-master-bin

      --sync_binlog=1

      --binlog-ignore-db=mysql

      --binlog_format=mixed

      --expire_logs_days=7

      --default-authentication-plugin=mysql_native_password

      --character-set-server=utf8mb4

      --collation-server=utf8mb4_general_ci

      --explicit_defaults_for_timestamp=true

      --lower_case_table_names=1

    links:

      -  mysql-slave-read

  mysql-slave-read:

    image: mysql:5.7.22

    restart: always

    hostname: mysql-slave-read

    container_name: mysql-slave-read

    ports:

      - 3317:3306

    environment:

      MYSQL_ROOT_PASSWORD: root

      TZ: Asia/Shanghai

    volumes:

      - ./slave/mysql/data:/var/lib/mysql

      - ./slave/mysql/log:/var/log/mysql

    command:

      --server_id=200

      --read_only=1

      --binlog-ignore-db=mysql

      --default-authentication-plugin=mysql_native_password

      --character-set-server=utf8mb4

      --collation-server=utf8mb4_general_ci

      --explicit_defaults_for_timestamp=true

      --lower_case_table_names=1

  mysql-slave-back:

    image: mysql:5.7.22

    restart: always

    hostname: mysql-slave-back

    container_name: mysql-slave-back

    ports:

      - 3318:3306

    environment:

      MYSQL_ROOT_PASSWORD: root

      TZ: Asia/Shanghai

    volumes:

      - ./slave-back/mysql/data:/var/lib/mysql

      - ./slave-back/mysql/log:/var/log/mysql

    command:

      --server_id=300

      --log-bin=slave-back-bin

      --default-authentication-plugin=mysql_native_password

      --character-set-server=utf8mb4

      --collation-server=utf8mb4_general_ci

      --explicit_defaults_for_timestamp=true

      --lower_case_table_names=1


//第二步：
FLUSH TABLES WITH READ LOCK;    //锁表


//第三步：
show master status   

//查看状态  记下 Position 和 File 的值，等下进行从库配置要用到

| File                    | Position |

| mysql-master-bin.000003 | 154      |

//第四步：到从表里面
a.

STOP SLAVE

b.

CHANGE MASTER TO

    MASTER_HOST='mysql-master',

MASTER_USER='root',

    MASTER_PASSWORD='root',

    MASTER_LOG_FILE='mysql-master-bin.000003',      //这里用到上面说到的

    MASTER_LOG_POS=154;    //这里用到上面说到的

c.

start slave

d.

show slave status

//最后一步，解锁
UNLOCK TABLE