一：背景
1.1. 概要
MySQL MHA+keepalived(脚本切换)高可用方案,发生故障时自动切换,保证数据库
安全可靠运行
具体概念图如下所示
1.2. 环境
系统：CentOS Linux release 7.6.1810 (Core)
数据库：mysql-5.6.33
VIP: keepalived-1.1.20
:或者使用脚本
Mha: mha4mysql-manager-0.55
二：介绍
2.1 目标
首先在服务器上创建好一主两从的环境。
然后配置mha+keepalived第三方软件
2.2 系统简介
此次主要包括[3]个数据库。具体情况如:
数据库所在服务器
数据库角色
需要安装软件
192.168.17.198
主
Mha node节点
192.168.17.194
从1
mha管理节点&node节点
192.168.17.199
从2
Mha node节点
192.168.17.217
客户端
只需要安装一个mysql客户端连接工具
三：MHA+KEEPALIVED环境准备
3.1搭建主从环境
关闭防火墙和selinux,4个宿主机都需要操作
配置主从
MASTER操作:
(1) 授权主从复制账号,你有几个数据库就需要创建几个主从复制账号,我的环境是3个数据库,就需要创建3个主从复制账号.
grant replication slave on *.* to 'repl'@'192.168.17.198' identified by '123456';
Query OK,0 rows affected (0.00sec) grant replication slave on *.* to 'repl'@'192.168.17.194' identified by '123456';
Query OK,0 rows affected (0.00sec) grant replication slave on *.* to 'repl'@'192.168.17.199' identified by '123456';
Query OK,0 rows affected (0.00sec)flush privileges;
Query OK,0 rows affected (0.00 sec)
(2) 创建mha的管理账号
grant all privileges on *.* to 'manager'@'192.168.17.%' identified by '123456';
Query OK,0 rows affected (0.01sec)flush privileges;
Query OK,0 rows affected (0.00 sec)
(3) 在master上进行逻辑全备,并将文件拷贝到其他两个从库上面
[root@localhost ~]# /data/mysql5633/mysql/bin/mysqldump -uroot -p --all-databases --master-data=2 >./all1227.sql
[root@localhost~]# scp all1227.sql root@192.168.17.194:/[root@localhost~]# scp all1227.sql root@192.168.17.199:/
slave1操作,并设置只读模式
(1) 将主库的备份导入数据库中
source /all1227.sql;change master to master_user='repl',master_password='123456',master_port=3306,master_host='192.168.17.198',master_log_file='mysql-bin.000003',master_log_pos=2816;
Query OK,0 rows affected, 2 warnings (0.02sec)start slave;
Query OK,0 rows affected, 1 warning (0.02sec) set global read_only=1;
flush privileges;
Slave2操作,并设置只读模式
(1) 将主库的备份导入数据库中
source /all1227.sql; change master to master_user='repl',master_password='123456',master_port=3306,master_host='192.168.17.198',master_log_file='mysql-bin.000003',master_log_pos=2816;
Query OK,0 rows affected, 2 warnings (0.12sec)start slave;
Query OK,0 rows affected, 1 warning (0.02sec) set global read_only=1;flush privileges;
然后分别在两个从库上面验证主从是否搭建好
show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host:192.168.17.198Master_User: repl
Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos:2816Relay_Log_File: relay-log.000002Relay_Log_Pos:283Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.2搭建MHA环境
在各个主机上面安装perl5.tgz的包,因为这个包里面有mha和perl了,直接解压就可以了
(1)master,slave1,slave2都要安装,安装都一样
[root@localhost/]# tar -zxvf perl5.tgz -C /usr/local/(2)在每个主机上分别添加环境变量/etc/profileexport PERLBREW_ROOT=/usr/local/perl5
export PATH=/usr/local/perl5/perls/perl-5.18.2/bin:$PATH
[root@localhost/]# [root@localhost /]# source /etc/profile(3)验证安装是否正确
[root@localhost/]# which perl
/usr/local/perl5/perls/perl-5.18.2/bin/perl(4)在slave1上面编辑mha的配置文件,这个文件放在哪里都可以,我的是在/etc/下面创建一个masterha的目录,在里面添加一个app1.cnf的配置文件
[root@localhost etc]#mkdir -p masterha
[root@localhost etc]# cd masterha/[root@localhost masterha]# vim app1.cnf
[server default]
manager_workdir=/data/manager/mha
manager_log=/data/manager/mha/manager.log
user=manager
password=123456ssh_user=root
repl_user=repl
repl_password=123456ping_interval=1shutdown_script=""#master_ip_failover_script="/data/manager/mha/scripts/master_ip_failover"#master_ip_online_change_script="/data/manager/mha/scripts/master_ip_online_change"#master_ip_online_change_script=""[server1]hostname=192.168.17.198port=3306master_binlog_dir="/data/mysql5633/mysql/binlog"candidate_master=1[server2]hostname=192.168.17.194port=3306master_binlog_dir="/data/mysql5633/mysql/binlog"candidate_master=1[server3]hostname=192.168.17.199port=3306master_binlog_dir="/data/mysql5633/mysql/binlog"candidate_master=1(5)创建文件中的目录
在slave1上面创建工作目录和脚本目录
[root@localhost masterha]#mkdir -p /data/manager/mha/scripts
如果使用脚本实现漂移就需要将脚本放在这个脚本目录下面
(6)在各个主机之间配置免秘钥登录,本机与本机也需要免秘钥登录
[root@localhost/]# ssh-keygen -t rsa
[root@localhost/]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.17.198[root@localhost/]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.17.194[root@localhost/]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.17.199(7)然后在slave01上验证联通性
[root@localhost masterha]# cd/usr/local/perl5/perls/perl-5.18.2/bin/[root@localhost bin]# masterha_check_ssh--conf=/etc/masterha/app1.cnf
Thu Dec27 15:35:32 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Dec27 15:35:32 2018 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Thu Dec27 15:35:32 2018 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Thu Dec27 15:35:32 2018 - [info] Starting SSH connection tests..
Thu Dec27 15:35:35 2018 -[debug]
Thu Dec27 15:35:32 2018 - [debug] Connecting via SSH from root@192.168.17.198(192.168.17.198:22) to root@192.168.17.194(192.168.17.194:22)..
Thu Dec27 15:35:34 2018 -[debug] ok.
Thu Dec27 15:35:34 2018 - [debug] Connecting via SSH from root@192.168.17.198(192.168.17.198:22) to root@192.168.17.199(192.168.17.199:22)..
Thu Dec27 15:35:35 2018 -[debug] ok.
Thu Dec27 15:35:44 2018 -[debug]
Thu Dec27 15:35:32 2018 - [debug] Connecting via SSH from root@192.168.17.194(192.168.17.194:22) to root@192.168.17.198(192.168.17.198:22)..
Thu Dec27 15:35:38 2018 -[debug] ok.
Thu Dec27 15:35:38 2018 - [debug] Connecting via SSH from root@192.168.17.194(192.168.17.194:22) to root@192.168.17.199(192.168.17.199:22)..
Thu Dec27 15:35:44 2018 -[debug] ok.
Thu Dec27 15:35:45 2018 -[debug]
Thu Dec27 15:35:33 2018 - [debug] Connecting via SSH from root@192.168.17.199(192.168.17.199:22) to root@192.168.17.198(192.168.17.198:22)..
Thu Dec27 15:35:39 2018 -[debug] ok.
Thu Dec27 15:35:39 2018 - [debug] Connecting via SSH from root@192.168.17.199(192.168.17.199:22) to root@192.168.17.194(192.168.17.194:22)..
Thu Dec27 15:35:45 2018 -[debug] ok.
Thu Dec27 15:35:45 2018 - [info] All SSH connection tests passed successfully.
(8)检测主从复制,检测之前要做软连接,在每个主机上面都要做
[root@localhost/]# ln -s /data/mysql5633/mysql/bin/mysql /usr/bin/[root@localhost/]# ln -s /data/mysql5633/mysql/bin/mysqlbinlog /usr/bin/[root@localhost bin]# masterha_check_repl--conf=/etc/masterha/app1.cnf
MySQL Replication Health is OK.
(9)在slave01启动mha
[root@localhost mha]# cd/usr/local/perl5/perls/perl-5.18.2/bin/[root@localhost bin]# nohup masterha_manager--conf=/etc/masterha/app1.cnf &>/dev/null &[1] 8200(10)监测mha的状态
[root@localhost bin]# masterha_check_status--conf=/etc/masterha/app1.cnf
app1 (pid:8200) is running(0:PING_OK), master:192.168.17.198
3.3搭建keepalived环境
编译安装keepalived,master和slave1都需要安装,安装步骤都是一样的
(1)安装keepalived
[root@localhost/]# tar -zxvf keepalived-1.1.20.tar.gz
[root@localhost/]# cd keepalived-1.1.20/[root@localhost keepalived-1.1.20]# ./configure
configure: error:!!! OpenSSL is not properly installed on your system. !!!
!!! Can not include OpenSSL headers files. !!!报错,那么久需要安装一下包.
[root@localhost keepalived-1.1.20]# yum -y install openssl openssl-devel
[root@localhost keepalived-1.1.20]# yum install popt-devel
安装完了,再编译一遍
[root@localhost keepalived-1.1.20]# ./configure
[root@localhost keepalived-1.1.20]# make && make install安装成功!(2)复制和创建配置文件
[root@localhost keepalived-1.1.20]# cd /usr/local/etc/rc.d/init.d/[root@localhost init.d]#cp keepalived /etc/rc.d/init.d/[root@localhost init.d]#lskeepalived
[root@localhost init.d]#cp keepalived /etc/sysconfig/[root@localhost init.d]#mkdir -p /etc/keepalived
[root@localhost init.d]# cd/usr/local/etc/keepalived/[root@localhost keepalived]#lskeepalived.conf samples
[root@localhost keepalived]#cp keepalived.conf /etc/keepalived/[root@localhost keepalived]# cd/usr/local/sbin/[root@localhost sbin]#lskeepalived
[root@localhost sbin]#cp keepalived /usr/sbin/编辑配置文件和脚本
主上的配置文件:
cd/etc/keepalived
[root@localhost keepalived]#catkeepalived.conf
#Configuration Fileforkeepalived
global_defs {
notification_email {24272501@qq.com
}
notification_email_from jiankong@staff.tuge.com
smtp_server mail.tuge.com
smtp_connect_timeout10}
vrrp_script check_mysql {
script"/etc/keepalived/check_mysql.sh"interval2weight2}
vrrp_instance vrrptest {
state MASTER
interface ens33
virtual_router_id51priority150advert_int1nopreempt
authentication {
auth_type PASS
auth_pass1111}
track_script {
check_mysql
}
virtual_ipaddress {192.168.17.185}
}
从上的配置文件:
cd/etc/keepalived
[root@localhost keepalived]#catkeepalived.conf
#Configuration Fileforkeepalived
global_defs {
notification_email {
24272501@qq.com
}
notification_email_from jiankong@staff.tuge.com
smtp_server mail.tuge.com
smtp_connect_timeout10}
vrrp_script check_mysql {
script"/etc/keepalived/check_mysql.sh"interval2weight2}
vrrp_instance vrrptest {
state BACKUP
interface ens33
virtual_router_id51priority100advert_int1nopreempt
authentication {
auth_type PASS
auth_pass1111}
track_script {
check_mysql
}
virtual_ipaddress {192.168.17.185}
}
(3)监测mysql的脚本
主和slave1都要创建这个脚本:
[root@localhost keepalived]#cat check_mysql.sh#!/bin/bash
port=`/bin/netstat -nlt|grep 3306|/usr/bin/wc -l`if [ ${port} -ne 1]then
/etc/init.d/keepalived stopfi然后给这个脚本755权限
[root@localhost keepalived]#chmod 755 check_mysql.sh然后主和slave1开启keepalived
[root@localhost keepalived]# service keepalived start
Reloading systemd: [ 确定 ]
Starting keepalived (via systemctl): [ 确定 ]
到此已经配置完了.
四：测试
4.1在主库测试情况
(1) 查看主库的vip,主库已经有vip192.168.17.185[root@localhost keepalived]# ip a1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:c0:47:63brd ff:ff:ff:ff:ff:ff
inet192.168.17.198/22brd 192.168.19.255scope global noprefixroute dynamic ens33
valid_lft 21010sec preferred_lft 21010sec
inet192.168.17.185/32scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::b8b6:7153:1247:32ca/64scope link noprefixroute
valid_lft forever preferred_lft forever3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39brd ff:ff:ff:ff:ff:ff
inet192.168.122.1/24 brd 192.168.122.255scope global virbr0
valid_lft forever preferred_lft forever4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39brd ff:ff:ff:ff:ff:ff
(2)在主库建立一个测试账号,在测试机使用vip登录,可以连接
[root@localhost~]# /data/mysql5633/mysql/bin/mysql -uhaha -ptiger -h192.168.17.185Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 9Server version:5.6.33-log Source distribution
Copyright (c)2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.(3)将master的mysql关闭掉,查看master的vip已经不存在了
[root@localhost keepalived]#/data/mysql5633/mysql/bin/mysql.server stop
Shutting down MySQL............. SUCCESS![root@localhost keepalived]# ip a1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever2:ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:c0:47:63brd ff:ff:ff:ff:ff:ff
inet192.168.17.198/22brd 192.168.19.255scope global noprefixroute dynamic ens33
valid_lft 20484sec preferred_lft 20484sec
inet6 fe80::b8b6:7153:1247:32ca/64scope link noprefixroute
valid_lft forever preferred_lft forever3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39brd ff:ff:ff:ff:ff:ff
inet192.168.122.1/24 brd 192.168.122.255scope global virbr0
valid_lft forever preferred_lft forever4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39 brd ff:ff:ff:ff:ff:ff
4.2在从库测试情况
(1)此时登录slave1查看是否有vip
[root@localhost~]# ip a1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:b6:a1:99brd ff:ff:ff:ff:ff:ff
inet192.168.17.194/22 brd 192.168.19.255scope global noprefixroute dynamic ens33
valid_lft 20509sec preferred_lft 20509sec
inet192.168.17.185/32scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::f29e:aa24:a832:b185/64scope link noprefixroute
valid_lft forever preferred_lft forever3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000link/ether 52:54:00:7d:3c:33brd ff:ff:ff:ff:ff:ff
inet192.168.122.1/24 brd 192.168.122.255scope global virbr0
valid_lft forever preferred_lft forever4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000link/ether 52:54:00:7d:3c:33brd ff:ff:ff:ff:ff:ff
(2)此时可以查看slave1的mha的切换日志,已经成功failover了
到此mha+keepalived自动切换已经配置完!
五:mha+脚本环境准备
5.1在主库绑定vip网卡
[root@localhost keepalived]# /sbin/ifconfig ens33:1 192.168.17.185/24[root@localhost keepalived]# ip a1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:c0:47:63brd ff:ff:ff:ff:ff:ff
inet192.168.17.198/22 brd 192.168.19.255scope global noprefixroute dynamic ens33
valid_lft 19122sec preferred_lft 19122sec
inet192.168.17.185/24 brd 192.168.17.255 scope global ens33:1valid_lft forever preferred_lft forever
inet6 fe80::b8b6:7153:1247:32ca/64scope link noprefixroute
valid_lft forever preferred_lft forever3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39brd ff:ff:ff:ff:ff:ff
inet192.168.122.1/24 brd 192.168.122.255scope global virbr0
valid_lft forever preferred_lft forever4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39 brd ff:ff:ff:ff:ff:ff
5.2将脚本放在slave1指定的目录下
1:先修改配置文件
[root@localhost masterha]# vim /etc/masterha/app1.cnf
[root@localhost scripts]# ls
master_ip_failover master_ip_online_change
[root@localhost scripts]# chmod 755 master_ip_failover
[root@localhost scripts]# ls
master_ip_failover master_ip_online_change
脚本在文件夹里面,由于内容比较多未粘贴出来.脚本只需要修改一下网卡的名字和vip地址
5.3脚本测试
1:在master上面将mysql停掉,查看vip已经释放了
[root@localhost keepalived]# ip a1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:c0:47:63brd ff:ff:ff:ff:ff:ff
inet192.168.17.198/22 brd 192.168.19.255scope global noprefixroute dynamic ens33
valid_lft 16598sec preferred_lft 16598sec
inet6 fe80::b8b6:7153:1247:32ca/64scope link noprefixroute
valid_lft forever preferred_lft forever3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39brd ff:ff:ff:ff:ff:ff
inet192.168.122.1/24 brd 192.168.122.255scope global virbr0
valid_lft forever preferred_lft forever4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000link/ether 52:54:00:9b:d6:39brd ff:ff:ff:ff:ff:ff
在slave1上面查看,VIP已经飘过来了
[root@localhost~]# ip a1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host
valid_lft forever preferred_lft forever2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:b6:a1:99brd ff:ff:ff:ff:ff:ff
inet192.168.17.194/22 brd 192.168.19.255scope global noprefixroute dynamic ens33
valid_lft 31705sec preferred_lft 31705sec
inet192.168.17.185/24 brd 192.168.17.255 scope global ens33:1valid_lft forever preferred_lft forever
inet6 fe80::f29e:aa24:a832:b185/64scope link noprefixroute
valid_lft forever preferred_lft forever3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000link/ether 52:54:00:7d:3c:33brd ff:ff:ff:ff:ff:ff
inet192.168.122.1/24 brd 192.168.122.255scope global virbr0
valid_lft forever preferred_lft forever4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000link/ether 52:54:00:7d:3c:33 brd ff:ff:ff:ff:ff:ff
扫一扫
120
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
