Linux – MySQL – Hostname Issue

centosdomain-name-systemhostnamelinuxMySQL

I'm experiencing an unusual issue with my production mysql server. Currently I only have two production servers so I had all the server host name entries in /etc/hosts files on each server. A few days ago I setup a DNS zone in hopes that I can remove the manual entries in the /etc/hosts file.

Due to the fact I only have two production servers, I setup my DNS so that each server had a real hostname, which could be aliased too so that I can control connections by simply updating dns records. For example, server1 hosts httpd and mysql, so in my DNS zone I have server1 with an A record of 4.4.4.2 and store-ro with an alias to server1. Obviously 4.4.4.2 is not my real ip, demonstrative only.

After some configuration, I am able to connect to the local mysql instance via command line, but my application keeps throwing Unknown MySQL server host 'store-ro' errors. The only way I can fix the issue is if I add the following back to the hosts file:

127.0.0.1 store-ro
# or
4.4.4.2 store-ro

(Not sure if it matters, but my app is using Zend_Db to connect to the database.)

So the app can connect to the server if I enter loopback ip or the actual ip, but won't work if removed from the hosts file, so I thought maybe it was a DNS issue, but it appears that the server can resolve the hostname fine:

$ -> nslookup store-ro

Server:   72.14.179.5
Address:  72.14.179.5#53

Non-authoritative answer:
store-ro.example.com    canonical name = server1.example.com.
Name:   server1.example.com
Address: 4.2.2.2

I do have SELinux enabled, and a cursory glance over relevant httpd connection settings appear to be correct:

$ -> getsebool -a | ack httpd

httpd_can_network_connect_db --> on
httpd_can_network_connect --> on

Plus there are no entries showing up in /var/log/audit/audit.log.

My hostname is set correctly:

$ -> hostname

server1

MySQL user perms:

mysql> select host, user from user;
+-----------+-------------+
| host      | user        |
+-----------+-------------+
| %         | ro_user_1   |
| %         | ro_user_2   |

And /etc/resolve.conf:

search example.com
nameserver 72.14.179.5
nameserver 72.14.188.5

And /etc/hosts:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

4.4.4.2 server1 server1.example.com # This shouldn't be necessary with DNS setup?

The only thing I can think of is if the alias to the cname may be causing the issue, but I doubt it. Any help is appreciated.

–Update–

Forgot to add the actual error: SQLSTATE[HY000] [2005] Unknown MySQL server host 'store-ro'

Per request here is the full row from the mysql table:

mysql> select * from user where user like '%core%'\G
*************************** 1. row ***************************
Host: %
User: ro_ml_core
Password: *2B5FEA45B69826783FD6F8601A9AFB1CFE916D52
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL

Best Answer

mysql> select User,Host from user where user like '%core%';
+------------+-----------+
| User       | Host      |
+------------+-----------+
| ro_ml_core | %         |
| ro_ml_core | localhost |
+------------+-----------+
2 rows in set (0.00 sec)

It is necessary to have both (% and localhost) accounts for ro_ml_core to be able to connect from anywhere as ro_ml_core. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when ro_ml_core connects from the localhost. As a result, ro_ml_core would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'ro_ml_core'@'%' account and thus comes earlier in the user table sort order.

About sort order:

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means "any host" and is least specific. The empty string '' also means "any host" but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific).

Related Solutions

BIND – Overriding DNS Entries in BIND for Internal Networks

The best method is via the response policy zone in Bind 9.8.1 or newer. It allows you to override single records in arbitrary zones (and there's no need to create a whole subdomain for that, only the single record you want to change), it allows you to override CNAMEs, etc. Other solutions such as Unbound cannot override CNAMEs.

https://www.redpill-linpro.com/sysadvent/2015/12/08/dns-rpz.html

EDIT: Let's do this properly then. I will document what I've done based on the tutorial linked above.

My OS is Raspbian 4.4 for Raspberry Pi, but the technique should work without any changes on Debian and Ubuntu, or with minimal changes on other platforms.

Go to where your Bind config files are kept on your system - here it's in /etc/bind. Create in there a file called db.rpz with the following contents:

$TTL 60
@            IN    SOA  localhost. root.localhost.  (
                          2015112501   ; serial
                          1h           ; refresh
                          30m          ; retry
                          1w           ; expiry
                          30m)         ; minimum
                   IN     NS    localhost.

localhost       A   127.0.0.1

www.some-website.com    A        127.0.0.1

www.other-website.com   CNAME    fake-hostname.com.

What does it do?

it overrides the IP address for www.some-website.com with the fake address 127.0.0.1, effectively sending all traffic for that site to the loopback address
it sends traffic for www.other-website.com to another site called fake-hostname.com

Anything that could go in a Bind zone file you can use here.

To activate these changes there are a few more steps:

Edit named.conf.local and add this section:

zone "rpz" {
  type master;
  file "/etc/bind/db.rpz";
};

The tutorial linked above tells you to add more stuff to zone "rpz" { } but that's not necessary in simple setups - what I've shown here is the minimum to make it work on your local resolver.

Edit named.conf.options and somewhere in the options { } section add the response-policy option:

options {
  // bunch
  // of
  // stuff
  // please
  // ignore

  response-policy { zone "rpz"; };
}

Now restart Bind:

service bind9 restart

That's it. The nameserver should begin overriding those records now.

If you need to make changes, just edit db.rpz, then restart Bind again.

Bonus: if you want to log DNS queries to syslog, so you can keep an eye on the proceedings, edit named.conf.local and make sure there's a logging section that includes these statements:

logging {
    // stuff
    // already
    // there

    channel my_syslog {
        syslog daemon;
        severity info;
    };
    category queries { my_syslog; };
};

Restart Bind again and that's it.

Test it on the machine running Bind:

dig @127.0.0.1 www.other-website.com. any

If you run dig on a different machine just use @the-ip-address-of-Bind-server instead of @127.0.0.1

I've used this technique with great success to override the CNAME for a website I was working on, sending it to a new AWS load balancer that I was just testing. A Raspberry Pi was used to run Bind, and the RPi was also configured to function as a WiFi router - so by connecting devices to the SSID running on the RPi I would get the DNS overrides I needed for testing.

Ubuntu – hostname -f says “hostname: the specified hostname is invalid”. Guess why

From this thread on Ubuntu Forums.

Apparently the underscore is an illegal character for hostnames. I had one in my hostname, and it gave me your failure message. Maybe you're getting something similar.

Best Answer

Related Solutions

BIND – Overriding DNS Entries in BIND for Internal Networks

Ubuntu – hostname -f says “hostname: the specified hostname is invalid”. Guess why

Related Topic