注意:本文出自 “阿飞”的博客 ,如果要转载本文章,请与作者联系!
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的安装和配置篇以及各种问题解决的文章。这个只能延后了,忙....