mysql的binlog日志详解(MySQL 有关MHA搭建与切换的几个错误log汇总)
mysql的binlog日志详解
MySQL 有关MHA搭建与切换的几个错误log汇总1:masterha_check_repl 副本集方面报错 replicates is not defined in the configuration file!
具体信息如下:
|
# /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
具体信息如下:
|
# /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
|
# /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,从节点需要关闭,否则报错。类似如下错误:
|
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 机制下数据库主从检查报错。
检查的命令:
|
/usr/ local /bin/masterha_check_repl --conf=/etc/mha/qqweixinordb.cnf |
主要的报错信息
|
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节点执行以下命令
|
ln -s /usr/ local /mysql/bin/mysqlbinlog /usr/ local /bin/mysqlbinlog ln -s /usr/ local /mysql/bin/mysql /usr/ local /bin/mysql |
再次检查,报错信息消失,OK。
6.root 账号密码过期
以root账号设置的ssh免密登陆,而ssh有过期限制,则mha ssh检查时报错:
|
/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 -
解决方案在root账号下,执行以下命令:
|
chage -M 99999 root |
以上就是MySQL 有关MHA搭建与切换的几个错误log汇总的详细内容,更多关于MySQL MHA搭建与切换的资料请关注开心学习网其它相关文章!
原文链接:https://www.cnblogs.com/xuliuzai/p/11980273.html
- mysql8.0.25安装及配置超详细教程(MySQL8.0.21安装步骤及出现问题解决方案)
- mysql left join索引怎么使用(详解mysql 使用left join添加where条件的问题分析)
- 修改mysql安装服务名称(Apache为mysql以及自己的项目设置虚拟路径)
- mysql查询慢有哪些原因(MySQL 查询速度慢的原因)
- mysql数据库与表的基本操作总结(Mysql、Oracle中常用的多表修改语句总结)
- mysql详细笔记(MySQL的内存表的基础学习教程)
- mysql将字符串转换为日期的函数(Mysql中时间戳转为Date的方法示例)
- mysqlsql按日期统计(sqlserver/mysql按天、按小时、按分钟统计连续时间段数据推荐)
- mysql有哪些约束(MySQL完整性约束的定义与实例教程)
- mysql改root密码命令(MySQL root密码的重置方法)
- 数据库mysql基本知识(深入理解r2dbc在mysql中的使用)
- mysql exists的用法(Mysql exists用法小结)
- mysqlbinlog怎么分析(MySQL中使用binlog时格式该如何选择)
- mysql性能怎么看(是什么影响了 MySQL 的性能?)
- mysqlworkbench怎么设置连接(详解MySQL Workbench使用教程)
- mysql自定义函数怎么设置(MySQL自定义变量?学不废不收费~)
- 清华大学难考吗(清华大学考研录取分数线)
- 观花盆栽佛肚竹盆景制作及养护(观花盆栽佛肚竹盆景制作及养护)
- 春天养佛肚竹,做好这几件事,叶绿根壮寓意好 越养越旺家(春天养佛肚竹做好这几件事)
- 律界衣品最好,时尚圈学识数高,41岁的Amal堪称现实版的傲骨贤妻(时尚圈学识数高)
- 刘智泰确认出演《Good Wife》 担纲男一号(刘智泰确认出演Good)
- 《傲骨之战》剧终 律政女魔头Diane,为什么是大女主天花板(傲骨之战剧终律政女魔头Diane)
热门推荐
- mariadb导入数据库命令(MySQL/MariaDB 如何实现数据透视表的示例代码)
- html5自动静音(html5录音功能实战示例)
- mysql 高级查询语法(MySQL查询语句进阶知识集锦)
- zabbix5.4监控磁盘可用内存大小(解析Zabbix 5.0磁盘自动发现和读写监控的问题)
- dedecms 怎么设置栏目(dedecms如何实现tag标签伪静态的方法)
- 云服务器是不是云主机(云主机和服务器的区别在哪里)
- sqlserver新建表参数错误(Sql Server中判断表、列不存在则创建的方法)
- 最新版dedecms安全策略(DedeCMS站点高级安全策略Linux篇)
- dedecms文章标题(dedecms5.7后台发布文章提示“标题不能为空”的解决方法)
- sql server日志备份查看(sql server数据库高可用日志传送的方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9