开心学习
当前位置：数据库 > > 正文

mysql的binlog日志详解（MySQL 有关MHA搭建与切换的几个错误log汇总）

时间：2022-01-27 01:07:17类别：数据库

mysql的binlog日志详解

MySQL 有关MHA搭建与切换的几个错误log汇总

1：masterha_check_repl 副本集方面报错  replicates is not defined in the configuration file!

具体信息如下：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
    		•
  • # /usr/local/bin/masterha_check_repl --conf=/etc/mha/app1.cnf
  • Thu Nov 21 15:33:15 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
  • Thu Nov 21 15:33:15 2018 - [info] Reading application default configuration from /etc/mha/app1.cnf..
  • Thu Nov 21 15:33:15 2018 - [info] Reading server configuration from /etc/mha/app1.cnf..
  • Thu Nov 21 15:33:15 2018 - [info] MHA::MasterMonitor version 0.56.
  • Thu Nov 21 15:33:16 2018- [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 179.179.19.179:3306 from which slave 179.179.19.180(179.179.19.180:3306) replicates is not defined in the configuration file!
  • Thu Nov 21 15:33:16 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/share/perl5/MHA/MasterMonitor.pm line 326.
  • Thu Nov 21 15:33:16 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
  • Thu Nov 21 15:33:16 2018 - [info] Got exit code 1 (Not master dead).
  •  
  • MySQL Replication Health is NOT OK!
    		•

    • 分析：MHA 漂移过后，我们知道配置信息中 主节点的信息就不在了，我们需要及时维护，否则/usr/local/bin/masterha_check_repl --conf=/etc/mha/XXX.cnf 检查副本集状态报错。

    2. masterha_master_switch 在线切换方面 报错 We should not start online master switch when one of connections are running long updates on the current master

    具体信息如下：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
    		•
  • # /usr/local/bin/masterha_master_switch --master_state=alive --conf=/etc/mha/app1.cnf
  •  
  • It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 179.179.19.184(179.179.19.184:3306)? (YES/no): y
  •  
  • Tue Nov 19 17:19:09 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
  • Tue Nov 19 17:19:09 2018 - [info] ok.
  • Tue Nov 19 17:19:09 2018 - [info] Checking MHA is not monitoring or doing failover..
  • Tue Nov 19 17:19:09 2018 - [info] Checking replication health on 179.179.19.185..
  • Tue Nov 19 17:19:09 2018 - [info] ok.
  • Tue Nov 19 17:19:09 2018 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln161] We should not start online master switch when one of connections are running long updates on the current master(179.179.19.184(179.179.19.184:3306)). Currently 1 update thread(s) are running.
  • Details:
  • {'Time' => '12815','db' => undef,'Id' => '1','User' => 'event_scheduler','State' => 'Waiting on empty queue','Command' => 'Daemon','Info' => undef,'Host' => 'localhost'}
  • Tue Nov 19 17:19:09 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.
    		•

    • 分析：set global event_scheduler=off; 主从都要关闭

    3. masterha_master_switch 在线切换方面 报错 Got Error: DBI .....failed: Access denied for user

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
    		•
  • # /usr/local/bin/masterha_master_switch --master_state=alive --conf=/etc/mha/app1.cnf
  •  
  • Starting master switch from 179.179.19.185(179.179.19:3306) to 179.179.19.184(179.179.19.184:3306)? (yes/NO): yes
  •  
  • Tue Nov 19 18:52:04 2018 - [info] Checking whether 179.179.19.184(179.179.19.184:3306) is ok for the new master..
  • Tue Nov 19 18:52:04 2018 - [info] ok.
  • Tue Nov 19 18:52:04 2018 - [info] ** Phase 1: Configuration Check Phase completed.
  • Tue Nov 19 18:52:04 2018 - [info]
  • Tue Nov 19 18:52:04 2018 - [info] * Phase 2: Rejecting updates Phase..
  • Tue Nov 19 18:52:04 2018 - [info]
  • Tue Nov 19 18:52:04 2018 - [info] Executing master ip online change script to disable write on the current master:
  • Tue Nov 19 18:52:04 2018 - [info]  /usr/local/bin/master_ip_online_change_appuanalysis --command=stop --orig_master_host=179.179.19.185 --orig_master_ip=179.179.19.185 --orig_master_port=3306--orig_master_user='weixinLX391P_xldbmha' --orig_master_password='weixinLX391P_xldbmha\)qlk' --new_master_host=179.179.19.184 --new_master_ip=179.179.19.184 --new_master_port=55988 --new_master_user='us_mha' --new_master_password='weixinLX391P_xldbmha\)qlk' --orig_master_ssh_user=root --new_master_ssh_user=root
  • Got Error: DBI connect(';host=179.179.19.184;port=3306;mysql_connect_timeout=4','weixinLX391P_xldbmha',...) failed: Access denied for user 'weixinLX391P_xldbmha'@'179.179.19.166' (using password: YES) at /usr/local/share/perl5/MHA/DBHelper.pm line 205.
  •  at /usr/local/bin/master_ip_online_change_app1 line 119.
  •  
  • Tue Nov 19 18:52:04 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.
    		•

    • 分析：账号密码有需要转移字符的。app1.cnf 文件中user账号相应的密码 password 不能有待转移的字符，例如本例中的')'，但是 账号 repl_user 相应的密码repl_password 没有此限制。

    4.如果使用的是xtrabackup，注意从节点会把event还原上去，可能会造成数据不一致，同步失败的问题。

    如果主节点有event，需要手动关闭从节点的event。例如，主节点有归档删除数据的event，从节点需要关闭，否则报错。类似如下错误：

  • ?
  • 1
    		•
  • Could not execute Delete_rows event on table ????DB.*****table; Can't find record in '*****', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos XXXXXXX
    		•

    • 5 .GTID 模式转换为传统模式后，MHA 机制下数据库主从检查报错。

    检查的命令：

  • ?
  • 1
    		•
  • /usr/local/bin/masterha_check_repl --conf=/etc/mha/qqweixinordb.cnf
    		•

    • 主要的报错信息

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
    		•
  • Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 106.
  • mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
  •  at /usr/local/bin/apply_diff_relay_logs line 493.
  • Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings check failed!
  • Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln413] Slave configuration failed.
  • Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/local/bin/masterha_check_repl line 48.
  • Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
  • Fri Aug 28 04:38:22 2019 - [info] Got exit code 1 (Not master dead).
  •  
  • MySQL Replication Health is NOT OK!
    		•

    • 解决方案--在每个DB节点执行以下命令

  • ?
  • 1
  • 2
  • 3
    		•
  • ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
  •  
  • ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
    		•

    • mysql的binlog日志详解（MySQL 有关MHA搭建与切换的几个错误log汇总）

     再次检查，报错信息消失，OK。 

    6.root 账号密码过期

    以root账号设置的ssh免密登陆，而ssh有过期限制，则mha ssh检查时报错:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
    		•
  • /usr/local/bin/masterha_check_ssh --conf=/etc/mha/qqorder.cnf
  • Thu Nov 5 10:09:09 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
  • Thu Nov 5 10:09:09 2018 - [info] Reading application default configuration from /etc/mha/pms20epime.cnf..
  • Thu Nov 5 10:09:09 2018 - [info] Reading server configuration from /etc/mha/pms20epime.cnf..
  • Thu Nov 5 10:09:09 2018 - [info] Starting SSH connection tests..
  • Thu Nov 5 10:09:09 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63]
  • Thu Nov 5 10:09:09 2018 - [debug] Connecting via SSH from root@172.181.191.191(172.181.191.191:22) to root@172.181.191.192(172.181.191.192:22)..
  •  
  • WARNING: Your password has expired.
  • Password change required but no TTY available.
  • Thu Nov 5 10:09:09 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln111] SSH connection from root@172.181.191.191(172.181.191.191:22) to root@172.181.191.192(172.181.191.192:22) failed!
  • Thu Nov 5 10:09:10 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63]
  • Thu Nov 5 10:09:09 2018 - [debug] Connecting via SSH from root@172.181.191.192(172.181.191.192:22) to root@172.181.191.191(172.181.191.191:22)..
  •  
  • WARNING: Your password has expired.
  • Password change required but no TTY available.
  • Thu Nov 5 10:09:10 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln111] SSH connection from root@172.181.191.192(172.181.191.192:22) to root@172.181.191.191(172.181.191.191:22) failed!
  • SSH Configuration Check Failed!
  •  at /usr/local/bin/masterha_check_ssh line 44.
    		•

    • 另外一种表现，就是第二次执行账号切换时报错  sudo su -

    mysql的binlog日志详解（MySQL 有关MHA搭建与切换的几个错误log汇总）

    解决方案在root账号下，执行以下命令:

  • ?
  • 1
    		•
  • chage -M 99999 root
    		•

    • 以上就是MySQL 有关MHA搭建与切换的几个错误log汇总的详细内容，更多关于MySQL MHA搭建与切换的资料请关注开心学习网其它相关文章！

    原文链接：https://www.cnblogs.com/xuliuzai/p/11980273.html

    标签：
    mysql log MHA
    上一篇下一篇

    猜您喜欢

    热门推荐

    © 2021 开心学习