docker mysql主从配置

• 准备工作

  • centos_1：192.168.168.101
  • centos_2：192.168.168.102

• 通过docker-compose分别安装mysql

创建安装目录

mkdir /opt/mysql -p
mkdir /opt/mysql/data -p
mkdir /opt/mysql/configs -p
cd /opt/mysql

添加docker-compose.yml

vim docker-compose.yml

version: '3.7'
​
services:
  mysql:
    image: mysql:8
    restart: always
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=qwe123
    volumes:
      - ./data:/var/lib/mysql
      - ./configs/my.cnf:/etc/mysql/my.cnf

• centos_1配置

添加mysql配置文件my.cnf

vim configs/my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server-id=1
log-bin = mysql-bin  #开启二进制日志
binlog-do-db = PersonLocation #需要同步的数据库，多个可用逗号分割
binlog-ignore-db = mysql,information_schema,performance_schema
auto-increment-increment = 10
auto-increment-offset = 1
​
# Custom config should go here
!includedir /etc/mysql/conf.d/

启动mysql

docker-compose up -d

• centos_2配置

添加mysql配置文件my.cnf

vim configs/my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server-id=2
log-bin = mysql-bin  #开启二进制日志
binlog-do-db = PersonLocation #需要同步的数据库，多个可用逗号分割
binlog-ignore-db = mysql,information_schema,performance_schema
auto-increment-increment = 10
auto-increment-offset = 2
​
# Custom config should go here
!includedir /etc/mysql/conf.d/

启动mysql

docker-compose up -d

• mysql 配置

分别进入两个mysql容器

docker exec -it mysql_mysql_1 bash

分别登录mysql

mysql -u root -p

分别创建数据库备份用户

CREATE USER 'bak'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

分别为备份用户授权

GRANT REPLICATION SLAVE ON *.* TO 'bak'@'%';

在 centos_1 mysql里查看master状态

show master status;

在 centos_2 mysql里查看master状态

show master status;

在 centos_1 mysql上配置slave

输入centos_2 mysql的IP地址和master状态信息

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.168.102',
    -> MASTER_PORT=3306,
    -> MASTER_USER='bak',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=1117;

开启主从同步，输入命令

 start slave;

查看主从同步状态，输入命令

show slave status\G;

在 centos_2 mysql上配置slave

输入centos_1 mysql的IP地址和master状态信息

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.168.101',
    -> MASTER_PORT=3306,
    -> MASTER_USER='bak',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=1117;

开启主从同步，输入命令

 start slave;

查看主从同步状态，输入命令

show slave status\G;