keepalived+mysql实现数据库集群高可用

keepalived+mysql实现数据库集群高可用

一、mysql双主双从部署

mysql安装部署（5.7）

1.首先下载mysql的yum源配置

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

2.安装mysql的yum源

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

3.yum方式安装mysql

yum -y install mysql-server  --nogpgcheck
--nogpgcheck  (不校验数字签名)

4.启动mysql并获取初始密码，修改密码

systemctl start mysqld  #启动
grep "password" /var/log/mysql/mysql.log  #获取初始密码
mysql -uroot -p'初始密码' password '新密码'   #修改初始密码

mysql双主配置（互为主从）
​1.修改master和slave的配置文件

#主库配置
vim /etc/my.cnf
+  server-id=1
+  log-bin=/var/log/mysql/mysql-bin

 #从库配置
vim /etc/my.cnf
+  server-id=2
+  log-bin=/var/log/mysql/mysql-bin

2.创建日志目录并授权（两台服务器都做）

mkdir /var/log/mysql
chown -R mysql.mysql /var/log/mysql

3.创建一个专门用于复制数据的用户

systemctl restart mysqld #因为改了配置文件，要先重启下服务，牢记
mysql -uroot -p''
+ grant replication slave on *.* to 'repl'@'%' identified by 'Abc123';
+ flush privileges;

4.在从服务器上登录测试一下新建的用户是否可用

mysql -urepl -p'Abc123' -h192.168.48.136

5.查看主服务器的binlog日志的名称和位置

mysql -uroot -p''
+ show master status\G
+记下file和position的信息，后面要用。

6.在从服务器配置连接到主服务器的相关信息

mysql -uroot -p''
+
mysql> CHANGE MASTER TO
MASTER_HOST='mysql-master',  -- 主服务器的主机名(也可以是 IP) 
MASTER_USER='repl',           -- 连接到主服务器的用户
MASTER_PASSWORD='123456',       -- 到主服务器的密码
MASTER_LOG_FILE='mysql-bin.000001',	-- 从主服务器的哪个binlog日志读取
MASTER_LOG_POS=589;					-- 从主服务器的binlog日志的哪个位置开始同步

7.启动从库复制线程并查看主从是否成功

start slave;
show slave status;

二、安装部署并配置keepalived

1.安装keepalived(两台服务器都安装）

yum -y install keepalived

2.master配置keepalived

[root@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 66
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.36.192.6/24
    }
    track_script {
        check_run
    }
}

3.slave配置keepalived

[root@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 66
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.36.192.6/24
    }
    track_script {
        check_run
    }
}

4.两台机子配置数据库检查脚本

[root@master ~]# vim /etc/keepalived/keepalived_check_mysql.sh
#!/bin/bash
/usr/bin/mysql -uroot -p'QianFeng@123!' -e "show status" &>/dev/null
if [ $? -ne 0 ] ;then
#	service keepalived stop
	systemctl stop keepalived
fi

[root@slave ~]#
vim /etc/keepalived/keepalived_check_mysql.sh
#!/bin/bash
/usr/bin/mysql -uroot -p'QianFeng@123!' -e "show status" &>/dev/null
if [ $? -ne 0 ] ;then
#	service keepalived stop
	systemctl stop keepalived
fi
[root@master ~]# chmod +x /etc/keepalived/keepalived_check_mysql.sh #添加可执行权限很重要
[root@slave ~]# chmod +x /etc/keepalived/keepalived_check_mysql.sh #添加可执行权限很重要

5.启动keepalived

[root@master ~]# systemctl restart keepalived 
[root@slave ~]# systemctl restart keepalived