mysql启用keepalive_mysql主从之keepalive+MySQL高可用

最新推荐文章于 2024-03-22 09:51:39 发布

weixin_39606137

最新推荐文章于 2024-03-22 09:51:39 发布

阅读量112

点赞数

文章标签： mysql启用keepalive

本文链接：https://blog.csdn.net/weixin_39606137/article/details/111492145

版权

一 keepalive介绍

1.1 keepalived 是什么

keepalived 是集群管理中保证集群高可用的一个服务软件，用来防止单点故障。

1.2 keepalived 工作原理

keepalived 是以VRRP 协议为实现基础的，VRRP 全称VirtualRouter Redundancy Protocol，即虚拟路由冗余协议。虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N 台提供相同功能的路由器组成一个路由器组，这个组里面有一个master 和多个backup，master 上面有一个对外提供服务的vip(该路由器所在局域网内其他机器的默认路由为该vip)，master 会发组播，当backup 收不到vrrp 包时就认为master 宕掉了，这时就需要根据VRRP 的优先级来选举一个backup 当master。这样的话就可以保证路由器的高可用了。

keepalived 主要有三个模块，分别是core、check 和vrrp。

core 模块为keepalived 的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。

check 负责健康检查，包括常见的各种检查方式。

vrrp 模块是来实现VRRP 协议的。

1.3 实验环境

master1:192.168.132.121

master2:192.168.132.122

虚拟IP:192.168.132.120

双主环境

master1

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.132.122Master_User: replication

Master_Port:3306Connect_Retry:60Master_Log_File: master-bin.000003Read_Master_Log_Pos:4975Relay_Log_File: relay-log.000003Relay_Log_Pos:913Relay_Master_Log_File: master-bin.000003Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:4975Relay_Log_Space:2618Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:2Master_UUID: 662134df-a196-11e9-b432-000c2963fd11

Master_Info_File:/data/mysql/master.infoSQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor moreupdates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 662134df-a196-11e9-b432-000c2963fd11:1-7Executed_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18,

662134df-a196-11e9-b432-000c2963fd11:1-7Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

master2

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.132.121Master_User: replication

Master_Port:3306Connect_Retry:60Master_Log_File: master-bin.000008Read_Master_Log_Pos:650Relay_Log_File: relay-log.000008Relay_Log_Pos:865Relay_Master_Log_File: master-bin.000008Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:650Relay_Log_Space:2098Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1Master_UUID: 63a7f26f-a196-11e9-a2b2-000c2991dd19

Master_Info_File:/data/mysql/master.infoSQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor moreupdates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18Executed_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18,

662134df-a196-11e9-b432-000c2963fd11:1-7Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

二配置keepalive

2.1 开始安装keepalived

[root@master1 ~]# cd /usr/local/src/

[root@master1 src]# wget https://www.keepalived.org/software/keepalived-2.0.17.tar.gz

[root@master1 src]# tar -xf keepalived-2.0.17.tar.gz

[root@master1 src]# cd keepalived-2.0.17

[root@master1 keepalived-2.0.17]# yum install openssl* libnl‐dev* gcc-c++

[root@master1 keepalived-2.0.17]# ./configure --prefix=/usr/local/keepalived

[root@master1 keepalived-2.0.17]# make

[root@master1 keepalived-2.0.17]# make install

[root@master1 keepalived-2.0.17]# cd /usr/local/keepalived/sbin/

[root@master1 sbin]# ll

-rwxr-xr-x. 1 root root 2381368 Jul 9 11:59 keepalived

[root@master1 sbin]# cp keepalived /usr/sbin/

[root@master1 sbin]# ll /usr/sbin/keepalived

-rwxr-xr-x. 1 root root 2381368 Jul 9 12:01 /usr/sbin/keepalived

[root@master1 sbin]# systemctl list-unit-files|grep keepalived

[root@master1 sbin]# systemctl enable keepalived

[root@master1 sbin]# systemctl list-unit-files|grep keepalived

[root@master1 sbin]# mkdir /etc/keepalived

[root@master1 sbin]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

[root@master1 sbin]# ll /etc/keepalived/

-rw-r--r--. 1 root root 3550 Jul 9 12:06 keepalived.conf

2.2 配置keepailve

root@master1 keepalived-2.0.17]# vim /etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global_defs {

router_id db01

}

vrrp_instance VI_1 {

state BACKUP

interface ens33

virtual_router_id51priority100nopreempt

advert_int1authentication {

auth_type PASS

auth_pass1111}

virtual_ipaddress {192.168.132.120}

}

virtual_server192.168.132.120 3306{

delay_loop6persistence_timeout50protocol TCP

real_server192.168.132.121 3306{

notify_down/etc/keepalived/kill_keepalived.shTCP_CHECK {

connect_timeout3nb_get_retry3connect_port3306delay_before_retry3}

}

配置简介

global_defs 区域主要是配置故障发生时的通知对象以及机器标识

notification_email_from Alexandre.Cassen@firewall.loc//通知邮件从哪个地址发出

smtp_server 192.168.200.1 //通知邮件的smtp 地址

smtp_connect_timeout 30 //连接smtp 服务器的超时时间

router_id db01 //标识本节点的字符串，通常为hostname

vrrp_skip_check_adv_addr

vrrp_strict

vrrp_garp_interval0vrrp_gna_interval0}

vrrp_instance VI_1 {//VRRP 实例state MASTER//MASTER 或BACKUP，为了防止脑裂现象，主备均需要设置为backup 模式，master 模式会抢占VIP

interface eth0 //节点固有IP(非VIP)的网卡，用来发VRRP 包

virtual_router_id 51 //取值在0-255 之间，用来区分多个instance 的VRRP 组播priority 100//优先级，同一个vrrp_instance 的MASTER优先级必须比BACKUP 高nopreempt//非抢占，配合backup,防止切换后，主库服务恢复正常后，IP 漂移过来advert_int 1//MASTER 与BACKUP 负载均衡器之间同步检查的时间间隔，单位为秒。

authentication {

auth_type PASS

auth_pass1111}

virtual_ipaddress {//虚拟ip 地址,可以有多个地址，每个地址占一行，不需要子网掩码

192.168.132.120}

}

virtual_server192.168.200.16 3306{

delay_loop6 //健康检查间隔，单位为秒

persistence_timeout 50 //会话保持时间，就是把用户请求转发给同一个服务器

protocol TCP //转发协议，有TCP 和UDP 两种，一般用TCP

real_server 192.168.132.121 3306 { //真实服务器，包括IP和端口号

notify_down kill_keepalived.shTCP_CHECK { #通过tcpcheck 判断RealServer 的健康状态

connect_timeout3#连接超时时间

nb_get_retry3#重连次数

delay_before_retry3#重连时间间隔

connect_port3306#检测端口

}

keepalived 服务配置

#设置不抢占模式(DB1 设置即可)

nopreempt

#虚拟路由标识，这个标识是一个数字(1‐255)，在一个VRRP

实例中主备服务器ID 必须一样

virtual_router_id 68

#优先级，数字越大优先级越高，在一个实例中主服务器优

先级要高于备服务器

priority 100 #从服务器99

#设置主备之间同步检查的时间间隔单位秒

advert_int 1

[root@master1 keepalived-2.0.17]# vim /etc/keepalived/kill_keepalived.sh

[root@master1 keepalived-2.0.17]# chmod +x /etc/keepalived/kill_keepalived.sh

#!/bin/bashkill -9 $(cat /var/run/keepalived.pid)

[root@master1 keepalived-2.0.17]# systemctl start keepalived

[root@master1 keepalived-2.0.17]# cat /var/run/keepalived.pid

16287

master2相同做法

配置文件

[root@master2 sbin]# cat /etc/keepalived/keepalived.conf

[root@master2 sbin]# cat /etc/keepalived/keepalived.conf! Configuration File forkeepalived

bal_defs {

router_id db02

}

vrrp_instance VI_1 {

state BACKUP

interface ens33

virtual_router_id51priority90nopreempt

advert_int1authentication {

auth_type PASS

auth_pass1111}

virtual_ipaddress {192.168.132.120}

}

virtual_server192.168.132.120 3306{

delay_loop6persistence_timeout50protocol TCP

real_server192.168.132.122 3306{

notify_down/etc/keepalived/kill_keepalived.shTCP_CHECK {

connect_timeout3nb_get_retry3connect_port3306delay_before_retry3}

}

2.3 检查状态

[root@master2 ~]# systemctl status keepalived

[root@master2 ~]# ps -ef|grep keepalived

[root@master1 ~]# ip addr

1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo

valid_lft forever preferred_lft forever

inet6 ::1/128scope host

valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:19brd ff:ff:ff:ff:ff:ff

inet192.168.132.121/24 brd 192.168.132.255scope global ens33

valid_lft forever preferred_lft forever

inet192.168.132.120/32scope global ens33 #虚拟IP地址

valid_lft forever preferred_lft forever

inet6 fe80::b469:7426:8024:a47c/64scope link

valid_lft forever preferred_lft forever3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:23brd ff:ff:ff:ff:ff:ff

inet192.168.43.85/24 brd 192.168.43.255scope global dynamic ens34

valid_lft 2810sec preferred_lft 2810sec

inet192.168.254.121/24 brd 192.168.254.255scope global ens34

valid_lft forever preferred_lft forever

inet6 fe80::4d8:1809:407a:5c0a/64scope link

valid_lft forever preferred_lft forever

master2查看

1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo

valid_lft forever preferred_lft forever

inet6 ::1/128scope host

valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:11brd ff:ff:ff:ff:ff:ff

inet192.168.132.122/24 brd 192.168.132.255scope global ens33

valid_lft forever preferred_lft forever

inet6 fe80::5d18:db0b:8907:de34/64scope link

valid_lft forever preferred_lft forever3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff

inet192.168.254.122/24 brd 192.168.254.255scope global ens34

valid_lft forever preferred_lft forever

inet6 fe80::c3d5:5055:468a:94f8/64scope link

valid_lft forever preferred_lft forever

三验证

授权后尝试使用192.168.132.20这个IP连接数据库

mysql> grant all on *.* to root@192.168.132.123 identified by '1234567';mysql>flush privileges;

[root@slave ~]# mysql -ureplication -h192.168.132.120 -p1234567

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| master1 |

| master2 |

| mysql |

| performance_schema |

| sys |

+--------------------+

关闭192.168.132.121的mysqld

[root@master1 ~]# systemctl stop mysqld

[root@master1 ~]# ip addr

1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo

valid_lft forever preferred_lft forever

inet6 ::1/128scope host

valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:19brd ff:ff:ff:ff:ff:ff

inet192.168.132.121/24 brd 192.168.132.255scope global ens33

valid_lft forever preferred_lft forever

inet6 fe80::b469:7426:8024:a47c/64scope link

valid_lft forever preferred_lft forever3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:23brd ff:ff:ff:ff:ff:ff

inet192.168.43.85/24 brd 192.168.43.255scope global dynamic ens34

valid_lft 3348sec preferred_lft 3348sec

inet192.168.254.121/24 brd 192.168.254.255scope global ens34

valid_lft forever preferred_lft forever

inet6 fe80::4d8:1809:407a:5c0a/64scope link

valid_lft forever preferred_lft forever

查看master2

[root@master2 ~]# ip addr

1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo

valid_lft forever preferred_lft forever

inet6 ::1/128scope host

valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:11brd ff:ff:ff:ff:ff:ff

inet192.168.132.122/24 brd 192.168.132.255scope global ens33

valid_lft forever preferred_lft forever

inet192.168.132.120/32scope global ens33 #虚拟IP已经转移过来

valid_lft forever preferred_lft forever

inet6 fe80::5d18:db0b:8907:de34/64scope link

valid_lft forever preferred_lft forever3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff

inet192.168.254.122/24 brd 192.168.254.255scope global ens34

valid_lft forever preferred_lft forever

inet6 fe80::c3d5:5055:468a:94f8/64scope link

valid_lft forever preferred_lft forever

使用slave连接

[root@slave ~]# mysql -uroot -h192.168.132.120 -p1234567mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| master1 |

| master2 |

| mysql |

| performance_schema |

| sys |

+--------------------+

依然可以访问,双主的高可用配置完成

在master1开启MySQL和keepalive后,虚拟IP并不会抢占回来,是因为设置的是非抢占模式,防止虚拟IP跳动影响性能

weixin_39606137

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql启用keepalive_mysql主从之keepalive+MySQL高可用

一 keepalive介绍1.1 keepalived 是什么keepalived 是集群管理中保证集群高可用的一个服务软件，用来防止单点故障。1.2 keepalived 工作原理keepalived 是以VRRP 协议为实现基础的，VRRP 全称VirtualRouter Redundancy Protocol，即虚拟路由冗余协议。虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N 台...
复制链接

扫一扫