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

转载 2016年05月31日 20:10:14

试验环境:

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的值

1
2
3
4
5
6
7
[root@master ~]# mysql -uroot -ppasswd -e 'show master status'
Warning: Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      414 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1
2
3
4
5
6
7
[root@slave ~]# mysql -uroot -ppasswd -e 'show master status'
Warning: Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| 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数据库

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

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

1
2
3
4
5
6
7
8
9
[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相关目录

1
2
3
4
5
6
[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

1
2
3
4
5
[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

1
2
[root@master keepalived-1.2.19]# make
[root@master keepalived-1.2.19]# make install

查看keepalived相关的文件

1
2
3
4
[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/目录

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

设置keepalived启动级别

1
2
[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服务器继续提供服务

1
2
3
[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,并测试数据复制功能

1
2
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连接状态

1
2
3
4
5
6
7
mysql> show variables like 'hostname%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
hostname      | master |
+---------------+--------+
1 row in set (0.00 sec)

wKioL1a97YXgwxcBAAAhF3WzdpI609.jpg

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


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

1
2
[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服务

相关文章推荐

MySQL + keepalived (CentOS7)高可用方案

本文主要介绍了利用 keepalived 实现 MySQL 数据库的高可用。 由于要做高可用,那么自然就需要 MySQL 双主模式,又因为数据库的特殊性,我们需要特别注意主备间的切换(不是想切就切)...
  • cjfeii
  • cjfeii
  • 2015年09月21日 10:34
  • 5748

Keepalived+MySQL主主数据同步实现数据库高可用(HA)

环境操作系统版本:CentOS7 64位 MySQL版本:5.6.33 Keepalived版本:v1.2.23 节点1IP:192.168.1.205 主机名:edu-mysql-01 ...
  • xyang81
  • xyang81
  • 2016年09月25日 16:24
  • 1871

MySQL的主主互备+KEEPALIVED实现高可用(转)

标签:keepalived MySQL主主互备高可用 试验环境: 主:192.168.1.210(CentOS6.5) 从属:192.168.1.211(CentOS6.5) VIP:...

Mysql+Keepalived双主互备高可用详细配置

Mysql+Keepalived双主互备架构搭架 配置前进行校时操作 #安装ntpdate工具 yum install ntpdate -y #使用ntpdate校时(后面的是ntp服务器...

Keepalived+MySQL主主互备模式实现高可用

http://www.iyunv.com/thread-38848-1-1.html 实验架构图:   修改DB1配置文件增加以下几项: [mysqld] log-bin=m...

Mysql 5.6 双主互备高可用(Keepalived+mysql) (一)

安装环境:Redhat 5.8 mysql版本:mysql-5.6.25 DB机器A:192.168.8.193 主机名:mysql01 DB机器B:192.168.8.194 主机名:mysql0...

Mysql 5.6 双主互备高可用(Keepalived+mysql) (二)

安装环境:Redhat 5.8 mysql版本:mysql-5.6.25 DB机器A:192.168.8.193 主机名:mysql01 DB机器B:192.168.8.194 主机名:mysql0...

MySQL双主互备+keepalived 实现流程

  • 2016年05月30日 14:56
  • 363KB
  • 下载

MySQL双主互备 + keepalived实战演练

  • 2016年07月18日 12:43
  • 334KB
  • 下载

Mysql高可用--双主热备+keepalived

第1章 MySQL-HA介绍 关于MySQL-HA,目前有多种解决方案,比如heartbeat、drbd、mmm、共享存储,但是它们各有优缺点。heartbeat、drbd配置较为复杂,需要自己写脚本...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL主主互备结合keepalived实现高可用
举报原因:
原因补充:

(最多只允许输入30个字)