Docker 搭建MySQL 主从复制环境

在 Linux 虚拟机上使用 Docker 搭建 MySQL 主从复制环境

# 使用 Docker 拉取 Mysql 镜像
[root@localhost ~]# docker pull mysql:8.0.25

使用 Docker 创建两个 mysql 容器，mysql1 作为 主机，端口号为 33061 ；mysql2 作为从机，端口号为33062。

这里为了简单搭建，使用的 mysql 镜像版本为 mysql 5.7 。如果使用 mysql 8.0 版本搭建过程会有些不一样。

创建并启动 mysql 主机和从机

# 参数
# --name ：表示创建的 mysql 容器名
# -p 33061:3306 ：表示将容器的 3306 端口映射到宿主机的 33061 端口，也就是虚拟机的 33061 端口。
# -e MYSQL_ROOT_PASSWORD ：表示环境配置，配置登录密码为123456
# mysql:5.7 ：表示使用的镜像和版本

# 创建并启动 mysql1 主机
[root@localhost ~]# docker run --name mysql1 -p 33061:3306 -d -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
# 创建并启动 mysql2 从机
[root@localhost ~]# docker run --name mysql2 -p 33062:3306 -d -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

1. 主机配置：

使用 Navicat 软件授权一个用户进行主从复制操作。

# *.* 表示所有库所有表，'rep1'@'%' 表示授权的用户具有的权限，123456 表示所设置的密码。
GRANT replication SLAVE on *.* to 'rep1'@'%' IDENTIFIED by '123456';

查看授权是否成功：

修改主机的mysql配置文件。由于 docker 容器并没有 vi 编辑器，无法通过 vi 编辑器修改配置文件。

有两种解决办法：

• 在容器中安装 vim 编辑器。
• 复制配置文件到本地中，在本地进行修改好后复制到 mysql 容器中覆盖掉原配置文件。

这里选择第二种方案：

1. 进入 mysql 容器，并复制配置文件信息

# 进入 mysql容器
docker exec -it mysql1 bash
# 查看配置文件信息
root@4a2347363a99:/# cat /etc/mysql/mysql.conf.d/mysqld.cnf

复制的配置文件信息：

# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# 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, version 2.0, 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  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[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

退出 mysql 容器

root@4a2347363a99:/# exit
exit

在本地创建名为 mysqld.cnf 的文件，存放在 /mysql-conf-temp/文件夹中

# 创建临时文件
mkdir ./mysql-conf-temp/
# 在文件夹中创建 mysqld.cnf 文件
vi ./mysql-conf-temp/mysqld.cnf

添加配置文件内容

log-bin=/var/run/mysqld/mysqld.pid 
server-id=1
# 将要复制的库
binlog-do-db=study

完整的配置文件信息

#opyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# 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, version 2.0, 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  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
log-bin=/var/run/mysqld/mysqld.pid
server-id=1
binlog-do-db=study
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

将本地中修改好的配置文件复制到 mysql 容器中

# 复制
[root@localhost ~]# docker cp ./mysql-conf-temp/mysqld.cnf mysql1:/etc/mysql/mysql.conf.d/
# 重启 mysql容器
[root@localhost ~]# docker restart mysql1

查看是否配置成功

在 Navicat 执行以下命令：

show master status;

显示如图所示表示配置成功：

2. 配置从机：

从机配置方法与主机配置方法一致，不同的是只需要在配置文件中添加以下内容：

server-id=2

修改后的配置文件：

# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# 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, version 2.0, 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  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
server-id=2
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

进入从机 mysql 容器

# 配置从机联系的 master
# master_host ：主机 的 ip 地址，这里我是安装在虚拟机上的，所以写虚拟机的地址
# master_port ：主机 的端口号
# master_user ：之前我们设置的主机权限用户名
# master_password ： 主机的密码
# master_log_file ：这是执行完 show master status; 后显示的 file
# master_log_pos ：这是执行完 show master status; 后显示的 position

change master to master_host='192.168.88.101',master_port=33061,master_user='rep1',master_password='123456',master_log_file='mysqld.000001',master_log_pos=154;

启动从机

mysql> start slave;

查看状态

mysql> show slave status\G;

如果显示结果中以下两条都为 yes 表示成功：

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

至此，mysql 主从复制配置完成。

可以使用 Navicat 软件软件进行测试：

在 MySQL 主机上添加配置的数据库（我这里配置的数据库名为：study ），刷新 MySQL 从机，从机也会增加 study 数据库。

在 MySQL 主机上修改配置的数据库（我这里配置的数据库名为：study ），刷新 MySQL 从机，从机也会修改 study 数据库。