ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql-MHA切换或检查复制时报1142问题定位和解决

mysql-MHA切换或检查复制时报1142问题定位和解决

原创 Linux操作系统 作者:ePower518 时间:2012-05-24 15:00:56 0 删除 编辑

====模拟当前slaves之间relay log存在差异,当前master mysql服务关闭后的mha自动切换
====1、在没有设置master_ip_failover_script的情况下的切换测试:测试预想结果:自动切换成功
=============================================
1)在241上,制造relay log同步不一致情况
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.02 sec)

2)在原来主库108上,shutdown mysql
[root@rac1 script]# mysqladmin -uroot -p shutdown

在管理主机查看日志没有任何日志:
tail -30 /var/log/masterha/app1/app1.log
Thu May 17 18:04:09 2012 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Fri May 18 10:26:04 2012 - [info] Got terminate signal. Exit.
原来是masterha没有启动:
[root@racdb ~]# masterha_check_status --conf=/etc/app1.cnf
app1 is stopped(2:NOT_RUNNING).

使用后台进程模式启动:
[root@racdb mha4mysql-manager-0.53]# nohup masterha_manager --conf=/etc/app1.cnf --remove_dead_master_conf < /dev/null > /var/log/masterha/app1/app1.log 2>&1 &
查看日志,启动失败退出。

3)、尝试重启108上的mysql,修改change master to master_host和master_log_file以及master_log_pos;
但没有设置master_user和master_password,报如下错误:
[root@racdb ~]# masterha_check_repl --conf=/etc/app1.cnf
Wed May 23 11:55:18 2012 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/Server.pm, ln381] 192.168.133.108(192.168.133.108:3306): User  does not exist or does not have REPLICATION SLAVE privilege! Other slaves can not start replication from this host.
Wed May 23 11:55:18 2012 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations.  at /usr/lib/perl5/site_perl/5.8.8/MHA/ServerManager.pm line 1305

4)、通过增加change master_user和master_password,解决user not exists问题;继续检查复制报如下错误。但,实际的复制没有问题。
[root@racdb ~]# masterha_check_repl --conf=/etc/app1.cnf
Wed May 23 12:03:20 2012 - [info]   Connecting to root@192.168.133.108(192.168.133.108:22)..
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to rac1-relay-bin.000002
    Temporary relay log file is /var/lib/mysql/rac1-relay-bin.000002
    Testing mysql connection and privileges..ERROR 1142 (42000) at line 1: CREATE command denied to user 'root'@'rac1.mttang.com' for table 'apply_diff_relay_logs_test'
mysql command failed with rc 1:0!
 at /usr/bin/apply_diff_relay_logs line 348
        main::check() called at /usr/bin/apply_diff_relay_logs line 467
        eval {...} called at /usr/bin/apply_diff_relay_logs line 447
        main::main() called at /usr/bin/apply_diff_relay_logs line 110
Wed May 23 12:03:20 2012 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/MasterMonitor.pm, ln193] Slaves settings check failed!
Wed May 23 12:03:20 2012 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/MasterMonitor.pm, ln372] Slave configuration failed.
Wed May 23 12:03:20 2012 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations.  at

没有做任何修改,后莫名奇妙的恢复。(此记1次)

5)、再次手工shutdown 192.168.133.109 master主库
检查app1.log出现如下错误:
Wed May 23 17:13:37 2012 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/MasterFailover.pm, ln295] Last failover was done at 2012/05/23 11:04:38. Current time is too early to do failover again. If you want to do failover, manually remove /var/log/masterha/app1/app1.failover.complete and run this script. again.
Wed May 23 17:13:37 2012 - [error][/usr/lib/perl5/site_perl/5.8.8/MHA/ManagerUtil.pm, ln178] Got ERROR:  at /usr/bin/masterha_master_switch line 53
解决方法:
rm -f /var/log/masterha/app1/app1.failover.complete
重新手工切换(或不用手工切换,直接运行manager),成功切换。

接下来,继续测试,在manager running的情况下,无master_ip_failover_script设置下的自动切换。注意,自动切换时,因为masterha_manager --remove_dead_master_conf  所以App1.cnf 中server1已经删除,检查不会出现问题,需要动态在/etc/app1.cnf中增加如下配置:

masterha_conf_host --command=add --conf=/etc/app1.cnf --hostname=192.168.133.109 --block=server1 --params="candidate_master=1;ignore_fail=1"

循环执行上述步骤到第4)步;上述问题重现。如果192.168.133.109上的skip-name-resolve没有设置,重启mysql后,检查则会报Create command denied to user  'root'@'rac2.mttang.com' for table 'apply_diff_relay_logs_test'
mysql command failed with rc 1:0!
 at /usr/bin/apply_diff_relay_logs line 348
        main::check() called at /usr/bin/apply_diff_relay_logs line 467
        eval {...} called at /usr/bin/apply_diff_relay_logs line 447
        main::main() called at /usr/bin/apply_diff_relay_logs line 110
如果,修改133.109上的my.cnf设置skip-name-resolve;则报如下错误:

Thu May 24 14:46:01 2012 - [info] Checking SSH publickey authentication and checking recovery script. configurations on all alive slave servers..
Thu May 24 14:46:01 2012 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=root --slave_host=192.168.133.109 --slave_ip=192.168.133.109 --slave_port=3306 --workdir=/var/log/masterha/app1 --target_version=5.5.20-log --manager_version=0.53 --relay_log_info=/var/lib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Thu May 24 14:46:01 2012 - [info]   Connecting to root@192.168.133.109(192.168.133.109:22)..
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to rac2-relay-bin.000002
    Temporary relay log file is /var/lib/mysql/rac2-relay-bin.000002
    Testing mysql connection and privileges..ERROR 1130 (HY000): Host '192.168.133.109' is not allowed to connect to this MySQL server
mysql command failed with rc 1:0!
 at /usr/bin/apply_diff_relay_logs line

解决方法:grant all privileges on *.* to 'root'@'192.168.133.109' identified by '12345678';

自此,1142在mha failover时的错误解决:在my.cnf中设置skip-name-resolve。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/88305/viewspace-730836/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-04-26

  • 博文量
    11
  • 访问量
    92055