MySQL DNS Caching

On December 12, 2008, SCCOOS metadata system was broken. The error was cryptic, but the message was clear: Connection to [IP] denied.

Resolution

Using the MySQL command-line tool:

mysql -u admin -p [-h ] [-P ]
mysql> flush hosts;


That’s it!

Chain of Events

• UCSD’s DNS was polluted, identifying 132.239.123.144 as ridge2000.org for the past few weeks.
• Sandbar was restarted last week, freeing up both the OS and MySQL DNS caches, which meant all new connections will need to query a DNS server to identify whether it’s an allowed machine or not (do you see a security vulnerability?).
• Both the OS and MySQL cached the hostname, but MySQL further rejected all connections from alfredo because MySQL thought that alfredo was ridge2000 instead.
• The mysql database, user table identifies user sccoos can connect to sandbar from alfredo.ucsd.edu.
• The OS cache probably cleared, but since so few machines access sandbar, the MySQL cache hadn’t filled up yet.
• Just last night (2008-12-22), the UCSD DNS purged the ridge2000.org name from its list, so UCSD’s DNS is clean, but any caches may still be polluted.

• Windows machines cycle their caches regularly, so the problem isn’t more wide-spread or commonly understood.
• The long-lasting MySQL DNS cache is disturbing, but alternatives would cripple the imperative nature of a database.

References

• MySQL DNS Reference
• Domain Name System

Tags: cache, dns, flush, mysql, sccoos