环境

硬件

配置	测试配置
CPU	1.8GHz
内存	4GB
核心	4核
带宽	1000Mb

软件

VMware® Workstation 16 Pro 16.1.1 build-17801498
CentOS Linux release 7.6.1810 (Core)
Mysql 5.7
keepalived-1.3.5-19.el7.x86_64

规划

HOST	IP	说明
node3	192.168.88.88	主节点
node4	192.168.88.94	备份节点
kp虚拟路由	192.168.88.101	vip

双主环境搭建

之前搭建了一套mysql一主一备的环境，过程参考文章《CentOS 7 + MySQL 5.7 搭建一主一备》，备份节点可以作为主节点的数据备份，也可以作为读写分离支持来降低主节点的性能压力，但是，如果主节点发生故障，备份节点无法做到自动接管主节点的业务（需要人工或者其他工具辅助切换）。

本次在一主一备的基础上，改造一套双主运行的环境。

node4

创建slave用户

mysql -p

# 只读赋权
GRANT SELECT ON *.* TO 'slave'@'192.168.88.88' IDENTIFIED BY 'Slave!23';
# 从权限 赋权
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.88.88' IDENTIFIED BY 'Slave!23';
# 刷新权限
FLUSH PRIVILEGES;

配置文件添加如下配置

# 自增id起始值
auto_increment_offset=2
# 每次自增数字
auto_increment_increment=8

node3

配置node3（slave）指向node4（master）

mysql -p

stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.88.94',MASTER_USER='slave', MASTER_PASSWORD='Slave!23',MASTER_AUTO_POSITION=1;
start slave;
show slave status\G;

配置文件添加如下配置

# 自增id起始值
auto_increment_offset=1
# 每次自增数字
auto_increment_increment=8

验证

node3上创建表t_test3

use db_test;

create table t_test3(
    i_id int auto_increment not null ,
    c_name varchar(8),
    constraint t_test3_pk primary key (i_id)
);

show tables;

node4上看见t_test3

use db_test;
show tables;

node4上的t_test3

node4上创建表t_test2

create table t_test2(
    i_id int auto_increment not null ,
    c_name varchar(8),
    constraint t_test2_pk primary key (i_id)
);

show tables;

node3上看见t_test2

use db_test;
show tables;

node3上的t_test2

node3上插入t_test2表一条数据

insert into t_test2 (c_name)  value ('aaa');
select i_id,c_name from t_test2;

node4中可见

node4中的数据

node4上插入t_test2表一条数据

insert into t_test2 (c_name)  value ('bbb');
select i_id,c_name from t_test2;

node3中可见

node3中的数据

至此，双主节点的mysql环境已经全部完成了。

keepalived搭建

kp的安装和配置过程可以参考之前文章《CentOS7+Keepalived+Nginx高可用配置》

其中的nginx验活脚本改为mysql验活脚本

check_msql.sh

#!/bin/bash
if [ "$(ps -ef | grep "/usr/sbin/mysqld"| grep -v grep )" == "" ];
then 
    systemctl start mysqld.service
    sleep 30 
    if [ "$(ps -ef | grep "/usr/sbin/mysqld"| grep -v grep )" == "" ];
    then
        systemctl stop keepalived
    fi
fi

node3配置文件

! Configuration File for keepalived

global_defs
{
    notification_email {
        acassen@firewall.loc
        failover@firewall.loc
        sysadmin@firewall.loc
    }
    notification_email_from Alexandre.Cassen@firewall.loc
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id LVS_DEVEL
    vrrp_skip_check_adv_addr
    vrrp_strict
    vrrp_garp_interval 0
    vrrp_gna_interval 0
    script_user root
    enable_script_security
}

vrrp_script mysql_check
{
    script "/joinway/tools/keepalived/check_msql.sh"
    interval 1
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass joinway
    }
    virtual_ipaddress {
        192.168.88.101
    }
    track_script {
        mysql_check
    }
    notify_master /joinway/tools/keepalived/master.sh
    notify_backup /joinway/tools/keepalived/backup.sh
    notify_fault /joinway/tools/keepalived/fault.sh
    notify_stop /joinway/tools/keepalived/stop.sh
}

node4配置文件

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
   script_user root
   enable_script_security
}

vrrp_script mysql_check {
  script "/joinway/tools/keepalived/check_msql.sh"
  interval 1
  user root
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass joinway
    }
    virtual_ipaddress {
        192.168.88.101
    }
    track_script {
        mysql_check
    }
    notify_master /joinway/tools/keepalived/master.sh
    notify_backup /joinway/tools/keepalived/backup.sh
    notify_fault /joinway/tools/keepalived/fault.sh
    notify_stop /joinway/tools/keepalived/stop.sh

}