Docker(九): 安装MySQL主从复制

Docker安装MySQL主从复制

Docker(三)：Docker安装MySQL

编辑docker-compose

创建mysql-ms-docker-compose.yml

version: '3'
services:
  mysql:
    container_name: mysql
    image: mysql:5.7
    hostname: master
    ports:
      - "3306:3306"
    networks:
      - mysql-network
    volumes:
      - /mydata/mysql/log:/var/log/mysql
      - /mydata/mysql/data:/var/lib/mysql
      - /mydata/mysql/conf:/etc/mysql
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - COMPOSE_PROJECT_NAME=mysql-server

  mysql-slave:
    container_name: mysql-slave
    image: mysql:5.7
    hostname: slave
    ports:
      - "3307:3306"
    networks:
      - mysql-network
    volumes:
      - /mydata/mysql-slave/log:/var/log/mysql
      - /mydata/mysql-slave/data:/var/lib/mysql
      - /mydata/mysql-slave/conf:/etc/mysql
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - COMPOSE_PROJECT_NAME=mysql-serverps

networks:
  mysql-network:
    driver: bridge

修改主配置文件

添加server-id=3306, 唯一值。开启binlog， log-bin=mysql-bin

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
server-id=3306
log-bin=mysql-bin
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
default-time-zone='+08:00'

修改从配置文件

添加server-id=3307， 唯一值。

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
server-id=3307
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
default-time-zone='+08:00'

启动mysql

docker-compose -f mysql-ms-docker-compose.yml up -d

主从关联

创建从库访问主库的用户并授权

主库中执行sql脚本

create user "slave_admin"@"%" identified by "123456";
grant replication slave on *.* to 'slave_admin'@'%' IDENTIFIED BY '123456' with grant option ;
flush privileges;

查看master状态

show master status;

配置slave

从库执行脚本， master_log_file对映上图File, master_log_pos对映上图Position

change master to master_host = 'master', master_port = 3306, master_user ='slave_admin', master_password = '123456', master_log_file  = 'mysql-bin.000003', master_log_pos = 903;
start slave ;

查看slave状态

show slave status;

错误排查

Slave_IO_Running != YES, 检查从库是否可以连接到主库及master_host = 'master', master_port = 3306, master_user ='slave_admin', master_password = '123456'是否正确。

Slave_SQL_Running != YES， 检查master_log_file = 'mysql-bin.000003', master_log_pos = 903配置是否正确。