使用Docker创建MariaDB主从复制

准备配置文件:

1.运行一个临时的容器,用于复制配置文件

docker run --name temp -e MYSQL_ROOT_PASSWORD=123456 -d mariadb

2.复制临时容器中的配置文件:

docker cp temp:/etc/mysql/my.cnf ./my.cnf

3.将my.cnf配置文件复制两个配置文件master.cnf 与 slave.cnf

master.cnf添加

[mysqld]
log-bin=mysql-bin
server-id=1
log-bin=/var/lib/mysql/mysql-bin

slave.cnf添加

[mysqld]
server-id=2
relay-log=/var/lib/mysql/relay-bin
relay-log-index=/var/lib/mysql/relay-bin.index

创建网络

docker network create common-network

根据自身的情况创建docker-compose.yml

version: "3"

services:
  mariadb-master:
    image: mariadb
    container_name: mariadb-master
    hostname: mariadb-master
    restart: always
    volumes:
      - $DOCKER_HOME/mariadb/mariadb-master/data:/var/lib/mysql
      - $DOCKER_HOME/mariadb/mariadb-master/logs:/logs
      - $DOCKER_HOME/mariadb/mariadb-master/conf/master.cnf:/etc/mysql/my.cnf
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    ports:
      - 3306:3306
    command:
      --lower_case_table_names=1
  mariadb-slave:
    image: mariadb
    container_name: mariadb-slave
    hostname: mariadb-slave
    restart: always
    volumes:
      - $DOCKER_HOME/mariadb/mariadb-slave/data:/var/lib/mysql
      - $DOCKER_HOME/mariadb/mariadb-slave/logs:/logs
      - $DOCKER_HOME/mariadb/mariadb-slave/conf/slave.cnf:/etc/mysql/my.cnf
    environment:
      MYSQL_ROOT_PASSWORD: 123456
    ports:
      - 3307:3306
    command:
      --lower_case_table_names=1

networks:
  default:
    external:
      name: common-network

确认无误后运行docker-compose.yml

docker-compose up -d

在mster中创建用户

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

查看master状态:

show master status;

image

在slave中与master建立关系

 change master to master_host='mariadb-master',master_port=3306,\
 master_user='repl',master_password='123456',master_log_file='mysql-bin.000005',\
 master_log_pos=0,MASTER_SSL=1;

开启slave

start slave;

查看slave的状态

show slave status;

image

Slave_IO_Running和Slave_SQL_Running 都为Yes则主从复制创建成功

注:如果是Slave_SQL_Running：no

解决办法如下:

MariaDB [(none)]> stop slave;
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G