環(huán)境
硬件
| 配置 | 測試配置 |
|---|---|
| CPU | 1.8GHz |
| 內(nèi)存 | 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
規(guī)劃
| HOST | IP | 說明 |
|---|---|---|
| node3 | 192.168.88.88 | 主節(jié)點 |
| node4 | 192.168.88.94 | 備份節(jié)點 |
| kp虛擬路由 | 192.168.88.101 | vip |
雙主環(huán)境搭建
之前搭建了一套mysql一主一備的環(huán)境，過程參考文章《CentOS 7 + MySQL 5.7 搭建一主一備》，備份節(jié)點可以作為主節(jié)點的數(shù)據(jù)備份，也可以作為讀寫分離支持來降低主節(jié)點的性能壓力，但是，如果主節(jié)點發(fā)生故障快耿，備份節(jié)點無法做到自動接管主節(jié)點的業(yè)務(wù)（需要人工或者其他工具輔助切換）。
本次在一主一備的基礎(chǔ)上幸缕，改造一套雙主運行的環(huán)境极舔。
node4
創(chuàng)建slave用戶
mysql -p
# 只讀賦權(quán)
GRANT SELECT ON *.* TO 'slave'@'192.168.88.88' IDENTIFIED BY 'Slave!23';
# 從權(quán)限 賦權(quán)
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.88.88' IDENTIFIED BY 'Slave!23';
# 刷新權(quán)限
FLUSH PRIVILEGES;
配置文件添加如下配置
# 自增id起始值
auto_increment_offset=2
# 每次自增數(shù)字
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
# 每次自增數(shù)字
auto_increment_increment=8
驗證
node3上創(chuàng)建表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上創(chuàng)建表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表一條數(shù)據(jù)
insert into t_test2 (c_name) value ('aaa');
select i_id,c_name from t_test2;
node4中可見
node4中的數(shù)據(jù)
node4上插入t_test2表一條數(shù)據(jù)
insert into t_test2 (c_name) value ('bbb');
select i_id,c_name from t_test2;
node3中可見
node3中的數(shù)據(jù)
至此凤覆，雙主節(jié)點的mysql環(huán)境已經(jīng)全部完成了。
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服務(wù)狀態(tài)正常
通過vip連接數(shù)據(jù)
vip正常
模擬node3掛了
node3掛了
node4接管服務(wù)
vip正常
插入一條數(shù)據(jù)拆魏，從自增id可以看出是node4插入的盯桦。
node4插入一條數(shù)據(jù)
node3恢復(fù)
node3恢復(fù)
vip連接正常
vip連接正常
插入2條數(shù)據(jù)，從自增可以看出渤刃，node3已經(jīng)接管
node3插入數(shù)據(jù)
