mysql+keepalived 双主搭建

简单了解

mysql双主搭建主要是保证在其中一台数据库宕机了，由备用库顶上，减少损失，保证数据库的高可用。与n多台的集群主从环境不同，简单实用。

实现方式：以mysql和keepalived为主实现，由keepalived来提供虚拟IP连接两台互为主从的mysql主机。

Ps：Keepalived 是一个基于VRRP协议来实现的LVS服务高可用方案，可以利用其来避免单点故障。一个LVS服务会有2台服务器运行Keepalived，一台为主服务器（MASTER），一台为备份服务器（BACKUP），但是对外表现为一个虚拟IP，主服务器会发送特定的消息给备份服务器，当备份服务器收不到这个消息的时候，即主服务器宕机的时候， 备份服务器就会接管虚拟IP，继续提供服务，从而保证了高可用性

部署

准备两台服务器：192.168.1.1和192.168.1.2

mysql下载

MySQL-client-5.7.27-1.el7.x86_64.rpm
MySQL-server-5.7.27-1.el7.x86_64.rpm

上传文件到/opt/mysql/文件夹

卸载MariaDB（CentOS7 默认自带）

rpm -qa | grep mariadb
yum -y remove mariadb-libs

安装

rpm -ivh MySQL-client-5.7.27-1.el7.x86_64.rpm
rpm -ivh MySQL-server-5.7.27-1.el7.x86_64.rpm

查看初始密码并启动

cat /root/.mysql_secret
service mysql start

修改配置文件

vi /etc/my.cnf
#在192.168.1.1的mysqld节点下面增加
port=3306
server-id=1
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1
binlog-ignore=mysql
binlog-ignore=information_schema
binlog-ignore=performance_schema
character_set_server=utf8
log-bin-trust-function-creators=1
#在192.168.1.2的mysqld节点下面增加
port=3306
server-id=2
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1
binlog-ignore=mysql
binlog-ignore=information_schema
binlog-ignore=performance_schema
character_set_server=utf8
log-bin-trust-function-creators=1
#重新启动登录
service mysqld restart
mysql -uroot -p

相互授权

#192.168.1.1数据库：
grant replication slave, replication client on *.* to 'root'@'192.168.1.2' identified by '123456'
flush privileges; //刷新MySQL的系统权限相关表
#192.168.1.2数据库：
grant replication slave, replication client on *.* to 'root'@'192.168.1.1' identified by '123456';
flush privileges; //刷新MySQL的系统权限相关表

日志配置

show master status
#192.168.1.1
mysql> change master to 
    ->master_host='192.168.1.2', 
    ->master_port=3306, 
    ->master_user='root',
    ->master_password='123456',
    ->master_log_file='mysql-bin.000002',
    ->master_log_pos=120;
#192.168.1.2
mysql> change master to 
    ->master_host='192.168.1.1', 
    ->master_port=3306, 
    ->master_user='root',
    ->master_password='123456',
    ->master_log_file='mysql-bin.000001',
    ->master_log_pos=120;
#启动
start slave;

Keepalived下载

keepalived-2.0.18.tar.gz

上传文件到/opt/keepalived/文件夹

安装

#解压编译
tar -zxvf keepalived-2.0.18.tar.gz
cd keepalived-2.0.18
./configure
make && make install
#拷贝执行文件到/usr/sbin/下
cp /usr/local/sbin/keepalived /usr/sbin/
#将init.d文件拷贝到etx下加入网卡配置
cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/ 
#创建keepalived文件夹
mkdir -p /etc/keepalived
#将keepalived配置文件拷贝到etc下
cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
#将keepalived日志输出到local0
vi /etc/sysconfig/keepalived
KEEPALIVED_OPTIONS="-D -d -S 0"
#在/etc/rsyslog.conf里添加
vi /etc/rsyslog.conf
local0.*  /var/log/keepalived.log
#启动keepalived和rsyslog服务
service rsyslog stop
service keepalived stop
service rsyslog start
service keepalived start

配置keepalived监控mysql

创建监控脚本

vi /etc/keepalived/check_mysql.sh
#!/bin/bash
#如果进程中没有mysql则将keepalived进程kill掉
A=`netstat -lnutp|grep mysql|wc -l`      ## 查看是否有 mysql进程 把值赋给变量A 
if [ $A -eq 0 ];then                    ## 如果没有进程值得为 零
       service keepalived stop          ## 则结束 keepalived 进程
fi

#设置check_mysql.sh脚本可执行权限：
chmod +x /etc/keepalived/check_mysql.sh

在192.168.1.1中配置

vi  /etc/keepalived/keepalived.conf
! Configuration File for keepalived
vrrp_script check_mysql { 
    script "/etc/keepalived/check_mysql.sh"         ##监控脚本
    interval 2                                      ##时间间隔，2秒
    weight 2                                        ##权重
}
#全局配置
global_defs {
   notification_email {  #指定keepalived在发生切换时需要发送email到的对象，一行一个
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc  #指定发件人
   #smtp_server 192.168.200.1                             #指定smtp服务器地址
   #smtp_connect_timeout 30                               #指定smtp连接超时时间
   router_id LVS_DEVEL                                  #运行keepalived机器的一个标识
}

vrrp_instance VI_1 { 
    state MASTER           #标示状态为MASTER 备份机为BACKUP
    interface eth0         #设置实例绑定的网卡
    virtual_router_id 51   #同一实例下virtual_router_id必须相同
    priority 100           #MASTER权重要高于BACKUP 比如BACKUP为99  
    advert_int 1           #MASTER与BACKUP负载均衡器之间同步检查的时间间隔单位是秒
    authentication {       #设置认证
        auth_type PASS     #主从服务器验证方式
        auth_pass 8888
    }
    virtual_ipaddress {    #设置vip
        192.168.1.3     #可以多个虚拟IP，换行即可
    }
      track_script {
        check_mysql        #监控脚本
    }
}

在192.168.1.2中配置

vi  /etc/keepalived/keepalived.conf
! Configuration File for keepalived
vrrp_script check_mysql { 
    script "/etc/keepalived/check_mysql.sh"         ##监控脚本
    interval 2                                      ##时间间隔，2秒
    weight 2                                        ##权重
}
#全局配置
global_defs {
   notification_email {  #指定keepalived在发生切换时需要发送email到的对象，一行一个
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc  #指定发件人
   #smtp_server 192.168.200.1                             #指定smtp服务器地址
   #smtp_connect_timeout 30                               #指定smtp连接超时时间
   router_id LVS_DEVEL                                  #运行keepalived机器的一个标识
}

vrrp_instance VI_1 { 
    state BACKUP           #标示状态为MASTER 备份机为BACKUP
    interface eth0         #设置实例绑定的网卡
    virtual_router_id 51   #同一实例下virtual_router_id必须相同
    priority 99            #MASTER权重要高于BACKUP 比如BACKUP为99  
    advert_int 1           #MASTER与BACKUP负载均衡器之间同步检查的时间间隔，单位是秒
    authentication {       #设置认证
        auth_type PASS     #主从服务器验证方式
        auth_pass 8888
    }
    virtual_ipaddress {    #设置vip
        192.168.1.3       #可以多个虚拟IP，换行即可
    }
      track_script {
        check_mysql        #监控脚本
    }
}

分别重新启动 keepalived

service keepalived stop
service keepalived start

测试

访问192.168.1.3 将访问到192.168.1.1的mysql
停掉192.168.1.1的mysql
访问192.168.1.3 将访问到192.168.1.2的mysql