MHA检测主从复制状态的时出现“User xxx does not exist or does not have REPLICATION SLAVE privilege”异常解决

注意：本文出自 “阿飞”的博客 ，如果要转载本文章，请与作者联系！

并注明来源: http://blog.csdn.net/faye0412/article/details/7978697

MYSQL-Master-HA检测主从复制状态的时出现“User xxx does not exist or does not have REPLICATION SLAVE privilege”异常：

[root@mhamaster ~]# masterha_check_repl --conf=/etc/app1.cnf
Fri Sep 14 09:59:26 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep 14 09:59:26 2012 - [info] Reading application default configurations from /etc/app1.cnf..
Fri Sep 14 09:59:26 2012 - [info] Reading server configurations from /etc/app1.cnf..
Fri Sep 14 09:59:26 2012 - [info] MHA::MasterMonitor version 0.53.
Fri Sep 14 09:59:29 2012 - [info] Dead Servers:
Fri Sep 14 09:59:29 2012 - [info] Alive Servers:
Fri Sep 14 09:59:29 2012 - [info]   192.168.1.60(192.168.1.60:3306)
Fri Sep 14 09:59:29 2012 - [info]   192.168.1.61(192.168.1.61:3306)
Fri Sep 14 09:59:29 2012 - [info]   192.168.1.62(192.168.1.62:3306)
Fri Sep 14 09:59:29 2012 - [info] Alive Slaves:
Fri Sep 14 09:59:29 2012 - [info]   192.168.1.61(192.168.1.61:3306)  Version=5.5.20-log (oldest major version between slaves) log-bin:enabled
Fri Sep 14 09:59:29 2012 - [info]     Replicating from 192.168.1.60(192.168.1.60:3306)
Fri Sep 14 09:59:29 2012 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Sep 14 09:59:29 2012 - [info]   192.168.1.62(192.168.1.62:3306)  Version=5.5.20-log (oldest major version between slaves) log-bin:enabled
Fri Sep 14 09:59:29 2012 - [info]     Replicating from 192.168.1.60(192.168.1.60:3306)
Fri Sep 14 09:59:29 2012 - [info]     Not candidate for the new Master (no_master is set)
Fri Sep 14 09:59:29 2012 - [info] Current Alive Master: 192.168.1.60(192.168.1.60:3306)
Fri Sep 14 09:59:29 2012 - [info] Checking slave configurations..
Fri Sep 14 09:59:29 2012 - [info]  read_only=1 is not set on slave 192.168.1.61(192.168.1.61:3306).
Fri Sep 14 09:59:29 2012 - [warning]  relay_log_purge=0 is not set on slave 192.168.1.61(192.168.1.61:3306).
Fri Sep 14 09:59:29 2012 - [info]  read_only=1 is not set on slave 192.168.1.62(192.168.1.62:3306).
Fri Sep 14 09:59:29 2012 - [warning]  relay_log_purge=0 is not set on slave 192.168.1.62(192.168.1.62:3306).
Fri Sep 14 09:59:29 2012 - [info] Checking replication filtering settings..
Fri Sep 14 09:59:29 2012 - [info]  binlog_do_db= , binlog_ignore_db= 
Fri Sep 14 09:59:29 2012 - [info]  Replication filtering check ok.
Fri Sep 14 09:59:29 2012 - [error][/usr/local/share/perl5/MHA/Server.pm, ln381] 192.168.1.61(192.168.1.61:3306): User repl does not exist or does not have REPLICATION SLAVE privilege! Other slaves can not start replication from this host.
Fri Sep 14 09:59:29 2012 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations.  at /usr/local/share/perl5/MHA/ServerManager.pm line 1305
Fri Sep 14 09:59:29 2012 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln478] Error happened on monitoring servers.
Fri Sep 14 09:59:29 2012 - [info] Got exit code 1 (Not master dead).

在MYSQL中我已经添加了repl复制帐号并给了权限了，但是为何还报这个异常呢：

User repl does not exist or does not have REPLICATION SLAVE privilege! Other slaves can not start replication from this host.

很奇怪的问题。后来我又检查了网络、DNS、iptalbes等，各种配置也都没问题。还按照一位兄弟分享出来解决各种MHA的问题（http://space.itpub.net/?uid-88305-action-viewspace-itemid-730836）的方式重新淘腾了一遍，还是无果。

后来，我就直接研究了MHA的源代码，发现在Server.pm和DBHelper.pm两个文件中的检测过程都没有问题...郁闷了。后来我直接使用了：

Repl_User_SQL => "SELECT Repl_slave_priv AS Value FROM mysql.user WHERE user = ?";

在MYSQL上执行，问题出来了：

mysql> SELECT Repl_slave_priv AS Value FROM mysql.user WHERE user = 'repl';
+-------+
| Value |
+-------+
| N     |
| Y     |
+-------+

这里为何有两个值呢？后来我又执行了一次：

mysql> SELECT user,host,password, Repl_slave_priv AS Value FROM mysql.user WHERE user = 'repl';
+-------+-------------+-------------------------------------------+-------+
| user  | host        | password                                  | Value |
+-------+-------------+-------------------------------------------+-------+
| repl  | %           | *027*****6D8E759C6F4352B*********8465B8F8 | N     |
| repl  | 192.168.1.% | *027*****6D8E759C6F4352B*********8465B8F8 | Y     |
+-------+-------------+-------------------------------------------+-------+
2 rows in set (0.00 sec)

一下我就明白了，原来我创建复制帐号的时候创建了两个repl，而权限分配的不一样。问题终于找到了。于是我把第一个删掉，问题解决。

很多时候各种问题的出现都是由于自己的大意啊。其实在这篇文章之前应该还有一篇MYSQL-Master-HA的安装和配置篇以及各种问题解决的文章。这个只能延后了，忙....