MySQL主从高可用之Keepalived

最新推荐文章于 2024-03-29 17:48:15 发布

只是甲

最新推荐文章于 2024-03-29 17:48:15 发布

阅读量1.1k

点赞数

分类专栏： # MySQL运维 MySQL从小工到专家之路文章标签：数据库 linux mysql

本文链接：https://blog.csdn.net/u010520724/article/details/108315020

版权

MySQL从小工到专家之路同时被 2 个专栏收录

181 篇文章 23 订阅

订阅专栏

MySQL运维

38 篇文章 9 订阅

订阅专栏

文章目录

前言
一.环境准备
二.Keepalived安装
三.主从配置文件
四.测试
参考文献

前言

MySQL配置主从后，从库作为容灾或只读库使用，如果主库宕机，需要DBA手工执行主从切换，然后通知应用层修改数据库配置。
宕机时间长，需要人工干预，严重影响用户的使用。

keepalived是由C语言编写的路由软件，是lvs的扩展项目，主要用作RealServer的健康状态检查以及LoadBalance主机和BackUP主机之间failover的实现。

当出现主库宕机的时候，vip会漂到备库，因为应用是通过vip进行连接的，所以除了漂移和切换过程中会存在短暂的不可用，DBA和开发人员无用认为干预，切换完成后，应用恢复正常。

一.环境准备

服务器类别	IP	数据库版本
主库	192.168.148.143	5.6.37	CentOS release 6.5
备库	192.168.148.144	5.6.37	CentOS release 6.5
	192.168.148.240(虚拟IP)

主从环境已经搭建完毕
虚拟IP需要同网段未使用的IP，不然会存在网络冲突

二.Keepalived安装

备注:主从需同时安装
下载地址:
https://www.keepalived.org/download.html

安装步骤:

yum -y install openssl-devel
cd /usr/local/src
tar -zxvf keepalived-1.2.13.tar.gz
cd keepalived-1.2.13
./configure && make && make install
cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived
cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/sbin/keepalived /usr/sbin/
chkconfig --add keepalived
chkconfig --level 345 keepalived on

三.主从配置文件

3.1 keepalived配置文件

主库配置文件

[root@mater ~]# ifconfig
eth2      Link encap:Ethernet  HWaddr 00:0C:29:EA:02:F7  
          inet addr:192.168.148.143  Bcast:192.168.148.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:feea:2f7/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:12367 errors:0 dropped:0 overruns:0 frame:0
          TX packets:12464 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:6312726 (6.0 MiB)  TX bytes:1280299 (1.2 MiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:4 errors:0 dropped:0 overruns:0 frame:0
          TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:240 (240.0 b)  TX bytes:240 (240.0 b)

[root@mater ~]# more /etc/keepalived/keepalived.conf
global_defs {
   router_id MySQL-HA
} 

vrrp_script check_run {
script "/home/mysql/mysql_check.sh"
interval 60
}

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth2
    virtual_router_id 51
    priority 100  
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    track_script {
    check_run
    }
    notify_master /home/mysql/master.sh
    notify_stop /home/mysql/stop.sh

    virtual_ipaddress {
        192.168.148.240
    }
}

从库配置文件
master与slave的keepalived配置文件中只有priority设置不同，master为100，slave为90，其它全一样。配置文件是以块形式组织的，每个块都在{}包围的范围内，#和！开头的行都是注释。

[root@candicate ~]# ifconfig
eth2      Link encap:Ethernet  HWaddr 00:0C:29:05:64:83  
          inet addr:192.168.148.144  Bcast:192.168.148.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fe05:6483/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:11968 errors:0 dropped:0 overruns:0 frame:0
          TX packets:6098 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:6166006 (5.8 MiB)  TX bytes:750631 (733.0 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:12 errors:0 dropped:0 overruns:0 frame:0
          TX packets:12 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:1344 (1.3 KiB)  TX bytes:1344 (1.3 KiB)

[root@candicate ~]# more /etc/keepalived/keepalived.conf 
global_defs {
   router_id MySQL-HA
} 

vrrp_script check_run {
script "/home/mysql/mysql_check.sh"
interval 60
}

vrrp_sync_group VG1 {
group {
VI_1
}
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth2 
    virtual_router_id 51
    priority 90  
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    track_script {
    check_run
    }
    notify_master /home/mysql/master.sh
    notify_stop /home/mysql/stop.sh

    virtual_ipaddress {
        192.168.148.240
    }
}

3.2 keepalived配置文件参数介绍

参数名	参数描述
router-id	主机标识符,主从可以相同也可以不相同
state	表示keepalived角色，都是设成BACKUP则以优先级为主要参考
interface	指定HA监听的网络接口 ifconfig看到的网络接口名，此处是eth2
virtual_router_id	虚拟路由标识，取值0-255，master-1和master-2保持一致
priority	优先级，用来选举master，取值范围1-255
advert_int	发VRRP包时间间隔，即多久进行一次master选举
nopreempt	不抢占，即允许一个priority值比较低的一个节点作为master
delay_loop	设置运行情况检查时间，单位为秒
lb_algorr	设置后端调度器算法，rr为轮询算法
lb_kindDR	设置LVS实现负载均衡的机制，有DR、NAT、TUN三种模式可选
persistence_timeout	会话保持时间，单位为秒
protocol	指定转发协议，有 TCP和UDP可选
notify_down	检查mysql服务down掉后执行的脚本

3.3 检查及启停脚本

主备两边的脚本相同
/home/mysql/mysql_check.sh文件用以检测MySQL服务是否正常，当发现连接不上mysql，自动把keepalived进程杀掉，让VIP进行漂移。

/home/mysql/master.sh的作用是状态改为master以后执行的脚本。首先判断复制是否有延迟，如果有延迟，等1分钟后，不论是否有延迟，都并停止复制，并且记录binlog和pos点。

/home/mysql/stop.sh表示Keepalived停止以后需要执行的脚本。检查是否还有复制写入操作，最后无论是否执行完毕都退出。

[mysql@master ~]$ more /home/mysql/mysql_check.sh 
#!/bin/bash
. /home/mysql/.bashrc
count=1

while true
do

/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306//mysql.sock -e "show status;" > /dev/null 2>&1
i=$?
ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
j=$?
if [ $i = 0 ] && [ $j = 0 ]
then
   exit 0
else
   if [ $i = 1 ] && [ $j = 0 ]
   then
       exit 0
   else
        if [ $count -gt 5 ]
        then
              break
        fi
   let count++
   continue
   fi
fi

done

/etc/init.d/keepalived stop
[mysql@master ~]$ 
[mysql@master ~]$ more /home/mysql/master.sh 
#!/bin/bash

. /home/mysql/.bashrc

Master_Log_File=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
Relay_Master_Log_File=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
Read_Master_Log_Pos=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
Exec_Master_Log_Pos=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}')

i=1

while true
do

if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ]
then
   echo "ok"
   break
else
   sleep 1

   if [ $i -gt 60 ]
   then
      break
   fi
   continue
   let i++
fi
done

/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "stop slave;"
/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "reset slave all;"
/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show master status;" > /tmp/master_status_$(date "+%y%m%d-%H%M").txt
[mysql@master ~]$ 
[mysql@master ~]$ 
[mysql@master ~]$ more /home/mysql/stop.sh 
#!/bin/bash

. /home/mysql/.bashrc

M_File1=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show master status\G" | awk -F': ' '/File/{print $2}')
M_Position1=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show master status\G" | awk -F': ' '/Position/{print $2}')
sleep 1
M_File2=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show master status\G" | awk -F': ' '/File/{print $2}')
M_Position2=$(/u01/my3306/bin/mysql -uroot -ptest -S /u01/my3306/mysql.sock -e "show master status\G" | awk -F': ' '/Position/{print $2}')

i=1

while true
do

if [ $M_File1 = $M_File1 ] && [ $M_Position1 -eq $M_Position2 ]
then
   echo "ok"
   break
else
   sleep 1

   if [ $i -gt 60 ]
   then
      break
   fi
   continue
   let i++
fi
done

3.4 启动keepalived

/etc/init.d/keepalived start

主库查看vip是否绑定在eth2网卡上

如果未成功，查看日志，并调整配置文件

[root@mater ~]# tail -100f /var/log/messages
Aug 22 20:50:53 mater Keepalived_vrrp[5284]: Registering Kernel netlink reflector
Aug 22 20:50:53 mater Keepalived_vrrp[5284]: Registering Kernel netlink command channel
Aug 22 20:50:53 mater Keepalived_healthcheckers[5283]: Netlink reflector reports IP 192.168.148.143 added
Aug 22 20:50:53 mater Keepalived_healthcheckers[5283]: Netlink reflector reports IP fe80::20c:29ff:feea:2f7 added
Aug 22 20:50:53 mater Keepalived_healthcheckers[5283]: Registering Kernel netlink reflector
Aug 22 20:50:53 mater Keepalived_healthcheckers[5283]: Registering Kernel netlink command channel
Aug 22 20:50:53 mater Keepalived_vrrp[5284]: Registering gratuitous ARP shared channel
Aug 22 20:51:13 mater Keepalived_vrrp[5284]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 22 20:51:13 mater Keepalived_vrrp[5284]: Configuration is using : 66310 Bytes
Aug 22 20:51:13 mater Keepalived_vrrp[5284]: Using LinkWatch kernel netlink reflector...
Aug 22 20:51:13 mater Keepalived_healthcheckers[5283]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 22 20:51:13 mater Keepalived_healthcheckers[5283]: Configuration is using : 6333 Bytes
Aug 22 20:51:13 mater Keepalived_vrrp[5284]: VRRP_Instance(VI_1) Entering BACKUP STATE
Aug 22 20:51:13 mater Keepalived_vrrp[5284]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Aug 22 20:51:13 mater Keepalived_healthcheckers[5283]: Using LinkWatch kernel netlink reflector...
Aug 22 20:51:13 mater Keepalived[5281]: Stopping Keepalived v1.2.13 (08/22,2020)
Aug 22 20:52:07 mater Keepalived[5364]: Starting Keepalived v1.2.13 (08/22,2020)
Aug 22 20:52:07 mater Keepalived[5365]: Starting Healthcheck child process, pid=5367
Aug 22 20:52:07 mater Keepalived[5365]: Starting VRRP child process, pid=5368
Aug 22 20:52:07 mater Keepalived_vrrp[5368]: Netlink reflector reports IP 192.168.148.143 added
Aug 22 20:52:07 mater Keepalived_vrrp[5368]: Netlink reflector reports IP fe80::20c:29ff:feea:2f7 added
Aug 22 20:52:07 mater Keepalived_vrrp[5368]: Registering Kernel netlink reflector
Aug 22 20:52:07 mater Keepalived_vrrp[5368]: Registering Kernel netlink command channel
Aug 22 20:52:07 mater Keepalived_vrrp[5368]: Registering gratuitous ARP shared channel
Aug 22 20:52:07 mater Keepalived_healthcheckers[5367]: Netlink reflector reports IP 192.168.148.143 added
Aug 22 20:52:07 mater Keepalived_healthcheckers[5367]: Netlink reflector reports IP fe80::20c:29ff:feea:2f7 added
Aug 22 20:52:07 mater Keepalived_healthcheckers[5367]: Registering Kernel netlink reflector
Aug 22 20:52:07 mater Keepalived_healthcheckers[5367]: Registering Kernel netlink command channel
Aug 22 20:52:27 mater Keepalived_vrrp[5368]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 22 20:52:27 mater Keepalived_vrrp[5368]: Configuration is using : 66310 Bytes
Aug 22 20:52:27 mater Keepalived_vrrp[5368]: Using LinkWatch kernel netlink reflector...
Aug 22 20:52:27 mater Keepalived_healthcheckers[5367]: Opening file '/etc/keepalived/keepalived.conf'.
Aug 22 20:52:27 mater Keepalived_healthcheckers[5367]: Configuration is using : 6333 Bytes
Aug 22 20:52:27 mater Keepalived_healthcheckers[5367]: Using LinkWatch kernel netlink reflector...
Aug 22 20:52:27 mater Keepalived_vrrp[5368]: VRRP_Instance(VI_1) Entering BACKUP STATE
Aug 22 20:52:27 mater Keepalived_vrrp[5368]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Aug 22 20:52:27 mater Keepalived_vrrp[5368]: VRRP_Script(check_run) succeeded
Aug 22 20:52:30 mater Keepalived_vrrp[5368]: VRRP_Instance(VI_1) Transition to MASTER STATE
Aug 22 20:52:30 mater Keepalived_vrrp[5368]: VRRP_Group(VG1) Syncing instances to MASTER state
Aug 22 20:52:31 mater Keepalived_vrrp[5368]: VRRP_Instance(VI_1) Entering MASTER STATE
Aug 22 20:52:31 mater Keepalived_vrrp[5368]: VRRP_Instance(VI_1) setting protocol VIPs.
Aug 22 20:52:31 mater Keepalived_vrrp[5368]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth2 for 192.168.148.240
Aug 22 20:52:31 mater Keepalived_healthcheckers[5367]: Netlink reflector reports IP 192.168.148.240 added
Aug 22 20:52:33 mater ntpd[1532]: Listen normally on 6 eth2 192.168.148.240 UDP 123
Aug 22 20:52:33 mater ntpd[1532]: peers refreshed
Aug 22 20:52:36 mater Keepalived_vrrp[5368]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth2 for 192.168.148.240
Aug 22 21:00:11 mater dhclient[1386]: DHCPREQUEST on eth2 to 192.168.148.254 port 67 (xid=0x41f5cfe)
Aug 22 21:00:11 mater dhclient[1386]: DHCPACK from 192.168.148.254 (xid=0x41f5cfe)
Aug 22 21:00:11 mater dhclient[1386]: bound to 192.168.148.143 -- renewal in 753 seconds.
Aug 22 21:00:11 mater NetworkManager[1336]: <info> (eth2): DHCPv4 state changed renew -> renew
Aug 22 21:00:11 mater NetworkManager[1336]: <info>   address 192.168.148.143
Aug 22 21:00:11 mater NetworkManager[1336]: <info>   prefix 24 (255.255.255.0)
Aug 22 21:00:11 mater NetworkManager[1336]: <info>   gateway 192.168.148.2
Aug 22 21:00:11 mater NetworkManager[1336]: <info>   nameserver '192.168.148.2'
Aug 22 21:00:11 mater NetworkManager[1336]: <info>   domain name 'localdomain'
Aug 22 21:12:44 mater dhclient[1386]: DHCPREQUEST on eth2 to 192.168.148.254 port 67 (xid=0x41f5cfe)
Aug 22 21:12:44 mater dhclient[1386]: DHCPACK from 192.168.148.254 (xid=0x41f5cfe)
Aug 22 21:12:44 mater dhclient[1386]: bound to 192.168.148.143 -- renewal in 740 seconds.
Aug 22 21:12:44 mater NetworkManager[1336]: <info> (eth2): DHCPv4 state changed renew -> renew
Aug 22 21:12:44 mater NetworkManager[1336]: <info>   address 192.168.148.143
Aug 22 21:12:44 mater NetworkManager[1336]: <info>   prefix 24 (255.255.255.0)
Aug 22 21:12:44 mater NetworkManager[1336]: <info>   gateway 192.168.148.2
Aug 22 21:12:44 mater NetworkManager[1336]: <info>   nameserver '192.168.148.2'
Aug 22 21:12:44 mater NetworkManager[1336]: <info>   domain name 'localdomain'
Aug 22 21:25:04 mater dhclient[1386]: DHCPREQUEST on eth2 to 192.168.148.254 port 67 (xid=0x41f5cfe)
Aug 22 21:25:04 mater dhclient[1386]: DHCPACK from 192.168.148.254 (xid=0x41f5cfe)
Aug 22 21:25:04 mater NetworkManager[1336]: <info> (eth2): DHCPv4 state changed renew -> renew
Aug 22 21:25:04 mater NetworkManager[1336]: <info>   address 192.168.148.143
Aug 22 21:25:04 mater NetworkManager[1336]: <info>   prefix 24 (255.255.255.0)
Aug 22 21:25:04 mater NetworkManager[1336]: <info>   gateway 192.168.148.2
Aug 22 21:25:04 mater NetworkManager[1336]: <info>   nameserver '192.168.148.2'
Aug 22 21:25:04 mater NetworkManager[1336]: <info>   domain name 'localdomain'
Aug 22 21:25:04 mater dhclient[1386]: bound to 192.168.148.143 -- renewal in 859 seconds.
Aug 22 21:39:23 mater dhclient[1386]: DHCPREQUEST on eth2 to 192.168.148.254 port 67 (xid=0x41f5cfe)
Aug 22 21:39:23 mater dhclient[1386]: DHCPACK from 192.168.148.254 (xid=0x41f5cfe)
Aug 22 21:39:23 mater NetworkManager[1336]: <info> (eth2): DHCPv4 state changed renew -> renew
Aug 22 21:39:23 mater NetworkManager[1336]: <info>   address 192.168.148.143
Aug 22 21:39:23 mater NetworkManager[1336]: <info>   prefix 24 (255.255.255.0)
Aug 22 21:39:23 mater NetworkManager[1336]: <info>   gateway 192.168.148.2
Aug 22 21:39:23 mater NetworkManager[1336]: <info>   nameserver '192.168.148.2'
Aug 22 21:39:23 mater NetworkManager[1336]: <info>   domain name 'localdomain'
Aug 22 21:39:23 mater dhclient[1386]: bound to 192.168.148.143 -- renewal in 728 seconds.
Aug 22 21:51:31 mater dhclient[1386]: DHCPREQUEST on eth2 to 192.168.148.254 port 67 (xid=0x41f5cfe)
Aug 22 21:51:31 mater dhclient[1386]: DHCPACK from 192.168.148.254 (xid=0x41f5cfe)
Aug 22 21:51:31 mater NetworkManager[1336]: <info> (eth2): DHCPv4 state changed renew -> renew
Aug 22 21:51:31 mater NetworkManager[1336]: <info>   address 192.168.148.143
Aug 22 21:51:31 mater NetworkManager[1336]: <info>   prefix 24 (255.255.255.0)
Aug 22 21:51:31 mater NetworkManager[1336]: <info>   gateway 192.168.148.2
Aug 22 21:51:31 mater NetworkManager[1336]: <info>   nameserver '192.168.148.2'
Aug 22 21:51:31 mater NetworkManager[1336]: <info>   domain name 'localdomain'
Aug 22 21:51:31 mater dhclient[1386]: bound to 192.168.148.143 -- renewal in 843 seconds.
Aug 22 22:05:34 mater dhclient[1386]: DHCPREQUEST on eth2 to 192.168.148.254 port 67 (xid=0x41f5cfe)
Aug 22 22:05:34 mater dhclient[1386]: DHCPACK from 192.168.148.254 (xid=0x41f5cfe)
Aug 22 22:05:34 mater dhclient[1386]: bound to 192.168.148.143 -- renewal in 870 seconds.
Aug 22 22:05:34 mater NetworkManager[1336]: <info> (eth2): DHCPv4 state changed renew -> renew
Aug 22 22:05:34 mater NetworkManager[1336]: <info>   address 192.168.148.143
Aug 22 22:05:34 mater NetworkManager[1336]: <info>   prefix 24 (255.255.255.0)
Aug 22 22:05:34 mater NetworkManager[1336]: <info>   gateway 192.168.148.2
Aug 22 22:05:34 mater NetworkManager[1336]: <info>   nameserver '192.168.148.2'
Aug 22 22:05:34 mater NetworkManager[1336]: <info>   domain name 'localdomain'

四.测试

4.1 通过vip远程登录mysql

通过vip远程登录mysql

建表并录入数据

mysql> create table t2(id int,name varchar(100));
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> insert into t2 values (1,'abc');
Query OK, 1 row affected (0.02 sec)

4.2 模拟mysql 主库 crash

主库执行

pkill -9 mysqld

[root@mater mysql]# pkill -9 mysqld
[root@mater mysql]# 
[root@mater mysql]# 
[root@mater mysql]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:ea:02:f7 brd ff:ff:ff:ff:ff:ff
    inet 192.168.148.143/24 brd 192.168.148.255 scope global eth2
    inet6 fe80::20c:29ff:feea:2f7/64 scope link 
       valid_lft forever preferred_lft forever

查看vip是否飘到备库

[root@candicate ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:05:64:83 brd ff:ff:ff:ff:ff:ff
    inet 192.168.148.144/24 brd 192.168.148.255 scope global eth2
    inet 192.168.148.240/32 scope global eth2
    inet6 fe80::20c:29ff:fe05:6483/64 scope link 
       valid_lft forever preferred_lft forever
[root@candicate ~]#

此时客户端正常连接

查看切换的时候的binlog位置
[root@candicate tmp]# more /tmp/master_status_200822-2249.txt
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
binlog.000001 120

4.3 切换后的处理

切换后，排查主库问题，启动主库，然后将主库变成新主库(原从库)的从库
测试过，当新主库异常关闭后，会自动切换到新的从库

参考文献

1.https://blog.csdn.net/wzy0623/article/details/80916567
2.https://blog.csdn.net/leshami/article/details/42010495
3.https://blog.csdn.net/wade1010/article/details/88858050

keepalived下载:
https://www.keepalived.org/download.html

只是甲

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL主从高可用之Keepalived

文章目录前言一.环境准备二.Keepalived安装三.主从配置文件3.1 keepalived配置文件3.2 keepalived配置文件参数介绍3.3 检查及启停脚本3.4 启动keepalived四.测试4.1 通过vip远程登录mysql4.2 模拟mysql 主库 crash4.3 切换后的处理参考文献前言MySQL配置主从后，从库作为容灾或只读库使用，如果主库宕机，需要DBA手工执行主从切换，然后通知应用层修改数据库配置。宕机时间长，需要人工干预，严重影响用户的使用。keepalived
复制链接

扫一扫