Keeplived+mysql双master高可用如何实现？
Keeplived+mysql双master高可用如何实现？
作者：墨篱弦
简易拓扑
https://oscimg.oschina.net/oscnet/up-18b70e55451e4e69d0f449e6329034bdcfa.png
实验环境
Mysql40.11（master-1） 192.168.40.11/24
Mysql40.12（master-2） 192.168.40.12/24
Keepalived 192.168.40.18/24
安装依赖环境和mysql（过程略）
配置master-1和master-2互为主从关系
修改master-1的mysql配置
vim /etc/my.cnf
#log config
log-bin=mysql-bin
relay-log=relay-log
relay-log-index=relay-log.index
server-id=11
innodb-file-per-table=ON
skip_name_resolve=ON
重启数据库
systemctl restart mysqld
连接数据库
mysql -uroot -p******
查看日志信息
show global variables like '%log%';
https://oscimg.oschina.net/oscnet/up-64a20398db3d3663300345fe3fea58549b4.png
https://oscimg.oschina.net/oscnet/up-52bdc44bcc14d0b72aefc988ae4093ca146.png
在master-1创建有复制权限的用户
grant replication slave on *.* to 'backup'@'%' identified by 'Backup123!@#';
flush privileges;
修改master-2的mysql配置
vim /etc/my.cnf
#log config
log-bin=log-bin
relay-log=relay-log
relay-log-index=relay-log.index
server-id=12
innodb_file_per_table=ON
skip_name_resolve=ON
在master-2创建有复制权限的用户
grant replication slave on *.* to 'backup'@'%' identified by 'Backup123!@#';
flush privileges;
重启数据库
systemctl restart mysqld
连接数据库
mysql -uroot -p123456
查看日志信息
show global variables like '%log%';
在master-1上查看log-bin文件名和pos值
show master status;
在master-2上开启主从
mysql> change master to
-> master_host='192.168.40.11',
-> master_user='backup',
-> master_password='Backup123!@#',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=591;
start slave;
查看主从状态
show slave status\G
master-1与master-2主从关系建立成功
同理现在创建master-2与master-1的主从关系
在master-2上查看log-bin文件名和pos值
show master status;
在master-1上开启主从
mysql> change master to
-> master_host='192.168.40.12',
-> master_user='backup',
-> master_password='Backup123!@#',
-> master_log_file=' log-bin.000003',
-> master_log_pos=591;
start slave;
查看主从状态
验证master-1和master-2是否互为主从
创建远程访问账号
grant all privileges on *.*to 'test'@'%' identified by '123qwe!@#QWE' with grant option;
端口为默认端口号
show global variables like '%port%';
在master-1创建新库 123
在master-2上查看数据库是否同步过来
img
在master-2上的数据库123上创建新表321
在master-1上的数据库123查看是否同步过来了新表
img
验证通过，master-1和master-2互为主从关系
在两台服务器上分别安装keepalived（过程略）
给master-1和master-2分别安装keepalived
Master-1安装keepalived
安装依赖坏境
yum -y install curl gcc openssl-devel libnl3-devel net-snmp-devel libnfnetlink-devel
创建/data/keepalived，创建/software
mkdir –p /data/keepalived
mkdir –p /software
上传安装包到/software, 解压安装包, 进入keepalived-2.1.5目录
tar -zxvf keepalived-2.1.5.tar.gz
cd keepalived-2.1.5
编译安装
./configure --prefix=/data/keepalived --sysconf=/etc
make & manke install
进入安装后的路径
cd /data/keepalived/
创建软连接，覆盖软连接
ln -s sbin/keepalived /sbin/
ln -snf sbin/keepalived /sbin
复制运行命令
cp /software/keepalived-2.1.5/keepalived/etc/init.d/keepalived /etc/init.d
chkconfig --add keepalived
添加到系统服务（开机启动），启动服务
chkconfig keepalived on
service keepalived start
Master-2安装和master-1相同
在master-1上创建chk_mysqld检测mysql是否运行的脚本，添加脚本执行权限
vim chk_mysqld.sh
#!/bin/bash
A=`ps -C mysqld --no-header|wc -l`
if [ $A -eq 0 ];then
systemctl start mysqld
sleep 2
if [ `ps -C mysqld --no-header|wc -l` -eq 0 ];then
killall keepalived
fi
fi
chmod 775 chk_mysqld.sh
配置master-1的keepalived
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MYSQL
}
vrrp_script chk_mysqld {
script "/root/chk_mysqld.sh"
interval 10
weight -5
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 18
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.40.18/24
}
track_script {
chk_mysqld
}
}
重启keepalived，查看是否拿到虚拟IP
service keepalived restart
ip add
在master-2上创建chk_mysqld检测mysql是否运行的脚本，添加脚本执行权限
vim chk_mysqld.sh
#!/bin/bash
A=`ps -C mysqld --no-header|wc -l`
if [ $A -eq 0 ];then
systemctl start mysqld
sleep 2
if [ `ps -C mysqld --no-header|wc -l` -eq 0 ];then
killall keepalived
fi
fi
chmod 775 chk_mysqld.sh
配置master-2的keepalived
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MYSQL
}
vrrp_script chk_mysqld {
script "/root/chk_mysqld.sh"
interval 10
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 18
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.40.18/24
}
track_script {
chk_mysqld
}
}
重启keepalived，查看是否拿到虚拟IP
service keepalived restart
ip add #因为设置了非抢占，默认谁优先开机启动完成，谁为主库，谁拿到vip
验证keepalived的非抢占
重启master-1并查看master网络信息
在查看master-1网络信息
验证数据库
目前主库在40.12
重启master-2看数据库是否会自动切换到master-1上
数据库发生了切换期间出现过一次丢包
对数据的影响还未验证。。
还可以测试下，如果直接down网口的情况下 服务器以及数据库是否能实现自动切换？