前言
环境准备:
虚拟机系统:centos 6.7
MySQL主节点(master):192.168.120.110 hadoop01
MySQL从节点(backup):192.168.120.111 hadoop02
MySQL版本:5.6.24
关于MySQL的安装,可以参考Centos6.7 系统安装MySQL进行主从节点MySQL的安装,安装完成之后确保各个节点关闭防火墙和selinux!
一、配置 Mysql 双主同步
1.1 对主从节点hadoop01、hadoop02的MySQL开启bin-log二进制文件
[root@hadoop01 ~]# vim /etc/my.cnf
[root@hadoop02 ~]# vim /etc/my.cnf
在MySQL两个主从节点hadoop01、hadoop02上的/etc/my.cnf文件中的 [mysqld] 下方原有内容基础上添加以下内容:
[mysqld]
#主机标识,通常选择IP地址的最后一段,也可以任意选择,只需要保证不一样即可
server-id=110
#指定二进制文件存储位置,必须保证该文件所在父级目录权限足够
log_bin=/home/hadoop/logs/mysql/mysql-bin.log
#只保留7天的二进制日志,以防磁盘被日志占满
expire-logs-days=7
#不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-ignore-db=gog
注意:必须保证 log_bin 配置的目录对mysql的操作用户有足够的读写权限,否则重启会导致服务启动失败!
1.2 主从节点hadoop01/hadoop02重启MySQL服务,重新加载配置文件
[root@hadoop01 ~]# service mysqld restart
[root@hadoop02 ~]# service mysqld restart
1.3 查看MySQL主节点的 master 状态
[root@hadoop01 ~]# mysql -uroot -proot
mysql> show master status;
1.4 在MySQL从节点hadoop02上创建MySQL主节点的同步账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.120.110' IDENTIFIED BY 'slave';
mysql> FLUSH PRIVILEGES;
1.5 在MySQL从节点hadoop02改变master主为hadoop01节点的MySQL
mysql> change master to master_host='192.168.120.110',master_user='slave',master_password='slave',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=412;
mysql> start slave;
1.6 在MySQL从节点hadoop02查看双主同步状态,是否配置成功,或者也可以查看mysqld.log文件是否报错
mysql> show slave status\G
1.7 查看MySQL从节点的 master 状态
[root@hadoop01 ~]# mysql -uroot -proot
mysql> show master status;
1.8 在MySQL主节点hadoop01上创建MySQL从节点的同步账户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.120.111' IDENTIFIED BY 'slave';
mysql> FLUSH PRIVILEGES;
1.9 在MySQL主节点hadoop01改变master主为hadoop02节点的MySQL
mysql> change master to master_host='192.168.120.111',master_user='slave',master_password='slave',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=412;
mysql> start slave;
1.10 在MySQL主节点hadoop01查看双主同步状态,是否配置成功,或者也可以查看mysqld.log文件是否报错
mysql> show slave status\G
1.11 在MySQL主从任意节点插入数据进行测试
1.11.1 在MySQL主节点创建test测试数据库及测试表student
mysql> create database test;
mysql> use test;
mysql> create table student(sid int,name varchar(20));
mysql> insert into student values(1,'dengchao');
mysql> select * from student;
1.11.2 在MySQL从节点hadoop02上进行查看是否自动备份同步
mysql> show databases;
mysql> use test;
mysql> show tables;
mysql> select * from student;
我们可以看到数据已经成功同步过去,同样在backup节点插入到student表的数据,master节点一样可以同步过去,此刻双主就成功啦,下面我们就需要使用Keepalived实现MySQL热备切换!
二、配置keepalived实现MySQL热备切换
2.1 安装keepalived依赖gcc
[root@hadoop01 ~]# yum install -y gcc
2.2 安装keepalived
[root@hadoop01 ~]# yum install -y keepalived
2.3 使用perl -MCPAN -e shell命令安装Net::SMTP_auth模块
[root@hadoop01 ~]# yum -y install perl-CPAN
[root@hadoop01 ~]# perl -MCPAN -e shell
capn > install Net::SMTP_auth
注意:第一次使用 perl -MCPAN -e shell命令的时候,会提示输入yes,输入yes即可。安装完成之后,会出现 capn > 交互界面,输入install Net::SMTP_auth,安装完成后再输入 exit 退出交互界面。
2.4 修改hadoop01节点上的/etc/keepalived/keepalived.conf文件
! Configuration File for keepalived
global_defs {
router_id mysql-ha
}
vrrp_instance VI_1 {
state BACKUP #master和slave都设置为BACKUP
interface eth0 #网络接口,修改静态IP的时候那个配置文件,centos6.7的好像是eth0,看实际情况
virtual_router_id 10 #任意值,两台主机的值相同
priority 100 #master和slave设置不同值,启动时会将priority大的设置为master
advert_int 1
nopreempt #设置为不抢占模式,仅master配置。当master挂掉后,slave当选为master,恢复后不再切换
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.120.112
}
}
virtual_server 192.168.120.112 3306 {
delay_loop 1 #轮训real_server时间间隔,此处设为1秒
lb_algo rr
lb_kind NAT
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.120.110 3306 {
weight 1
notify_down /etc/keepalived/stopKeepAlived.sh #mysql挂掉时执行的脚本,需具备执行权限
TCP_CHECK {
connect_timeout 3
bingto 192.168.120.112
nb_get_retry 2
delay_before_retry 1
connect_port 3306
}
}
}
2.5 在hadoop02节点上执行上面 2.1-2.3 步骤
2.6 修改hadoop02节点上的/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 10
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.120.112
}
}
virtual_server 192.168.120.112 3306 {
delay_loop 1
lb_algo rr
lb_kind NAT
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.120.111 3306 {
weight 1
notify_down /etc/keepalived/stopKeepAlived.sh
TCP_CHECK {
connect_timeout 10
bingto 192.168.120.112
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
2.7 分别在hadoop01、hadoop02节点添加 /etc/keepalived/stopKeepAlived.sh 脚本文件
[root@hadoop01 ~]# touch /etc/keepalived/stopKeepAlived.sh
在/etc/keepalived/stopKeepAlived.sh文件中,添加以下内容:
#!/bin/bash
/usr/bin/pkill keepalived && /usr/bin/perl /etc/keepalived/sendmail.pl
注:当MySQL宕机的时候,执行该脚本会关闭keepalived进程,并且发送邮件,当重新启动MySQL数据库的时候,需要先重启MySQL,然后再启动keepalived进程。
2.8 分别在hadoop01、hadoop02节点创建MySQL宕机时邮件发送的配置文件
[root@hadoop01 ~]# touch /etc/keepalived/sendmail.pl
在/etc/keepalived/sendmail.pl文件中,添加以下内容:
#!/usr/bin/perl -w
use Net::SMTP_auth;
use strict;
use Sys::Hostname;
my $host=hostname; #获取当前主机名
my $mailhost = 'smtp.qq.com'; #SMTP服务器地址,此处为QQ邮箱
my $mailfrom = 'xxxx@qq.com'; #发送邮箱的用户
my @mailto = 'xxxx@qq.com'; #接受邮件的客户
my $subject = 'keepalived up on backup';
my $text = "正文\n $host 节点MySQL服务器宕机,请您知悉!"; # 指定宕机的MySQL的主机
my $user = 'xxxx@qq.com'; #发送邮箱的用户
my $passwd = 'xxxx'; #注意是要填写qq客户端授权的密码
&SendMail();
##############################
# Send notice mail
##############################
sub SendMail() {
my $smtp = Net::SMTP_auth->new( $mailhost, Timeout => 120, Debug => 1 )
or die "Error.\n";
$smtp->auth( 'LOGIN', $user, $passwd );
foreach my $mailto (@mailto) {
$smtp->mail($mailfrom);
$smtp->to($mailto);
$smtp->data();
$smtp->datasend("To: $mailto\n");
$smtp->datasend("From:$mailfrom\n");
$smtp->datasend("Subject: $subject\n");
$smtp->datasend("\n");
$smtp->datasend("$text\n\n");
$smtp->dataend();
}
$smtp->quit;
}
2.9 分别在hadoop01、hadoop02两个节点上,添加/etc/keepalived/目录下的所有文件的执行权限
[root@hadoop01 ~]# chmod 744 /etc/keepalived/*
[root@hadoop02 ~]# chmod 744 /etc/keepalived/*
2.10 设置QQ邮箱作为SMTP服务器
2.10 重启两个节点的MySQL服务并启动两台主机的keepalived服务
[root@hadoop01 ~]# service mysqld restart
[root@hadoop01 ~]# /etc/init.d/keepalived start
2.11 随意在hadoop01或者hadoop02或者其他集群节点的MySQL的客户端连接192.168.120.112
[root@hadoop02 ~]# mysql -uroot -proot -h 192.168.120.112
mysql> show variables like 'hostname%';
2.12 测试MySQL热备切换
2.12.1 关闭当前master节点hadoop01上的MySQL进程
[root@hadoop01 ~]# service mysqld stop
2.12.2 在上面连接192.168.120.112MySQL的客户端再次查看当前hostname
mysql> show variables like 'hostname%';
当我们停掉hadoop01节点上的mysqld服务后,我们再次查看就发现有一次查询报错,再次查询发现当前主从MySQL的主节点已成功自动切换成hadoop02,如果出现第一次没切换成功,重新启动两个节点的MySQL服务和keepalived服务再重试,因为我们配置的是互为主从,可以继续关闭hadoop02节点上的MySQL服务,再次查看是否能够自动切换!
2.12.3 关闭hadoop01节点上的 keepalived 服务
[root@hadoop01 ~]# /etc/init.d/keepalived stop
我们去设置的接收邮件账号,查看是否有发送过来关于MySQL宕机切换的邮件,成功收到邮件,如下图:
至此,我们MySQL的主从备份及热备切换大功告成啦!如果觉得本博客不错的,欢迎分享!