pt-table-sync, Strange Issues regarding Hostname

Question

I am trying to run pt-table-sync to re-synchronise a MYSQL Master-Slave replication. I have two VPS Systems which are running Debian 5, and the replication is running without any issues.

The problem I have is that when I run pt-table-sync on the MASTER server, it keeps failing to connect to the SLAVE server. I am running the following command:
pt-table-sync --dry-run --sync-to-master h='ip of slave server',u='user',p='password'
Which returns:
Host 'my website address' is not allowed to connect to this MySQL server at ...

However, if I try to connect to the slave mysql server remotely from the master server:
mysql -h 'ip of slave server' -u 'user' -p'password'

Then I have no issues whatsoever, and can access the database fine.

On my slave server I have tried adjusting the mysql user account that the MASTER uses when connecting to it, adjusting the hosts, and even setting them to 'any' temporarily to see whether it would work.

What I also found strange is that depending on the exact pt-table-sync command I issue, the Host name of the machine seemed to change. For example:

pt-table-sync --dry-run --sync-to-master h='ip of slave server',u='user',p='password'
--> Host 'my website address' is not allowed to connect to this MySQL server at ...

pt-table-sync --dry-run --sync-to-master 'ip of slave server'
--> Access denied for user 'root'@''my VPS name'' (using password: NO)

I am unsure why it is providing different 'Host' names depending on the two commands? Any ideas why this would be failing when I can connect directly to mysql remotely?

Answer 1

I solved this issue: the error is not coming from the slave DB server, but from the master itself! pt-table-check tries to connect first of all to the slave, and then to the master, using the same user and password! That's why you got a different user on the error if you put a wrong password (in that case the error comes from the slave).

In my case it was trying to connect to the master as user@[IP address] instead of user@localhost (I was running the command on the master) and such a user was not defined. I hope it helps.

Davide

Answer 2

I had a similar problem, caused by different passwords for the MySQL root user on both servers. After I set an identical password on both ends everything started working.

Answer 3

Ya, just as you said user3207102, I got the problem solved.

Here is what I did.

1. First I checked if my user could simply login to master from slave or not.

   $ mysql -uroot -pmypassword123 -h master.ip.addr
   ERROR 1045 (28000): Access denied for user 'root'@'zeronepal.local' (using password: YES)
   

2. I kenw I could login to the master from its own machine, So I checked if root user could login from other host or not.

mysql> show grants for 'root'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

Dooh !! I didn't have the grants... So I give the root user the privilege with password same as that of slave

mysql> grant all privileges on . to 'root'@'%' identified by 'mypassword123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Finally on running the command from on my master...

$./pt-table-sync -uroot --execute --sync-to-master slave.ip.addr --tables employees.blah --ask-pass
Enter password for 192.168.2.214:

... tadaa... it worked, Hurray (happy). Thanks guyz.