文章目录
前言
Mysql是一个市占率相当高的关系型数据库,做技术开发基本都会涉及到。那么,保证Mysql的高可用、数据安全性是一个相当重要的问题。这里介绍较为常用的一种基于keepalived的MySQl HA
1 MysqlHA概述
1.1 Mysql主从复制
MySQL的HA离不开其主从复制的技术。主从复制是指一台服务器充当主数据库服务器(master),另一台或多台服务器充当从数据库服务器(slave),从服务器(slave)自动向主服务器(master)同步数据。实现MySQL的HA,需使两台服务器互为主从关系。
1.2 Keepalived
Keepalived是基于VRRP(Virtual Router Redundancy Protocol,虚拟路由器冗余协议)协议的一款高可用软件。Keepailived有一台主服务器(master)和多台备份服务器(backup),在主服务器和备份服务器上面部署相同的服务配置,使用一个虚拟IP地址对外提供服务,当主服务器出现故障时,虚拟IP地址会自动漂移到备份服务器。
2 Mysql主从复制实现方法
2.1 一主一从
- 集群规划
hadoop102 | hadoop103 | hadoop104 |
---|---|---|
MySQL(master) | MySQL(slave) |
- 配置master
- 修改hadoop103中MySQL的/usr/my.cnf配置文件
[mysqld]
#开启binlog
log_bin = mysql-bin
#binlog日志类型
binlog_format = row
#MySQL服务器唯一id
server_id = 1
- 重启hadoop103的MySQL服务
[zhouchen@hadoop103 ~]$ sudo service mysql restart
- 进入mysql客户端,执行以下命令,查看master状态
mysql>show master status;
- 配置slave
- 修改hadoop104中MySQL的/usr/my.cnf配置文件
[mysqld]
#MySQL服务器唯一id
server_id = 2
#开启slave中继日志
relay_log=mysql-relay
- 重启hadoop104的MySQL服务
[zhouchen@hadoop104 ~]$ sudo service mysql restart
- 进入hadoop104的mysql客户端
执行以下命令
mysql>
CHANGE MASTER TO
MASTER_HOST='hadoop203',
MASTER_USER='root',
MASTER_PASSWORD='zhou59420',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
- 启动slave
mysql> start slave;
- 查看slave状态
mysql>show slave status \G;
2.2 双主
- 集群规划
hadoop102 | hadoop103 | hadoop104 |
---|---|---|
MySQL(master,slave) | MySQL(slave,master) |
- 配置master
- 修改hadoop104中MySQL的/usr/my.cnf配置文件
[mysqld]
#开启binlog
log_bin = mysql-bin
#binlog日志类型
binlog_format = row
#MySQL服务器唯一id
server_id = 2
#开启slave中继日志
relay_log=mysql-relay
- 重启hadoop104的MySQL服务
[zhouchen@hadoop104 ~]$ sudo service mysql restart
- 进入hadoop104的MySQL客户端,执行以下命令,查看master状态
mysql>show master status;
- 配置slave
- 修改hadoop103中MySQL的/usr/my.cnf配置文件
[mysqld]
#MySQL服务器唯一id
server_id = 1
#开启binlog
log_bin = mysql-bin
#binlog日志类型
binlog_format = row
#开启slave中继日志
relay_log=mysql-relay
- 重启hadoop103的MySQL服务
[zhouchen@hadoop103 ~]$ sudo service mysql restart
- 进入hadoop103的mysql客户端
执行以下命令
mysql>
CHANGE MASTER TO
MASTER_HOST='hadoop204',
MASTER_USER='root',
MASTER_PASSWORD='zhou59420',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
- 启动slave
mysql> start slave;
- 查看slave状态
mysql>show slave status \G;
3 Keepalived实现方法
须在hadoop103,hadoop104两台节点上部署Keepalived
3.1 部署hadoop103
- 通过yum方式安装Keepalived
sudo yum install -y keepalived
- 修改Keepalived配置文件/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MySQL-ha
}
vrrp_instance VI_1 {
state master #初始状态
interface eth0 #网卡
virtual_router_id 51 #虚拟路由id
priority 100 #优先级
advert_int 1 #Keepalived心跳间隔
nopreempt #只在高优先级配置,原master恢复之后不重新上位
authentication {
auth_type PASS #认证相关
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100 #虚拟ip
}
}
#声明虚拟服务器
virtual_server 192.168.1.100 3306 {
delay_loop 6
persistence_timeout 30
protocol TCP
#声明真实服务器
real_server 192.168.139.134 3306 {
notify_down /var/lib/mysql/killkeepalived.sh #真实服务故障后调用脚本
TCP_CHECK {
connect_timeout 3 #超时时间
nb_get_retry 1 #重试次数
delay_before_retry 1 #重试时间间隔
}
}
}
- 编辑脚本文件/var/lib/mysql/killkeepalived.sh
#! /bin/bash
sudo service keepalived stop
- 加执行权限
sudo chmod +x /var/lib/mysql/killkeepalived.sh
- 启动Keepalived服务
sudo service keepalived start
- 设置Keepalived服务开机自启
sudo chkconfig keepalived on
- 确保开机时MySQL先于Keepalived启动
- 查看MySQL启动次序
sudo vim /etc/init.d/mysql
- 查看Keepalived启动次序
sudo vim /etc/init.d/keepalived
- 若Keepalived先于MySQL启动,则需要按照以下步骤设置二者启动顺序
a. 修改/etc/init.d/mysql
sudo vim /etc/init.d/mysql
b.重新设置mysql开机自启
sudo chkconfig --del mysql
sudo chkconfig --add mysql
sudo chkconfig mysql on
c.修改/etc/init.d/keepalived
sudo vim /etc/init.d/keepalived
d.重新设置keepalived开机自启
sudo chkconfig --del keepalived
sudo chkconfig --add keepalived
sudo chkconfig keepalivedon
3.2 部署hadoop104
- 通过yum方式安装Keepalived
sudo yum install -y keepalived
- 修改Keepalived配置文件/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MySQL-ha
}
vrrp_instance VI_1 {
state master #初始状态
interface eth0 #网卡
virtual_router_id 51 #虚拟路由id
priority 100 #优先级
advert_int 1 #Keepalived心跳间隔
authentication {
auth_type PASS #认证相关
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100 #虚拟ip
}
}
#声明虚拟服务器
virtual_server 192.168.1.100 3306 {
delay_loop 6
persistence_timeout 30
protocol TCP
#声明真实服务器
real_server 192.168.1.104 3306 {
notify_down /var/lib/mysql/killkeepalived.sh #真实服务故障后调用脚本
TCP_CHECK {
connect_timeout 3 #超时时间
nb_get_retry 1 #重试次数
delay_before_retry 1 #重试时间间隔
}
}
}
- 编辑脚本文件/var/lib/mysql/killkeepalived.sh
#! /bin/bash
sudo service keepalived stop
- 加执行权限
sudo chmod +x /var/lib/mysql/killkeepalived.sh
- 启动Keepalived服务
sudo service keepalived start
- 设置Keepalived服务开机自启
sudo chkconfig keepalived on
- 确保开机时MySQL先于Keepalived启动
- 查看MySQL启动次序
sudo vim /etc/init.d/mysql
- 查看Keepalived启动次序
sudo vim /etc/init.d/keepalived
- 若Keepalived先于MySQL启动,则需要按照以下步骤设置二者启动顺序
a.修改/etc/init.d/mysql
sudo vim /etc/init.d/mysql
b.重新设置mysql开机自启
sudo chkconfig --del mysql
sudo chkconfig --add mysql
sudo chkconfig mysql on
c.修改/etc/init.d/keepalived
sudo vim /etc/init.d/keepalived
d.重新设置keepalived开机自启
sudo chkconfig --del keepalived
sudo chkconfig --add keepalived
sudo chkconfig keepalivedon