#干货满满#MySQL主从部署

MySQL主从部署

前提：需要搭建好2台mysql服务器

具体搭建步骤可以参考我的另一篇文章；

https://bbs.sangfor.com.cn/forum.php?mod=viewthread&tid=250949

1.安装相关依赖包，并下载keepalived安装包，解压，配置，编译

cd /opt

yum -y install gcc openssl-devel popt-devel psmisc

wget https://www.keepalived.org/software/keepalived-2.2.7.tar.gz

tar -zxvf keepalived-2.2.7.tar.gz

cd keepalived-2.2.7

./configure --prefix=/opt/keepalived-2.2.7

make && make install

2.将文件复制到对应目录下

mkdir /etc/keepalived

cp keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf

cp keepalived/etc/init.d/keepalived /etc/init.d/

cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp bin/keepalived /usr/sbin/

3.新建/etc/keepalived/shutdown.sh文件，内容为：

vi /etc/keepalived/shutdown.sh

#!/bin/bash

#该脚本是在mysql服务出现异常时，将keepalived应用停止，从而使虚拟vip主机自动连接到另一台mysql上

killall keepalived

4. 使用ifconfig命令查看下网卡名称5. 修改主服务器/etc/keepalived/keepalived.conf配置文件，内容如下：

! Configuration File for keepalived

global_defs {

   notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

   notification_email_from Alexandre.Cassen@firewall.loc

   smtp_server 192.168.46.1

   smtp_connect_timeout 30

   router_id LVS_MASTER

   vrrp_skip_check_adv_addr

   vrrp_strict

   vrrp_garp_interval 0

   vrrp_gna_interval 0

}

vrrp_instance VI_1 {

    state MASTER

    #interface为刚才查到的本机网卡名称

    interface ens33

    #同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。

    #如server-1、server-2为一组，virtual_router_id=51

    #server-3、server-4为一组，则virtual_router_id不能为51

    virtual_router_id 51

    #字数越大，优先级越高，master必须大于backup

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

       #该ip为虚拟出来的vip地址

       192.168.46.200

    }

}

#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口

virtual_server 192.168.46.200 3306 {

    delay_loop 6

    lb_algo rr

    lb_kind NAT

    persistence_timeout 50

    protocol TCP

    #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口

    real_server 192.168.46.146 3306 {

        #当该ip 端口连接异常时，执行该脚本

        notify_down /etc/keepalived/shutdow.sh

        TCP_CHECK {

            #实际物理机ip地址

            connect_ip 192.168.46.146

            #实际物理机port端口

            connect_port 3306

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

        }

    }

}

6. 修改从服务器/etc/keepalived/keepalived.conf配置文件，详细内容如下：

! Configuration File for keepalived

global_defs {

   notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

   notification_email_from Alexandre.Cassen@firewall.loc

   smtp_server 192.168.46.1

   smtp_connect_timeout 30

   router_id LVS_MASTER

   vrrp_skip_check_adv_addr

   vrrp_strict

   vrrp_garp_interval 0

   vrrp_gna_interval 0

}

vrrp_instance VI_1 {

    state BACKUP

    #interface为刚才查到的本机网卡名称

    interface ens33

    #同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。

    #如server-1、server-2为一组，virtual_router_id=51

    #server-3、server-4为一组，则virtual_router_id不能为51

    virtual_router_id 51

    #字数越大，优先级越高，master必须大于backup

    priority 90

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

       #该ip为虚拟出来的vip地址

       192.168.46.200

    }

}

#配置virtual_server  ip为上面配置的虚拟vip地址  端口为mysql的端口

virtual_server 192.168.46.200 3306 {

    delay_loop 6

    lb_algo rr

    lb_kind NAT

    persistence_timeout 50

    protocol TCP

    #real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口

    real_server 192.168.46.147 3306 {

        #当该ip 端口连接异常时，执行该脚本

        notify_down /etc/keepalived/shutdow.sh

        TCP_CHECK {

            #实际物理机ip地址

            connect_ip 192.168.46.147

            #实际物理机port端口

            connect_port 3306

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

        }

    }

}

7. 将主从服务器keepalived加入开机自启，并启动服务，查看服务状态

systemctl enable keepalived

systemctl start keepalived

systemctl status keepalived

8. 通过虚拟出来的VIP服务器地址查看MySQL数据库