MYSQL(主主)双机热备+Keepalived实现故障切换
MYSQL(主主)双机热备+Keepalived实现故障切换
环境介绍:
Mysql VIP :192.168.160.13
Master1:192.168.160.11
Master2:192.168.160.12
Linux 环境:Centos 6.9
Mysql版本:Mysql5.6
1. 双主配置
第一步,配置my.cnf
Master1 : 192.168.160.11
[root@redis1 ~]# vi /etc/my.cnf
修改或添加以下配置:
#主标服务标识号,必需唯一
server-id = 1
# log_bin日志名称格式:${datadir}/mysql-bin.0000*
log_bin = mysql-bin
# 日志内容记录格式(建议使用这种)
binlog_format = mixed
# 不建议适用这种配置,如果跨域操作会出现同步异常。
# binlog_do_db = task
# binlog_ignore_db = mysql
#只在从库设置replicate_wild_do_table= hadoop.%或replicate_wild_ignore_table可以避免跨库更新问题
#如果有多个库需要同步的时候,需要多配置几次以下语句,而不是直接使用逗号分隔。(错误写法:replicate-wild-do-table=task.%,test.%)
replicate-wild-do-table=task.%
log-slave-updates
#经过1日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。n=1是最安全的做法,但效率最低。默认设置是n=0。
sync_binlog = 1
# auto_increment,控制自增列AUTO_INCREMENT的行为
# 用于MASTER-MASTER之间的复制,防止出现重复值,
# auto_increment_offset=1设置步长,这里设置为1,这样Master的auto_increment字段产生的数值是:1, 3, 5, 7, …等奇数ID
auto_increment_offset = 1
# auto_increment_increment=n有多少台服务器,n就设置为多少,
auto_increment_increment = 2
#忽略所有复制产生的错误
slave-skip-errors = all
Master2 : 192.168.160.12
[root@redis1clone ~]# vi /etc/my.cnf
修改或添加以下配置:
#主标服务标识号,必需唯一(区别于master1)
server-id = 2
# auto_increment_offset=1设置步长,这里设置为2,这样Master的auto_increment字段产生的数值是:2,4,6,8 …等偶数ID
auto_increment_offset = 2
####省略其他几项和master1相同####
分别重启mysql
[root@redis1 ~]# service mysqld restart
第二步,设置同步(change master)
.
.
1. 分别登录mysql配置远程同步权限账户:
登录master1设置192.168.160.12可远程同步权限账户:
mysql> grant replication slave on *.* to 'replicate'@'192.168.160.12' identified by '123456';
mysql> flush privileges;
登录master2设置192.168.160.11可远程同步权限账户:
mysql> grant replication slave on *.* to 'replicate'@'192.168.160.11' identified by '123456';
mysql> flush privileges;
.
.
.
2. 设置master2同步更新master1
登录master1的mysql,执行如下指令:
——查看master1最新二进制日志文件及操作事物位置
mysql> show master status;
确保当前数据库无其他操作,如果是在生产环境下,记得先锁表再查看
先锁表:
mysql> flush tables with read locks;
再查看:
mysql> show master status;
完成同步后记得解锁(完成change master指令后)
解锁:
mysql> unlock tables;
登录master2的mysql,执行如下指令
# 1.停止slave服务进程
# 2.执行同步指令change master to
# 3.启动slave服务进程
# 4.查看slave服务状态
mysql>stop slave;
mysql>change master to
>master_host='192.168.160.11',
>master_user='replicate',
>master_password='123456',
>master_log_file='mysql-bin.000007',
>master_log_pos=386;
mysql> start slave;
mysql> show slave status\G
如图两项为YES则说明设置同步更新成功
.
.
.
3. 设置master1同步更新master2(重复步骤2)
—登录master2的mysql,执行查看指令:查看master2最新二进制日志文件及操作事物位置
—登录master1的mysql,执行同步指令change master to
.
.
.
测试:略.
.
2. keepalived安装配置
master1:
-
安装keepalived依赖包
[root@redis1clone ~]# yum install -y pcre-devel openssl-devel popt-devel
-
下载源码及安装编译
[root@redis1clone ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz [root@redis1clone ~]# tar zxvf keepalived-1.2.7.tar.gz [root@redis1clone ~]# cd keepalived-1.2.7 [root@redis1 keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived [root@redis1 keepalived-1.2.7]# make && make install
-
Keepalived配置
[root@redis1clone ~]# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs { notification_email { 512902802@qq.com } notification_email_from admin@test.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MYSQL_HA #标识,双主相同 } vrrp_instance VI_1 { state BACKUP #两台都设置BACKUP interface eth0 #绑定虚拟IP的网络接口 virtual_router_id 51 #VRRP组名,两个节点的设置必须一样,以指明各个节点属于同一VRRP组 priority 100 #主节点的优先级(1-254之间),备用节点必须比主节点优先级低 advert_int 1 #组播信息发送间隔,两个节点设置必须一样 nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置 authentication { #设置验证信息,两个节点必须一致 auth_type PASS auth_pass 1111 } virtual_ipaddress { #指定虚拟IP,两个节点设置必须一样 192.168.160.13 } } virtual_server 192.168.160.13 3306 { #linux虚拟服务器(LVS)配置 delay_loop 2 #每个2秒检查一次real_server状态 lb_algo Wrr #LVS调度算法,rr|wrr|lc|wlc|lblc|sh|dh lb_kind DR #LVS集群模式 ,NAT|DR|TUN 如果不关闭,备用服务器不能通过VIP连接主MySQL persistence_timeout 50 #会话保持时间,同一IP的连接50秒内被分配到同一台真实服务器 protocol TCP #使用的协议是TCP还是UDP real_server 192.168.160.11 3306 { #检测本地mysql,backup也要写检测本地mysql weight 3 #权重 notify_down /usr/local/keepalived/mysql.sh #当mysql服down时,执行此脚本,杀死keepalived实现切换 TCP_CHECK { connect_timeout 3 #连接超时 nb_get_retry 3 #重试次数 delay_before_retry 3 #重试间隔时间 } } } ~
[root@redis1clone ~]# vi /usr/local/keepalived/mysql.sh
#!/bin/bash pkill keepalived
[root@redis1clone ~]# chmod +x /usr/local/keepalived/mysql.sh
-
将Keepalived加入系统服务
[root@redis1clone ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/ [root@redis1clone ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ [root@redis1clone ~]# mkdir /etc/keepalived/ [root@redis1clone ~]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ [root@redis1clone ~]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ [root@redis1clone ~]# /etc/init.d/keepalived start
->master2服务器只修改priority为90、nopreempt不设置、real_server设置本地IP。
->两个mysql都必须要有可远程访问的账户,例如:``` mysql> grant all on *.* to'keepalived'@'192.168.160.%' identified by 'ayou123'; mysql> flush privileges; ```
-
Keepalived测试
使用Mysql VIP :192.168.160.13连接
连接成功后在13上执行插入语句:
参考文章:
1.MySQL高可用性之Keepalived+Mysql(双主热备)
2.高可用的Mysql双机热备(Mysql_HA)
推荐阅读
-
Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)
-
关于使用Keepalived实现Nginx的自动重启及双主热备高可用问题
-
MySQL高可用性之Keepalived+MySQL(双主热备)
-
MySQL高可用性之Keepalived+MySQL(双主热备)
-
MySQL数据库双机热备------主-主备份配置
-
keepalived+nginx实现搭建双机主备+双主热备
-
mysql lvs keepalived 双主热备
-
mysql lvs keepalived 双主热备
-
Mysql+Keepalived双主热备高可用操作记录
-
关于MySQL 的高可用性:Keepalived 双主热备