数据库高可用 MHA 搭建手册

2022-02-06 10:36:33

一：背景

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个宿主机都需要操作

数据库都是编译安装的,此步骤已省略.可以查看以前的文章https://www.cnblogs.com/xiaoyou2018/p/9831272.html

配置主从

MASTER操作:

(1) 授权主从复制账号,你有几个数据库就需要创建几个主从复制账号,我的环境是3个数据库,就需要创建3个主从复制账号.

<root@localhost:mysql3306.sock [(none)]> grant replication slave on *.* to 'repl'@'192.168.17.198' identified by '123456';

Query OK, 0 rows affected (0.00 sec)



<root@localhost:mysql3306.sock [(none)]> grant replication slave on *.* to 'repl'@'192.168.17.194' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

 
<root@localhost:mysql3306.sock [(none)]> grant replication slave on *.* to 'repl'@'192.168.17.199' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

 
<root@localhost:mysql3306.sock [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

(2) 创建mha的管理账号

<root@localhost:mysql3306.sock [(none)]> grant all privileges on *.* to 'manager'@'192.168.17.%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)

<root@localhost:mysql3306.sock [(none)]> 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) 将主库的备份导入数据库中

<root@localhost:mysql3306.sock [(none)]> source /all1227.sql;

<root@localhost:mysql3306.sock[test]>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.02 sec)

<root@localhost:mysql3306.sock [test]> start slave;

Query OK, 0 rows affected, 1 warning (0.02 sec)

<root@localhost:mysql3306.sock [(none)]> set global read_only=1;

<root@localhost:mysql3306.sock [(none)]> flush privileges;

Slave2操作,并设置只读模式

(1) 将主库的备份导入数据库中

<root@localhost:mysql3306.sock [test]> source /all1227.sql;

<root@localhost:mysql3306.sock[test]> 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.12 sec)

 

<root@localhost:mysql3306.sock [test]> start slave;

Query OK, 0 rows affected, 1 warning (0.02 sec)

<root@localhost:mysql3306.sock [(none)]> set global read_only=1;

<root@localhost:mysql3306.sock [(none)]>flush privileges;

然后分别在两个从库上面验证主从是否搭建好

<root@localhost:mysql3306.sock [test]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.17.198
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2816
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_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/profile
export 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=123456
ssh_user=root
repl_user=repl
repl_password=123456
ping_interval=1
shutdown_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.198
port=3306
master_binlog_dir="/data/mysql5633/mysql/binlog"
candidate_master=1
[server2]
hostname=192.168.17.194
port=3306
master_binlog_dir="/data/mysql5633/mysql/binlog"
candidate_master=1
[server3]
hostname=192.168.17.199
port=3306
master_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 Dec 27 15:35:32 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Dec 27 15:35:32 2018 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Thu Dec 27 15:35:32 2018 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Thu Dec 27 15:35:32 2018 - [info] Starting SSH connection tests..
Thu Dec 27 15:35:35 2018 - [debug] 
Thu Dec 27 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 Dec 27 15:35:34 2018 - [debug]   ok.
Thu Dec 27 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 Dec 27 15:35:35 2018 - [debug]   ok.
Thu Dec 27 15:35:44 2018 - [debug] 
Thu Dec 27 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 Dec 27 15:35:38 2018 - [debug]   ok.
Thu Dec 27 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 Dec 27 15:35:44 2018 - [debug]   ok.
Thu Dec 27 15:35:45 2018 - [debug] 
Thu Dec 27 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 Dec 27 15:35:39 2018 - [debug]   ok.
Thu Dec 27 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 Dec 27 15:35:45 2018 - [debug]   ok.
Thu Dec 27 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]# ls
keepalived
[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]# ls
keepalived.conf  samples
[root@localhost keepalived]# cp keepalived.conf /etc/keepalived/
[root@localhost keepalived]# cd /usr/local/sbin/
[root@localhost sbin]# ls
keepalived
[root@localhost sbin]# cp keepalived /usr/sbin/
编辑配置文件和脚本
主上的配置文件:
cd /etc/keepalived
[root@localhost keepalived]# cat keepalived.conf 
#Configuration File for keepalived
global_defs {
notification_email {
  24272501@qq.com
        }
  notification_email_from jiankong@staff.tuge.com
  smtp_server mail.tuge.com
  smtp_connect_timeout 10
}
vrrp_script check_mysql {
     script "/etc/keepalived/check_mysql.sh"
     interval 2
     weight 2
     }
vrrp_instance vrrptest {
        state MASTER
        interface ens33
        virtual_router_id 51
        priority 150
        advert_int 1
        nopreempt
authentication {
        auth_type PASS
        auth_pass 1111
}
track_script {
     check_mysql
     }
virtual_ipaddress {
         192.168.17.185
}
}
从上的配置文件:
cd /etc/keepalived
[root@localhost keepalived]# cat keepalived.conf 
#Configuration File for keepalived
global_defs {
notification_email {
  24272501@qq.com
        }
  notification_email_from jiankong@staff.tuge.com
  smtp_server mail.tuge.com
  smtp_connect_timeout 10
}
vrrp_script check_mysql {
     script "/etc/keepalived/check_mysql.sh"
     interval 2
     weight 2
     }
vrrp_instance vrrptest {
        state BACKUP
        interface ens33
        virtual_router_id 51
        priority 100
        advert_int 1
        nopreempt
authentication {
        auth_type PASS
        auth_pass 1111
}
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 stop
fi
然后给这个脚本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 a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:c0:47:63 brd ff:ff:ff:ff:ff:ff
    inet 192.168.17.198/22 brd 192.168.19.255 scope global noprefixroute dynamic ens33
       valid_lft 21010sec preferred_lft 21010sec
    inet 192.168.17.185/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::b8b6:7153:1247:32ca/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:9b:d6:39 brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:9b:d6:39 brd ff:ff:ff:ff:ff:ff
(2)在主库建立一个测试账号,在测试机使用vip登录,可以连接
[root@localhost ~]# /data/mysql5633/mysql/bin/mysql -uhaha -ptiger -h192.168.17.185
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server 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 clear the current input statement.

<haha@192.168.17.217:3306 [(none)]> 
(3)将master的mysql关闭掉,查看master的vip已经不存在了
[root@localhost keepalived]# /data/mysql5633/mysql/bin/mysql.server stop
Shutting down MySQL............. SUCCESS! 
[root@localhost keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:c0:47:63 brd ff:ff:ff:ff:ff:ff
    inet 192.168.17.198/22 brd 192.168.19.255 scope global noprefixroute dynamic ens33
       valid_lft 20484sec preferred_lft 20484sec
    inet6 fe80::b8b6:7153:1247:32ca/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:9b:d6:39 brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:9b:d6:39 brd ff:ff:ff:ff:ff:ff

4.2在从库测试情况

(1)此时登录slave1查看是否有vip
[root@localhost ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:b6:a1:99 brd ff:ff:ff:ff:ff:ff
    inet 192.168.17.194/22 brd 192.168.19.255 scope global noprefixroute dynamic ens33
       valid_lft 20509sec preferred_lft 20509sec
    inet 192.168.17.185/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::f29e:aa24:a832:b185/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:7d:3c:33 brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:7d:3c:33 brd 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 a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:c0:47:63 brd ff:ff:ff:ff:ff:ff
    inet 192.168.17.198/22 brd 192.168.19.255 scope global noprefixroute dynamic ens33
       valid_lft 19122sec preferred_lft 19122sec
    inet 192.168.17.185/24 brd 192.168.17.255 scope global ens33:1
       valid_lft forever preferred_lft forever
    inet6 fe80::b8b6:7153:1247:32ca/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:9b:d6:39 brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/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