mysql双主+keepalived

概述介绍《mysql双主+keepalived》开发教程，希望对您有用。

《MysqL双主+keepalived》要点：
本文介绍了MysqL双主+keepalived，希望对您有用。如果有疑问，可以联系我们。

简单原理

1、在两台服务器上分别部署双主keepalived,主keepalived会在当前服务器配置虚拟IP用于MysqL对外提供服务

2、在两台服务器上分别部署主主MysqL,用于故障切换

3、当MysqL服务器挂掉后,主keepalived会降低当前机器权重,备keepalived服务器会把虚拟IP抢过来配置在备服务器上,使备服务器的MysqL能接替工作继续对外提供服务

4、由于keepalived只能检测服务器是否宕机来实现故障自动切换,但不能针对应用级别(MysqL)的检测,因此,需要编写脚本实时监测MysqL服务是否运行正常,当检测MysqL运行不正常时就降低权重,来实现故障自动切换

角色分配：

IP地址	部署应用
192.168.1.200	MysqL001+keepalived01
192.168.1.201	MysqL002+keepalived02

虚拟IP192.168.1.100初始配置在keepalived01,无需手动配置,keepalived会自动配置

准备工作：

#关闭iptables

service iptables stop

chkconfig iptables off

#关闭selinux

setenforce 0

修改/etc/selinux/config文件,将SEliNUX=enforcing改为SEliNUX=Disabled

#同步主机时间

ntpdate 202.120.2.101

=====================================================================

一、配置MysqL双主服务

#用yum安装MysqL服务

yum install MysqL-shared-compat-5.6.23-1.el6.x86_64.rpm

yum install MysqL-server-5.6.23-1.el6.x86_64.rpm

yum install MysqL-clIEnt-5.6.23-1.el6.x86_64.rpm

yum install MysqL-devel-5.6.23-1.el6.x86_64.rpm

yum install MysqL-shared-5.6.23-1.el6.x86_64.rpm

#创建数据目录

mkdir -pv /home/mydata/data

chown -R MysqL.MysqL /home/mydata

chmod -R +w /home/mydata

#修改主配置文件

vi /etc/my.cnf

#在MysqL001上

[MysqLd]

datadir = /home/mydata/data #MysqL的数据存放位置

port = 3306 #MysqL的端口号

socket = /var/lib/MysqL/MysqL.sock

log-bin = master-bin

server_ID = 1 #MysqL的ID号

log-bin = MysqL-bin

relay-log = MysqL-relay-bin

replicate-wild-ignore-table = MysqL.%

replicate-wild-ignore-table = test.%

replicate-wild-ignore-table = information_schema.%

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

pID-file = /home/mydata/data/MysqL.pID

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

innodb_file_per_table = on

thread_concurrency = 8

skip_name_resolve = on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_tableS

#在MysqL002上

[MysqLd]

datadir = /home/mydata/data

port = 3306

socket = /var/lib/MysqL/MysqL.sock

log-bin = master-bin

server_ID = 2

log-bin = MysqL-bin

relay-log = MysqL-relay-bin

replicate-wild-ignore-table = MysqL.%

replicate-wild-ignore-table = test.%

replicate-wild-ignore-table = information_schema.%

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

pID-file = /home/mydata/data/MysqL.pID

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

innodb_file_per_table = on

thread_concurrency = 8

skip_name_resolve = on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_tableS

#初始化MysqL

/usr/bin/MysqL_install_db --datadir=/home/mydata/data --user=MysqL

#相互为对方主机授权复制账号

grant replication slave on *.* to 'repl_user'@'192.168.%.%' IDentifIEd by 'repl_passwd';

#查看master的状态

show master status;

#授权给check用户,用来检测MysqL

grant replication clIEnt on *.* to 'check'@'localhost' IDentifIEd by 'check';

#配置对方为自己的master

在MysqL001上：

change master to \

master_host='192.168.1.201',

master_user='repl_user',

master_password='repl_passwd',

master_log_file='MysqL-bin.000003',

master_log_pos=333; 【这个数字是上面show master status;】

在MysqL002上：

change master to \

master_host='192.168.1.200',

master_log_pos=333;

#启动服务,两台机器上都执行

start slave;

#查看是否运行正常

show slave status\G;

二、配置keepalived

在MysqL001上：

vi /etc/keepalived/keepalived.conf

#######################################################

! Configuration file for keepalived

global_defs {

notification_email {

2011820123@qq.com

}

notification_email_from keepalived@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_ID MysqL_DEVEL

}

vrrp_script check_MysqLd { #设置检测脚本

script "/etc/keepalived/check.sh" #指定检测脚本的存放位置

interval 2 #间隔时间

weight -51 #权重,降权51

}

vrrp_instance VI_1 {

state BACKUP #注意,这里两台服务器都要是BACKUP

interface em1

virtual_router_ID 60

priority 100

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_MysqLd

}

virtual_ipaddress {

192.168.1.100 #虚拟IP

}

}

#######################################################

在MysqL002上：

vi /etc/keepalived/keepalived.conf

#######################################################

! Configuration file for keepalived

global_defs {

notification_email {

2011820123@qq.com

}

notification_email_from keepalived@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_ID MysqL_DEVEL

}

vrrp_script check_MysqLd {

script "/etc/keepalived/check.sh"

interval 2

weight -51

}

vrrp_instance VI_1 {

state BACKUP #注意,这里两台服务器都要是BACKUP

interface em1

virtual_router_ID 60

priority 90

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_MysqLd

}

virtual_ipaddress {

192.168.1.100

}

}

#######################################################

"state BACKUP”,在这里两台服务器要都设为BACKUP,如果一台是MASTER,另一台是BACKUP,那么当MASTER恢复数据之后,主keepalived要切换到MASTER上,这样BACKUP上的数据就会丢失.因此在这两台MysqL服务器上都设置成BACKUP,那么当MASTER恢复后,不会自动切换回去

#重启keepalived服务

service keepalived restart

五、编辑MysqL监控脚本

vi /etc/keepalived/check.sh

#######################################################

#/bin/bash

live=`ss -tnlp | grep 3306 | wc -l` #检查MysqL的3306端口是否存在

yes=`MysqL -ucheck -pcheck -e "show slave status\G" | head -13 | tail -2 | awk -F: '{print $2}' | grep Yes | wc -l` #检查‘show slave status’是否正常

if [ $live -ge 1 ];then #如果$live大于等于1,执行下步 *** 作

if [ $yes -eq 2 ];then #如果‘show slave status’出现两个yes,就退出

exit 0

else

/etc/init.d/keepalived restart #否则,重启keepalived服务,退出

exit 1

fi

else

/etc/init.d/keepalived restart #如果$live小于1,那么重启keepalived服务

exit 1

fi

#######################################################

#给脚本执行权限

chmod +x /etc/keepalived/check.sh

欢迎参与《MysqL双主+keepalived》讨论，分享您的想法，内存溢出PHP学院为您提供专业教程。

总结

以上是内存溢出为你收集整理的mysql双主+keepalived全部内容，希望文章能够帮你解决mysql双主+keepalived所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。