当前位置：首页 > news >正文

Docker数据库的主从复制

news 2024/12/25 11:36:58

有很多种方式,我这里使用的是docker镜像配置两个mysql容器做主从复制,一个做主服务器，另一个做从服务器，前提是有docker。
创建容器:
第一个容器:

mkdir mysql
cd mysql
docker run -id \
-p 3307:3306 \
--name=ls \
-v /root/mysql/conf:/etc/mysql/conf.d \
-v /root/mysql/logs:/logs \
-v /root/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

第二个容器:

mkdir mysql2
cd mysql2
docker run -id \
-p 3308:3306 \
--name=my_mysql2 \
-v /root/mysql2/conf:/etc/mysql/conf.d \
-v /root/mysql2/logs:/logs \
-v /root/mysql2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

springcloud开发与实战的容器:
docker run -d \
--name mysql3 \
-p 3309:3306 \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=123 \
-v /root/mysql3/data:/var/lib/mysql \
-v /root/mysql3/conf:/etc/mysql/conf.d \
-v /root/mysql3/init:/docker-entrypoint-initdb.d \
--network hm-net\
mysql

mkdir mysql3
cd mysql3
docker run -id \
-p 3309:3306 \
--name=my_mysql3 \
-v /root/mysql3/conf:/etc/mysql/conf.d \
-v /root/mysql3/logs:/logs \
-v /root/mysql3/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql

如果你的容器有问题可以删除容器
docker rm -f 容器名

查看网络信息:ifconfig

确保你的数据库能连接，如果不能连接可以执行:
systemctl stop NetworkManager

是什么原因造成的呢？其实就是因为linux里两个网络配置工具network和NetworkManager冲突导致的，NetworkManager一般用于安装了桌面环境的Linux系统，一般情况下我们直接使用以下明令禁止使用NetworkManager就行了。

重启网络: systemctl restart network

关闭防火墙: systemctl stop firewalld

再次执行:ifconfig查看网络信息

命令

启动容器: docker start 容器名

重启docker: sudo systemctl restart docker

docker开启了之后会尝试启动容器: docker update --restart=always 容器ID或名称
(配置了开机自启docker然后docker就会启动容器)

关闭docker开启了之后会尝试启动容器:docker update --restart=no 容器ID或名称

进入了mysql重启服务: service mysql restart

重启mysql容器: docker restart my_mysql

使用命令连接mysql: docker exec -it 容器名 mysql -u root -p

docker能连接mysql了尝试在windows连接数据库

如果能连接两个数据库了

执行:
cd root
ls
应该能看见两个文件夹

对主服务器的MySQL进行设置启用二进制日志

进入mysql:
cd /root/mysql

创建my.cnf文件:
vi my.cnf

修改my.cnf文件:

# Copyright (c) 2017, 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 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
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-bin=mysql-bin #[必须]启用二进制日志
server-id=100 #[必须]服务器唯一ID

# Custom config should go here
!includedir /etc/mysql/conf.d/

以上是文件里面要写的内容

将创建好的my.cnf文件复制到容器内:前提是要在包含my.cnf这个的文件夹的地方操作
docker cp my.cnf my_mysql:/etc/mysql/

进入数据库 docker exec -it my_mysql /bin/bash

在MySQL数据库中创建一个名为 xiaoming 的用户，密码是123456,并赋予该用户复制从服务器（REPLICATION SLAVE）的权限，同时指定该用户的认证方式为 mysql_native_password，并最后刷新权限:
CREATE USER 'xiaoming'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'xiaoming'@'%';
ALTER USER 'xiaoming'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
FLUSH PRIVILEGES;

查看主库状态: show master status

以上，完成主数据库的配置

这是我的主库状态

从库配置

进入mysql2:
cd mysql2

在从库的文件夹创建 my.cnf
vi my.cnf

修改my.cnf文件:

[mysqld]

server-id=101 #[必须]服务器唯一ID

# Custom config should go here
!includedir /etc/mysql/conf.d/

以上内容复制进去
将创建好的my.cnf文件复制到容器内
我的从库名字是mysql2,执行以下代码
docker cp my.cnf my_mysql2:/etc/mysql/

再次进入mysql中
docker exec -it my_mysql2 mysql -u root -p

master_host的内容是第一台虚拟机的ip地址，port端口号就是你之前创建容器的端口号

执行语句
CHANGE MASTER TO
MASTER_HOST='192.168.182.15',
MASTER_PORT=3307,
MASTER_USER='xiaoming',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;
执行语句
start slave;

查看从库状态
show slave status;