mysql-MHA架构实践记录

た入场券 2022-11-05 00:37 151阅读 0赞

### mha架构实践记录 ###

*  一、规划
 *  二、下载安装
 *   *  1、清理环境
     *  2、关闭防火墙
     *  3、设置主从配置
     *  4、开启半同步复制
     *  5、MHA
     *   *  1、环境准备
         *  2、下载安装
         *  3、mha配置检测
         *  4、模拟mha故障转移

> 前提条件，系统安装为centos7，mysql5.7未提供centos8的版本。

# 一、规划 #

<table> 
 <thead> 
  <tr> 
   <th>角色</th> 
   <th>ip</th> 
   <th>权限</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>master</td> 
   <td>113.31.153.7</td> 
   <td>可读写、主库</td> 
  </tr> 
  <tr> 
   <td>slave1</td> 
   <td>113.31.153.1</td> 
   <td>只读、从库</td> 
  </tr> 
  <tr> 
   <td>slave2</td> 
   <td>106.75.210.2</td> 
   <td>只读、从库</td> 
  </tr> 
  <tr> 
   <td>MHA</td> 
   <td>8.131.93.1</td> 
   <td>高可用监控</td> 
  </tr> 
 </tbody> 
</table>

# 二、下载安装 #

mysql安装(https://blog.csdn.net/wudinaniya/article/details/81094578)

https://downloads.mysql.com/archives/community/

## 1、清理环境 ##

（mariadb是从mysql来的，是它的一个分支，需要清理掉）

centos7会自带一个mariadb

1、查看是否已安装

rpm -qa |grep -i mysql
    rpm -qa |grep -i mariadb（centos7默认系统自带的）

2、清理

rpm -e mariadb --nodeps
    rpm -e mariadb-libs --nodeps

3、按照依赖关系依次安装rpm包 依赖关系依次为common→libs→client→server

rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
    rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
    rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
    rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm

> 注：ivh中， i-install安装；v-verbose进度条；h-hash哈希校验\*\*

4、启动mysql服务并重置密码

初始化用户并启动服务，设置为开机启动

mysqld --initialize --user=mysql
    systemctl start mysqld
    systemctl enable mysqld

查看初始密码

tail -500f /var/log/mysqld.log |grep password

用默认密码登录，修改密码，登录mysql，执行

mysql -uroot -p
    set password=password('root');

## 2、关闭防火墙 ##

centos7用firewalld，要先关闭iptables，同时设置firewalld开机禁用

systemctl stop iptables
    systemctl stop firewalld
    systemctl disabled firewalld.service

检查下，ecs外网防火墙是否开放了3306端口，否则下面执行ssh是不通的

## 3、设置主从配置 ##

1、修改master

#bin_log配置
    log_bin=mysql-bin
    server-id=1
    sync-binlog=1
    binlog-ignore-db=information_schema
    binlog-ignore-db=mysql
    binlog-ignore-db=performance_schema
    binlog-ignore-db=sys
    #relay_log配置
    relay_log=mysql-relay-bin
    log_slave_updates=1
    relay_log_purge=0

2、修改slave配置，用server-id做区分

#bin_log配置
    log_bin=mysql-bin
    #服务器ID,从库1是2,从库2是3
    server-id=2
    sync-binlog=1
    binlog-ignore-db=information_schema
    binlog-ignore-db=mysql
    binlog-ignore-db=performance_schema
    binlog-ignore-db=sys
    #relay_log配置
    relay_log=mysql-relay-bin
    log_slave_updates=1
    relay_log_purge=0
    
    read_only=1

修改 完都要重新启动

systemctl restart mysqld

3、master授权

// 登录主库进行授权
    mysql -uroot -proot
    grant replication slave on *.* to root@'%' identified by 'your_password'; 
    grant all privileges on *.* to root@'%' identified by 'your_password';
    flush privileges;
    
    show master status;

4、登录mysql slave节点，开启同步

stop slave;
    change master to master_host='113.31.153.7',master_port=3306,master_user='root',master_password ='root',master_log_file='mysql-bin.000021',master_log_pos=1872;
    start slave;
    
    show slave status \G;

## 4、开启半同步复制 ##

1、master需要一个插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';
    show variables like '%semi%';

修改my.cnf

# 自动开启半同步复制
    rpl_semi_sync_master_enabled=ON
    rpl_semi_sync_master_timeout=1000

2、slave需要一个插件

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
    show variables like '%semi%';

修改my.cnf

# 自动开启半同步复制
    rpl_semi_sync_slave_enabled=ON

## 5、MHA ##

### 1、环境准备 ###

> 四台机器ssh互通，设置完成后通过ssh访问无需密码

1、四台机器分别执行

ssh-keygen -t rsa

2、三台mysql分别执行命令将公钥拷贝到mha

ssh-copy-id mha_ip

3、mha上查看秘钥

cat /root/.ssh/authorized_keys

4、执行命令将mha的秘钥也拷贝进来

cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys

5、将mha的秘钥拷贝到三台mysql

scp /root/.ssh/authorized_keys root@113.31.153.7:/root/.ssh/authorized_keys
    scp /root/.ssh/authorized_keys root@113.31.153.1:/root/.ssh/authorized_keys
    scp /root/.ssh/authorized_keys root@106.75.210.245:/root/.ssh/authorized_keys

6、通过ssh命令测试互联（无需密码）

### 2、下载安装 ###

MySQL5.7对应的MHA版本是0.5.8，github上分别下载MHA manager和 node的安装包

https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
    https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58

1、mha4台节点安装mha4mysql-node

yum install perl-DBD-MySQL -y
    wget https://github.com/yoshinorim/mha4mysqlnode/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
    rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2、mha上还需要安装mha4mysql-manager。

MHA的manager又依赖了perl-Config-Tiny、perl-Log-Dispatch、perl-Parallel-ForkManager

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

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

3、mha监控实例的日志文件

mkdir -p /var/log/mha/app1
    touch /var/log/mha/app1/manager.log

4、监控全局配置文件修改

vim /etc/masterha_default.cnf

[server default]
    #主库用户名，在master mysql的主库执行下列命令建一个新用户
    #create user 'mha'@'%' identified by '123123';
    #grant all on *.* to mha@'%' identified by '123123';
    #flush privileges;
    user=mha
    password=123123
    port=3306
    #ssh登录账号
    ssh_user=root
    #从库复制账号和密码
    repl_user=root
    repl_password=123456
    port=3306
    #ping次数
    ping_interval=1
    #二次检查的主机
    secondary_check_script=masterha_secondary_check -s 106.75.210.2 -s
    113.31.153.7 -s 113.31.153.1

5、修改监控实例配置文件

mkdir -p /etc/mha
    vim /etc/mha/app1.cnf

[server default]
    #MHA监控实例根目录
    manager_workdir=/var/log/mha/app1
    #MHA监控实例日志文件
    manager_log=/var/log/mha/app1/manager.log
    #[serverx] 服务器编号
    #hostname 主机名
    #candidate_master 可以做主库
    #master_binlog_dir binlog日志文件目录
    [server1]
    hostname=113.31.153.7
    candidate_master=1
    master_binlog_dir="/var/lib/mysql"
    [server2]
    hostname=113.31.153.1
    candidate_master=1
    master_binlog_dir="/var/lib/mysql"
    [server3]
    hostname=106.75.210.2
    candidate_master=1
    master_binlog_dir="/var/lib/mysql"

### 3、mha配置检测 ###

mha manager上执行以下两个检测

1、ssh通信检测

masterha_check_ssh --conf=/etc/mha/app1.cnf

2、mysql主从复制检测

masterha_check_repl --conf=/etc/mha/app1.cnf

3、mha监控启动

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

4、查看监控日志命令

tail -500f /var/log/mha/app1/manager.log

### 4、模拟mha故障转移 ###

1、停掉master节点