【MySQL】一主一从复制搭建

一、基本步骤

1. 主从节点能够相互ping通

2. 主节点开启bin-log

3. 主从节点配置不同的server-id

4. 主节点用于复制的用户具有REPLICATION SLAVE权限

5. 从节点开启复制

二、docker-compose

2.1 目录

mysql/
|--master/
   |--conf/
      |--conf.d/
         |--my.cnf
|--slave/
   |--conf/
      |--conf.d/
         |--my.cnf
|--docker-compose.yml

2.2 master 配置文件 my.cnf：

[mysqld]
server-id=1
log-bin=mysql-bin
lower_case_table_names=1

2.3 slave 配置文件 my.cnf：

[mysqld]
server-id=2
log-bin=mysql-bin
lower_case_table_names=1

2.4 docker-compose.yml：

version: '3.8'

services:
  master:
    image: mysql
    privileged: true
    ports:
      - 3333:3306
    volumes:
      - ./master/conf/conf.d/:/etc/mysql/conf.d/
    environment:
      MYSQL_ROOT_PASSWORD: root
  slave:
    image: mysql
    privileged: true
    ports:
      - 4444:3306
    volumes:
      - ./slave/conf/conf.d/:/etc/mysql/conf.d/
    environment:
      MYSQL_ROOT_PASSWORD: root

三、配置复制

3.1 master

-- 创建拥有复制权限的用户
CREATE USER 'slave' @'%' IDENTIFIED BY 'slave';
GRANT REPLICATION SLAVE ON *.* TO 'slave' @'%';

SHOW MASTER STATUS;

3.2 slave

用主节点的服务名代替其 HOST:

CHANGE MASTER TO MASTER_HOST = 'master',
                 MASTER_USER = 'slave',
                 MASTER_PASSWORD = 'slave',
                 MASTER_LOG_FILE = 'mysql-bin.000003',
                 MASTER_LOG_POS = 1403,
                 GET_MASTER_PUBLIC_KEY = 1;

START SLAVE;
SHOW SLAVE STATUS;

四、注意事项

MySQL8 默认的密码认证插件为caching_sha2_password，主从复制时如果不使用安全连接，则必须指定 MASTER_PUBLIC_KEY_PATH或 GET_MASTER_PUBLIC_KEY选项在该 CHANGE MASTER TO语句中启用基于RSA密钥对的密码交换。（文档https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slaveinit.html）

或者使用mysql_native_password密码插件创建用户：

CREATE USER 'slave' @'%' IDENTIFIED WITH mysql_native_password BY 'slave';