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网口的情况下 服务器以及数据库是否能实现自动切换？