CentOS7.3安装部署MHA高可用方案

本是古典何须时尚 2023-07-23 13:58 21阅读 0赞

# 环境信息 #

<table> 
 <thead> 
  <tr> 
   <th align="left">服务器IP</th> 
   <th align="left">系统版本</th> 
   <th align="left">服务</th> 
   <th align="left">角色</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">192.168.1.1</td> 
   <td align="left">CentOS Linux release 7.3.1611 (Core)</td> 
   <td align="left">mysql 5.7.25 + MHA 0.58 Node</td> 
   <td align="left">主库</td> 
  </tr> 
  <tr> 
   <td align="left">192.168.1.2</td> 
   <td align="left">CentOS Linux release 7.3.1611 (Core)</td> 
   <td align="left">mysql 5.7.25 + MHA 0.58 Node</td> 
   <td align="left">从库</td> 
  </tr> 
  <tr> 
   <td align="left">192.168.1.3</td> 
   <td align="left">CentOS Linux release 7.3.1611 (Core)</td> 
   <td align="left">mysql 5.7.25 + MHA 0.58 Node + Manager</td> 
   <td align="left">管理节点</td> 
  </tr> 
  <tr> 
   <td align="left">192.168.1.100</td> 
   <td align="left">CentOS Linux release 7.3.1611 (Core)</td> 
   <td align="left">VIP</td> 
   <td align="left">虚拟IP</td> 
  </tr> 
 </tbody> 
</table>

## 主从复制 ##

# 主库备份
    mysqldump -uroot -p  -S  /tmp/mysql.sock  --add-drop-database --all-databases --flush-privileges --single-transaction --events --default-character-set=utf8mb4 --set-gtid-purged=OFF --master-data=2  > /opt/dump.sql
    # 从库导入
    source /opt/dump.sql
    CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.xxxxx', MASTER_LOG_POS=xxx, MASTER_USER='repl', MASTER_PASSWORD='******';
    start slave; 
    show slave status\G;

## 创建mysql用户 ##

groupadd mysql
    useradd -r -m -g mysql -s /bin/bash mysql

## MHA日志目录 ##

mkdir -p  /data/mha/mysql/log 
    chown -R mysql:mysql /data/mha
    chmod 755 -R /data/mha

## 三节点互信 ##

# 三个节点互信，或至少配置MHA管理节点可以免密到数据节点
    ssh-keygen -t rsa -P '' -f ~/.ssh/id_rsa
    ssh-copy-id -i ~/.ssh/id_rsa.pub -p 22 -o StrictHostKeyChecking=no mysql@x.x.x.x

## 主库创建MHA监控用户、purge用户 ##

GRANT SUPER, RELOAD, REPLICATION CLIENT, SELECT ON *.* TO 'mha'@'192.168.1.%' IDENTIFIED BY '******';
    GRANT CREATE, DROP, SELECT, UPDATE, INSERT, DELETE ON mysql.apply_diff_relay_logs_test TO 'mha'@'192.168.1.%';
    GRANT SELECT, SUPER, REPLICATION CLIENT,RELOAD  ON *.* TO 'purge'@'localhost' IDENTIFIED BY '******';

purge用来定时清理relay log，用户名称可自行定义

## 从库设置read-only参数 ##

set global read_only=1,super_read_only=1,relay_log_purge=0;
    show variables where variable_name in ('read_only', 'super_read_only', 'relay_log_purge');

## 三个节点安装依赖和软件包 ##

yum install -y perl-DBD-MySQL
    # 在Centos中使用iputils软件包来安装arping
    yum install -y iputils 
    yum install -y perl-ExtUtils-CBuilder 
    yum install -y perl-ExtUtils-MakeMaker
    yum install -y perl-CPAN 
    yum install -y perl-Config-Tiny
    yum install -y perl-Log-Dispatch
    yum install -y perl-Parallel-ForkManager

## 三个节点安装 node 软件包 ##

cd ./tar
    tar -zxvf ./mha4mysql-node-0.58.tar.gz
    cd ./mha4mysql-node-0.58
    perl Makefile.PL 
    make 
    make install 
    # 添加libmysqlclient环境
    echo "/usr/lib64/mysql/" >>   /etc/ld.so.conf  
    ldconfig
    cat /etc/ld.so.conf

## 管理端安装 manager 软件包 ##

cd ./tar
    tar -zxvf ./mha4mysql-manager-0.58.tar.gz
    cd ./mha4mysql-manager-0.58
    perl Makefile.PL 
    make 
    make install

## 管理节点创建目录和配置文件 ##

mkdir -p /etc/mha/mysql
    chown -R mysql:mysql /etc/mha
    vim /etc/mha/mysql/app.cnf
    #
    # The MHA manager configuration file.
    #
    # For explanations see
    # http://code.google.com/p/mysql-master-ha
    
    # * note: 
    # The files default end with '.cnf', for example like app.cnf
    # 
    [server default]
    
    manager_workdir=/data/mha/mysql/log   
    remote_workdir=/data/mha/mysql/log  
    
    manager_log=/data/mha/mysql/log/manager.log
    
    # 设置ssh账号和端口
    ssh_user=mysql
    ssh_port=22
    
    # 设置MHA的管理账号和密码
    user=mha
    password=******
    
    # 设置主从复制的账号和密码
    repl_user=repl
    repl_password=******
    
    # 每隔一秒监测一次
    ping_interval=1
    
    # 定义故障切换和在线切换
    ping_type=CONNECT
    master_ip_failover_script=/usr/local/bin/master_ip_failover
    master_ip_online_change_script=/usr/local/bin/master_ip_online_change
    
    # 防止网络抖动误切换
    secondary_check_script= /usr/local/bin/masterha_secondary_check -s 192.168.1.1 -s 192.168.1.2  --user=mysql --master_port=3306
    
    [server1]
    hostname=192.168.1.1
    port=3306
    ssh_port=22
    candidate_master=1
    check_repl_delay=0
    master_binlog_dir=/data/mysql/binlog
    
    [server2]
    hostname=192.168.1.2
    port=3306
    ssh_port=22
    candidate_master=1
    check_repl_delay=0
    master_binlog_dir=/data/mysql/binlog

### 切换时VIP管理脚本 ###

# 自动切换时VIP管理脚本
    cp /opt/master_ip_failover  /usr/local/bin/
    chmod 755  /usr/local/bin/master_ip_failover
    # 在线切换时VIP管理脚本
    cp /opt/master_ip_online_change  /usr/local/bin/
    chmod 755  /usr/local/bin/master_ip_online_change
    # ===============================================
    
    vim /usr/local/bin/master_ip_failover
    # ------- 增加 VIP 绑定功能 -------
    my $vip = '192.168.1.100';
    my $vip_netmask = '255.255.255.0'
    my $vip_dev = 'eth0';
    my $key = '1';
    my $ssh_start_vip = "/sbin/ifconfig $vip_dev:$key $vip netmask $vip_net_mask up && /usr/sbin/arping -q -A -c 1 -I $vip_dev $vip";  
    my $ssh_stop_vip = "/sbin/ifconfig $vip_dev:$key down ";
    my $ssh_port = 22;
    
    #......此处省略部分脚本....... #
    
    # ------ 启动VIP ------ 
    sub start_vip() { 
        `ssh -p $ssh_port -o StrictHostKeyChecking=no $ssh_user\@$new_master_ip \" $ssh_start_vip \"`;
    }
    
    # ------ 停止VIP ------ 
    sub stop_vip() { 
         return 0  unless  ($ssh_user);
        `ssh -p $ssh_port -o StrictHostKeyChecking=no $ssh_user\@$orig_master_ip \" $ssh_stop_vip \"`;
    }
    # ===============================
    # 备注： master_ip_online_change 脚本参考 master_ip_failover 
    # ！！！

### masterha\_secondary\_check ###

# 由于masterha_secondary_check 脚本写死了端口，这里使用替换参数方法来修改
    vim  /usr/local/bin/masterha_secondary_check
    $ssh_user    = "mysql" unless ($ssh_user);
    $ssh_port    = 22     unless ($ssh_port);
    $master_port = 3306   unless ($master_port);

## 所有数据节点创建软连接 ##

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
    ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
    ln -s /usr/local/bin/filter_mysqlbinlog /usr/bin/filter_mysqlbinlog
    ln -s /usr/local/bin/purge_relay_logs /usr/bin/purge_relay_logs
    ln -s /usr/local/bin/save_binary_logs /usr/bin/save_binary_logs
    ln -s /usr/local/bin/apply_diff_relay_logs /usr/bin/apply_diff_relay_logs
    # 为用户授权ifconfig权限
    chmod u+s /sbin/ifconfig
    chmod u+s /usr/sbin/arping

## 环境检查 ##

su - mysql -c "/usr/local/bin/masterha_check_ssh --conf=/etc/mha/mysql/app.cnf" 
    su - mysql -c "/usr/local/bin/masterha_check_repl --conf=/etc/mha/mysql/app.cnf"

## 启动 ##

su - mysql -c "nohup /usr/local/bin/masterha_manager --conf=/etc/mha/mysql/app.cnf --ignore_last_failover </dev/null> /data/mha/mysql/log/manager.log 2>&1 & "
    # 状态检查
    su - mysql -c "/usr/local/bin/masterha_check_status --conf=/etc/mha/mysql/app.cnf"