mysql 双主热备主从复制

Love The Way You Lie 2023-01-18 04:07 181阅读 0赞

关于msyql主从复制的原理

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70][]

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

**1、环境准备**

安装三个mysql应用服务端口分别设置为192.168.81.134:3310（主节点master）、192.168.81.134:3311（主节点master1）、192.168.81.134:3308（从节点slave2）

**2、双主热备、主从复制架构**

**   ** master和master1互为主从关系，slave1为单独从节点

（1）master与master1双主配置

在master节点的my.cnf文件配置如下

#主从复制配置
    server-id =1
    log-bin = mysql-bin
    binlog-ignore-db = mysql
    binlog-ignore-db = information_schema
    binlog-ignore-db = performance-schema
    binlog-do-db = canteen_db
    #binlog_format=mixed
    #主-主热备主库配置
    #log-slave-updates
    #sync_binlog = 1
    auto-increment-offset = 1
    auto-increment-increment = 2
    slave-skip-errors = all
    replicate-do-db = canteen_db
    replicate-ignore-db = mysql,information-schema,performance-schema

在master1的节点的my.cnf配置如下

#主从复制从库配置
    #binlog_format=mixed
    server-id = 6
    log-bin = mysql-bin
    replicate-do-db = canteen_db
    replicate-ignore-db = mysql
    replicate-ignore-db = information_schema
    replicate-ignore-db = performance_schema
    #双主热备从库配置
    #sync_binlog = 1
    auto-increment-increment = 2
    auto-increment-offset = 1
    slave-skip-errors = all
    binlog-do-db = canteen_db
    binlog-ignore-db = mysql,information_schema,performance_schema

分别启动master、 master1节点，打开会话执行主节点授权：

grant replication  slave,reload,super  on *.*   to   'root'@'192.168.81.134'   identified  by  'root';
    
    flush privileges;
    
    show  master  status;

master从机授权

change  master to master_host='192.168.81.134', master_user='root',master_password='root',master_port=3311,
             master_log_file='mysql-bin.000003',master_log_pos=619;
    		 
    flush privileges;
    
    start slave;
    
    show  slave  STATUS;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 1][]

master1从机授权

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 2][]

（2）slave2从节点my.cnf配置

server-id = 3
    log-bin=mysql-bin
    #binlog_format=mixed
    replicate-do-db=canteen_db
    replicate-ignore-db=mysql
    replicate-ignore-db=information_schema
    replicate-ignore-db=performance_schema

启动slave2节点，设置从机授权，这样一个简单的双主热备主从复制就搭建好了。

（3）故障转移

双主热备的目的是为了实现故障转移，一旦maste节点挂了，master1还能继续充当主节点工作，保证集群的高可用。

** 3、 在msyql集群中接入keepalived，实现主节点自动切换**

（1） 实现高可用，分别在两台虚拟机(192.168.81.81.134、192.168.81.135)上安装keepalived和mysql,双主热备配置按照上述mysql热备步骤配置即可，端口都设置为3310，使用docker创建，容器名称为master1-mysql；然后配置keepalived，现在以192.168.81.81.134为主机，192.168.81.135为备机

keepalived安装

yum install -y curl gcc openssl-devel libnl3-devel net-snmp-devel
    yum install -y keepalived

keepalived配置 ，切换至  /etc/keepalived/目录下，编辑keepalived.conf文件。

主机192.168.81.134配置如下：

global_defs {
      default_interface eth0
      router_id LVS_DEVEL
    }
    
    vrrp_instance VI_1 {
      #网卡配置要与所在物理机一直有eth0 或者ens33
      interface eth0
      #主机MASTER 从机BACKUP
      state MASTER
      #keepalive集群id
      virtual_router_id 51
      advert_int 1
      #优先级
      priority 150
      #不抢占
      nopreempt
      #本机实际ip
      mcast_src_ip 192.168.81.134
    
      #虚拟化ip，可以有多个
      virtual_ipaddress {
        192.168.81.205
      }
    
      #集群节点通信授权
      authentication {
        auth_type PASS
        auth_pass 11111
      }
    
    
      notify "/home/master/shell/notify.sh"
    }

其中notify.sh用来检测134机器上的mysql，一旦134的mysql挂了，134的keepalived调用次脚本停止服务，这时外部访问自动切换到135的keepalived上，这样就可以实现故障转移

\#notify.sh

#!/bin/bash
    cname=master1-mysql
    upStatus=Up
    echo "keepalived开始执行$cname健康检查"
    while(true)
    do
      conStatus=`docker ps -a| grep $cname | awk '{print $7}'`
      if [[ "$upStatus" == "$conStatus" ]]; then
        echo "$cname容器状态$conStatus健康运行"
        sleep 5
      else
        echo "$cname容器状态$conStatus异常运行,停止本机keepalived运行切换至BACKUP"
        systemctl stop keepalived.service
        if [ $? -eq 0 ]; then
            echo "关闭keepalived成功"
        else
            cho "关闭keepalived失败"
        fi
        break  
      fi
    done

b、 备机192.168.81.135配置如下：

启动keepalived后，mysql访问虚拟ip地址 192.168.81.205

（2）关于keepalived的操作命令：

启动keepalived

systemctl start keepalived

加入开机启动keepalive

systemctl enable keepalived

重新启动keepalived

systemctl restart keepaived

查看keepalived状态

systemctl status keepalived

停止服务keepaived

systemctl stop keepalived

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70]: /images/20221021/13cb503a6a214ac4856a537f0a261f42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 1]: /images/20221021/68498526096b415482e5eab52d8146a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70 2]: /images/20221021/4fb6a981afb141acab6a0e278d268403.png