MySQL+Keepalived

Posted on by liujie

Keepalived+mysql 自动切换

项目环境：
VIP 10.36.192.99
mysql1 10.36.192.215 keepalived-master
mysql2 10.36.192.228 keepalived-salve

一、mysql 主主同步 （不使用共享存储，数据保存本地存储）
二、安装keepalived
三、keepalived 主备配置文件
四、mysql状态检测脚本/root/bin/keepalived_check_mysql.sh
五、测试及诊断

两台服务器都安装mysql数据库并启动

登入主服务器MySQL数据库

mysql> grant all on *.* to ‘root’@’10.36.192.99’ identified by ‘Qa@123456!’;

mysql> flush privileges;

登入备用服务器MySQL数据库

mysql> grant all on *.* to ‘root’@’10.36.192.99’ identified by ‘Qa@123456!’;

mysql> create database qq default charset ‘utf8’;

mysql> flush privileges;

主服务器

[root@keepalived-master ~]# yum -y install keepalived

[root@keepalived-master ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@keepalived-master ~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id master
}
vrrp_script check_run {
   script "/etc/keepalived/keepalived_check_mysql.sh"
   interval 5
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 60
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.36.192.99/24
    }
    track_script {
        check_run
    }
}

备用服务器

[root@keepalived-slave ~]# yum -y install keepalived

[root@keepalived-slave ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

[root@keepalived-slave ~]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id backup
}
vrrp_script check_run {
   script "/etc/keepalived/keepalived_check_mysql.sh"
   interval 5
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface ens33
    virtual_router_id 60
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.36.192.99/24
    }
    track_script {
        check_run
    }
}

mysql状态检测脚本/etc/keepalived/keepalived_check_mysql.sh（两台MySQL同样的脚本）

[root@keepalived-master ~]# vim /etc/keepalived/keepalived_check_mysql.sh

#!/bin/bash
/usr/bin/mysql -uroot -p'Qa@123456!' -e "show status" &>/dev/null 
if [ $? -ne 0 ] ;then 

	systemctl stop keepalived
fi

[root@keepalived-master ~]# chmod +x /etc/keepalived/keepalived_check_mysql.sh

两边均启动keepalived
方式一:
[root@keepalived-master ~]# systemctl start keepalived
[root@keepalived-master ~]# systemctl enable keepalived

如图主服务器正常时vip在主库当主服务器坏掉后自动切换到备库

在主服务器正常时使用其他服务器登录查看mysql数据库

[root@nginx1 ~]# mysql -uroot -p’Qa@123456!’ -h 10.36.192.99 -P 3306 -e ‘show databases’
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+

在主服务器数据库挂掉时使用其他服务器登录查看mysql数据库

[root@keepalived-master ~]# mysql -p’Qa@123456!’ -e ‘show databases’
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| qq |
| sys |
+——————–+

Related Posts