环境
硬件
| 配置 | 测试配置 |
|---|---|
| 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
}
验证
kp服务状态正常
通过vip连接数据
vip正常
模拟node3挂了
node3挂了
node4接管服务
vip正常
插入一条数据，从自增id可以看出是node4插入的。
node4插入一条数据
node3恢复
node3恢复
vip连接正常
vip连接正常
插入2条数据，从自增可以看出，node3已经接管
node3插入数据
