Usage stats on DNS server

One way would be turning the BIND logging:

logging{
  channel simple_log {
    file "/var/log/bind.log" versions 3 size 5m;
    severity warning;
    print-time yes;
    print-severity yes;
    print-category yes;
  };
  category default{
    simple_log;
  };
};

Or if you have rndc configure you can use:

rndc querylog

H Pierre,

You mentioned MySQL so I am assuming Linux and Bind.

should be something like this inside the named.conf

logging{
  channel queries_log{
    file "/var/log/named/dns.log" versions 5 size 500m;
    print-time yes;
  };
  category queries{
    queries_log;
  };
};

The answers already given here show how to get your queries in a flat text file. You asked about using MySQL as a backend and this is definitely possible.

After configuring BIND for query logging per the examples given, you can send those messages to a syslog server (either local to the BIND server or on another host.

Walking you through that would be beyond the scope of this site, though. Something like this tutorial would be a good place to start, though:

Set up rsyslog to store syslog messages in MySQL

Using the name server itself (for instance BIND in Prix's response) loads seriously the software (which now has to format every request and to do an I/O) and depends on the software you use.

I suggest instead to capture the paquets (either on the machine itself or on another machine, with Ethernet port mirroring) and then to use a statistics program. For the capture, use tcpdump or tshark or pcapdump (one thing I like about pcapdump is that it does sampling). The Mother of DNS statistics program is DSC.

If you like RDBMS, DNSmezzo puts the result of the capture in a PostgreSQL database where you can study at will with SQL requests.

Just as an aside, even though some managed dns providers charge by the query, it is sometimes a guesstimate derived from sampling because it takes some serious processing to do exact counts when many domains are involved.