CENTOS6.3利用Keepalived构建双主MySQL+双机热备.doc

CENTOS6.3利用Keepalived构建双主MySQL+双机热备之前的博文介绍了如何配置MYSQL双主互备.见/archives/698这里介绍如何配合前者实现Keepalived双机热备系统环境：CentOS 6.3 x64MySQL版本：mysql-5.6.10Keepalived版本：keepalived-1.2.7MySQL-VIP：53MySQL-master1：01MySQL-master2：49首先关闭iptables和SELINUX# service iptables stop# setenforce 0# vi /etc/sysconfig/selinux-SELINUX=disabled-注: 若线上需要开启iptables,需加一条规则使keepalived的vrrp通行# iptables -A INPUT -p vrrp -j ACCEPT1.在MySQL-master1：01服务器上keepalived安装及配置编译安装，实际以本机kernel版本为准# wget/software/keepalived-1.2.7.tar.gz# tar zxvf keepalived-1.2.7.tar.gz# cd keepalived-1.2.7# ./configure -prefix=/usr/local/keepalived -with-kernel-dir=/usr/src/kernels/2.6.32-279.el6.x86_64# make & make install设置keepalived开机启动脚本# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/# chkconfig keepalived on新建一个配置文件，默认keepalived启动会去/etc/keepalived目录下寻找配置文件# mkdir /etc/keepalived# vi /etc/keepalived/keepalived.conf-global_defs notification_email #当主、备份设备发生改变时，通过邮件通知notification_email_smtp_server smtp_connect_timeout 30router_id MySQL-havrrp_instance VI_1# 在初始化状态下定义为主设备state BACKUP# 注意网卡接口interface eth0virtual_router_id 51# 优先级，另一台改为90priority 100advert_int 1# 不主动抢占资源nopreemptauthentication # 认证方式，可以是PASS或AH两种认证方式auth_type PASS# 认证密码auth_pass 1111virtual_ipaddress # 虚拟IP地址，随着state的变化而增加删除53virtual_server 53 3306 # 每个2秒检查一次real_server状态delay_loop 2# LVS算法lb_algo wrr# LVS模式lb_kind DR# 会话保持时间persistence_timeout 60protocol TCPreal_server 01 3306 # 权重weight 3# 检测到服务down后执行的脚本notify_down /etc/rc.d/keepalived.shTCP_CHECK # 连接超时时间connect_timeout 10# 重连次数nb_get_retry 3# 重连间隔时间delay_before_retry 3# 健康检查端口connect_port 3306-编写检测服务down后所要执行的脚本# vi /etc/rc.d/keepalived.sh-#!/bin/sh/etc/init.d/keepalived stop-# chmod +x /etc/rc.d/keepalived.sh注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服务状态。当发现real_server服务故障时，便触发此脚本.我们可以看到，脚本就一个命令:通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移.另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP.以上脚本是为了在测试环境实现VIP切换功能，生产环境建议使用如下脚本-#!/bin/bash# 环境变量PATH=/bin:/usr/bin:/sbin:/usr/sbin; export PATH# 暂停5秒执行，防止数据库人工正常重启sleep 5# mysql_id(存活=1 死掉=0)mysql_id=ps -C mysqld -noheader |wc -l# 判断mysql_id若死掉，则重启mysql一次，若仍然无法启动mysql则杀掉keepaliaved进程实现VIP切换if $mysql_id -eq 0 ;then/etc/init.d/mysqld restartsleep 5if $mysql_id -eq 0 ;then/etc/init.d/keepalived stopfifi-启动keepalived# /etc/init.d/keepalived start查看连接状态# ps -aux | grep keepalived返回：-root 1387 1 0 21:13 ? 00:00:00 keepalived -Droot 1390 1387 0 21:13 ? 00:00:00 keepalived -Droot 1391 1387 0 21:13 ? 00:00:00 keepalived -Droot 1976 1911 0 21:16 pts/0 00:00:00 grep keepalived-测试查看VIP信息# ip addr-.inet 01/24 brd 55 scope global eth0inet 53/32 scope global eth0inet6 fe80:20c:29ff:feb2:9199/64 scope linkvalid_lft forever preferred_lft forever-注:如果出现主从在切换VIP时，主的VIP未自动关闭，从的正常开启，导致都获取到VIP，造成互相冲突的情况，可手动输入如下命令删除一方的VIP地址# ip addr del 虚拟ip dev eth0但此方法只是一个临时的解决方案，当手动删除VIP后，重启keepalived服务会无法正常开启vip，需要重启服务器方能恢复。查看vrrp通信# tcpdump vrrp-.18:10:56.365730 IP 01 : VRRPv2, Advertisement, vrid 52, prio 40, authtype simple, intvl 1s, length 2018:10:57.366825 IP 01 : VRRPv2, Advertisement, vrid 52, prio 40, authtype simple, intvl 1s, length 2018:10:58.367914 IP 01 : VRRPv2, Advertisement, vrid 52, prio 40, authtype simple, intvl 1s, length 20-注:如果主keepalived挂掉，这里的日志会切换到备keeplived的信息找一台局域网PC，然后去ping MySQL的VIP，这时候MySQL的VIP是可以ping的通的停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本,去kill掉keepalived进程# service mysqld stop# ps -aux | grep keepalived无返回结果2.在MySQL-master2：49服务器上keepalived安装及配置编译安装，实际以本机kernel版本为准# wget/software/keepalived-1.2.7.tar.gz# tar zxvf keepalived-1.2.7.tar.gz# cd keepalived-1.2.7# ./configure -prefix=/usr/local/keepalived -with-kernel-dir=/usr/src/kernels/2.6.32-279.el6.x86_64# make & make install设置keepalived开机启动脚本# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/# chkconfig keepalived on新建一个配置文件，默认keepalived启动会去/etc/keepalived目录下寻找配置文件# mkdir /etc/keepalived# vi /etc/keepalived/keepalived.conf-global_defs notification_email notification_email_smtp_server smtp_connect_timeout 30router_id MySQL-havrrp_instance VI_1# 两台配置此处均是BACKUPstate BACKUP# 注意网卡接口interface eth0virtual_router_id 51# 优先级，另一台改为100priority 90advert_int 1# 不主动抢占资源nopreemptauthentication auth_type PASSauth_pass 1111virtual_ipaddress 53virtual_server 53 3306 # 每个2秒检查一次real_server状态delay_loop 2# LVS算法lb_algo wrr# LVS模式lb_kind DR# 会话保持时间persistence_timeout 60protocol TCPreal_server 49 3306 weight 3# 检测到服务down后执行的脚本notify_down /etc/rc.d/keepalived.shTCP_CHECK # 连接超时时间connect_timeout 10# 重连次数nb_get_retry 3# 重连间隔时间delay_before_retry 3# 健康检查端口connect_port 3306-编写检测服务down后所要执行的脚本# vi /etc/rc.d/keepalived.sh-#!/bin/sh/etc/init.d/keepalived stop-# chmod +x /etc/rc.d/keepalived.sh启动keepalived# /etc/init.d/keepalived start查看连接状态# ps -aux | grep keepalived返回：-root 1387 1 0 21:13 ? 00:00:00 keepalived -Droot 1390 1387 0 21:13 ? 00:00:00 keepalived -Droot 1391 1387 0 21:13 ? 00:00:00 keepalived -Droot 1976 1911 0 21:16 pts/0 00:00:00 grep keepalived-至此配置完成，网站后台只需要配置MySQL-VIP：53虚拟地址即可这里实际连接的master数据库，以哪台master先开mysql服务为准。当master1：01 mysql端口挡掉或者服务器异常关闭，keepalived会自动跳转到master2：49.因为两台数据库的数据时同步的,用户访问的是MySQL-VIP：53虚拟地址，所以网站数据连接会无缝透明转接到master2服务器，实现双机热备+数据同步功能。保证网站数据库的实时可用性。注：当某一台master服务器挂掉恢复后，需同时打开MYSQL服务和keepalived服务，保证另一台服务器如果挂掉会无缝转接。所以建议mysql与keeplived设置服务开机启动。# chkconfig mysqld on# chkconfig keepalived on-大功告成-CENTOS6.3下MYSQL主主互备架构配置MySQL主主互备结构是基于mysql增量日志基础上的，区别于主从复制结构.在主主复制结构中，两台服务器的任何一台上面的数据库存发生了改变都会同步到另一台服务器上，这个改变是基于sql语句的改变，如果删除系统数据库源文件或删除后新创建同名MYSQL表实现同步则无效。这样两台服务器互为主从，并且都能向外提供服务，这就比使用主从复制具有更好的性能.接下来我将使用两个相同的环境来实现这个效果：系统环境：centos6.3数据库： mysql-5.6.10注:在做此项配置之前首先要保证数据库当前无任何变动，建议同步前关闭WEB服务等影响数据库变动的服务安装配置mysql(略)1.主1 mysql配置:(01)删除之前遗留bin日志# rm -rf /usr/local/mysql/log/bin*修改mysql配置文件：# vi /etc/f添加：-# Replication Master Server 1# bin日志路径log-bin = /usr/local/mysql/log/bin.log# 服务器ID号server-id = 1# 忽略mysql数据库复制binlog-ignore-db=mysql# 每次增长2auto-increment-increment = 2# 设置自动增长的字段的偏移量，即初始值为2 auto-increment-offset = 1-# mysql -u root -p123456在主1上为主2添加同步帐号 grant replication slave on *.* to slave49 identified by 123456;将主1的数据库表全部备份，并发送到主2服务器上。# /usr/local/mysql/bin/mysqldump -u root -p123456 -opt -skip-lock-tables -flush-logs -all-database /root/allbak.sql# cd # scp allbak.sqlroot49:/root重启服务# service mysqld restart# mysql -u root -p123456;配置连接到主2服务器（注意这步一定要在主2添加账户后执行） stop slave; change master to master_host=49,master_user=slave,master_password=123456; start slave;2.主2 mysql配置（49）删除之前遗留bin日志# rm -rf /usr/local/mysql/log/bin*修改mysql配置文件：# vi /etc/m