mysql+keepalived实现高可用

一、所做准备

centos7 搭建虚拟两台和vip：

keepalived01：(192.168.48.134)

keepalived02：(192.168.48.135)

vip：(192.168.48.100)

 二、安装数据库(两台主机都要执行)：

(1)新搭建的虚拟机如果出现未找到命令，则需要下载安装对应的插件

yum install wget

(2)下载完成后再次执行mysql安装命令：

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

(3)下载完成查看下载目录：

(4)安装mysql包

yum -y install mysql57-community-release-el7-10.noarch.rpm

(5)安装mysql

yum -y install mysql-community-server

(6)如果出现下面的失败提示

解决方法

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

(7)如果执行命令过程中提示：Unable to find a match: mysql-community-server

则可以通过命令解决

yum module disable mysql

(8)启动 Mysql 服务

systemctl start mysqld.service

出现以下图示表示启动成功

若没有报错，进入下一步操作，若执行报错，多半是没有权限，执行下面语句赋予权限，然后重试

chown mysql:mysql -R /var/lib/mysql

(9)查看mysql状态

 (10)查看初始密码（红色部分为初始密码)

grep 'password' /var/log/mysqld.log

(11)如果登录密码不正确忘记了可以看我的另一篇关于修改mysql忘记密码的博客[写文章-CSDN创作中心]

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 

解决方法：

ALTER USER USER IDENTIFIED BY '2658994493';

如果还报：You must reset your password using ALTER USER statement before executing this statement.

解决办法：

set global validate_password_policy=LOW;
set global validate_password_length=9;

再次执行ALTER USER USER IDENTIFIED BY '2658994493';

最后执行刷新MySQL的系统权限命令：FLUSH PRIVILEGES; 

上述问题可以查看次篇解决：

MySQL 报错：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before-CSDN博客

到此mysql安装成功！

三、配置主从134(主)、135(主)：进行主备的高可用

(1)数据同步授权

master01

mysql> grant replication slave,replication client on *.* to root@'192.168.48.%' identified by "2658994493";
mysql> flush privileges;
mysql> flush tables with read lock;
mysql> show master status;

 master02

mysql> grant replication slave,replication client on *.* to root@'192.168.48.%' identified by "2658994493";
mysql> flush privileges;
mysql> flush tables with read lock;
mysql>  show master status;

 (2)master01做同步操作

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.48.135',master_user='root',master_password='2658994493',master_log_file='mysql-bin.000001',master_log_pos=620;
mysql> start slave;
mysql> show slave status \G;

master02做同步操作

mysql> unlock tables;
mysql> slave stop;
mysql> change master to master_host='192.168.48.134',master_user='root',master_password='2658994493',master_log_file='mysql-bin.000001',master_log_pos=1092;
mysql> start slave;
mysql> show slave status \G;

 

(3)master01和master02两台服务器都要授权允许root用户远程登录，用于在客户端登陆测试！

(4)主主验证

master01

master02 中查询验证staudent表存不存在

四、Keepalived的安装配置

(1) 两台主机都安装Keepalived服务，此处使用光盘自带yum源安装

[root@master01 home]# yum install -y keepalived
[root@master02 home]# yum install -y keepalived

(2)修改master01上的keepalived配置

! Configuration File for keepalived

global_defs {
   notification_email {
     root@localhost
   }
   notification_email_from root@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id master01
   vrrp_skip_check_adv_addr   vrrp_iptables
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}
#定义检查脚本
vrrp_script check_mysql_status {
   script "/etc/keepalived/chk_mysql.sh"
   interval 2
   weight -50
   fall 3
   rise 5
   timeout 3
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.48.100
    }
    track_script {
        check_mysql_status
    }
}

(3)修改master02上的keepalived配置

! Configuration File for keepalived

global_defs {
   notification_email {
     kmt@wangwei.cn
  }
   notification_email_from root@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id master02
   vrrp_skip_check_adv_addr   vrrp_iptables
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script check_mysql_status {
   script "/etc/keepalived/chk_mysql.sh"
   interval 2
   weight -50
   fall 3
   rise 5
   timeout 3
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 80
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.48.100
    }
    track_script {
        check_mysql_status
    }
}

 (4)编写检查脚本，两台主机都一样

[root@mysql-master01 ~]# vim /etc/keepalived/chk_mysql.sh

#!/bin/bash

mysqlcmd="/usr/local/mysql/bin/mysql"
user="root"
password="2658994493"

$mysqlcmd -u$user -p$password -e "show status;" &> /dev/null
if [ $? -eq 0 ];then
    echo "mysql_status=1"
    exit 0
else
    /usr/bin/systemctl stop keepalived
fi

[root@master01 ~]# chmod +x /etc/keepalived/chk_mysql.sh
[root@master01 ~]# scp /etc/keepalived/chk_mysql.sh 192.168.48.135:/etc/keepalived/

(5)启动两台主机的keepalived服务，并设为开机启动

[root@master01 ~]# systemctl start keepalived
[root@master01 ~]# systemctl enable keepalived

[root@master02 ~]# systemctl start keepalived
[root@master02 ~]# systemctl enable keepalived

(6)测试：

刚开始vip在master01上，但我的master02启动的早，所以vip在master02上

ip a|grep 192.168.48.100

master01是不存在的

 现在我们停止master02看看vip会不会到master01

systemctl stop keepalived

查看master01有没有vip,从下面的结果可以看出vip已经漂移到master01上

现在我们将master02的keepalived启动，我这里是非抢占模式，故vip还会在master01上，不会回到master02上，下面我们看结果是否一致：

systemctl start keepalived

 正如预期，master02上没有vip,vip依旧在master01上，测试成功

接下来测试关闭mysql服务看vip的变动

首先我们关闭master01上的mysql（因为此时vip正在mater01上），制造mysql故障

在master01上关闭mysql，预期结果是vip会飘到master02上，下面我们来验证：

关闭master01上的mysql        

systemctl stop mysqld

发现vip跑到了master02上

master01上vip飘到了master02上