基于Docker部署Mysql主从复制-实战详解篇

今天药忘吃喽~ 2022-12-01 11:51 217阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70][]

## 一、前言 ##

MySQL的主从复制详细讲解，根据网上教程也踩了很多坑，浪费了一些时间 ，特地全面的梳理下基于docker构建的mysql主从复制构建过程、遇到的问题以及提供安装包样例等

希望一篇文章足以解决

> 搭建环境：一主（192.168.26.148）   一从（192.168.26.123）
> 
> Centos 7.2 64位
> 
> MySQL 5.7.24
> 
> Docker 18.06.1-ce
> 
> docker安装包共享：
> 
> mysql主从 复制共享：
> 
> 链接：https://pan.baidu.com/s/1jzo3YiG6XLwIqSFKlSC55g   
> 提取码：6666

## 二、MySql主从复制实战 ##

### **2.1 docker安装** ###

待共享

### 2.2 安装mysql ###

将安装包解压到 **/opt/tar/**，形成**/opt/tar/db-master**目录（里面包含安装mysql的脚本配置）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 1][]

**1)安装主节点：在主节点执行./install-mysql-master.sh （代码如下）**

#导入images
    docker load </opt/tar/db-master/app-mysql.tar
    #启动镜像
    sleep  3
    #启动
    docker run -i -t -d -p 3306:3306 --restart=always  -v /etc/localtime:/etc/localtime -v /opt/logs/mysq_logs:/logs  -v /opt/tar/db-master/db/mysql:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123@gla.net.cn --name app-mysql mysql5.7.24 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    
    sleep 2
    docker cp /opt/tar/db-master/mysqld.cnf  app-mysql:/etc/mysql/mysql.conf.d/mysqld.cnf
    sleep 2
    docker restart  app-mysql

**2）在slave节点执行./install-mysql-slave.sh（代码如下）**

#导入images
    docker load </opt/tar/db-master/app-mysql.tar
    #启动镜像
    sleep  3
    #启动
    docker run -i -t -d -p 3306:3306 --restart=always  -v /etc/localtime:/etc/localtime -v /opt/logs/mysq_logs:/logs  -v /opt/tar/db-master/db/mysql:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123@gla.net.cn --name app-mysql mysql5.7.24 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    
    sleep 2
    docker cp /opt/tar/db-master/mysqld-slave.cnf  app-mysql:/etc/mysql/mysql.conf.d/mysqld.cnf
    docker cp /opt/tar/db-master/auto-slave.cnf  app-mysql:/var/lib/mysql/auto.cnf 
    sleep 2
    docker restart  app-mysql

使用Navicat连接测试

### 2.3 主从配置 ###

**1）创建主容器的复制账号（Navicat执行SQL）**

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by 'backup';
    show grants for 'backup'@'%';

出现如下信息表示授权成功

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 2][]

**2）修改/etc/mysql/my.cnf，在 \[mysqld\] 节点最后加上后保存**

注意my.cnf加载了两个目录，所以这里的修改的文件为

**/etc/mysql/mysql.conf.d/mysqld.cnf**

log-bin=mysql-bin
    server-id=1

log-bin=mysql-bin 使用binary logging，mysql-bin是log文件名的前缀

server-id=1 唯一服务器ID，非0整数，不能和其他服务器的server-id重复**（主和从的要不一样）**

两个主从配置文件分别对应/opt/tar/db-master/目录下**mysqld.cnf** 和**mysqld-slave.cnf**

### 3）配置主从复制 ###

**1.在slave节点配置连接**

#停止掉slave
    STOP SLAVE
    
    CHANGE MASTER TO 
    MASTER_HOST='192.168.26.148',
    MASTER_PORT=3306,
    MASTER_USER='backup',
    MASTER_PASSWORD='backup';
    
    #启动slave
    START SLAVE;
    
    show slave status;

> MASTER\_HOST 填Navicat连接配置中的ip应该就可以
> 
> MASTER\_PORT 主容器的端口
> 
> MASTER\_USER 同步账号的用户名
> 
> MASTER\_PASSWORD 同步账号的密码
> 
>

**2.检查是否配置成功**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 3][]

Slave\_IO\_State 如果是**Waiting for master to send event并且如下两个选项都为yes则配置成功**

![20200827124325205.png][]

**3.问题排查**

**part1: 如果错误可通过如下命令查看docker中的日志（很重要，快速定位问题）**

docker logs app-mysql -f

**part2:重启**

STOP SLAVE;
    
    CHANGE MASTER TO 
    MASTER_HOST='ip',
    MASTER_PORT=端口,
    MASTER_USER='用户名',
    MASTER_PASSWORD='密码';
    
    START SLAVE;

**part3:如果有一个不是Yes，一半是重启从容器后，事务回滚引起的，那么给出解决方法如下**

stop slave ;
    set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    start slave ;

**4.测试主从复制**

首先，在主实例中创建一个测试数据库test2（148）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 4][]

打开（刷新）从实例123，可见test2库已存在

![20200827124916429.png][]

## 三、错误排查 ##

**1）Slave failed to initialize relay log info structure from the repository**

mysql> start slave;
    ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
    mysql>  reset slave;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> start slave IO_THREAD;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> stop slave IO_THREAD;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> reset slave;
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.20 sec)
    
    mysql> show slave status\G

2）[Mysql主从架构报错-Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work][Mysql_-Fatal error_ The slave I_O thread stops because master and slave have equal MySQL server UUIDs_ these UUIDs must be different for replication to work]

在搭建Mysql主从架构过程中，由于从服务器是克隆的主服务器系统，导致主从mysql uuid相同， Slave\_IO无法启动，报错如下：

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决办法，修改其中一台服务器的server-uuid，并保证server-uuid的格式正确，修改完成之后重启Mysql服务就可以了。

在修改配置文件之前，先登录Mysql客户端查看uuid，把返回的uuid复制，放到要修改的配置文件即可

MySQL [hb3]> select uuid();
    +--------------------------------------+
    | uuid()                               |
    +--------------------------------------+
    | a39fd903-3a1c-11e8-ab46-000c29133368 |
    +--------------------------------------+
    1 row in set (0.00 sec)

修改uuid配置文件

# find / -name 'auto.cnf'
    # vim /data/mysql/auto.cnf
    [auto]
    server-uuid=804f2ebe-3a1c-11e8-ab46-000c29133368 # 按照这个16进制格式，修改server-uuid，重启mysql即可

**对应auto-save.cnf 配置，应为都是基于一个mysql镜像构建的，所以uuid会重复，需要修改下**

**3）如果无Navicat，可进入容器进行执行**

进入master 容器  
进入 mysql，密码为 123123

# mysql -uroot -p

成功进入后，创建用户 whosme 并给予 slave 权限

参考链接：[https://my.oschina.net/u/3773384/blog/1810111][https_my.oschina.net_u_3773384_blog_1810111]

[https://www.cnblogs.com/wt645631686/p/8310195.html][Mysql_-Fatal error_ The slave I_O thread stops because master and slave have equal MySQL server UUIDs_ these UUIDs must be different for replication to work]

**4）开放3306端口**

[root@VM_0_17_centos ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
    [root@VM_0_17_centos ~]# firewall-cmd --reload

**5）查看错误日志**

docker logs slave -f

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 5][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70]: /images/20221123/132f40916ed04475933628c8a0f4e86a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 1]: /images/20221123/41fd8b8f77e640dfb5d665ad5b070a44.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 2]: /images/20221123/230273010e1e4488a37a386ef16a838d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 3]: /images/20221123/19e933e2d3c8484cbf2101fbb2655474.png
[20200827124325205.png]: /images/20221123/ad6a2c58638440bcb7a358dcc5a117d0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 4]: /images/20221123/1ff7050d39c448d9b1899e1aa46302e3.png
[20200827124916429.png]: /images/20221123/d84243b231394991b9c9c901654dfd46.png
[Mysql_-Fatal error_ The slave I_O thread stops because master and slave have equal MySQL server UUIDs_ these UUIDs must be different for replication to work]: https://www.cnblogs.com/wt645631686/p/8310195.html
[https_my.oschina.net_u_3773384_blog_1810111]: https://my.oschina.net/u/3773384/blog/1810111
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6aHVhbl8x_size_16_color_FFFFFF_t_70 5]: /images/20221123/d0cbf7cdd4fd4b31a56ad81207d7317a.png