Mysql+Keepalived实现双主互备高可用详细配置-创新互联-成都快上网建站

Mysql+Keepalived实现双主互备高可用详细配置-创新互联

Mysql+Keepalived双主互备架构搭架
Mysql + Keepalived 实现双主互备高可用详细配置
配置前进行校时操作

十余年的台安网站建设经验,针对设计、前端、开发、售后、文案、推广等六对一服务,响应快,48小时及时工作处理。全网整合营销推广的优势是能够根据用户设备显示端的尺寸不同,自动调整台安建站的显示方式,使网站能够适用不同显示终端,在浏览器中调整网站的宽度,无论在任何一种浏览器上浏览网站,都能展现优雅布局与设计,从而大程度地提升浏览体验。成都创新互联公司从事“台安网站设计”,“台安网站推广”以来,每个客户项目都认真落实执行。
#安装ntpdate工具
yum install ntpdate -y
#使用ntpdate校时(后面的是ntp服务器)
ntpdate pool.ntp.org

一、配置mysql双主备

0.测试mysql安装

yum install mariadb mariadb-server -y
systemctl restart mariadb
systemctl enable mariadb
mysql_secure_installation

1.DB1修改配置文件(需重启)

vim /etc/my.cnf
#在[mysqld]添加
server-id=166
#开启mysql日志功能
log-bin=mysql-bin
#定义日志命名格式 
relay-log=mysql-relay-bin
#以下table复制过滤
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=performance_schema.%

2.DB2修改配置文件(需重启)

vim /etc/my.cnf
#在[mysqld]添加
server-id=168
#开启mysql日志功能
log-bin=mysql-bin
#定义日志命名格式 
relay-log=mysql-relay-bin

3.DB1,DB2分别创建复制帐号

mysql -u root -p
#创建用户slave_up允许从192.168.1网段登录
create user 'slave_cp'@'192.168.1.%' identified by 'pass';
grant replication slave on *.* to 'slave_cp'@'192.168.1.%';
exit

4.DB1获取二进制日志信息

mysql -u root -p
#对数据库进行只读锁定(防止查看二进制日志同时有人对数据库修改操作)
flush tables with read lock;
#查询主机二进制文件信息
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 494 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#解除只读锁定
unlock tables;
exit

5.同步数据库

#DB1
#备份主数据库,并上传给从数据库
mysqldump -uroot -p123456 --all-databases --lock-all-tables >/tmp/dbdump.sql
scp /tmp/dbdump.sql 192.168.1.168:/tmp/
#DB2
#从服务器导入主服务器数据库
mysql -uroot -p 

6.在DB2上将DB1设置为主服务器

#开启mysql防火墙端口
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload
 change master to
master_host='192.168.1.168' ,
master_user='slave_cp', 
master_password='pass',
master_log_file='mysql-bin.000001', 
master_log_pos=494;
#启动slave
start slave;
#查看是否正常工作
show slave status\G
exit

7.在DB2获取二进制日志信息

mysql -u root -p
#对数据库进行只读锁定(防止查看二进制日志同时有人对数据库修改操作)
flush tables with read lock;
#查询主机二进制文件信息
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 513721| | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#解除只读锁定
unlock tables;
exit

8.在DB1将DB2设置为主服务器

change master to
master_host='192.168.1.168' ,
master_user='slave_cp', 
master_password='pass',
master_log_file='mysql-bin.000001', 
master_log_pos=513721;
#启动slave
start slave;
#查看是否正常工作
show slave status\G
exit

二、Keepalived配置(/etc/keepalived/keepalived.conf)(yum install keepalived -y)(注意:关闭selinux策略 setenforce 0)

1.keepalived配置文件配置

! Configuration File for keepalived
global_defs {
 #设置报警通知邮件地址,可以设置多个
 notification_email { 
 msun1996@163.com
 } 
 #设置邮件的发送地址 
 notification_email_from keepalived@msun.com 
 #设置smtp server的地址,该地址必须是存在的
 smtp_server 127.0.0.1 
 #设置连接smtp server的超时时间 
 smtp_connect_timeout 30 
 #运行Keepalived服务器的标识,发邮件时显示在邮件标题中的信息 
 router_id mysql_msun 
}
#检测haproxy脚本
vrrp_script chk_mysql {
 script "/etc/keepalived/mysqlcheck/check_slave.sh"
 interval 2
 weight 2
}
#定义VRRP实例,实例名自定义
vrrp_instance mysql_msun {
 #指定Keepalived的角色,MASTER主机 BACKUP备份
 state BACKUP #此处两个都设置为BACKUP
 #指定HA监测的接口 
 interface eno16777736 
 #虚拟路由标识,这个标识是一个数字(1-255),在一个VRRP实例中主备服务器ID必须一样 
 virtual_router_id 68 
 #优先级,数字越大优先级越高,在一个实例中主服务器优先级要高于备服务器 
 priority 100 #从服务器99
 #设置主备之间同步检查的时间间隔单位秒 
 advert_int 1 
 #设置不抢占模式(DB1设置即可)
 nopreempt 
 #设置验证类型和密码 
 authentication { 
 #验证类型有两种{PASS|HA} 
 auth_type PASS 
 #设置验证密码,在一个实例中主备密码保持一样 
 auth_pass 1689 
 }
 track_script {
 chk_mysql # 执行监控的服务
 } 
 #定义虚拟IP地址,可以有多个,每行一个
 virtual_ipaddress { 
 192.168.1.160
 } 
}

2.创建监控脚本(/etc/keepalived/mysqlcheck/check_slave.sh)

#!/bin/bash
#This scripts is check for Mysql Slave status

Mysqlbin=/usr/bin/mysql
user=root
pw='123456'
port=3306
host=127.0.0.1
#大延时
sbm=120
#Check for $Mysqlbin
if [ ! -f $Mysqlbin ];then
 echo 'Mysqlbin not found,check the variable Mysqlbin'
 exit 99
fi
#Get Mysql Slave Status
IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
#Check if the mysql run
if [[ -z "$IOThread" ]];then
 exit 1
fi
#Check if the thread run 
if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
 exit 1
 elif [[ $SBM -ge $sbm ]];then
 exit 1
 else
 exit 0
fi

3.防火墙策略

#运行VPPR组播(keepalived)
firewall-cmd --permanent --add-rich='rule family="ipv4" destination address="224.0.0.18" protocol value="vrrp" accept'
firewall-cmd --reload

4.测试

1.正常启动DB1、DB2信息

[root@DB1 ~]# ip a
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever
[root@DB2 ~]# ip a
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever

2.关闭DB1数据库,观察IP

[root@DB1 ~]# systemctl stop mariadb
[root@DB1 ~]# ip a
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic 
 valid_lft 2591817sec preferred_lft 604617sec
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever
[root@DB2 ~]# ip a
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic 
 valid_lft 2591669sec preferred_lft 604469sec
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever

3.重启主机mariadb,观察IP

[root@DB1 ~]# ip a #DB1不切换成主机(因为设置为不抢占模式)
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic 
 valid_lft 2591605sec preferred_lft 604405sec
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever
[root@DB2 ~]# ip a
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic 
 valid_lft 2591533sec preferred_lft 604333sec
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever

4.再次关闭DB2的maraiadb,问题出现了,预想当DB2宕机,IP未能正常漂移!!!

[root@DB2 ~]# systemctl stop mariadb 
[root@DB2 ~]# ip a 
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet 192.168.1.160/32 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic 
 valid_lft 2591326sec preferred_lft 604126sec
 inet6 fe80::20c:29ff:feda:7d9d/64 scope link 
 valid_lft forever preferred_lft forever
[root@DB1 ~]# ip a
1: lo:  mtu 65536 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
 valid_lft forever preferred_lft forever
 inet6 ::1/128 scope host 
 valid_lft forever preferred_lft forever
2: eno16777736:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
 link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
 inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
 valid_lft forever preferred_lft forever
 inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic 
 valid_lft 2591241sec preferred_lft 604041sec
 inet6 fe80::20c:29ff:fe43:3013/64 scope link 
 valid_lft forever preferred_lft forever

关于这个问题的思考:
先说说本案例中采用的是Keepalived+mysql的组合,Keepalived用了双备模式,采用脚本检测应用是否正常来决定keepalived的权重,从而让正常的数据库能得到VIP而正常工作,考虑到了如果DB1宕掉(仅mysql服务)(DB1的权重会低于DB2),DB2接管服务,而DB1再次正常时(权重又会高于DB2),为了避免数据库来回切换,而在DB1的keepalived配置中设置了不抢占模式来避免此问题。但是,导致了一个新的问题,就算是DB2的数据库不正常工作,(权重更低于DB2),但因为DB1不抢占模式,DB2仍然会占有VIP,(除非是Keepalived服务宕掉)。

关于这个问题的思考解决方案

1.keepalived取消使用不抢占模式,从而使主机切换正常,但这样也就使DB1的数据库服务宕掉再正常启动时,会抢占DB2的VIP,使数据库进行不必要的切换。

2.修改检测脚本,keepalived使用检测脚本正常时返回0,不正常时,直接让keepalived服务本身宕掉。这样情况下,DB1服务mysql服务再次正常(并启动keepalived),不会抢占DB2的VIP;当DB2mysql服务不正常工作时,(会自动停止keepalived),从而使DB1正常得到VIP。

#!/bin/bash
#This scripts is check for Mysql Slave status

Mysqlbin=/usr/bin/mysql
user=root
pw='123456'
port=3306
host=127.0.0.1
#大延时
sbm=120
#Check for $Mysqlbin
if [ ! -f $Mysqlbin ];then
 echo 'Mysqlbin not found,check the variable Mysqlbin'
 pkill keepalived
fi
#Get Mysql Slave Status
IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
#Check if the mysql run
if [[ -z "$IOThread" ]];then
 pkill keepalived
fi
#Check if the thread run 
if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
 pkill keepalived
 elif [[ $SBM -ge $sbm ]];then
 pkill keepalived
 else
 exit 0
fi

创新互联www.cdcxhl.cn,专业提供香港、美国云服务器,动态BGP最优骨干路由自动选择,持续稳定高效的网络助力业务部署。公司持有工信部办法的idc、isp许可证, 机房独有T级流量清洗系统配攻击溯源,准确进行流量调度,确保服务器高可用性。佳节活动现已开启,新人活动云服务器买多久送多久。


名称栏目:Mysql+Keepalived实现双主互备高可用详细配置-创新互联
URL标题:http://kswjz.com/article/dsjpch.html
扫二维码与项目经理沟通

我们在微信上24小时期待你的声音

解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流