首页 Linux正文

MySQL+keepalived双主热备高可用

王昊 Linux 2019-12-14 285 0

我们通常说的双主热备是指两台机器都在运行,但是两台机器不是同时在提供服务。当提供服务的一台出现故障的时候,另外一台会马上自动接管并且提供服务,而且切换的时间非常短。MySQL双主复制即互为Master-Slave,但是只有一个Master提供写操作,但是一个Master宕机后不能实现动态切换。使用Keepalived,可以通过虚拟IP,实现双主对外的统一接口以及自动检查、失败切换机制,从而实现MySQL数据库的高可用

keepalived工作原理

keepalived 是一个类似于 layer3, 4 & 7 交换机制的软件,也就是我们平时说的第 3 层、第 4 层和第 7 层交换

Keepalived 的作用是检测 web 服务器的状态,如果有一台 web 服务器、Mysql 服务器宕机,或工作出现故障,Keepalived 将检测到后,会将有故障的web 服务器或者 Mysql 服务器从系统中剔除,当服务器工作正常后 Keepalived自动将 web、Mysql 服务器加入到服务器群中,这些工作全部自动完成,不需要人工干涉,需要人工做的只是修复故障的 WEB 和 Mysql 服务器。

说到keepalived不得不说的一个协议,VRRP协议,可以说这个协议就是keepalived实现的基础。
1)Keepalived的工作原理是VRRP(Virtual Router Redundancy Protocol)虚拟路由冗余协议。在VRRP中有两组重要的概念:VRRP路由器和虚拟路由器,主控路由器和备份路由器。
2)VRRP路由器是指运行VRRP的路由器,是物理实体,虚拟路由器是指VRRP协议创建的,是逻辑概念。一组VRRP路由器协同工作,共同构成一台虚拟路由器。
3)Vrrp中存在着一种选举机制,用以选出提供服务的路由即主控路由,其他的则成了备份路由。当主控路由失效后,备份路由中会重新选举出一个主控路由,来继续工作,来保障不间断服务。

master1:192.168.17.195
master2:192.168.17.197
VIP:192.168.17.100

关闭防火墙与selinux

#设置或停止防火墙:
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# systemctl disable firewalld.service
#关闭selinux:
临时关闭:
[root@localhost ~]# setenforce 0
永久关闭(修改配置文件):
[root@localhost ~]# vim /etc/selinux/config
改成SELINUX=disabled

在master1和2中都安装MySQL

yum安装MySQL

修改master1的配置文件

cp /etc/my.cnf /etc/my.cnf.bak

vim /etc/my.cnfi

[mysqld]
datadir=/data/mysql                    # 数据存储文件指定
socket=/var/lib/mysql/mysql.sock       # sock文件路径

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log          #错误日志文件路径
pid-file=/var/run/mysqld/mysqld.pid    #进程文件路径

server-id=1                            # mysql服务id

log-bin=mysql-bin                      #二进制日志 存储数据发生改变的sql语句

auto-increment-offset=1                #增量值得起点
auto-increment-increment=2             #每次增量间隔

#binlog_do_db=<dbname>                 #设置需要同步的数据库名称

重启mysql

systemctl restart mysqld

数据同步授权

mysql> grant replication slave,replication client on . to root@’192.168.17.%’ identified by “Whaoub.com123”;

刷新权限

mysql> flush privileges;

最好将库锁住,仅仅允许读,以保证数据一致性;待主主同步环境部署后再解锁;
锁住后,就不能往表里写数据,但是重启mysql服务后就会自动解锁
注意该参数设置后,如果自己同步对方数据,同步前一定要记得先解锁

mysql> flush tables with read lock;

查看下log bin日志和pos值位置

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      630 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

修改master2的配置文件

vim /etc/my.cnf

[mysqld]
datadir=/data/mysql            
socket=/var/lib/mysql/mysql.sock    

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log        
pid-file=/var/run/mysqld/mysqld.pid    

server-id=2

log-bin=mysql-bin    

auto-increment-offset=2
auto-increment-increment=2    

#binlog_do_db=<dbname>

重启mysql

systemctl restart mysqld

数据同步授权

mysql> grant replication slave,replication client on . to root@’192.168.17.%’ identified by “Whaoub.com123”;

mysql> flush privileges;

mysql> flush tables with read lock;

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

master1

先解锁master1的表解锁,为保持数据的一致性

mysql> unlock tables;

先去stop slave 以下报错是slave未开启

mysql> slave stop;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave stop' at line 1

mysql> change master to master_host=’192.168.17.197’,master_user=’root’,master_password=’Wh13046423712..’,master_log_file=’mysql-bin.000001’,master_log_pos=154;

要注意修改master_host、master_log_file和master_log_pos的值,与 master2 要一致

mysql> start slave;

mysql> show slave status \G

找到如下两行

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两个状态为 YES,代表 已经启动两个线程,一个为 IO 线程,一个为 SQL 线程

master2做同步操作

mysql> unlock tables;

mysql> slave stop;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave stop' at line 1

mysql> change master to master_host=’192.168.17.195’,master_user=’root’,master_password=’Wh13046423712..’,master_log_file=’mysql-bin.000001’,master_log_pos=630;

要注意修改master_host、master_log_file和master_log_pos的值,与 master1 要一致

mysql> start slave;

mysql> show slave status \G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

master1和master2两台服务器都要授权允许root用户远程登录,用于在客户端登陆测试

mysql> grant all on . to root@’192.168.17.%’ identified by “Whaoub123.com”;

mysql> flush privileges;

Keepalived的安装配置

master1和master2都安装keepalived

cd /usr/local/

wget http://www.keepalived.org/software/keepalived-2.0.8.tar.gz

tar zxvf keepalived-2.0.8.tar.gz

cd keepalived-2.0.8/

yum -y install openssl-devel libnl-devel libnl3-devel libnfnetlink-devel

yum -y groupinstall “Development Tools”

./configure –prefix=/usr/local/keepalived

make && make install

cp /usr/local/keepalived/bin/* /bin/

cp /usr/local/keepalived/sbin/* /sbin/

cp -rf /usr/local/keepalived/etc/keepalived/ /etc/

cp /usr/local/keepalived/etc/sysconfig/* /etc/sysconfig/

修改keepalived的配置文件

master1上的keepalived配置

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived   
global_defs {   
   notification_email {                 #关于邮件的设置
     whaoub@163.com                     #email send to
   }   
   notification_email_from whaoub@163.com  
   smtp_server 127.0.0.1   
   smtp_connect_timeout 30   
   router_id LVS_DEVEL   
}   
# VIP1   
vrrp_instance VI_1 {   
    state MASTER                        #master1设置为MASTER
    interface eth0                      #指定虚拟ip的网卡接口
    lvs_sync_daemon_inteface eth0   
    virtual_router_id 151               #路由器标识,MASTER和BACKUP必须是一致的
    priority 100                        #定义优先级,数字越大,优先级越高,在同一个vrrp_instance下,MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后,就可以将VIP资源再次抢回来
    advert_int 5   
    nopreempt   
    authentication {                    #认证类型PASS|AH(IPSEC)
        auth_type PASS   
        auth_pass 2222   
    }   
    virtual_ipaddress {                 #虚拟IP的设置即vip
        192.168.17.100   
    }   
}   
virtual_server 192.168.17.100 3306 {   
    delay_loop 6      
    lb_algo wrr      
    lb_kind DR     
    persistence_timeout 60      
    protocol TCP           
    real_server 192.168.17.195 3306 {   
        weight 100          
        notify_down /data/sh/mysql.sh   
        TCP_CHECK {   
        connect_timeout 10   
        nb_get_retry 3   
        delay_before_retry 3   
        connect_port 3306   
        }   
    }   
} 

创建/data/sh/mysql.sh 脚本

mkdir -p /data/sh

vim /data/sh/mysql.sh

#!bin/bash
pkill  keepalived

chmod o+x /data/sh/mysql.sh

service keepalived restart

master2上的keepalived配置

只修改priority为90、nopreempt不设置、real_server设置本地IP

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived   
global_defs {   
   notification_email {
     whaoub@163.com 
   }   
   notification_email_from whaoub@163.com  
   smtp_server 127.0.0.1   
   smtp_connect_timeout 30   
   router_id LVS_DEVEL   
}   
# VIP1   
vrrp_instance VI_1 {   
    state BACKUP
    interface eth0
    lvs_sync_daemon_inteface eth0   
    virtual_router_id 151
    priority 90
    advert_int 5   
    nopreempt   
    authentication {
        auth_type PASS   
        auth_pass 2222   
    }   
    virtual_ipaddress {
        192.168.17.100   
    }   
}   
virtual_server 192.168.17.100 3306 {   
    delay_loop 6      
    lb_algo wrr      
    lb_kind DR     
    persistence_timeout 60      
    protocol TCP           
    real_server 192.168.17.197 3306 {   
        weight 100          
        notify_down /data/sh/mysql.sh   
        TCP_CHECK {   
        connect_timeout 10   
        nb_get_retry 3   
        delay_before_retry 3   
        connect_port 3306   
        }   
    }   
} 

创建/data/sh/mysql.sh 脚本

mkdir -p /data/sh

vim /data/sh/mysql.sh

#!bin/bash
pkill  keepalived

chmod 755 /data/sh/mysql.sh

service keepalived restart

master1和master2两台服务器都要授权允许root用户远程登录,用于在客户端登陆测试

mysql > grant all on . to root@’%’ identified by “Whaoub123.com”;

mysql> flush privileges;

有需求的可以设置iptables防火墙规则

vim /etc/sysconfig/iptables

-A INPUT -s 182.148.15.0/24 -d 224.0.0.18 -j ACCEPT       #允许组播地址通信
-A INPUT -s 182.148.15.0/24 -p vrrp -j ACCEPT             #允许VRRP(虚拟路由器冗余协)通信
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT    #开放mysql的3306端口

/etc/init.d/iptables restart

Mysql+keepalived故障转移的高可用测试

通过Mysql客户端通过VIP连接,看是否连接成功,若连接不上可检查防火墙相关问题

mysql -h192.168.17.100 -uroot -p

默认情况下,vip是在master1上的。

使用”ip add”命令查看vip切换情况

ip add

1: lo: <LOOPBACK,UP,LOWER_UP> 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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:fb:11:04 brd ff:ff:ff:ff:ff:ff
    inet 192.168.17.195/24 brd 192.168.17.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.17.100/32 scope global eth0        #这个32位子网掩码的vip地址表示该资源目前还在master1机器上
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fefb:1104/64 scope link 
       valid_lft forever preferred_lft forever

停止master1机器上的mysql服务,根据配置文件内容,mysql的3306端口停了,就会执行脚本内容,将keepalived停掉,从而vip资源将会切换到master2机器上。(mysql服务没有起来的时候,keepalived服务也无法顺利启动)

最后测试停止 master1 Mysql服务,是否会自动切换到 master2 上。

systemctl stop mysqld.service

ps -ef|grep mysql

root     103020   2739  0 01:35 pts/0    00:00:00 grep --color=auto mysql

ps -ef |grep keepalived

root     112903 112610  0 05:22 pts/1    00:00:00 grep --color=auto keepalived

mysql与keepalived都已经停掉

查看日志,分别查看master1与master2的主备转换

tail -fn 100 /var/log/messages

版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。

评论