前言
MySQL主从架构，我们已经在vmware虚拟机上实践过了，接下来我们一起探讨在docker中如何使用MySQL主从架构。
一. mysql主从架构原理
1.1 MySQL主从复制分类
- 一主一从
- 主主复制
也叫互为主从（M-M）
每个服务器需要做同样的同步更新，破坏了事务的隔离性和数据的一致性。 - 一主多从（提高从库的读取的速度）
一主多备：用来读写分离，master写， 其他的slave进行读的操作。
缺点： 所有的压力全部在master - 多主一从 （从MySQL5.7之后开始支持）
- 级联复制 （M-S-S）
slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates
Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志。
- M-M-M 铁三角
缺陷： 复制延迟，高并发情况下延迟 更大。
1.2 MySQL主从复制
- 实时灾备：用于故障切换
- 读写分离，提供查询服务
- 备份 ，为了避免业务受到影响
1.3 主从架构如何工作
第一步： master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。
第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程
第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。
二. 安装docker
2.1 实验环境
hostname/IP
[root@mufeng56 ~]# vim /etc/hosts
[root@mufeng56 ~]# hostname
mufeng56
[root@mufeng56 ~]# ifconfig |grep inet |awk '{print $2}' |head -1
192.168.1.56
内存/cpu/版本
[root@mufeng56 ~]# lsmem |grep 'Total online memory'
Total online memory: 2G
[root@mufeng56 ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@mufeng56 ~]#
关闭防火墙，关闭selinux
[root@mufeng56 ~]# iptables -F
[root@mufeng56 ~]# systemctl stop firewalld.service
Display all 141 possibilities? (y or n)
[root@mufeng56 ~]# systemctl stop firewalld.service
[root@mufeng56 ~]# setenforce 0
[root@mufeng56 ~]#
2.2 安装docker
1）下载阿里云repo镜像
[root@mufeng56 ~]# wget https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo -O /etc/yum.repos.d/docker-ce.repo
--2023-05-15 14:52:08-- https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 183.204.36.235, 117.161.156.225, 117.161.156.224, ...
正在连接 mirrors.aliyun.com (mirrors.aliyun.com)|183.204.36.235|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：2081 (2.0K) [application/octet-stream]
正在保存至: “/etc/yum.repos.d/docker-ce.repo”
100%[====================================================================================>] 2,081 --.-K/s 用时 0.003s
2023-05-15 14:52:08 (607 KB/s) - 已保存 “/etc/yum.repos.d/docker-ce.repo” [2081/2081])
[root@mufeng56 ~]# cd /etc/yum.repos.d/
[root@mufeng56 yum.repos.d]# ls
CentOS-Base.repo CentOS-Debuginfo.repo CentOS-Media.repo CentOS-Sources.repo docker-ce.repo
CentOS-CR.repo CentOS-fasttrack.repo centos.repo CentOS-Vault.repo
[root@mufeng56 yum.repos.d]#
2）安装最新的版本
[root@mufeng56 ~]# yum install docker-ce -y
3）启动docker并测试
[root@mufeng56 ~]# systemctl start docker
[root@mufeng56 ~]# docker --version
Docker version 23.0.6, build ef23cbc
[root@mufeng56 ~]#
4）设置镜像加速地址
[root@mufeng56 ~]# sudo mkdir -p /etc/docker
[root@mufeng56 ~]# sudo tee /etc/docker/daemon.json <<-'EOF'
> {
> "registry-mirrors": ["https://ywrd8tun.mirror.aliyuncs.com"]
> }
> EOF
{
"registry-mirrors": ["https://ywrd8tun.mirror.aliyuncs.com"]
}
[root@mufeng56 ~]# sudo systemctl daemon-reload
[root@mufeng56 ~]# sudo systemctl restart docker
三. 安装MySQL-master
3.1下载MySQL镜像
[root@mufeng56 ~]# docker pull mysql:5.7
5.7: Pulling from library/mysql
72a69066d2fe: Pull complete
93619dbc5b36: Pull complete
99da31dd6142: Pull complete
626033c43d70: Pull complete
37d5d7efb64e: Pull complete
ac563158d721: Pull complete
d2ba16033dad: Pull complete
0ceb82207cd7: Pull complete
37f2405cae96: Pull complete
e2482e017e53: Pull complete
70deed891d42: Pull complete
Digest: sha256:f2ad209efe9c67104167fc609cca6973c8422939491c9345270175a300419f94
Status: Downloaded newer image for mysql:5.7
docker.io/library/mysql:5.7
[root@mufeng56 ~]# docker images;
REPOSITORY TAG IMAGE ID CREATED SIZE
nginx latest 605c77e624dd 16 months ago 141MB
mysql 5.7 c20987f18b13 17 months ago 448MB
3.2 创建MySQL-master并创建配置文件
[root@mufeng56 ~]# docker run -p 3307:3306 --name mysql-master \
> -v /mydata/mysql-master/log:/var/log/mysql \
> -v /mydata/mysql-master/data:/var/lib/mysql \
> -v /mydata/mysql-master/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=root \
> -d mysql:5.7
bfe0e8bb37aa38fc8d86e0540a89e93d8b9251efc4770a5e3404ec47580dc8ec
[root@mufeng56 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bfe0e8bb37aa mysql:5.7 "docker-entrypoint.s…" 8 seconds ago Up 7 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
3.3 修改master配置文件
[root@mufeng56 conf]# pwd
/mydata/mysql-master/conf
[root@mufeng56 conf]# ls
[root@mufeng56 conf]# vim my.cnf
[root@mufeng56 conf]# cat my.cnf
[mysqld]
server_id=101
binlog-ignore-db=mysql
log-bin=mall-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
注释：
[mysqld]
#设置serveID ，在局域网中是唯一的
server_id=101
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能
log-bin=mall-mysql-bin
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
3.4 重启master
[root@mufeng56 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bfe0e8bb37aa mysql:5.7 "docker-entrypoint.s…" 7 minutes ago Up 7 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@mufeng56 ~]# docker restart mysql-master
mysql-master
[root@mufeng56 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bfe0e8bb37aa mysql:5.7 "docker-entrypoint.s…" 7 minutes ago Up 6 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@mufeng56 ~]#
3.5 进入master创建数据和数据同步用户
- 登录数据库
[root@mufeng56 ~]# docker exec -it mysql-master /bin/bash
root@bfe0e8bb37aa:/# mysql -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- 授权
mysql> create user 'slave'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
root@bfe0e8bb37aa:/#
四. 新建从服务器
4.1 创建MySQL-slave 容器
[root@itlaoxin41 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bfe0e8bb37aa mysql:5.7 "docker-entrypoint.s…" 17 minutes ago Up 9 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@itlaoxin41 ~]# docker run -p 3308:3306 --name mysql-slave \
> -v /mydata/mysql-slave/log:/var/log/mysql \
> -v /mydata/mysql-slave/data:/var/lib/mysql \
> -v /mydata/mysql-slave/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=root \
> -d mysql:5.7
cb5ac2559c3b7d6e4bbc585797f9d6e0adef93dce05ef712de138b71de8149cb
[root@itlaoxin41 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cb5ac2559c3b mysql:5.7 "docker-entrypoint.s…" 7 seconds ago Up 6 seconds 33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp mysql-slave
bfe0e8bb37aa mysql:5.7 "docker-entrypoint.s…" 17 minutes ago Up 9 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@itlaoxin41 ~]#
4.2 修改配置文件
[root@itlaoxin41 conf]# pwd
/mydata/mysql-slave/conf
[root@itlaoxin41 conf]# vim my.cnf
[root@itlaoxin41 conf]# cat my.cnf
[mysqld]
server_id=102
binlog-ignore-db=mysql
log-bin=mall-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=mall-mysql-relay-bin
log_slave_updates=1
read_only=1
配置文件的注释：
[mysqld]
#设置serveID ，在局域网中是唯一的
server_id=101
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能
log-bin=mall-mysql-bin
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读（具有super权限的用户除外）
read_only=1
4.3 重启从服务器
[root@itlaoxin41 conf]# docker restart mysql-slave
mysql-slave
[root@itlaoxin41 conf]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cb5ac2559c3b mysql:5.7 "docker-entrypoint.s…" 8 minutes ago Up 1 second 33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp mysql-slave
bfe0e8bb37aa mysql:5.7 "docker-entrypoint.s…" 26 minutes ago Up 18 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
五. 配置主从同步
5.1 在主服务器查看同步状态
[root@mufeng56 ~]# docker exec -it mysql-master /bin/bash
root@bfe0e8bb37aa:/# mysql -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mall-mysql-bin.000001 | 617 | | mysql | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
5.2 在从服务器上进行相应的配置
mysql> change master to master_host='192.168.1.56',master_user='slave',master_password='123456',master_port=3307,master_log_file='mall-mysql-bin.000001',master_log_pos=617, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
查看是否开启同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.56
Master_User: slave
Master_Port: 3307
Connect_Retry: 30
Master_Log_File: mall-mysql-bin.000001
Read_Master_Log_Pos: 617
Relay_Log_File: mall-mysql-relay-bin.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: mall-mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 617
Relay_Log_Space: 537
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: a2bc1b87-f2f0-11ed-ab38-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
5.3 测试同步内容
主上插入数据
mysql> create database db02;
Query OK, 1 row affected (0.00 sec)
mysql>
从上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db02 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
总结
本文主要讲解了使用docker实现MySQL的主从架构的全过程，欢迎关注我一起交流和学习。