Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having some trouble using replication when the machines are configured to use host names instead of IP addresses. The "skip-name-resolve" is OFF.

If I use IP addresses, everything is fine. Using DNS names, it all stops.

On the Master

Replication is configured with the following command:

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'slavename.internal.domain' IDENTIFIED BY 'repl_pwd';

On the Slave

The slave connection is configured as such:

change master to
    master_host='mastername',
    master_user='repluser',
    master_password='repl_pwd',
    master_log_file='mastername.000001',
    master_log_pos=1;

The Error I am Getting

When starting the replication slave, the log file shows the following messages:

120127 14:07:48 [Note] Slave SQL thread initialized, starting replication in log 'mastername.000001' at position 4, relay log './mysql-relay-bin.000001' position: 4
120127 14:07:48 [ERROR] Slave I/O: error connecting to master 'repluser@mastername:3306' - retry-time: 60  retries: 86400, Error_code: 1130

If I then try to connect using the command line from the client machine using:

mysql -h mastername -u repluser -prepl_pwd

I get the error:

ERROR 1130 (HY000): Host 'the.ip.address.here' is not allowed to connect to this MySQL server

I really hope someone out there can help me with my domain resolution issues!!!

share|improve this question
    
What version of MySQL (just curious) –  Derek Downey Jan 27 '12 at 15:09
    
We're using Percona 5.5 on SUSE Enterprise 10, running on VMWare hosted VMs. –  Dave Rix Jan 28 '12 at 14:21
add comment

2 Answers

up vote 0 down vote accepted

I feel terrible for forgetting about this question!!!

I have located and fixed the problem as follows.

When the DNS names were added to DNS, the corresponding reverse lookup was not created.

This resulted in MySQL not being able to perform the reverse lookup from the IP address to the correct DNS name, and therefore rejecting the connection.

So, we added a set of reverse lookups from IP address to DNS names, ran FLUSH HOSTS; on all of the MySQL boxes, and everything started working.

We require the use of DNS names for all connections, due to our disaster recovery solution being in a separate data centre, and a virtually identical VM farm, but with the IP addresses of all the machines modified only slightly. If / When we have a complete fail-over to the other data centre, all the software and communications will just "work", as the DNS resolution will always give the correct address based on the data centre.

You just need to be careful not to have TWO (or more) names reverse looking up from the same IP address, as there is no guarantee which of the two names will be returned - sometimes your connection will work, and other times it won't.

Hope this helps someone with the same problem!

Regards,
Dave

share|improve this answer
add comment

I'm not sure what you have tried yet, but here's the obligatory link to the MySQL docs on how MySQL will resolve hostnames. Specifically:

The thread takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address.

I noticed you are using slavename.internal.domain. I would ensure that gethostbyaddr(ip) on the slave resolves to the slavename.internal.domain, otherwise it won't work.

That beings said, I've always found domain resolve to be a big hassle and really unreliable. So I enable skip-name-resolve for the minor performance boost of ignoring DNS and grant based on IPs.

share|improve this answer
    
I've always preferred the IP only route, but unfortunately on this contract the client requires domain name resolve - see my comment on Rolando's post for more detail... –  Dave Rix Jan 28 '12 at 14:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.