docker 配置 mysql 主从复制

最新推荐文章于 2022-04-03 09:40:27 发布

Json2011315

最新推荐文章于 2022-04-03 09:40:27 发布

阅读量194

点赞数

分类专栏： docker mysql

本文链接：https://blog.csdn.net/yinjinshui/article/details/101537685

版权

mysql 同时被 2 个专栏收录

25 篇文章 2 订阅

订阅专栏

docker

11 篇文章 0 订阅

订阅专栏

在这里插入图片描述

1.主服务器要配置binlog
2.从服务器要配置relaylog
3.从服务器如何有权读取master的binlog?(binlog是比较敏感的)----》【主要通过授权的方式：master要授权slave账号】
4.从服务器用账号链接master

原因：
主从分离
主：binlog-format=row/statement/mixed   【日志格式】
Statement: 2进制记录的执行语句，如update,insert.....
Row：2进制记录的是磁盘变化

那个方式好：
Update age =age+1 where id=13; //语句长而磁盘变化少，宜用row
Update salary=salary+100; //语句短，但影响上万行，磁盘变化大，宜用statement
Mixed ，混合，由系统根据语句，来决定

1.拉取官方的镜像,标签为5.7

[root@localhost ~]# docker pull mysql:5.7
[root@localhost ~]# docker images
REPOSITORY                                     TAG                 IMAGE ID            CREATED             SIZE
mysql                                          5.7                 383867b75fd2        2 weeks ago         373MB

说明：此处是通过拉取官方镜像的方式，也可以自行通过 Dockerfile构建

2.创建文件目录

[root@localhost ~]# mkdir -p /var/docker-mysql57
[root@localhost ~]# cd /var/docker-mysql57

创建主的mysql容器配置目录

[root@localhost docker-mysql57]# mkdir -p mysql-master/data mysql-master/logs mysql-master/conf
[root@localhost docker-mysql57]# cd mysql-master/
[root@localhost mysql-master]# ls
conf  data  logs

3.创建容器并启动【创建主mysql】
一定要在此目录下执行【自行定义的目录】：

[root@localhost mysql-master]# pwd
/var/docker-mysql57/mysql-master
[root@localhost mysql-master]# docker run -p 3500:3306 --name mysql-master -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
8db4f0250932df8219f1d04238b70656ca7a8883dd5343eb55f7e3f30a0b0967

命令说明：

-p 3500:3306：将容器的 3500端口映射到主机的 3306 端口。

-v $PWD/conf:/etc/mysql/conf.d：将主机当前目录下的 conf/my.cnf 挂载到容器的 /etc/mysql/my.cnf。

-v $PWD/logs:/logs：将主机当前目录下的 logs 目录挂载到容器的 /logs。

-v $PWD/data:/var/lib/mysql ：将主机当前目录下的data目录挂载到容器的 /var/lib/mysql 。

-e MYSQL_ROOT_PASSWORD=123456：初始化 root 用户的密码。

解释说明：

docker run -p 3500:3306 --name mysql-master(创建容器的名称) -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7(镜像名称加标签--IMAGE)

查看容器

[root@localhost mysql-master]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                     PORTS                               NAMES
8db4f0250932        mysql:5.7           "docker-entrypoint.s…"   2 minutes ago       Up 2 minutes               33060/tcp, 0.0.0.0:3500->3306/tcp   mysql-master

进入容器：

[root@localhost mysql-master]# docker exec -it mysql-master bash

在这里插入图片描述利用navicat外部链接mysql:

4.创建容器并启动【创建从mysql】

[root@localhost docker-mysql57]# mkdir -p mysql-slave/data mysql-slave/logs mysql-slave/conf
[root@localhost docker-mysql57]# cd mysql-master/
[root@localhost mysql-master]# ls
conf  data  logs

创建容器

[root@localhost mysql-slave]# pwd
/var/docker-mysql57/mysql-slave
[root@localhost mysql-slave]# docker run -p 3501:3306 --name mysql-slave -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
2467554bca690d2378a7bebe1af796a854467ef5336ccb369fd730e7cf35953b
[root@localhost mysql-slave]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                      PORTS                               NAMES
2467554bca69        mysql:5.7           "docker-entrypoint.s…"   5 seconds ago       Up 3 seconds                33060/tcp, 0.0.0.0:3501->3306/tcp   mysql-slave
8db4f0250932        mysql:5.7           "docker-entrypoint.s…"   14 minutes ago      Up 14 minutes               33060/tcp, 0.0.0.0:3500->3306/tcp   mysql-master

截图
在这里插入图片描述进入容器：

[root@localhost mysql-slave]# docker exec -it mysql-slave bash

进入mysql：

root@2467554bca69:/# mysql -uroot -p123456
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.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

利用navicat外部链接mysql:
在这里插入图片描述
4.配置主从的配置文件
4.1配置my.cnf配置文件【主mysql】
[root@localhost mysql-master]# vim conf/my.cnf
配置文件内容：

[client]
port = 3306

[mysqld]
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
#user=mysql
character_set_server=utf8
init_connect='SET NAMES utf8'

#主id
server-id=1
#开启bin-log日志
log-bin=mysql-bin
#二进制日志的格式 mixed/row/statement
binlog-format=mixed
#定期自动清理日志【自行决定是否开启】
#expire_logs_days=7

配置好文件之后，重启主的mysql

[root@localhost mysql-master]# docker restart 8db4f0250932
8db4f0250932
[root@localhost mysql-master]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                      PORTS                               NAMES
dce867809902        mysql:5.7           "docker-entrypoint.s…"   13 minutes ago      Up 13 minutes               33060/tcp, 0.0.0.0:3502->3306/tcp   mysql-test
2467554bca69        mysql:5.7           "docker-entrypoint.s…"   19 minutes ago      Up 19 minutes               33060/tcp, 0.0.0.0:3501->3306/tcp   mysql-slave
8db4f0250932        mysql:5.7           "docker-entrypoint.s…"   34 minutes ago      Up 3 seconds                33060/tcp, 0.0.0.0:3500->3306/tcp   mysql-master

截图：（需要留意是否正常启动，反之配置有问题）
在这里插入图片描述
4.2配置从的my.cnf配置

[root@localhost mysql-slave]# pwd
/var/docker-mysql57/mysql-slave
[root@localhost mysql-slave]# vim conf/my.cnf

配置文件内容

[client]
port = 3306

[mysqld]
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
character_set_server=utf8
init_connect='SET NAMES utf8'

#从id
server-id=2
#开启bin-log日志
log-bin=mysql-bin
#读取主bin-log日志
relay-bin=mysql-replay

配置好文件之后，重启从的mysql

[root@localhost mysql-slave]# docker restart 2467554bca69
2467554bca69
[root@localhost mysql-slave]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                      PORTS                               NAMES
dce867809902        mysql:5.7           "docker-entrypoint.s…"   27 minutes ago      Up 27 minutes               33060/tcp, 0.0.0.0:3502->3306/tcp   mysql-test
2467554bca69        mysql:5.7           "docker-entrypoint.s…"   33 minutes ago      Up 8 seconds                33060/tcp, 0.0.0.0:3501->3306/tcp   mysql-slave
8db4f0250932        mysql:5.7           "docker-entrypoint.s…"   47 minutes ago      Up 13 minutes               33060/tcp, 0.0.0.0:3500->3306/tcp   mysql-master
734557f4d40a        hello-world         "/hello"                 52 minutes ago      Exited (0) 52 minutes ago                                       recursing_banach

（需要留意是否正常启动，反之配置有问题）【请按照实际的容器id操作】
在这里插入图片描述

5.进入主的mysql【进入mysql-master容器】，并配置从的账户（以下操作都在mysql-master容器操作【主mysql】）
5.1进入mysql-master容器

[root@localhost mysql-master]# docker exec -it mysql-master bash
root@8db4f0250932:/# mysql -uroot -p123456
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.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

查看bin-log日志是否开启

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

5.2分配从的用户。并刷新

mysql> grant replication client,replication slave on *.* 'slave_user'@'%' identified by '123456';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''slave_user'@'%' identified by '123456'' at line 1
mysql> grant replication client,replication slave on *.* to 'slave_user'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      615 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

在这里插入图片描述读取主的容器（mysql-master）ip

[root@localhost mysql-master]# docker inspect mysql-master

在这里插入图片描述

6.进入从的mysql【进入mysql-slave容器】（以下操作都在mysql-slave容器操作【从mysql】）
6.1进入mysql-slave容器

[root@localhost ~]# docker exec -it mysql-slave bash
root@2467554bca69:/# mysql -uroot -p123456
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.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

6.2查看bin-log是否开启：
在这里插入图片描述

6.3配置读取主bin-log的用户

mysql> show slave status;
Empty set (0.01 sec)

mysql> change master to
    -> master_host='172.17.0.2',
    -> master_user='slave_user',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=615;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

截图说明：
在这里插入图片描述
6.4查看是否连接上主服务器

mysql> show slave status\G;

在这里插入图片描述命令说明：

start slave; ------>开启从服务器
stop slave ------>停止从服务器
reset slave ------>重启从服务器

开启从服务器：

mysql> start slave;

启动状态：
在这里插入图片描述
7.在window操作或linux操作
只需要在主mysql创建数据库，然后在从查看是否有数据库
8.总结
1.只可以一主多从，不可以多主

Json2011315

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
docker 配置 mysql 主从复制

1.拉取官方的镜像,标签为5.7[root@localhost ~]# docker pull mysql:5.7[root@localhost ~]# docker imagesREPOSITORY TAG IMAGE ID CREATED ...
复制链接

扫一扫

专栏目录