mysql启用keepalive_mysql主从之keepalive+MySQL高可用
一 keepalive介绍1.1 keepalived 是什么keepalived 是集群管理中保证集群高可用的一个服务软件，用来防止单点故障。1.2 keepalived 工作原理keepalived 是以VRRP 协议为实现基础的，VRRP 全称VirtualRouter Redundancy Protocol，即虚拟路由冗余协议。虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N 台
一 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
master1
mysql>show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting formaster 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; waiting for 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: Waiting formaster 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; waiting for 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_id 51priority 100nopreempt
advert_int 1authentication {
auth_type PASS
auth_pass 1111}
virtual_ipaddress {
192.168.132.120}
}
virtual_server 192.168.132.120 3306{
delay_loop 6persistence_timeout 50protocol TCP
real_server 192.168.132.121 3306{
notify_down /etc/keepalived/kill_keepalived.shTCP_CHECK {
connect_timeout 3nb_get_retry 3connect_port 3306delay_before_retry 3}
}
}
配置简介
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_interval 0vrrp_gna_interval 0}
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_pass 1111}
virtual_ipaddress { //虚拟ip 地址,可以有多个地址，每个地址占一行，不需要子网掩码
192.168.132.120}
}
virtual_server 192.168.200.16 3306{
delay_loop 6 //健康检查间隔，单位为秒
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_timeout 3#连接超时时间
nb_get_retry 3#重连次数
delay_before_retry 3#重连时间间隔
connect_port 3306#检测端口
}
}
}
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/bash
kill -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_id 51priority 90nopreempt
advert_int 1authentication {
auth_type PASS
auth_pass 1111}
virtual_ipaddress {
192.168.132.120}
}
virtual_server 192.168.132.120 3306{
delay_loop 6persistence_timeout 50protocol TCP
real_server 192.168.132.122 3306{
notify_down /etc/keepalived/kill_keepalived.shTCP_CHECK {
connect_timeout 3nb_get_retry 3connect_port 3306delay_before_retry 3}
}
}
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:00inet 127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever
2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:19brd ff:ff:ff:ff:ff:ff
inet 192.168.132.121/24 brd 192.168.132.255scope global ens33
valid_lft forever preferred_lft forever
inet 192.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 forever
3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:23brd ff:ff:ff:ff:ff:ff
inet 192.168.43.85/24 brd 192.168.43.255scope global dynamic ens34
valid_lft 2810sec preferred_lft 2810sec
inet 192.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:00inet 127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever
2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:11brd ff:ff:ff:ff:ff:ff
inet 192.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 forever
3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff
inet 192.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:00inet 127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever
2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:19brd ff:ff:ff:ff:ff:ff
inet 192.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 forever
3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:91:dd:23brd ff:ff:ff:ff:ff:ff
inet 192.168.43.85/24 brd 192.168.43.255scope global dynamic ens34
valid_lft 3348sec preferred_lft 3348sec
inet 192.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:00inet 127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever
2: ens33: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:11brd ff:ff:ff:ff:ff:ff
inet 192.168.132.122/24 brd 192.168.132.255scope global ens33
valid_lft forever preferred_lft forever
inet 192.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 forever
3: ens34: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff
inet 192.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跳动影响性能
作者：梦中泪
关于作者：云计算，linux，虚拟化，存储
---------------------------------------------------------------------------
个性签名：我以为我很颓废,今天我才知道,原来我早报废了。
如果觉得本篇文章最您有帮助，欢迎转载，且在文章页面明显位置给出原文链接！记得在右下角点个“推荐”，博主在此感谢！
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
0
0- 0
已为社区贡献4条内容
所有评论(0)