MySQL/notes

Navigation

computing: software: databases: engines: MySQL: notes

Overview

This page is for any loose notes on MySQL for which there isn't yet a specific page.

MySQL on a DNS server

Some observations on 2007-09-21, from the owner/operator of a small web hosting provider. These only apply if:

• the web sites are on a different machine from the MySQL server
• the web site machine is connected to the MySQL server directly (typically via crossover cable), rather than going through the LAN

What happened, and the solution (in brief):

Tonight, my cluster bit the dust. Every time we would restart MySQL, we would immediately be deluged with huge amounts of "unauthenticated users" hitting up the DB server. Thanks to the good folks in #MySQL on EFNet IRC, they turned me onto http://hackmysql.com/dns

For /etc/my.cnf

After [mysqld]

ADD this, on its own line: skip-name-resolve

---------------------

If you're running your DNS on the same machine as your DB server, make SURE you add the three following lines to /etc/resolv.conf:

nameserver 127.0.0.1

nameserver 192.168.0.10

nameserver 192.168.0.20

Modifying the two 192.168.x.x ranges to be the IP's of your internal private network.

At one point, my DB server was deluged with over 2300 requests that were trying to do reverse DNS on 192.168.0.10 (which is the web server, .20 is the DB server). Since the entire 192.168.X.X block is set aside for internal private networks, chances are that you will NOT have those resolving into hostnames. The problem was that my DB server was trying to resolve all 2300 requests into a hostname before it allowed DB queries to go through.

You'll have to restart (or shut down/kill off all MySQL processes, remove mysql.sock and your pid file) and then start MySQL up again.

I hope this saves someone the two hours of headache I've just gone through getting this sorted.

Full post is here.