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 {
"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在主上才能看到
后续多台实验进行中，敬请等待
