docker下的mysql主从复制初探

记录一下自己配置调试的过程

使用的环境及工具

• docker-compose,docker-ce
• navcat
• vm,ubuntu_server-14.04,mysql-5.7.25

步奏

• 拉取mysql镜像

docker pull mysql=5.7

• 使用docker-compose.yml 编排 容器， 我的配置如下

version: '3'
services:
    db_m:
      image: mysql:5.7
      container_name: master
      ports:
        - "3310:3306"
      environment:
        MYSQL_ROOT_PASSWORD: future
        MYSQL_DATABASE: my_test
        LANG: C.UTF-8
      volumes:
        - ./data/master:/var/lib/mysql
        - ./config/master:/etc/mysql/mysql.conf.d/

      command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci']
      #restart:always
    db_s:
      image: mysql:5.7
      container_name: slave
      ports:
        - "3311:3306"
      environment:
        MYSQL_ROOT_PASSWORD: future
        MYSQL_DATABASE: my_test
        LANG: C.UTF-8
      volumes:
        - ./data/slave:/var/lib/mysql
        - ./config/slave:/etc/mysql/mysql.conf.d/
      command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci']
      #restart:always

目的是创建一主一从两个mysql的容器，将mysql端口分别带出来，设置好root用户的密码以及字符格式，最重要的是 将mysql的data数据映射出来，再映射一个配置文件的目录

• 创建容器

docker-compose up -d

• 容器创建好了之后就要配置2个mysql，我的配置如下

主：

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# my settings
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,
NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
server-id = 100
log-bin=/var/lib/mysql/mysql-bin.log
binlog-do-db=my_test

从:

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# my settings
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,
NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
server-id = 101
log-bin=/var/lib/mysql/mysql-bin_slave.log
replicate-do-db=my_test
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60 

需要设置sql_mode,不然有些sql语句不能执行，设置2个mysql的server-id,设置成不同的就可以了，再打开mysql的bin-log.

• 在主库创建从库同步的用户

grant replication client , replication slave on *.* to sla@'%' identified by 'sla_123';
flush privileges;

这里我是在navcat里执行的

• 手动同步数据

cp ./data/master ./data/slave

这里是将保证从库跟主库数据文件一致,再查看gtid

show global variables like 'gtid_executed'

在主库跟从库中分别执行，检查gtid是否一致。

• 设置主从关系，在从库中执行

top slave;

change master to master_host = '192.168.40.132',
master_port = 3310,master_user = 'sla',master_password='sla_123';

start slave;

这里的master_host 是我虚拟机的ip，port是主库带出来的端口

验证

• 检查主从关系是否配置成功，验证主从复制功能是否实现

在从库中执行

show slave status

• 验证主从复制功能

在主库中新建一张表，并添加一些数据，打开从库，可发现从库也存在该表，数据也是一致的，此时，docker下的mysql主从复制功能就基本上实现了

结尾

另外喜欢封面图片的朋友还可以去这里看看

此文章仅为记录所用
by chuandong