MHA故障处理小结

我就是我 2023-03-13 15:17 30阅读 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">Ubuntu 16.04.6 LTS</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">Ubuntu 16.04.6 LTS</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">Ubuntu 16.04.6 LTS</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">Ubuntu 16.04.6 LTS</td> 
   <td align="left">VIP</td> 
   <td align="left">虚拟IP</td> 
  </tr> 
 </tbody> 
</table>

# 故障处理汇总： #

## 1）Can’t locate DBD/mysql.pm ##

root@ubuntu:~# su - mysql -c "/usr/local/bin/masterha_check_repl --conf=/etc/mha/mysql/app.cnf"
    Fri May 15 11:41:01 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Fri May 15 11:41:01 2020 - [info] Reading application default configuration from /etc/mha/mysql/app.cnf..
    Fri May 15 11:41:01 2020 - [info] Reading server configuration from /etc/mha/mysql/app.cnf..
    Fri May 15 11:41:01 2020 - [info] MHA::MasterMonitor version 0.58.
    Fri May 15 11:41:01 2020 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln301] install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (you may need to install the DBD::mysql module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.22.1 /usr/local/share/perl/5.22.1 /usr/lib/x86_64-linux-gnu/perl5/5.22 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.22 /usr/share/perl/5.22 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base .) at (eval 151) line 3. Perhaps the DBD::mysql perl module hasn't been fully installed,
    or perhaps the capitalisation of 'mysql' isn't right. Available drivers: DBM, ExampleP, File, Gofer, Mem, Proxy, Sponge. at /usr/local/share/perl/5.22.1/MHA/DBHelper.pm line 208. at /usr/local/share/perl/5.22.1/MHA/Server.pm line 168. Fri May 15 11:41:01 2020 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln301] install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (you may need to install the DBD::mysql module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.22.1 /usr/local/share/perl/5.22.1 /usr/lib/x86_64-linux-gnu/perl5/5.22 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.22 /usr/share/perl/5.22 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base .) at (eval 151) line 3.
    Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right.
    Available drivers: DBM, ExampleP, File, Gofer, Mem, Proxy, Sponge.
     at /usr/local/share/perl/5.22.1/MHA/DBHelper.pm line 208.
     at /usr/local/share/perl/5.22.1/MHA/Server.pm line 168.
    Fri May 15 11:41:02 2020 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
    Fri May 15 11:41:02 2020 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm line 329.
    Fri May 15 11:41:02 2020 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
    Fri May 15 11:41:02 2020 - [info] Got exit code 1 (Not master dead).
    
    MySQL Replication Health is NOT OK!

### 故障原因： ###

缺少安装MHA依赖包：libdbd-mysql-perl

### 解决办法： ###

安装Perl模块依赖包

root@ubuntu:~# apt-get install -y libdbd-mysql-perl
    root@ubuntu:~# apt-get install -y libconfig-tiny-perl
    root@ubuntu:~# apt-get install -y liblog-dispatch-perl
    root@ubuntu:~# apt-get install -y libparallel-forkmanager-perl
    # 
    检查是否安装成功
    root@ubuntu:~# dpkg -l libdbd-mysql-perl 
    Desired=Unknown/Install/Remove/Purge/Hold
    | Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
    |/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
    ||/ Name                                                 Version                         Architecture                    Description
    +++-====================================================-===============================-===============================-==============================================================================================================
    ii  libdbd-mysql-perl                                    4.033-1ubuntu0.1                amd64                           Perl5 database interface to the MySQL database

## 2）Got MySQL error ##

root@ubuntu:~# su - mysql -c "/usr/local/bin/masterha_check_repl --conf=/etc/mha/mysql/app.cnf"
    Fri May 15 14:22:05 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Fri May 15 14:22:05 2020 - [info] Reading application default configuration from /etc/mha/mysql/app.cnf..
    Fri May 15 14:22:05 2020 - [info] Reading server configuration from /etc/mha/mysql/app.cnf..
    Fri May 15 14:22:05 2020 - [info] MHA::MasterMonitor version 0.58.
    Fri May 15 14:22:05 2020 - [error][/usr/local/share/perl/5.22.1/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.1.2(192.168.1.2:3306) :1045:Access denied for user 'mha'@'192.168.1.3' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
    Fri May 15 14:22:05 2020 - [error][/usr/local/share/perl/5.22.1/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.1.1(192.168.1.1:3306) :1045:Access denied for user 'mha'@'192.168.1.3' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
    Fri May 15 14:22:05 2020 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln301]  at /usr/local/share/perl/5.22.1/MHA/ServerManager.pm line 297.
    Fri May 15 14:22:05 2020 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln301]  at /usr/local/share/perl/5.22.1/MHA/ServerManager.pm line 297.
    Fri May 15 14:22:06 2020 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
    Fri May 15 14:22:06 2020 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm line 329.
    Fri May 15 14:22:06 2020 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
    Fri May 15 14:22:06 2020 - [info] Got exit code 1 (Not master dead).
    
    MySQL Replication Health is NOT OK!

### 故障原因： ###

主库需要创建mha账号

### 解决办法： ###

连接主库，创建mha账号并授权mysql.apply\_diff\_relay\_logs\_test表读写权限

root@localhost 15:15:08 [(none)]> GRANT SUPER, RELOAD, REPLICATION CLIENT, SELECT ON *.* TO 'mha'@'192.168.1.%' IDENTIFIED BY '******';
    root@localhost 15:15:08 [(none)]> GRANT CREATE, DROP, SELECT, UPDATE, INSERT, DELETE ON mysql.apply_diff_relay_logs_test TO 'mha'@'192.168.1.%' ;

## 3）Error happened on checking configurations ##

root@ubuntu:~# su - mysql -c "/usr/local/bin/masterha_check_repl --conf=/etc/mha/mysql/app.cnf"
    Fri May 15 15:42:22 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Fri May 15 15:42:22 2020 - [info] Reading application default configuration from /etc/mha/mysql/app.cnf..
    Fri May 15 15:42:22 2020 - [info] Reading server configuration from /etc/mha/mysql/app.cnf..
    Fri May 15 15:42:22 2020 - [info] MHA::MasterMonitor version 0.58.
    Fri May 15 15:42:23 2020 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. Redundant argument in sprintf at /usr/local/share/perl/5.22.1/MHA/NodeUtil.pm line 201.
    Fri May 15 15:42:23 2020 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
    Fri May 15 15:42:23 2020 - [info] Got exit code 1 (Not master dead).

### 故障原因： ###

MHA0.58 在 ubuntu-16.04 的 bug

### 解决办法： ###

修改 /usr/local/share/perl/5.22.1/MHA/NodeUtil.pm脚本

root@ubuntu:~# vim /usr/local/share/perl/5.22.1/MHA/NodeUtil.pm
    
    sub parse_mysql_major_version($) { 
      my $str = shift;
      my $result = sprintf( '%03d%03d', $str =~ m/(\d+)/g );
      return $result;
    }
    
    # 修改为
    sub parse_mysql_major_version($) { 
      my $str = shift;
      $str =~ /(\d+)\.(\d+)/;
      my $strmajor = "$1.$2";
      my $result = sprintf( '%03d%03d', $strmajor =~ m/(\d+)/g );
      return $result;
    }