Mysql +MHA+LVS+KEEPALIVED高可用,读写分离,负载均衡(学习笔记十七)

http://blog.sina.com.cn/s/blog_166c0ec620102wz03.html

一、MHA及相关软件简介

MHA是一款MySQL高可用开源软件,实现MySQL一主多从架构下,主备的failover自动切换、手动切换、状态监控等功能,是比较常用的高可用解决方案之一。

LVS是linux下的一款虚拟服务器软件,内部实现了虚拟路由协议,实现多节点服务器之间负载均衡。

Keepalived高可用软件,支持LVS算法,从而结合LVS实现负载均衡服务器的高可用。

二、服务器及软件版本

操作系统:CentOS6.5 64

MySQL:5.7

IP地址主机名称MySQL软件LVSKeepalived

10.211.55.41centos6mha01mysql clientlvs主节点keep主节点

10.211.55.42centos6mha02mysql master

10.211.55.43centos6mha03mysql slave1 (备用master)lvs realserver

10.211.55.44centos6mha04mysql slave2lvs realserver

10.211.55.45centos6mha05mysql clientlvs备节点keep备节点

vip:10.211.55.100 master写入

10.211.55.200 slave读取

读写分离策略,通过mha的failover检查脚本,实现写入vip的绑定切换,即绑定master为写入服务器

通过keepalived配置vip实现slave节点读取请求的统一接入

三、MySQL主从部署

mysql软件的安装过程略过,本次采用通用二进制安装包,解压安装,主要针对主从架构的三台服务器

10.211.55.42、10.211.55.43、10.211.55.44,另外两台只作为mysql的客户端,因为涉及一些mysql相关脚本的调用,所以yum安装mysql相关库即可,当然用二进制安装包安装更好,下面主要是主从相关配置项,mysql通用配置项省略。

10.211.55.42:

vim /etc/my.cnf

server_id=42 #以主机IP的末位作为标识,区分不同mysql节点

log_bin=mysql-bin

binlog_format=mixed

#for replication

binlog_cache_size=1M

expire_logs_days=14 #日志过期时间

relay_log=mysql-relay-bin

skip-slave-start #关闭slave复制进程随数据库启动而启动,方便维护

relay_log_purge=0 #为了保证主从环境顺利的failover切换,禁用自动清除,采用脚本定期清除

relay_log_index=mysql-relay-bin.index

从库10.211.55.43、10.211.55.44的配置除了server_id不同,其余配置与10.211.55.42相同

四、MHA软件安装

1、安装yum源

为了方便后续软件的安装,每台机器安装,最好将此源rpm下载到本地备用,wget以下地址即可

rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

yum clean all

2、创建MHA用户(在主从环境的主上执行)

mysql> grantall privileges on *.* to 'mha'@'10.211.55.%' identified by 'oracle';

mysql> flush privileges;

3、创建软连接(在所有mysql上都执行)

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

4、配置SSH无密码登录认证

各节点生成秘钥,并将公钥传送到其他服务器,包括自身,如

创建秘钥

ssh-keygen -t rsa

传送到其他机器生成认证文件

ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.211.55.41

ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.211.55.42

ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.211.55.43

ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.211.55.44

ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.211.55.45

5、配置hosts

[root@panda ~]# vim /etc/hosts

10.211.55.41 centos6mha01

10.211.55.42 centos6mha02

10.211.55.43 centos6mha03

10.211.55.44 centos6mha04

10.211.55.45 centos6mha05

说明:在所有机器上的 /etc/hosts中添加上面配置.

5、部署MHA Node

在所有运行MySQL服务的服务器上安装运行MHA Node,无论是master还是slave。由于MHA Manager需要MHA Node,因此在运行MHA Manager的服务器上也需要安装MHA Node。当然也可以在任意一个slave上运行MHA Manager。因为部署步骤相同,所以就列出一个安装步骤。

10.211.55.41 10.211.55.42 10.211.55.43 10.211.55.44

为了perl-DBD-MySQL安装合适的相关依赖,建议删除操作系统自带的mysql组件

查找相关mysql-lib包,并删除之

rpm -qa|grep mysql

rpm -e --nodeps mysql-libXXXX.rpm

yum install -y perl-DBD-MySQL perl-DBI cpan git

tar -xf mha4mysql-node-0.57.tar.gz

cd mha4mysql-node-0.57

perl Makefile.PL

make && make install

6、部署MHA Manager

MHA Manager仅运行在作为manager的服务器上。当然也可以部署在其中任意一台slave上。

这个示例里是部署在10.211.55.41

(1)安装MHA Manager

yum install -y perl perl-Config-Tiny perl-Email-Date-Format perl-Log-Dispatch perl-MIME-Lite perl-MIME-Types perl-Mail-Sender perl-Mail-Sendmail perl-MailTools perl-Parallel-ForkManager perl-Params-Validate perl-Time-HiRes perl-TimeDate

tar mha4mysql-manager-0.57.tar.gz

cd mha4mysql-manager-0.57

perl Makefile.PL

make && make install

(2)规范mha目录

mkdir -p /usr/local/mha/conf

mkdir -p /usr/local/mha/logs

mkdir -p /usr/local/mha/workstatus/app1

cp mha4mysql-manager-0.57/samples/conf/* /usr/local/mha/conf

(3)配置app1.cnf

cd /usr/local/mha/conf

cp app1.cnf app1.cnf.old

vim app1.cnf

[server default]

manager_log=/usr/local/mha/logs/app1.log

manager_workdir=/usr/local/mha/workstatus/app1

master_binlog_dir=/mydata/data

remote_workdir=/var/log/mha/app1

user=mha

password=oracle

port=3306

repl_password=oracle

repl_user=repl

secondary_check_script= /usr/local/bin/masterha_secondary_check -s centos6mha02 -s centos6mha03 -s centos6mha04

master_ip_failover_script= /usr/local/bin/master_ip_failover

#master_ip_online_change_script= /usr/local/bin/master_ip_online_change

[server1]

candidate_master=1

hostname=centos6mha02

[server2]

candidate_master=1

hostname=centos6mha03

[server3]

hostname=centos6mha04

(4)、配置全局配置文件

cp masterha_default.cnf masterha_default.cnf.old

vim masterha_default.cnf

[server default]

log-level=debug

check_repl_delay=1

check_repl_filter=1

ping_interval=5

ping_type=CONNECT

(5)设置定时清理relay脚本

使用如下定时任务设置crontab来定期清理relay log

10.211.55.42 - 44

vim /etc/cron.d/purge_relay_logs

0 4 * * * /usr/local/bin/purge_relay_logs --user=root --password=oracle --disable_relay_log_purge --port=3306 --workdir=/mydata/data >>/usr/local/mha/logs/purge_relay_logs.log 2>&1

(6)failover切换脚本

伴随mha启动和failover切换调用的检查脚本,实现mysql master切换时的vip绑定

10.211.55.41

vim /usr/local/bin/master_fail_over

#!/usr/bin/env perl

use strict;

use warnings FATAL => 'all';

use Getopt::Long;

my (

  $command,    $ssh_user,   $orig_master_host, $orig_master_ip,

  $orig_master_port, $new_master_host, $new_master_ip, $new_master_port

);

my $vip = '10.211.55.100/24';

my $key = '1';

my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip up";

my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

$ssh_user = "root";

GetOptions(

  'command=s'    => \$command,

  'ssh_user=s'    => \$ssh_user,

  'orig_master_host=s' => \$orig_master_host,

  'orig_master_ip=s' => \$orig_master_ip,

  'orig_master_port=i' => \$orig_master_port,

  'new_master_host=s'=> \$new_master_host,

  'new_master_ip=s' => \$new_master_ip,

  'new_master_port=i'=> \$new_master_port,

);

exit &main();

sub main {

  print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

  if ( $command eq "stop" || $command eq "stopssh" ) {

    my $exit_code = 1;

    eval {

      print "Disabling the VIP on old master: $orig_master_host \n";

      &stop_vip();

      $exit_code = 0;

    };

    if ($@) {

      warn "Got Error: $@\n";

      exit $exit_code;

    }

    exit $exit_code;

  }

  elsif ( $command eq "start" ) {

    my $exit_code = 10;

    eval {

      print "Enabling the VIP - $vip on the new master - $new_master_host \n";

      &start_vip();

      $exit_code = 0;

    };

    if ($@) {

      warn $@;

      exit $exit_code;

    }

    exit $exit_code;

  }

  elsif ( $command eq "status" ) {

    print "Checking the Status of the script.. OK \n";

    `ssh $ssh_user\@$orig_master_ip \" $ssh_start_vip \"`;

    exit 0;

  }

  else {

    &usage();

    exit 1;

  }

}

sub start_vip() {

  `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;

}

sub stop_vip() {

  return 0unless($ssh_user);

  `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;

}

sub usage {

  print

  "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --n

ew_master_ip=ip --new_master_port=port\n";

}

(7)mha ssh连通性及m/s复制状态检查

10.211.55.41

cd /usr/local/bin

masterha_check_ssh --conf=/usr/local/mha/con/app1.cnf

masterha_check_repl --conf=/usr/local/mha/con/app1.cnf

如发现异常和错误,需要检查做过的相关步骤,比如ssh无密码登录是否通过,mysql master slave服务是否开启,相关的rpm包是否安装完全等

(8)启动mha管理进程

nohup masterha_manager --conf=/usr/local/mha/conf/app1.cnf --ignore_last_failover /usr/local/mha/logs/manager.log 2>&1 &

观察写入vip的绑定情况

在当前mysql master服务器上,也就是10.211.55.42上执行以下命令可以发现10.211.55.100被绑定在eth0上

ip a或者ifconfig

通过客户端对vip发起mysql连接请求,成功登入。

此时mha的高可用架构就完成了。

五、LVS安装与配置

lvs主备节点均瑶安装 10.211.55.41 、10.211.55.45

yum install libnl* popt* -y

以下步骤根据实际服务器的内核版本目录修改2.6.32-358.el6.x86_64,安装lvs必要步骤,否则编译失败

ln -s /usr/src/kernels/2.6.32-358.el6.x86_64/ /usr/src/linux

wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.26.tar.gz

tar zxf ipvsadm-1.26.tar.gz

cd ipvsadm-1.26

make

make install

将lvs载入内核

ipvsadm

查看lvs虚拟路由列表

lsmod |grep ip_vs

realserver脚本

10.211.55.43 10.211.55.44分别执行

绑定读vip到mysql slave节点及设置arp抑制

vim arp_vip.sh

#!/bin/bash

vip=10.211.55.200

open() {

ifconfig lo:Rvip ${vip}/32 up

sysctl -w net.ipv4.conf.lo.arp_announce=2

sysctl -w net.ipv4.conf.lo.arp_ignore=1

sysctl -w net.ipv4.conf.all.arp_announce=2

sysctl -w net.ipv4.conf.all.arp_ignore=1

}

close(){

ifconfig lo:Rvip down

sysctl -w net.ipv4.conf.lo.arp_announce=0

sysctl -w net.ipv4.conf.lo.arp_ignore=0

sysctl -w net.ipv4.conf.all.arp_announce=0

sysctl -w net.ipv4.conf.all.arp_ignore=0

}

case $1 in

start)

    open ;;

stop)

    close;;

*)

    echo "Usage: $0 need argument [start|stop]"

;;

esac

执行后观察,vip已绑定在lo上

ifconfig

六、Keepalived安装与配置

lvs主备节点安装10.211.55.41 10.211.55.45

yum install openssl* popt* -y

tar zxf keepalived-1.3.5.tar.gz

cd keepalived-1.3.5

./configure --prefix=/usr/localkeepalived

make

检查编译状态,0为成功,否则返回检查

echo $?

make install

拷贝相关命令及配置

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

拷贝服务命令到系统目录,如遇安装目录下没有,

可以拷贝安装文件目录下的keepalived-1.3.5/keepalived/etc/init.d/keepalived

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

添加keepalived配置文件,主备的差别已注明

mkdir -p /etc/keepalived

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

    hahaha@sina.com

}

    notification_email_from alert-noreply@test.com.cn

    smtp_server 127.0.0.1

    smtp_connect_timeout 30

    router_id MHA_LVS_HA

}

# db master server.

vrrp_instance VI_1 {

    state MASTER

    interface eth0

    virtual_router_id 52

    priority 200 #节点优先级,主节点要高于从节点,主节点200从节点150

    advert_int 5

    authentication {

    auth_type PASS

    auth_pass cuixd

}

    virtual_ipaddress {

        10.211.55.200/24

    }

}

# VIP 10.211.55.200

virtual_server 10.211.55.200 3306 {

    delay_loop 10

    lb_algo rr

    lb_kind DR

    protocol TCP

    real_server 10.211.55.43 3306 {

        weight 1

        TCP_CHECK {

            connect_port 3306

            connect_timeout 10

            delay_before_retry 5

        }

        MISC_CHECK {

            misc_path "/etc/keepalived/check_slave.py 10.211.55.43 3306"

            #misc_path "/etc/keepalived/monitor_mysql.sh 10.211.55.43"

            misc_dynamic

        }

    }

    real_server 10.211.55.44 3306 {

        weight 1

        TCP_CHECK {

            connect_port 3306

            connect_timeout 10

            delay_before_retry 5

        }

        MISC_CHECK {

            misc_path "/etc/keepalived/check_slave.py 10.211.55.44 3306"

            #misc_path "/etc/keepalived/monitor_mysql.sh 10.211.55.44"

            misc_dynamic

        }

    }

}

keepalived mysql状态检查脚本check_slave.py,如遇状态检查失败,将失败节点从lvs读负载均衡列表中剔除

vim /etc/keepalived/check_slave.py

#!/usr/bin/env python

#encoding:utf-8

import MySQLdb

import sys

ip=sys.argv[1]

user='repl'

pwd='oracle'

port=int(sys.argv[2])

sbm=200

Slave_IO_Running = ''

Slave_SQL_Running = ''

Seconds_Behind_Master = ''

e=''

try:

    conn = MySQLdb.connect(host=ip,user=user,passwd=pwd,port=port,charset='utf8')

    cur = conn.cursor()

    cur.execute('show slave status')

    db_info = cur.fetchall()

    for n in db_info:

        Slave_IO_Running = n[10]

        Slave_SQL_Running = n[11]

        Seconds_Behind_Master = n[32]

    cur.close()

    conn.close()

except MySQLdb.Error,e:

    print "MySQLdb Error",e

if e == "":

    if db_info != ():

        if Slave_IO_Running == "No" or Slave_SQL_Running == "No":

            #print 'thread err'

            exit(1)

        else:

            if Seconds_Behind_Master > sbm:

                #print 'timeout err'

                exit(1)

            else:

                #print 'OK'

                exit(0)

    else:

        #print 'slave err'

        exit(1)

else:

    #print 'db err'

    exit(1)

启动keepalived

service keepalived start

观察日志及读VIP绑定情况

taif /var/log/message

当前lvs主节点即10.211.55.41上

ip a

看到10.211.55.200绑定在eth0上,表示成功

通过客户端对10.211.55.200发起mysql连接,成功登入。

七、测试与验证

1、验证读vip的高可用及负载均衡

客户端发起多次如下连接,检查server_id在两台slave切换表示负载均衡功能正常

mysql -urepl -h10.211.55.200 -poracle -e "show variables like 'server_id'"

保持多个客户端连接在lvs主机上查看连接

ipvsadm -Ln

关闭10.211.55.44 mysql服务后,重复上述检查,应能成功登入,server_id只有43

2、恢复初始状态,将10.211.55.44 mysql服务启动,设置半同步,开启salve复制,设置只读

service mysqld start

mysql>set global rpl_semi_sync_slave_enabled=on ;

mysql>start slave;

mysql>set global read_only=1;

3、验证mha failover切换及写vip绑定,读vip从新mysql master上剔除

关闭10.211.55.42 mysql服务

检查mha切换日志,应能成功切换,master切换至10.211.55.43

tailf /usr/local/mha/logs/app1.log

观察写vip的绑定切换到了10.211.55.43

ip a

发起对写vip的mysql连接,依然能成功登入表示正常

mysql -urepl -h10.211.55.100 -poracle

发起对读vip的mysql连接,能够成功登入,且只有10.211.55.44

mysql -urepl -h10.211.55.200 -poracle -e "show variables like 'server_id'"

lvs主节点10.211.55.41上

ipvsadm -Ln

4、验证lvs的高可用

关闭lvs主节点的keepalived

service keepalived stop

观察从节点10.211.55.45日志变化,此时切换为master主节点

tailf /var/log/message

查看读vip绑定已切换到10.211.55.45

ip a

此时发起对两个vip的mysql连接,依然能够正常登入,表示LVS主备高可用正常。

八、问题备注

1、安装过程多为联网状态安装,必要时应先准备好安装包,在非联网状态下使用,如mysql 、mha、lvs、keepalived,相关依赖的rpm建议在部署前联网状态下安装完毕

2、perl-MySQL-DBD在操作系统存在自带mysql-lib相关rpm的情况下,进行yum install perl-MySQL-DBD很有可能无法正常安装,建议先删除自带包

3、check_slave.py是keepalived配置中的mysql检查脚本,涉及mysql远程登录,所以在本地应包含mysql客户端,即10.211.55.41、10.211.55.45要有mysql客户端

4、check_salve.py是python脚本,对格式对齐要求非常严格,编辑时需仔细,且需要安装python连接mysql的module和mysql libs,yum install MySQL-python mysql-lib*



mysql主从复制与lvs+keepalived实现负载高可用

目录

1、前言    4

2、原理    4

2.1、概要介绍    4

2.2、工作原理    4

2.3、实际作用    4

3方案    4

3.1、环境    4

3.2、架构图    5

3.3、设计原理    6

4、相关软件安装    6

4、配置mysql的主从    7

5、通过lvs+keepalived实现负载与热备,并实现读写分离    8


1、前言

最近研究了下高可用的东西,这里总结一下mysql主从复制读写分离度的高可用方案,可以提高服务器的使用效率,也可以提高提高维护效率。同时应用的效率也会有一定的提升,如果改造需要应用修改读取的ip地址与写入的ip地址,改造起来还算容易。

2、原理      

2.1、概要介绍

如果将TCP/IP划分为5层,则Keepalived就是一个类似于3~5层交换机制的软件,具有3~5层交换功能,其主要作用是检测web服务器的状 态,如果某台web服务器故障,Keepalived将检测到并将其从系统中剔除,当该web服务器工作正常后Keepalived自动将其加入到服务器 群中,这些工作全部自动完成,而不需要人工干预,只需要人工修复故障的web服务器即可。

2.2、工作原理

Keepalived基于VRRP协议来实现高可用解决方案,利用其避免单点故障,通常这个解决方案中,至少有2台服务器运行Keepalived,即一 台为MASTER,另一台为BACKUP,但对外表现为一个虚拟IP,MASTER会发送特定消息给BACKUP,当BACKUP收不到该消息时,则认为 MASTER故障了,BACKUP会接管虚拟IP,继续提供服务,从而保证了高可用性,3层机理是发送ICMP数据包即PING给某台服务器,如果不痛,则认为其故障,并从服务器群中剔除。4层机理是检测TCP端口号状态来判断某台服务器是否故障,如果故障,则从服务器群中剔除。5层机理是根据用户的设定检查某个服务器应用程序是否正常运行,如果不正常,则从服务器群中剔除。3、

2.3、实际作用 

Keepalived+lvs主要用作RealServer的健康检查,以及负载均衡设备MASTER和BACKUP之间failover的实现。

3方案

本案例先使用两台linux做双机MASTER-SLAVE高可用,实现都写分离,用于提高查询性能),采用MYSQL5.6.x的半同步实现数据复制和同步,使用keepalived来监控MYSQL和提供读写VIP浮动。Keepalived在这里主要用作RealServer的健康状态检查以及LoadBalance主机和BackUP主机之间failover的实现

任何一台主机宕机都不会影响对外提供服务(读写vip可以浮动),保持服务的高可用。

3.1、环境

主机A:192.168.150.171

主机B:192.168.150.172

W-VIP:192.168.150.173  (负责写入)

R-VIP:192.168.150.174   (负责读取)

Client:任意,只要能访问以上三个IP即可

3.2、架构图

具体架构图如下:


3.3、设计原理(异常情况)

1、    服务器A和B,通过mysql的slave进程是用binlog同步数据。

2、    通过keepalived启用两个虚IP:W-VIP/R-VIP,一个负责写入,一个负责读取,实现读写分离。

3、    A和B都存在时,W-VIP下将请求转发至主机A,R-VIP将请求转发给A和B,实现负载均衡。

4、    当主机A异常时,B接管服务,W-VIP/R-VIP此时漂到了主机B上,此时这两个虚IP下都是主机B,实现高可用

5、    当主机B异常时,R-VIP会将B踢出,其他不变

具体实现后的效果

正常状态

Prot LocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  192.168.150.173:3306 wrr persistent 60

  -> 192.168.150.171:3306           Local   3      0          0         

TCP  192.168.150.174:3306 wrr persistent 60

  -> 192.168.150.172:3306           Route   3      0          0         

  -> 192.168.150.171:3306           Local   1      0          0         

A故障后,B的状态

Prot LocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  192.168.150.173:3306 wrr persistent 60

  -> 192.168.150.172:3306           Local   3      0          0         

TCP  192.168.150.174:3306 wrr persistent 60

  -> 192.168.150.172:3306           Local   3      0          0  

架构图

4、相关软件安装

1、    mysql  可以根据需要进行安装,此处省略

2、    lvs+keepalived的安装

关联lvs与keepalived的ipvs所需的内核信息

ln -s /usr/src/kernels/2.6.18-194.el5-x86_64/ /usr/src/linux

安装lvs

下载:wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz

tar -zxvf ipvsadm-1.24.tar.gz

cd tar -zxvf ipvsadm-1.24

make

make install

yum install ipv* 安装 

验证

ipvsadm –v

ipvsadm v1.24 2003/06/07 (compiled with getopt_long and IPVS v1.2.0)说明安装成功

安装keepalived

tar –zxvf keepalived-1.2.12.tar.gz

cd keepalived-1.2.12

./configure --prefix=/usr/local/keepalived/

make

make install

ln -s /usr/local/keepalived/etc/keepalived /etc/

ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

ln -s /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

ln -s /usr/local/keepalived/bin/genhash /bin/

ln -s /usr/local/keepalived/sbin/keepalived /sbin/

configure时注意Use IPVS Framework、IPVS sync daemon support 、Use VRRP Framework要返回yes,否则无法关联ipvs功能

4、配置mysql的主从

Master(210.171)的配置

vi /etc/my.cnf

添加如下内容:

server-id = 1    ##master ID

binlog-do-db = ppl  ##允许同步的库

binlog-ignore-db = mysql  ##忽略同步的库,也就是不能同步的库

##配置文件中还需开启log-bin,例如log-bin = mysql-bin

mysql –uroot –p

以下内容在mysql中执行

mysql> grant replication slave on *.* to ‘repdb01’@’%’ identified by '123456';

mysql>create database db01;

mysql>flush logs;

mysql>show master status;

mysql>use db01

mysql> create table test(name char);

返回一表格如下,记住File的内容,等下slave的配置中要用到


Slave的配置

vi /etc/my.cnf

添加如下内容:

server-id = 2  ##slave ID

master-host = 192.168.150.171  ##指定master的地址

master-user = repdb01    ##同步所用的账号

master-password = 123456   ##同步所用的密码

master-port = 3306     ##master上mysql的端口

replicate-do-db = db01   ##要同步的库名

replicate-ignore-db = mysql  ##忽略的库名

slave-skip-errors = 1062   ##当同步异常时,那些错误跳过,本例为1062错误

#log-slave-updates  ##同步的同时,也记录自己的binlog日志,如果还有台slave是通过这台机器进行同步,那需要增加此项,

#skip-slave-start  ##启动时不自动开启slave进程

#read-only  ##将库设为只读模式,只能从master同步,不能直接写入(避免自增键值冲突)

mysql –uroot –p

以下内容在mysql中执行

mysql>create database db01;

mysql>change master to master_log_file=’mysql-bin.000007’,master_log=106;

mysql>slave start;

mysql>show slave status \G

在返回值中查看,如果slave_IO_Runing与slave_SQL_Runing的值都为Yes说明同步成功


5、通过lvs+keepalived实现负载与热备,并实现读写分离

Master上的配置

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

   router_id MySQL-HA

}

vrrp_instance VI_1 {

    state BACKUP

    interface eth0

    virtual_router_id 90

    priority 100

    advert_int 1

    notify_master "/usr/local/mysql/bin/remove_slave.sh"

    nopreempt

    authentication {

        auth_type PASS

        auth_pass abcd1234

    }

    virtual_ipaddress {

        192.168.150.173 label eth0:1

        192.168.150.174 label eth0:2

    }

}

virtual_server 192.168.150.173 3306 {

    delay_loop 2

lb_algo wrr

lb_kind DR

    persistence_timeout 60

    protocol TCP

    real_server 192.168.150.171 3306 {

    weight 3

    notify_down /usr/local/mysql/bin/mysql.sh

    TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

        connect_port 3306

        }

    }

}

virtual_server 192.168.150.174 3306 {

    delay_loop 2

    lb_algo wrr

    lb_kind DR

    persistence_timeout 60

    protocol TCP

    real_server 192.168.150.171 3306 {

    weight 1

    notify_down /usr/local/mysql/bin/mysql.sh

    TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

        connect_port 3306

        }

    }

    real_server 192.168.150.172 3306 {

    weight 3

    TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

        connect_port 3306

        }

    }

}

vi /usr/local/mysql/bin/remove_slave.sh

#!/bin/bash

user=root

password=123456

log=/root/mysqllog/remove_slave.log

#--------------------------------------------------------------------------------------

echo "`date`" >> $log

/usr/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log

/bin/sed -i 's#read-only#\#read-only#' /etc/my.cnf

chomd 755 /usr/local/mysql/bin/remove_slave.sh

vi /usr/local/mysql/bin/mysql.sh

#!/bin/bash

/etc/init.d/keepalived stop

Slave上的配置

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

   router_id MySQL-HA

}

vrrp_instance VI_1 {

    state BACKUP

    interface eth0

    virtual_router_id 90

    priority 99

    advert_int 1

    notify_master "/usr/local/mysql/bin/remove_slave.sh"

    authentication {

        auth_type PASS

        auth_pass ppl.com

    }

    virtual_ipaddress {

        192.168.150.173 label eth0:1

        192.168.150.174 label eth0:2

    }

}

virtual_server 192.168.150.173 3306 {

    delay_loop 2

    lb_algo wrr

    lb_kind DR

    persistence_timeout 60

    protocol TCP

    real_server 192.168.150.172 3306 {

        weight 3

    notify_down /usr/local/mysql/bin/mysql.sh

    TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

        connect_port 3306

        }

    }

}

virtual_server 192.168.150.174 3306 {

    delay_loop 2

    lb_algo wrr

    lb_kind DR

    persistence_timeout 60

    protocol TCP

    real_server 192.168.150.172 3306 {

        weight 3

    notify_down /usr/local/mysql/bin/mysql.sh

    TCP_CHECK {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

        connect_port 3306

        }

    }

#    real_server 192.168.150.172 3306 {

#        weight 3

#        TCP_CHECK {

#            connect_timeout 10

#            nb_get_retry 3

#            delay_before_retry 3

#            connect_port 3306

#        }

#    }

}

vi /usr/local/mysql/bin/remove_slave.sh

#!/bin/bash

user=root

password=123456

log=/root/mysqllog/remove_slave.log

#--------------------------------------------------------------------------------------

echo "`date`" >> $log

/usr/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log

/bin/sed -i 's#read-only#\#read-only#' /etc/my.cnf

chomd 755 /usr/local/mysql/bin/remove_slave.sh

vi /usr/local/mysql/bin/mysql.sh

#!/bin/bash

/etc/init.d/keepalived stop

vi /usr/local/keepalived/bin/lvs-rs.sh

#!/bin/bash

WEB_VIP=192.168.150.174

. /etc/rc.d/init.d/functions

case "$1" in

start)

       ifconfig lo:0 $WEB_VIP netmask 255.255.255.255 broadcast $WEB_VIP

       /sbin/route add -host $WEB_VIP dev lo:0

       echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore

       echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce

       echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore

       echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce

       sysctl -p >/dev/null 2>&1

       echo "RealServer Start OK"

       ;;

stop)

       ifconfig lo:0 down

       route del $WEB_VIP >/dev/null 2>&1

       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore

       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce

       echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore

       echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce

       echo "RealServer Stoped"

       ;;

status)

        # Status of LVS-DR real server.

        islothere=`/sbin/ifconfig lo:0 | grep $WEB_VIP`

        isrothere=`netstat -rn | grep "lo:0" | grep $web_VIP`

        if [ ! "$islothere" -o ! "isrothere" ];then

            # Either the route or the lo:0 device

            # not found.

            echo "LVS-DR real server Stopped."

        else

            echo "LVS-DR Running."

        fi

;;

*)

        # Invalid entry.

        echo "$0: Usage: $0 {start|status|stop}"

        exit 1

;;

esac

exit 0

chmod 755 /usr/local/keepalived/bin/lvs-rs.sh

echo “/usr/local/keepalived/bin/lvs-rs.sh start” >>/etc/rc.local

vi /etc/my.cnf

将这两个参数前边的 # 去掉,重启mysql

#skip-slave-start  

#read-only

登陆mysql,手动将slave进程启动

mysql>slave start;

先启动master上的keepalived,正常后再启动slave上的。

启动后 主库可以查看ip a 

[root@rac3 ~]# ip a

1: lo: mtu 16436 qdisc noqueue 

    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host 

       valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast qlen 1000

    link/ether 00:50:56:95:06:1f brd ff:ff:ff:ff:ff:ff

    inet 192.168.150.171.171/24 brd 192.168.0.255 scope global eth0

    inet 192.168.150.173/32 scope global eth0:1

    inet 192.168.150.174/32 scope global eth0:2

inet6 fe80::250:56ff:fe95:61f/64 scope link 

       valid_lft forever preferred_lft forever

3: sit0: mtu 1480 qdisc noop 

link/sit 0.0.0.0 brd 0.0.0.0

slave上查看 

[root@rac1 keepalive]# ip a

1: lo: mtu 16436 qdisc noqueue 

    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet 127.0.0.1/8 scope host lo

    inet 192.168.150.174/32 brd 192.168.150.174 scope global lo:0

inet6 ::1/128 scope host 

       valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast qlen 1000

    link/ether 00:50:56:95:5e:b4 brd ff:ff:ff:ff:ff:ff

    inet 192.168.150.188/24 brd 192.168.0.255 scope global eth0

    inet 192.168.150.252/24 brd 192.168.0.255 scope global secondary eth0:1

    inet 192.168.150.186/24 brd 192.168.0.255 scope global secondary eth0:4

inet6 fe80::250:56ff:fe95:5eb4/64 scope link 

       valid_lft forever preferred_lft forever

3: eth1: mtu 1500 qdisc pfifo_fast qlen 1000

    link/ether 00:50:56:95:11:ba brd ff:ff:ff:ff:ff:ff

    inet 10.10.10.188/24 brd 10.10.10.255 scope global eth1

    inet 169.254.157.163/16 brd 169.254.255.255 scope global eth1:1

inet6 fe80::250:56ff:fe95:11ba/64 scope link 

       valid_lft forever preferred_lft forever

4: sit0: mtu 1480 qdisc noop 

link/sit 0.0.0.0 brd 0.0.0.0

发现210.174 读的vip 在主备机上都可以看到

210.173 写入vip在主上才能看到

后续多台实验进行中,敬请等待

推荐阅读更多精彩内容