docker构建mysql主从复制

环境准备

• ubuntu-server-16.04
• docker

准备工作

• 安装docker

#安装docker
sudo apt-get update
sudo apt-get install docker.io
#校验docker是否安装成功(输出版本即成功)
docker -v

• 使用docker拉取mysql镜像（本案例使用mysql_5.6版本）

#从镜像库拉取mysql镜像
sudo docker pull mysql:5.6.35
#查看所有镜像
sudo docker images

1.png

• 在本地新建mysql配置目录

#创建目录文件夹
cd /usr/local
mkdir mysql
#在mysql下创建文件夹和文件(下面目录结构)
--mysql
    --master
        --data
        --conf
           --my.cnf
    --salver
        --data
        --conf
           --my.cnf 

• 编写主库和从库配置的my.cnf文件
  • 主库配置

# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
# 从库修改为其他的数字保证唯一就行
server_id = 1  
log-bin= mysql-bin
#修改为1
read-only=0
#指定同步的DB
#binlog-do-db=blogging
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

#log-error  = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

• 从库配置

# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
# 从库修改为其他的数字保证唯一就行
server_id = 2 
log-bin= mysql-bin
#修改为1
read-only=1
#指定同步的DB
#binlog-do-db=blogging
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

#log-error  = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

• 不同之处

  • server-id 保证唯一即可
  • read-only=1 从库配置文件改成1 为只读

• 启动mysql镜像实例

  • -e 指定mysql实例root用户的密码
  • -v 挂载本地配置文件（挂载路径根据自己实际物理路径修改）
  • 需要暴露的端口号根据自身实际情况修改
  • 从库实例启动修改实例名称，端口，挂载路径即可

docker run --name mysql_master -p 8001:3306 -e MYSQL_ROOT_PASSWORD=xxxx -v /usr/local/mysql/master/data/:/var/lib/mysql -v /usr/local/mysql/master/conf/my.cnf:/etc/mysql/my.cnf  -d mysql:5.6.35

配置主从

• 主库配置

#进入mysql_master实例环境(实例名称根据自身实际名称)
docker exec -it mysql_master /bin/bash
#登陆mysql数据库(输入自己设置的密码登陆mysql数据库)
mysql -uroot -p
#创建一个同步账户（根据实际设置，从库同步需要设置）
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
# 查询主库的状态（File、Position 这两个属性需要记录。从库同步需要设置） 
show master status;

• 从库配置

#同主库进入方法
#关闭从库的同步
stop slave;
#修改从库的同步对象（ip,同步账户密码，端口根据实际修改；file和log根据主库查询的修改）
change master to master_host='127.0.01',master_user='backup',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=120,master_port=8001;
#修改完成启动同步
start slave;
#查看同步状态（两个状态为yes即为成功）
show slave status\G;

2.png

测试

• 使用工具连接两数据库，主库做数据库实例的增删改，看从库是否有相应的变化

后话

• 版本问题，本主从配置对mysql_8.x不适用
• 需修改的地方
  • my.cnf 文件（可以从mysql 8.x版本贴一份过来做修改）
  • mysql 8.x 创建用户有所改动，具体情况自行百度。