mysql双主+keepalived

简单原理

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

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

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

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

角色分配：

虚拟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_user='repl_user',

master_password='repl_passwd',

master_log_file='mysql-bin.000003',

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