Keepalived + Mysql双主模式 搭建高可用集群

一、mysql安装

       https://blog.csdn.net/qq_23160237/article/details/102467487

 

二、mysql配置双主模式

1）修改/etc/my.cnf

# 增加以下参数
# server-id 主备机子不能相同
# auto-increment-offset 自增长主键起始点不能相同，防止主键冲突

# 主server1
server-id=1
log-bin=mysql-bin1
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2     
auto-increment-offset = 1    
slave-skip-errors = all

# 备server2
server-id=2
log-bin=mysql-bin2
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2        
auto-increment-offset = 2 
slave-skip-errors = all

# mysql重启
systemctl restart mysqld

 

2）相互授权访问

# 登录 mysql -uroot -p
use mysql;
select  User,authentication_string,Host from user;
update user set Host='%' where User='root';
flush privileges;

 

3）配置主从复制

• show master status;

• change master to master_host='192.168.2.103', master_user='root', master_password='root',master_log_file='mysql-bin.000002',master_log_pos=154;
• 注意103是master主机ip，00002是103的bin-log文件，master_log_pos也是103的位置

• start slave;

• show slave status\G;

 

备注：103另外一台服务器也是相同配置

 

3）测试

 

三、keepalived搭建高可用

1）安装

 yum  -y install keepalived

2）配置

vim /etc/keepalived/keepalived.conf

# 102服务器
global_defs {
   router_id Node102
}

vrrp_script check_server {
   script "/opt/bin/mysql-check.sh"
   interval 1
   weight -10
   fall 2
   rise 1
}

vrrp_instance VI_1 {
    state BACKUP
    interface bond0
    virtual_router_id 88
    priority 95
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.202
    }
    track_script {
        check_server
    }
}


# 103服务器
global_defs {
   router_id Node103
}

vrrp_script check_server {
   script "/opt/bin/mysql-check.sh"
   interval 1
   weight -10
   fall 2
   rise 1
}

vrrp_instance VI_1 {
    state BACKUP
    interface bond0
    virtual_router_id 88
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.202
    }
    track_script {
        check_server
    }
}

备注：同一个局域网里面非同一个keepalived集群 virtual_router_id  不能相同，否则无法启动

 

3）编写检测脚本

#!/bin/bash

mysqladmin -u root -proot ping &>/dev/null
if [ $? -eq 0 ];then
   echo "mysql is up"
   exit 0
else
   echo "mysql is down"
   exit 1
fi

 

4）启动

systemctl start keepalived
systemctl enable keepalived