Docker-Compose实现Mysql主从

我从cnaaa.com购买了云服务器。

1. 简介

通过使用docker-compose搭建一个主从数据库，本示例为了解耦 将两个server拆分到了两个compose文件中，当然也可以放到一个compose文件中

演示mysql版本：5.7.16

2. 部署流程

master节点：

1. 安装mysql-server

2. 修改配置

3. 创建用于同步的账号并授权

4. 检查相关配置

slave节点：

1. 安装mysql-server

2. 修改配置

3. 选择主节点

4. 检查相关配置并验证同步功能

3. master节点

3.1 安装mysql

1. 创建mysql文件夹并进入文件夹（文件夹名称mysql）

2. 创建docker-compose文件内容如下

   <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n34" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;"># docker-compose.yml
   version: '3'
   services:
   mysql:
   restart: "no"
   image: mysql:5.7.16
   container_name: mysql-master
   volumes:

   • ./datadir:/var/lib/mysql
   • ./conf/mysql:/etc/mysql
     environment:
   • "MYSQL_ROOT_PASSWORD=123456"
   • "TZ=Asia/Shanghai"
     ports:
   • 3306:3306
     networks:
   • mysql-net
     networks:
     mysql-net:
     driver: bridge</pre>

   注意：因为要把配置文件挂在到服务中去，所以要先把容器中的配置文件copy到宿主机上

   1. 先启动一个用于copy文件的容器

      <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n40" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">$ docker run --name mysql-temp -e MYSQL_ROOT_PASSWORD=root --rm -d mysql:5.7.16</pre>

   2. 将mysql-temp容器中的配置文件copy出来，现在conf文件夹中就是mysql自带的所有配置文件

      <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n44" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">$ docker cp mysql-temp:/etc/mysql conf</pre>

      [图片上传失败...(image-6861e-1664289286258)]

   3. 因为当前conf目录中的my.cnf还是个link，所以直接使用当前目录中的备份文件作为主要的配置文件

      <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n49" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">$ mv my.cnf.fallback my.cnf</pre>

3. 修改配置文件my.cnf

   在文件的最下方加入配置信息

   <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n54" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">[mysqld]
   log-bin=mysql-bin # 开启 binlog
   server-id=1 # 当前server在cluster中的id，必须保证在cluster中唯一

   只保留7天的二进制日志，以防磁盘被日志占满(可选)

   expire-logs-days = 7

   不备份的数据库 （可选）

   binlog-ignore-db=information_schema
   binlog-ignore-db=performation_schema
   binlog-ignore-db=sys</pre>

4. 启动mysql服务，通过输出内容得知真实的网络名称为mysql_mysql-net，也就是当前所在文件夹的名称拼接了文件中指定的网络名称

   [图片上传失败...(image-4f2637-1664289286259)]

5. 服务启动完毕后，创建用于同步的用户并授权

   创建的用户名称为slave密码为123456

   <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n62" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">CREATE USER 'slave' @'%' IDENTIFIED BY '123456';
   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'slave' @'%';

   刷新权限

   FLUSH PRIVILEGES;</pre>

6. 查看master状态信息

   <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n66" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">SHOW MASTER STATUS;

   查看Mater数据有哪些slave

   select * from information_schema.processlist as p where p.command = 'Binlog Dump'; </pre>

   [图片上传失败...(image-bc9842-1664289286259)]

4. slave节点

安装步骤同master相同，只把需要修改的展示一下，当前的目录结构如下

[图片上传失败...(image-f521bb-1664289286262)]

docker-compose.yaml 主要修改了网络相关的信息和container_name（网络名称上面有解释）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n73" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">version: '3'
services:
mysql:
restart: "no"
image: mysql:5.7.16
container_name: mysql-slave
volumes:

• ./datadir:/var/lib/mysql
• ./conf:/etc/mysql
  environment:
• "MYSQL_ROOT_PASSWORD=123456"
• "TZ=Asia/Shanghai"
  ports:
• 3307:3306
  networks:
• mysql_mysql-net
  networks:
  mysql_mysql-net:
  external: true # 来自外部</pre>

my.cnf添加的内容如下：

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n76" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">[mysqld]
server-id=2
relay_log=relay-log

开启只读 意味着当前的数据库用作读，当然这也只会影响到非root的用户，如果使用root用户操作本库是不会有影响的

read_only=ON</pre>

设置完成后启动salve server，连接slave并关联master节点

• MASTER_HOST：直接使用container_name

• MASTER_LOG_FILE/MASTER_LOG_POS：直接使用安装master步骤中的最后一步的值，其实就是指定同步的bin-log文件名称和Offset

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n84" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;</pre>

管理完成后 启动salve

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n87" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">START SLAVE;</pre>

最后查看slave status

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n90" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">SHOW SLAVE STATUS;</pre>

[图片上传失败...(image-343d70-1664289286261)]

5. 验证

在master上创建test数据库并创建user表，刷新查看salve库，出现了对应的库表

[图片上传失败...(image-6e4fa-1664289286261)]

经验证数据同步也没有问题。

6. 可能遇到的问题

SHOW SLAVE STATUS时发现slave_io_running=No salve_sql_running=No，可能的原因有很多，可以查看如下的字段中输出的内容

[图片上传失败...(image-43d64b-1664289286261)]

可能的原因：

1. 主从网络不通

2. 两台节点的server-id重复，直接修改对应的id即可

3. 数据库的uuid相同（可能是因为数据库文件是直接copy过来的导致的），在对应的库下生成不同于master的uuid到auto.cnf中即可

4. sql执行失败，可能是slave刚添加进来，也没有master库的数据库instance，导致操作对应的库时slave这边根本没有对应的instance或者table又或是记录，引发的报错。这个只能具体问题具体解决了

5. master和slave的MASTER_LOG_FILE/MASTER_LOG_POS值设置的有问题，在slave节点上STOP SLAVE;然后重新连接下master即可

7. 同步部分数据库实例或表

在master节点上添加配置【可选】（如果只希望从库读取到部分实例）

在my.cnf文件中加入如下配置

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n115" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">#需要同步的数据库名 有多个库添加多行即可
binlog-do-db=test
binlog-do-db=test1

排除的数据库

binlog-ignore-db=sys</pre>

salve端：在my.cnf文件中加入如下配置，这样的话salve只会读取配置的db或table，master对其他db的操作也不会影响slave

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n118" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">#如果salve库名称与master库名相同，使用本配置
replicate-do-db=test

如果master库名[test]与salve库名[test001]不同，使用以下配置[需要做映射]

replicate-rewrite-db = test -> test001

如果不是要全部同步[默认全部同步]，则指定需要同步的表

replicate-wild-do-table=test.user

replicate-wild-do-table=test.role</pre>