Docker部署MySQL主从复制(Docker deploying MySQL master-slave replication)

一、为什么要部署MySQL的主从复制

关于这方面的理论网上可查询,这里可以参考的链接 https://blog.51cto.com/zkhylt/1773679

二、MySQL主从同步复制原理

https://blog.51cto.com/zkhylt/1773679

三、实验环境的部署

环境:

  • VMware workstation –> centos7 IP:192.168.86.130
  • CentOS7 –> Docker

3.1 拉取镜像

docker pull mysql:5.7

因为我之前拉取过镜像所以有下面的显示

[root@tiger hongwei]# docker pull mysql:5.7
5.7: Pulling from library/mysql
Digest: sha256:16e159331007eccc069822f7b731272043ed572a79a196a05ffa2ea127caaf67
Status: Image is up to date for mysql:5.7
docker.io/library/mysql:5.7

查看镜像

[root@tiger hongwei]# docker images
REPOSITORY   TAG       IMAGE ID       CREATED      SIZE
mysql        5.7       a3d35804fa37   5 days ago   462MB

3.2 启动容器

启动mysql的master容器

docker run -d -p 3310:3306 -v /home/mysql/master/config:/etc/mysql/ -v/home/mysql/master/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_master mysql:5.7
[root@tiger ~]# docker run -d -p 3310:3306 -v /home/mysql/master/config:/etc/mysql/ -v/home/mysql/master/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_master mysql:5.7
c7b1ad38770bc62d9d95dbdaaaef9a73173a8f8858a1c2a4fd93ea35f1185e6e

启动mysql的slave容器

docker run -d -p 3311:3306 -v /home/mysql/slave1/config:/etc/mysql/ -v/home/mysql/slave1/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_slave1 mysql:5.7
[root@tiger ~]# docker run -d -p 3311:3306 -v /home/mysql/slave1/config:/etc/mysql/ -v/home/mysql/slave1/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_slave1 mysql:5.7
3d0489188d1919e9ef20f3c5dc88cdeda4e4870171ef4bf4ea3d79dd70af5d55

命令解释:

  • -d: 后台运行容器,并返回容器ID
  • -p 3311:3306 :将容器的3306端口映射到宿主机3311端口
  • -e MYSQL_ROOT_PASSWORD=123456 :环境变量配置数据库连接密码
  • -v /home/mysql/slave1/config:/etc/mysql/ :将配置文件夹挂载到宿主机
  • –name mysql_slave1:将容器命名为 mysql_slave1

查看运行的容器

[root@tiger ~]# docker container ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
3d0489188d19   mysql:5.7   "docker-entrypoint.s…"   5 minutes ago   Up 4 minutes   33060/tcp, 0.0.0.0:3311->3306/tcp, :::3311->3306/tcp   mysql_slave1
c7b1ad38770b   mysql:5.7   "docker-entrypoint.s…"   9 minutes ago   Up 9 minutes   33060/tcp, 0.0.0.0:3310->3306/tcp, :::3310->3306/tcp   mysql_master

测试mysql_master容器与mysql_slave1容器的网络互连

测试前分别给mysql_master,mysql_salve容器安装工具

ping
apt-get update && apt-get install iputils-ping

查看网桥信息

bridge
[root@tiger ~]# docker network inspect bridge
...
这里省略了打印的信息,
查看到mysql_master与mysql_slave1是同一网桥,
mysql_master --> "IPv4Address":"172.17.0.2/16"
mysql_slave1 --> "IPv4Address":"172.17.0.3/16"

这里关于docker的网络知识就不详细展开,请另外自行学习!

[root@tiger ~]# docker exec -it mysql_master /bin/bash
root@c7b1ad38770b:/# ping 172.17.0.3
PING 172.17.0.3 (172.17.0.3) 56(84) bytes of data.
64 bytes from 172.17.0.3: icmp_seq=1 ttl=64 time=0.250 ms
64 bytes from 172.17.0.3: icmp_seq=2 ttl=64 time=0.296 ms
64 bytes from 172.17.0.3: icmp_seq=3 ttl=64 time=0.238 ms
^C
--- 172.17.0.3 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 6ms
rtt min/avg/max/mdev = 0.238/0.261/0.296/0.028 ms
[root@tiger ~]# docker exec -it mysql_slave1 /bin/bash
root@3d0489188d19:/# ping 172.17.0.2
PING 172.17.0.2 (172.17.0.2) 56(84) bytes of data.
64 bytes from 172.17.0.2: icmp_seq=1 ttl=64 time=3.24 ms
64 bytes from 172.17.0.2: icmp_seq=2 ttl=64 time=0.375 ms
64 bytes from 172.17.0.2: icmp_seq=3 ttl=64 time=0.238 ms
^C
--- 172.17.0.2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 13ms
rtt min/avg/max/mdev = 0.238/1.285/3.243/1.385 ms

四、编写主从复制的配置文件

4.1 编写mysql_master配置文件

[root@tiger config]# cd /home/mysql/master/config
[root@tiger config]# vim my.cnf

my.cnf的配置如下

[mysqld]
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库 需要复制的主数据库名字
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT

4.2 编写mysql_slave1配置文件

[root@tiger config]# cd /home/mysql/slave1/config
[root@tiger config]# vim my.cnf
[mysqld]
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

4.3 重启容器

[root@tiger config]# docker restart mysql_master mysql_slave1

五、 主从机设置

5.1 主机设置

docker exec -it mysql_master /bin/bash #进入容器
mysql -uroot -p123456 #连接mysql

5.2 在主机上建立账户并授权slave

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      438 | testdb       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

这里的和需要记录下来,从机设置的时候需要

File
Position

:需要复制的数据库。

Binlog_Do_DB

:不需要复制的数据库。

Binlog_Ignore_DB

注意:此步骤后不要再操作主服务器MySQL,防止主服务器状态值变化

5.4 从机设置

docker exec -it mysql_slave1 /bin/bash
mysql -uroot -p123456
CHANGE MASTER TO MASTER_HOST='192.168.86.130',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_PORT=3310, 
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=438;

如果上面出现的故障执行下面两条命令

stop slave;
reset slave;
然后重新配置CHANGE MASTER......

启动从服务器的复制功能

start slave;

查看从服务器的状态

show slave status\G
这里省略了打印...
主要看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes就代表成功了。

疑难杂症

  • 导致Slave_IO_Running: Connecting问题,可以自行查询百度,谷歌搜索引擎,

我这里的问题是填了master容器的IP,这里应该填的是宿主(Centos)的IP,如果是云服务器,那么就是云服务器的IP

MASTER_HOST
  • 导致Slave_SQL_Running: No问题,就是操作了主服务器MySQL新建一个库,导致从服务器的失去一些状态找不到这个库,我这里解决的方法是在主服务器show master status查看Position,然后从服务器执行CHANGE MASTER….MASTER_LOG_POS=具体值回到新建库的时候的指向。

六、测试

在主服务器中新建一个数据库,然后查看从服务器的状态是否也有,接下来在主服务器中新建表和插入几条记录,观察从服务器的状态是否同步,如果从服务器跟主服务器的状态一致,那么就成功了。

testdb
testdb

参考

https://blog.51cto.com/zkhylt/1773679

https://blog.csdn.net/m0_57711043/article/details/120015999?utm_source=app&app_version=5.3.1

————————

1、 Why deploy master-slave replication of MySQL

The theory of this aspect can be inquired on the Internet. Here is the link for reference https://blog.51cto.com/zkhylt/1773679

2、 Principle of MySQL master-slave synchronous replication

https://blog.51cto.com/zkhylt/1773679

3、 Deployment of experimental environment

Environmental Science:

  • VMware workstation –> centos7 IP:192.168.86.130
  • CentOS7 –> Docker

3.1 pulling image

docker pull mysql:5.7

Because I have pulled the image before, it is shown below

[root@tiger hongwei]# docker pull mysql:5.7
5.7: Pulling from library/mysql
Digest: sha256:16e159331007eccc069822f7b731272043ed572a79a196a05ffa2ea127caaf67
Status: Image is up to date for mysql:5.7
docker.io/library/mysql:5.7

View mirror

[root@tiger hongwei]# docker images
REPOSITORY   TAG       IMAGE ID       CREATED      SIZE
mysql        5.7       a3d35804fa37   5 days ago   462MB

3.2 start up container

Start the master container of MySQL

docker run -d -p 3310:3306 -v /home/mysql/master/config:/etc/mysql/ -v/home/mysql/master/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_master mysql:5.7
[root@tiger ~]# docker run -d -p 3310:3306 -v /home/mysql/master/config:/etc/mysql/ -v/home/mysql/master/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_master mysql:5.7
c7b1ad38770bc62d9d95dbdaaaef9a73173a8f8858a1c2a4fd93ea35f1185e6e

Start the slave container of MySQL

docker run -d -p 3311:3306 -v /home/mysql/slave1/config:/etc/mysql/ -v/home/mysql/slave1/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_slave1 mysql:5.7
[root@tiger ~]# docker run -d -p 3311:3306 -v /home/mysql/slave1/config:/etc/mysql/ -v/home/mysql/slave1/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456  --name mysql_slave1 mysql:5.7
3d0489188d1919e9ef20f3c5dc88cdeda4e4870171ef4bf4ea3d79dd70af5d55

Command interpretation:

  • -d: Run the container in the background and return the container ID
  • -P 3311:3306: map the 3306 port of the container to the host 3311 port
  • -e MYSQL_ ROOT_ Password = 123456: environment variable configuration database connection password
  • -v /home/mysql/slave1/config:/etc/mysql/ :将配置文件夹挂载到宿主机
  • –name mysql_slave1:将容器命名为 mysql_slave1

View running containers

[root@tiger ~]# docker container ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
3d0489188d19   mysql:5.7   "docker-entrypoint.s…"   5 minutes ago   Up 4 minutes   33060/tcp, 0.0.0.0:3311->3306/tcp, :::3311->3306/tcp   mysql_slave1
c7b1ad38770b   mysql:5.7   "docker-entrypoint.s…"   9 minutes ago   Up 9 minutes   33060/tcp, 0.0.0.0:3310->3306/tcp, :::3310->3306/tcp   mysql_master

测试mysql_master容器与mysql_slave1容器的网络互连

Give MySQL before testing_ master,mysql_ Salve container installation tool

ping
apt-get update && apt-get install iputils-ping

View Bridge Information

bridge
[root@tiger ~]# docker network inspect bridge
...
这里省略了打印的信息,
查看到mysql_master与mysql_slave1是同一网桥,
mysql_master --> "IPv4Address":"172.17.0.2/16"
mysql_slave1 --> "IPv4Address":"172.17.0.3/16"

Here, the network knowledge about docker will not be expanded in detail. Please learn by yourself!

[root@tiger ~]# docker exec -it mysql_master /bin/bash
root@c7b1ad38770b:/# ping 172.17.0.3
PING 172.17.0.3 (172.17.0.3) 56(84) bytes of data.
64 bytes from 172.17.0.3: icmp_seq=1 ttl=64 time=0.250 ms
64 bytes from 172.17.0.3: icmp_seq=2 ttl=64 time=0.296 ms
64 bytes from 172.17.0.3: icmp_seq=3 ttl=64 time=0.238 ms
^C
--- 172.17.0.3 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 6ms
rtt min/avg/max/mdev = 0.238/0.261/0.296/0.028 ms
[root@tiger ~]# docker exec -it mysql_slave1 /bin/bash
root@3d0489188d19:/# ping 172.17.0.2
PING 172.17.0.2 (172.17.0.2) 56(84) bytes of data.
64 bytes from 172.17.0.2: icmp_seq=1 ttl=64 time=3.24 ms
64 bytes from 172.17.0.2: icmp_seq=2 ttl=64 time=0.375 ms
64 bytes from 172.17.0.2: icmp_seq=3 ttl=64 time=0.238 ms
^C
--- 172.17.0.2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 13ms
rtt min/avg/max/mdev = 0.238/1.285/3.243/1.385 ms

4、 Write master-slave copied configuration files

4.1 writing MySQL_ Master profile

[root@tiger config]# cd /home/mysql/master/config
[root@tiger config]# vim my.cnf

my. CNF is configured as follows

[mysqld]
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库 需要复制的主数据库名字
binlog-do-db=testdb
#设置logbin格式
binlog_format=STATEMENT

4.2 writing MySQL_ Slave1 profile

[root@tiger config]# cd /home/mysql/slave1/config
[root@tiger config]# vim my.cnf
[mysqld]
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

4.3 restart the container

[root@tiger config]# docker restart mysql_master mysql_slave1

5、 Master slave setting

5.1 host settings

docker exec -it mysql_master /bin/bash #进入容器
mysql -uroot -p123456 #连接mysql

5.2 establish an account on the host and authorize the slave

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |      438 | testdb       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

The and here need to be recorded, and the slave needs to be set up

File
Position

: the database that needs to be replicated.

Binlog_Do_DB

: a database that does not need to be replicated.

Binlog_Ignore_DB

< strong > note: after this step, do not operate the master server Mysql to prevent the master server status value from changing < / strong >

5.4 slave setting

docker exec -it mysql_slave1 /bin/bash
mysql -uroot -p123456
CHANGE MASTER TO MASTER_HOST='192.168.86.130',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_PORT=3310, 
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=438;

If the above fault occurs, execute the following two commands

stop slave;
reset slave;
然后重新配置CHANGE MASTER......

Start replication from server

start slave;

View the status of the slave server

show slave status\G
这里省略了打印...
主要看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes就代表成功了。

< strong > difficult and miscellaneous diseases < / strong >

  • Cause slave_ IO_ Running: for connecting questions, you can query Baidu and Google search engines,

The problem here is to fill in the IP of the master container. Here, you should fill in the IP of the host (CentOS). If it is a ECs, it is the IP of the ECS

MASTER_HOST
  • Cause slave_ SQL_ Running: no problem is that the master server MySQL is operated to create a new library, resulting in the loss of some state of the slave server. The library cannot be found. My solution here is to check the position in the master server show master status, and then execute change master from the server MASTER_ LOG_ POS = the specific value returns to the point when creating a new library.

6、 Testing

Create a new database in the master server, and then check whether the status of the slave server also exists. Next, create a new table and insert several records in the master server to observe whether the status of the slave server is synchronized. If the status of the slave server is consistent with that of the master server, it is successful.

testdb
testdb

reference resources

https://blog.51cto.com/zkhylt/1773679

https://blog.csdn.net/m0_57711043/article/details/120015999?utm_source=app&app_version=5.3.1