docker安装mysql-主从

要先配置docker-compose，根据docker-compose配置主从

sudo curl -L https://github.com/docker/compose/releases/download/1.17.1/docker-compose-`uname -s`-`uname -m` > /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose

1.新建配置文件

• 创建mysql主从库配置目录

mkdir -p /docker-data/mysql/master/conf
mkdir -p /docker-data/mysql/slave/conf

• 在/docker-data/mysql/master/conf下，创建my.cnf配置文件

[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 配置大小写不敏感
lower_case_table_names=1
## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能（关键）
log-bin=mysql-bin

• 在/docker-data/mysql/slave/conf下，创建my.cnf配置文件

[client]
port=3306
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 配置大小写不敏感
lower_case_table_names=1
## 同一局域网内注意要唯一
server-id=101 

2.使用docker-compose来管理mysql主从库

• 在/docker-data/mysql下创建docker-compose.yaml

vi docker-compose.yml

version: '3'
services:
  mysql-slave:
    image: mysql:5.7
    depends_on:
      - mysql-master
    links:
      - mysql-master
    volumes:
      - /docker-data/mysql/slave/data:/var/lib/mysql
      - /docker-data/mysql/slave/conf:/etc/mysql
      - /docker-data/mysql/slave/log:/var/log/mysql
    ports:
         - "3308:3306"
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    container_name: mysql-slave
  mysql-master:
    image: mysql:5.7
    volumes:
      - /docker-data/mysql/master/data:/var/lib/mysql
      - /docker-data/mysql/master/conf:/etc/mysql
      - /docker-data/mysql/master/log:/var/log/mysql
    ports:
         - "3307:3306"
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    container_name: mysql-master

• 启动compose来启动mysql

docker-compose up -d

3.用客户端登录

• 操作主数据库配置
  ip+3307
  root
  root

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

SHOW MASTER STATUS ;

image.png

• 操作从数据库
  ip+3308
  root
  root
  从数据库配置，连接从库配置从库同步连接（指定连接的ip/port/user/passwd以及上图中show master status命令得到的file和position值）

CHANGE MASTER TO MASTER_HOST='172.16.223.132',MASTER_PORT=3307,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=609;
#启动同步进程
START SLAVE;

4.测试结果状态

• 主库查询进程：SHOW PROCESSLIST

  
  image.png

• 从库查询进程：SHOW PROCESSLIST

  
  image.png