MySQL高可用基础之keepalived+双主复制.docx
- 文档编号:28240279
- 上传时间:2023-07-09
- 格式:DOCX
- 页数:9
- 大小:16.82KB
MySQL高可用基础之keepalived+双主复制.docx
《MySQL高可用基础之keepalived+双主复制.docx》由会员分享,可在线阅读,更多相关《MySQL高可用基础之keepalived+双主复制.docx(9页珍藏版)》请在冰豆网上搜索。
MySQL高可用基础之keepalived+双主复制
环境:
MySQL-VIP:
192.168.1.3
MySQL-master1:
192.168.1.1
MySQL-master2:
192.168.1.2
OS版本:
CentOSrelease6.4(Final)Linux2.6.32-358.el6.x86_64
MySQL版本:
5.6.14
Keepalived版本:
1.2.13
一、MySQLmaster-master配置
1、修改MySQL配置文件/etc/f
#Server1配置
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION
port=6603
server_id=1
lower_case_table_names=1
character_set_server=utf8
autocommit=off
innodb_flush_log_at_trx_commit=1
skip_name_resolve=ON
innodb_strict_mode=ON
log_bin=mysql-bin
innodb_data_home_dir=/usr/local/mysql/data
innodb_log_group_home_dir=/usr/local/mysql/data
auto_increment_increment=2
auto_increment_offset=1
#Server2配置
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION
port=6603
server_id=2
lower_case_table_names=1
character_set_server=utf8
autocommit=off
innodb_flush_log_at_trx_commit=1
skip_name_resolve=ON
innodb_strict_mode=ON
log_bin=mysql-bin
innodb_data_home_dir=/usr/local/mysql/data
innodb_log_group_home_dir=/usr/local/mysql/data
auto_increment_increment=2
auto_increment_offset=2
2、将192.168.1.1设为192.168.1.2的主服务器
#在Server1上执行
grantreplicationslaveon*.*to'repl'@'%'identifiedby'repl';
showmasterstatus;
#在Server2上执行
changemasterto
master_host='192.168.1.1',
master_port=6603,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000027',
master_log_pos=120;
startslave;
showslavestatus\G
3、将192.168.1.2设为192.168.1.1的主服务器
#在Server2上执行
grantreplicationslaveon*.*to'repl'@'%'identifiedby'repl';
showmasterstatus;
#在Server1上执行
changemasterto
master_host='192.168.1.2',
master_port=6603,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000010',
master_log_pos=351;
startslave;
showslavestatus\G
4、MySQL双Master同步测试
二、keepalived安装及配置
1、192.168.1.1服务器上keepalived安装及配置
安装keepalived
#tar-zxvfkeepalived-1.2.13.tar.gz
#cdkeepalived-1.2.13
#./configure--prefix=/usr/local/keepalived--with-kernel-dir=/usr/src/kernels/2.6.32-358.el6.x86_64
#make
#makeinstall
配置keepalived
新建一个配置文件,默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
#mkdir/etc/keepalived
#vi/etc/keepalived/keepalived.conf
#ConfigurationFileforkeepalived
global_defs{
router_idMySQL-ha
}
vrrp_instanceVI_1{
stateBACKUP #两台配置此处均是BACKUP
interfaceeth1
virtual_router_id51
priority100 #优先级,另一台改为90
advert_int1
nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置
authentication{
auth_typePASS
auth_pass1111
}
virtual_ipaddress{
192.168.1.3
}
}
virtual_server192.168.1.36603{
delay_loop2 #每个2秒检查一次real_server状态
lb_algowrr #LVS算法
lb_kindDR #LVS模式
persistence_timeout60 #会话保持时间
protocolTCP
real_server192.168.1.16603{
weight3
notify_down/usr/local/mysql/bin/failover.sh #检测到服务down后执行的脚本
TCP_CHECK{
connect_timeout10 #连接超时时间
nb_get_retry3 #重连次数
delay_before_retry3 #重连间隔时间
connect_port6603 #健康检查端口
}
}
编写检测服务down后所要执行的脚本
#vi/usr/local/mysql/bin/failover.sh
#!
/bin/sh
pkillkeepalived
#chmod+x/usr/local/mysql/bin/failover.sh
注:
此脚本是上面配置文件notify_down选项所用到的,keepalived使用notify_down选项来检查real_server的服务状态,当发现real_server服务故障时,便触发此脚本;我们可以看到,脚本就一个命令,通过pkillkeepalived强制杀死keepalived进程,从而实现了MySQL故障自动转移。
另外,我们不用担心两个MySQL会同时提供数据更新操作,因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP,而不是两台MySQL的IP+VIP
启动keepalived
#/usr/local/keepalived/sbin/keepalived-D
#psaux|grepkeepalived
测试
找一台局域网PC,然后去pingMySQL的VIP,这时候MySQL的VIP是可以ping的通的
停止MySQL服务,看keepalived健康检查程序是否会触发我们编写的脚本
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--addkeepalived
#chkconfig--level345keepalivedon
2、192.168.1.2上keepalived安装及配置
安装keepalived
#tar-zxvfkeepalived-1.2.13.tar.gz
#cdkeepalived-1.2.13
#./configure--prefix=/usr/local/keepalived--with-kernel-dir=/usr/src/kernels/2.6.32-358.el6.x86_64
#make
#makeinstall
配置keepalived
这台配置和Server1上基本一样,但有三个地方不同:
优先级为90、无抢占设置、real_server为本机IP
#mkdir/etc/keepalived
#vi/etc/keepalived/keepalived.conf
#ConfigurationFileforkeepalived
global_defs{
router_idMySQL-ha
}
vrrp_instanceVI_1{
stateBACKUP #两台配置此处均是BACKUP
interfaceeth1
virtual_router_id51
priority100 #优先级,另一台改为90
advert_int1
nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置
authentication{
auth_typePASS
auth_pass1111
}
virtual_ipaddress{
192.168.1.3
}
}
virtual_server192.168.1.36603{
delay_loop2 #每个2秒检查一次real_server状态
lb_algowrr #LVS算法
lb_kindDR #LVS模式
persistence_timeout60 #会话保持时间
protocolTCP
real_server192.168.1.16603{
weight3
notify_down/usr/local/mysql/bin/failover.sh #检测到服务down后执行的脚本
TCP_CHECK{
connect_timeout10 #连接超时时间
nb_get_retry3 #重连次数
delay_before_retry3 #重连间隔时间
connect_port6603 #健康检查端口
}
}
编写检测服务down后所要执行的脚本
#vi/usr/local/mysql/bin/failover.sh
#!
/bin/sh
pkillkeepalived
#chmod+x/usr/local/mysql/bin/failover.sh
启动keepalived
#/usr/local/keepalived/sbin/keepalived-D
#psaux|grepkeepalived
测试
停止MySQL服务,看keepalived健康检查程序是否会触发我们编写的脚本
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--addkeepalived
#chkconfig--level345keepalivedon
三、测试
1、MySQL远程登录测试
使用客户端登录VIP测试
2、keepalived故障转移测试
客户端一直去pingVIP,然后关闭192.168.1.1上的keepalived,正常情况下VIP就会切换到192.168.1.2上面去
开启192.168.1.1上的keepalived,关闭192.168.1.2上的keepalived,看是否能自动切换,正常情况下VIP又会属于192.168.1.1
注:
keepalived切换速度还是非常块的,整个切换过程只需1-3秒
3、MySQL故障转移测试
在192.168.1.1上关闭MySQL服务,看VIP是否会切换到192.168.1.2上
开启192.168.1.1上的MySQL和keepalived,然后关闭192.168.1.2上的MySQL,看VIP是否会切换到192.168.1.1上
客户端连接的MySQL的VIP,在切换时执行了一个MySQL查询命令
这个方案可以在一定程度上解决MySQL高可用的问题,即应用访问VIP,当一个MySQLServer出现问题,会自动切换到另一个,切换过程很快,对应用透明。
但这种简单配置只能有一台服务器工作,另一个备用,这样无法扩展读写,也没法做负载均衡。
目前MySQL负载均衡方案一般是一个HA(keepalived、MHA等)+一个负载均衡器(LVS、haproxy等)。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 可用 基础 keepalived 复制