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

====模拟当前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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/88305/viewspace-730836/