#夏日挑战赛#数据库的高可用配置（mysql） 原创 精华

【本文正在参加星光计划计划3.0–夏日挑战赛】
活动链接：https://ost.51cto.com/posts/13641

一、环境配置：

1、操作系统版本：CentOS7

2、MySQL版本：5.7.28

3、VIP（虚IP）：10.0.0.140

4、机器列表及功能：

IP	hostname	server_id
10.0.0.158	s142	142
10.0.0.159	s143	143
10.0.0.160	s144	144

二、搭建

1、在s142、s143、s144机器上安装mysql5.7

①下载mysql-5.7.28，URL：​ ​https://downloads.mysql.com/archives/community/​​ 我这里下载的是64位版本
②下载后文件为：mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

2. 卸载自带的mariadb和mysql

①检查是否安装了mariadb和mysql，有时候默认安装了

[root@localhost ~]#rpm -qa | grep mariadb
[root@localhost ~]#rpm -qa | grep mysql

②如果没有，就可以安装mysql，如果有，需要先卸载（remove后为上面命令查询到的内容，全文件名，我这里没有，没法展示）

[root@localhost ~]#yum remove mariadb-xxx

3.解压文件，修改目录名方便配置

[root@localhost ~]#tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /opt/soft/
[root@localhost ~]#mkdir -p /opt/soft
[root@localhost ~]#cd /opt/soft
[root@soft ~]#mv mysql-5.7.28-linux-glibc2.12-x86_64 mysql-5.7.28

4.在/usr/local/目录下创建到/opt/soft/mysql-5.7.28的软链接

[root@localhost ~]#cd /usr/local
[root@soft ~]#ln -s /opt/soft/mysql-5.7.28 mysql

5.添加mysql用户，修改mysql目录权限，并用此用户执行应用

[root@localhost ~]#useradd -s /bin/false -M mysql
[root@localhost ~]#cd /opt/soft
[root@soft ~]#chown -R mysql:mysql mysql-5.7.28

6.拷贝配置文件，将mysql的配置文件拷贝为/etc/目录下的my.cnf，并修改配置文件

[root@localhost ~]#vim /etc/my.cnf[mysqld]
# binlog 配置
log-bin=/usr/local/mysql/logs/mysql-bin.log
expire-logs-days=14
max-binlog-size=500M
server-id=1
# GENERAL
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
user=mysql
default-storage-engine=InnoDB
character-set-server=utf8lower_case_table_names = 1explicit_defaults_for_timestamp=true
[mysqld_safe]
log-error=/usr/local/mysql/mysql-error.log
pid-file=/usr/local/mysql/mysqld.pid
[client]
socket=/usr/local/mysql/mysql.sock
[mysql]
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock

7.安装mysql，进入mysql目录执行以下命令

cd /opt/soft/mysql-5.7.28
mkdir -p /usr/local/mysql/logs
chown mysql:mysql /usr/local/mysql/logs
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

注：如果出现如下错误，说明需要安装依赖包：

① 安装autoconf依赖包：

yum -y install autoconf

②再次执行

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

出现以下信息，代表成功，要保存一下passwd

8.拷贝启动程序，将mysql的启动程序拷贝到/etc/init.d/目录下

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

9.安装完，启动mysql服务

service mysqld start

注：如果出现如下错误

[root@s144 support-files]# service mysqld start
Starting MySQL.2020-01-31T23:14:27.412533Z mysqld_safe error: log-error set to '/usr/local/mysql/mysql-error.log', however file don't exists. Create writable for user 'mysql'.
ERROR! The server quit without updating PID file (/usr/local/mysql/data/s144.pid).

说明mysql-error.log不存在，手动去创建，并修改权限

cd /opt/soft/mysql-5.7.28
touch mysql-error.log
chown mysql:mysql mysql-error.log

出现SUCCESS，说明启动成功

10.配置环境变量，编辑/etc/profile，方便在任何地方用mysql命令

vim /etc/profile
#mysql
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin

注：别忘记重新编译  /etc/profile

source /etc/profile

11.登录mysql，修改passwd

①首次登录没有passwd，提示输入passwd时，输入第7步安装时生成的passwd：p5j2jfX7am.h

mysql -uroot -p

②这里要先使用alter user重置passwd，不然会报错，我这里 修改mysql root用户passwd 为  111111 ：

mysql> alter user 'root'@'localhost' identified by '111111';
mysql> flush privileges;

③ 至此本机登录passwd修改完成，若是想让其他机器访问，需要配置远程访问：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '111111' WITH GRANT OPTION;

12.创建复制用户及复制配置

①在主节点（master）上配置复制用户：

create user canal_repl_user;
grant replication slave on *.*  to canal_repl_user identified by '111111';
flush privileges;
grant all on *.* to root identified by '111111';

②在从节点(slave)上执行主从复制命令：

CHANGE MASTER TO
MASTER_HOST='192.168.30.142',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_USER='canal_repl_user',
MASTER_PASSWORD='111111';

注：

#master_log_file=‘master-bin.000001’,#5.6后不需要指定

#master_log_pos=189;
③启动主从复制

START SLAVE;

④查看主从复制信息

SHOW SLAVE STATUS;

⑤说明主从复制成功，可以在主库中创建一个库，看看从库是否同步

13.在每台机器上安装yum源头及MHA依赖的perl包

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

如图安装成功：

1 4.配置ssh免密登录

①分别在s142/s143/s144机器上生成ssh秘钥：

ssh-keygen

②将各自公钥id_rsa.pub发送到另外两台机器，并追加到 ~/.ssh/authorized_keys中：
s142:

mv id_rsa.pub  id_rsa_142.pub
scp id_rsa_142.pub s143:~/.ssh/
scp id_rsa_142.pub s144:~/.ssh/

s143:

mv id_rsa.pub id_rsa_143.pub
scp id_rsa_143.pub s142:~/.ssh/
scp id_rsa_143.pub s144:~/.ssh/

s144:

mv id_rsa.pub id_rsa_144.pub
scp id_rsa_144.pub s142:~/.ssh/
scp id_rsa_144.pub s143:~/.ssh/

s142:

cat id_rsa_143.pub >> authorized_keys
cat id_rsa_144.pub >> authorized_keys

s143:

cat id_rsa_142.pub >> authorized_keys
cat id_rsa_144.pub >> authorized_keys

s144:

cat id_rsa_143.pub >> authorized_keys
cat id_rsa_142.pub >> authorized_keys

15.安装MHA

①分别在s142、s143、s144上下载node安装包并安装：

wget https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

②在s142上安装manager

wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

16.配置MHA Manager

①配置全局配置文件

新建 /etc/masterha_default.cnf （一定要是这个路径，不然后期masterha_check_ssh会提示未找到全局文件）

vim /etc/masterha_default.cnf

[server default]
user=root
password=111111
ssh_user=root
repl_user=canal_repl_user
repl_password=111111
ping_interval=1
#master_binlog_dir=/usr/local/mysql/logs
secondary_check_script=masterha_secondary_check -s s142 -s s143 -s s144 
master_ip_failover_script="/opt/soft/mha/scripts/master_ip_failover"
master_ip_online_change_script="/opt/soft/mha/scripts/master_ip_online_change"
report_script="/opt/soft/mha/scripts/send_report"

②配置主配置文件
新建/opt/soft/mha/app1/app1.cnf文件，并配置如下信息：

[server default]
manager_workdir=/opt/soft/mha
manager_log=/opt/soft/mha/manager.log

password=111111
user=root

ping_interval=1

repl_password=111111
repl_user=canal_repl_user

#master_binlog_dir=/usr/local/mysql/logs
#secondary_check_script=masterha_secondary_check -s s142 -s s143 -s s144
#master_ip_failover_script="/opt/soft/mha/scripts/master_ip_failover"
#master_ip_online_change_script="/opt/soft/mha/scripts/master_ip_online_change"
#report_script="/opt/soft/mha/scripts/send_report"

#ssh用户
ssh_user=root

[server1]
hostname=s142
port=3306
master_binlog_dir=/usr/local/mysql/logs
candidate_master=1
check_repl_delay=0

[server2]
hostname=s143
port=3306
master_binlog_dir=/usr/local/mysql/logs
candidate_master=1
check_repl_delay=0

[server3] 
hostname=s144
port=3306
master_binlog_dir=/usr/local/mysql/logs
ignore_fail=1
no_master=1

③配置VIP切换
为了防止脑裂发生,推荐生产环境采用脚本的方式来管理虚拟 ip,而不是使用 keepalived来完成。

vim /opt/soft/mha/scripts/master_ip_failover

　　#!/usr/bin/env perl
    use strict;
    use warnings FATAL => 'all';
    use Getopt::Long;

    my (
        $command,   $ssh_user,  $orig_master_host,
        $orig_master_ip,$orig_master_port, $new_master_host, $new_master_ip,$new_master_port
    );

    #定义VIP变量
    my $vip = '192.168.30.140/24';
    my $key = '1';
    my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
    my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";

    GetOptions(
        'command=s'     => \$command,
        'ssh_user=s'        => \$ssh_user,
        'orig_master_host=s'    => \$orig_master_host,
        'orig_master_ip=s'  => \$orig_master_ip,
        'orig_master_port=i'    => \$orig_master_port,
        'new_master_host=s' => \$new_master_host,
        'new_master_ip=s'   => \$new_master_ip,
        'new_master_port=i' => \$new_master_port,
    );

    exit &main();

    sub main {
        print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
        if ( $command eq "stop" || $command eq "stopssh" ) {
            my $exit_code = 1;
            eval {
                print "Disabling the VIP on old master: $orig_master_host \n";
                &stop_vip();
                $exit_code = 0;
            };
            if ($@) {
                warn "Got Error: $@\n";
                exit $exit_code;
            }
            exit $exit_code;
        }

        elsif ( $command eq "start" ) {
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
            $exit_code = 0;
        };

        if ($@) {
            warn $@;
            exit $exit_code;
            }
        exit $exit_code;
        }

        elsif ( $command eq "status" ) {
            print "Checking the Status of the script.. OK \n";
            exit 0;
        }
        else {
            &usage();
            exit 1;
        }
    }

    sub start_vip() {
        `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;    }
    sub stop_vip() {
        return 0 unless ($ssh_user);
        `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;    }
    sub usage {
        print
        "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

④配置VIP脚本

vim /opt/soft/mha/scripts/master_ip_online_change

#!/bin/bash
source /root/.bash_profile

vip=`echo '192.168.30.140/24'`  #设置VIP
key=`echo '1'`

command=`echo "$1" | awk -F = '{print $2}'`
orig_master_host=`echo "$2" | awk -F = '{print $2}'`
new_master_host=`echo "$7" | awk -F = '{print $2}'`
orig_master_ssh_user=`echo "${12}" | awk -F = '{print $2}'`
new_master_ssh_user=`echo "${13}" | awk -F = '{print $2}'`

#要求服务的网卡识别名一样，都为ens33(这里是)
stop_vip=`echo "ssh root@$orig_master_host /usr/sbin/ifconfig ens33:$key down"`
start_vip=`echo "ssh root@$new_master_host /usr/sbin/ifconfig ens33:$key $vip"`

if [ $command = 'stop' ]
  then
    echo -e "\n\n\n****************************\n"
    echo -e "Disabled thi VIP - $vip on old master: $orig_master_host \n"
    $stop_vip
    if [ $? -eq 0 ]
      then
    echo "Disabled the VIP successfully"
      else
    echo "Disabled the VIP failed"
    fi
    echo -e "***************************\n\n\n"
  fi

if [ $command = 'start' -o $command = 'status' ]
  then
    echo -e "\n\n\n*************************\n"
    echo -e "Enabling the VIP - $vip on new master: $new_master_host \n"
    $start_vip
    if [ $? -eq 0 ]
      then
    echo "Enabled the VIP successfully"
      else
    echo "Enabled the VIP failed"
    fi
    echo -e "***************************\n\n\n"
fi

⑤.配置报警邮件脚本

首先配置邮件发送设置信息

#mail邮件发送程序，需要先配置好发送这信息

vim /etc/mail.rc

set from=qixing@163.com
set smtp=smtp.163.com
set smtp-auth-user=qixing
#拿163邮箱来说这个不是passwd，而是授权码
set smtp-auth-password=qixing
set smtp-auth=login

编写邮件发送脚本：

vim /opt/soft/mha/script/send_report


#!/bin/bash
source /root/.bash_profile
# 解析变量
orig_master_host=`echo "$1" | awk -F = '{print $2}'`
new_master_host=`echo "$2" | awk -F = '{print $2}'`
new_slave_hosts=`echo "$3" | awk -F = '{print $2}'`
subject=`echo "$4" | awk -F = '{print $2}'`
body=`echo "$5" | awk -F = '{print $2}'`
#定义收件人地址
email="qixing@163.com"

tac /var/log/mha/app1/manager.log | sed -n 2p | grep 'successfully' > /dev/null
if [ $? -eq 0 ]
    then
    messages=`echo -e "MHA $subject 主从切换成功\n master:$orig_master_host --> $new_master_host \n $body \n 当前从库:$new_slave_hosts"` 
    echo "$messages" | mail -s "Mysql 实例宕掉，MHA $subject 切换成功" $email >>/tmp/mailx.log 2>&1 
    else
    messages=`echo -e "MHA $subject 主从切换失败\n master:$orig_master_host --> $new_master_host \n $body" `
    echo "$messages" | mail -s ""Mysql 实例宕掉，MHA $subject 切换失败"" $email >>/tmp/mailx.log 2>&1  
fi

⑥将脚本赋予可执行权限

chmod +x /opt/soft/mha/scripts/master_ip_failover 
chmod +x /opt/soft/mha/scripts/master_ip_online_change 
chmod +x /opt/soft/mha/scripts/send_report

17.验证MHA配置信息是否正常

① 检查ssh配置：

masterha_check_ssh --conf=/opt/soft/mha/app1/app1.cnf

成功！！！
②(在所有机器执行)

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

③检查主从复制情况：

masterha_check_repl --conf=/opt/soft/mha/app1/app1.cnf

  健康！！！

18.在master节点上绑定VIP，只需绑定一次，后续会随主备切换而自动切换

ifconfig ens33:1 192.168.30.140/24

①如过遇到问题，需手动删除，可执行如下命令：

ifconfig ens33:1 del 192.168.30.140或ifconfig ens33:1 down #关闭vip

②可以查看绑定VIP是否成功：

ip addr

说明绑定成功！

19.在MHA的manager节点上启动MHA管理进程

nohup masterha_manager --conf=/opt/soft/mha/app1/app1.cnf --ignore_last_failover /opt/soft/mha/app1/manager.log 2>&1

&命令参数：

–remove_dead_master_conf       该参数代表当发生主从切换后，老的主库的ip将会从配置文件中移除。

–manger_log                    日志存放位置
观察manager.log日志，查看是否有成功，一般最后打印如下日志，说明成功：

Thu Jul  2 15:00:05 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

20.查看MHA状态

masterha_check_status --conf=/opt/soft/mha/app1/app1.cnf

说明MHA正在运行中，主节点是s142

21.停止MHA管理进程

masterha_stop --conf=/opt/soft/mha/app1/app1.cnf

manager.log日志会打印终止日志：

22.手动进行主备切换(在进行手动切换前要先停值manager进程)

masterha_master_switch --conf=/opt/soft/mha/app1/app1.cnf --master_state=alive --new_master_host=s143 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0

说明切换成功！