首页手记 MySQL主主互备结合keepalived实现高可用

MySQL主主互备结合keepalived实现高可用

标签：

MySQL

试验环境：

master：192.168.1.210（CentOS6.5）

slave：192.168.1.211（CentOS6.5）

VIP：192.168.1.208

MySQL主主互备模式配置

step1：Master服务的/etc/my.cnf配置

[mysqld]

basedir = /usr/local/mysql

datadir = /var/lib/mysql

port = 3306

socket = /var/lib/mysql/mysql.sock

server_id = 1

log-bin = mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.% #指定不需要复制的库，mysql.%表示mysql库下的所有对象

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

step2：Slave服务的/etc/my.cnf配置

[mysqld]

basedir = /usr/local/mysql

datadir = /var/lib/mysql

port = 3306

socket = /var/lib/mysql/mysql.sock

server_id = 2

log-bin = mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

step3：重启两台主从mysql服务

[root@master ~]# service mysqld restart

Shutting down MySQL.. [ OK ]

Starting MySQL. [ OK ]

[root@slave ~]# service mysqld restart

Shutting down MySQL.. [ OK ]

Starting MySQL. [ OK ]

step4：查看主从的log-bin日志状态

记录File和Position的值

[root@master ~]# mysql -uroot -ppasswd -e 'show master status'

Warning: Using a password on the command line interface can be insecure.

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 414 | | | |

+------------------+----------+--------------+------------------+-------------------+

[root@slave ~]# mysql -uroot -ppasswd -e 'show master status'

Warning: Using a password on the command line interface can be insecure.

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 414 | | | |

+------------------+----------+--------------+------------------+-------------------+

step5：创建主从同步replication用户

1、master

mysql> grant replication slave on *.* to 'replication'@'192.168.1.211' identified by 'replication';

mysql> flush privileges;

mysql> change master to

-> master_host='192.168.1.211',

-> master_user='replication',

-> master_password='replication',

-> master_port=3306,

-> master_log_file='mysql-bin.000001',

-> master_log_pos=414;

mysql> start slave;

2、slave

mysql> grant replication slave on *.* to 'replication'@'192.168.1.210' identified by 'replication';

mysql> flush privileges;

mysql> change master to

-> master_host='192.168.1.210',

-> master_user='replication',

-> master_password='replication',

-> master_port=3306,

-> master_log_file='mysql-bin.000001',

-> master_log_pos=414;

mysql> start slave;

同步失败可能需要停止或重设slave

mysql> stop slave;

mysql> reset slave;

step6：分别在master和slave上查看slave状态，验证是否成功配置主主复制模式

1、master

wKiom1a9p3TCJIhtAACcT4EvyoI330.jpg

2、slave

wKioL1a9qAGhFg9WAACSAFHbWEE538.jpg

slave状态同步过程可能需要重启MySQL服务

[root@master ~]# service mysqld restart

[root@slave ~]# service mysqld restart

step7：验证，在master上创建test1数据库，slave上查看是否同步

1、master上创建test1数据库

[root@master ~]# mysql -uroot -ppasswd -e 'create database test1'

2、slave上查看是否同步创建test1

[root@slave ~]# mysql -uroot -ppasswd -e 'show databases'

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test1 |

+--------------------+

安装和配置keepalived实现MySQL双主高可用

step1：安装keepalived

方法一：使用yum安装keepalived，需要安装epel-release源

[root@master ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

[root@slave ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

[root@slave ~]# yum -y install keepalived

wKiom1a9r5ziXXxnAADRYEWW7dI218.jpg

查看keepalived相关目录

[root@slave ~]# ls /usr/sbin/keepalived

/usr/sbin/keepalived

[root@slave ~]# ls /etc/init.d/keepalived

/etc/init.d/keepalived

[root@slave ~]# ls /etc/keepalived/keepalived.conf

/etc/keepalived/keepalived.conf

方法二：从keepalived官方网站http://www.keepalived.org下载源代码包编译安装

1、下载keepalived最新版

[root@master ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

[root@slave ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

2、安装keepalived依赖软件包

[root@master ~]# yum install pcre-devel openssl-devel popt-devel libnl-devel

3、解压并安装keepalived

[root@master ~]# tar zxf keepalived-1.2.19.tar.gz

[root@master ~]# cd keepalived-1.2.19

[root@master keepalived-1.2.19]# ./configure --prefix=/usr/local/keepalived

--sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64

wKiom1a9ypfA0sbqAABMQd1mThE780.jpg

[root@master keepalived-1.2.19]# make

[root@master keepalived-1.2.19]# make install

查看keepalived相关的文件

[root@master keepalived-1.2.19]# ls /etc/keepalived/

keepalived.conf samples

[root@master keepalived-1.2.19]# ls /etc/init.d/keepalived

/etc/init.d/keepalived

链接/usr/local/keepalived/sbin/keepalived到/sbin/目录

[root@master keepalived-1.2.19]# ln -s /usr/local/keepalived/sbin/keepalived /sbin/

设置keepalived启动级别

[root@master keepalived-1.2.19]# chkconfig --add keepalived

[root@master keepalived-1.2.19]# chkconfig --level 35 keepalived on

step2：配置keepalived

1、Master的keepalived.conf配置文件

! Configuration File for keepalived

global_defs {

notification_email {

root@huangmingming.cn

741616710@qq.com

}

notification_email_from keepalived@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_instance HA_1 {

state BACKUP #master和slave都配置为BACKUP

interface eth0 #指定HA检测的网络接口

virtual_router_id 80 #虚拟路由标识，主备相同

priority 100 #定义优先级，slave设置90

advert_int 1 #设定master和slave之间同步检查的时间间隔

nopreempt #不抢占模式。只在优先级高的机器上设置即可

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress { #设置虚拟IP，可以设置多个，每行一个

192.168.1.208/24 dev eth0 #MySQL对外服务的IP，即VIP

}

virtual_server 192.168.1.208 3306 {

delay_loop 2 #每隔2秒查询real server状态

lb_algo wrr #lvs 算法

lb_kinf DR #LVS模式（Direct Route）

persistence_timeout 50

protocol TCP

real_server 192.168.1.210 3306 { #监听本机的IP

weight 1

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

TCP_CHECK {

connect_timeout 10 #10秒无响应超时

bingto 192.168.1.208

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

keepalived检测脚本，当其中一台MySQL服务出现故障down掉时，实现自动切换到正常的MySQL服务器继续提供服务

[root@master ~]# vim /usr/local/keepalived/bin/mysql.sh

#!/bin/bash

pkill keepalived

2、Slave的keepalived.conf配置文件

! Configuration File for keepalived

global_defs {

notification_email {

root@huangmingming.cn

741616710@qq.com

}

notification_email_from keepalived@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_instance HA_1 {

state BACKUP #master和slave都配置为BACKUP

interface eth0 #指定HA检测的网络接口

virtual_router_id 80 #虚拟路由标识，主备相同

priority 90 #定义优先级，slave设置90

advert_int 1 #设定master和slave之间同步检查的时间间隔

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress { #设置虚拟IP，可以设置多个，每行一个

192.168.1.208/24 dev eth0 #MySQL对外服务的IP，即VIP

}

virtual_server 192.168.1.208 3306 {

delay_loop 2

lb_algo wrr

lb_kinf DR

persistence_timeout 50

protocol TCP

real_server 192.168.1.211 3306 { #监听本机的IP

weight 1

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

TCP_CHECK {

connect_timeout 10

bingto 192.168.1.208

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

step3：授权VIP的root用户权限

授权远程主机可以通过VIP登录MySQL，并测试数据复制功能

mysql> grant all on *.* to root@'192.168.1.208' identified by '741616710';

mysql> flush privileges;

step4：测试keepalived高可用功能

1、远程主机登录通过VIP192.168.1.208登录MySQL，查看MySQL连接状态

mysql> show variables like 'hostname%';

+---------------+--------+

| Variable_name | Value |

+---------------+--------+

| hostname | master |

+---------------+--------+

1 row in set (0.00 sec)

wKioL1a97YXgwxcBAAAhF3WzdpI609.jpg

从上面查看的结果看样看出在正常情况下连接的是master

2、故障测试，停止master的MySQL服务，再次查看是否转移至slave服务器上

[root@master ~]# service mysqld stop

Shutting down MySQL.... SUCCESS!

mysql> show variables like 'hostname%';

ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> show variables like 'hostname%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 1268

Current database: *** NONE ***

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| hostname | slave |

+---------------+-------+

1 row in set (0.01 sec)

wKiom1a97ezz4qMQAABPHTYltu0845.jpg

由测试结果可以看出，keepalived成功转移MySQL服务

keepalived MySQL主主互备

点击查看更多内容

为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

评论

共同学习，写下你的评论

评论加载中...

展开查看更多评论

作者其他优质文章

正在加载中

largeQ

手记
篇

粉丝

92

获赞与收藏

585

关注作者，订阅最新文章

相关文章推荐

MySQL主主互备模式(Keepalived)

MySQL主主模式+Keepalived高可用

Keepalived+Nginx双主热备实现Nginx高可用

Keepalived+Nginx双机主备实现Nginx高可用

mysql高可用方案之Keepalived+主主复制

阅读免费教程

MySQL 进阶教程

32个小节 51177 958

MySQL 入门教程

32个小节 128952 1611

后端通用面试教程

41个小节 30273 342

推荐

评论

收藏

共同学习，写下你的评论



感谢您的支持，我会继续努力的～

扫码打赏，你说多少就多少

赞赏金额会直接到老师账户

支付方式

打开微信扫一扫，即可进行扫码打赏哦

今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与放弃机会

点击
抽奖

慕课手记新用户专享福利

恭喜你，你的运气太好了，居然抽中了 100个积分！

恭喜你，抽中了价值元的专栏！

太棒了，直接落到你账户里！

积分商城里的罗技鼠标、机械键盘、
Kindle 阅读器、小米平衡车
Apple iPad （10.2英寸）、大额优惠券
在等着你去兑换了噢

作者：

免费赠送

兑换码：1111222211 复制

优惠券可用于购买实战课、体系课
无门槛使用

先去看看，有什么好东西马上兑换我爱学习，选课去


热搜

最近搜索清空

MySQL主主互备结合keepalived实现高可用

相关文章推荐

阅读免费教程