mysql and BIND 9.4.2

[Chris Hastie]

On 11/08/08 23:23, Johnson, James wrote:
> Thanks Chris, I'll look into this. Have you or anyone you know ever set
> something like this before? What I'm trying to do is replace our name
> servers, they will be Virtualized.
>
>
>   

I have several nameservers running from a replicated MySQL database, on
both Ubuntu and FreeBSD, and in the past on Debian. It's not really a
FreeBSD issue this, so you might be better off with the bind-dlz-testers
mailing list (http://bind-dlz.sourceforge.net/mailing_list.html).
There's a good deal of information on configuring bind-dlz on the
bind-dlz site, and a good deal of information on configuring replication
in MySQL on the MySQL site. Here are a few pointers from my experience:

Like any database project, spend some time thinking carefully about your
database schema before you start. As I recall, the examples on the
bind-dlz site place SOA records and other RRs in the same table. I
prefer to separate these out, eg

--
-- Table structure for table `dns_rr`
--

CREATE TABLE `dns_rr` (
  `rr_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `zone` varchar(255) NOT NULL DEFAULT '',
  `host` varchar(255) NOT NULL DEFAULT '@',
  `ttl` mediumint(8) unsigned NOT NULL DEFAULT '86400',
  `rr_type` enum('A','AAAA','MX','PTR','NS','TXT','CNAME','RP','SRV')
NOT NULL DEFAULT 'A',
  `mx_priority` smallint(5) unsigned DEFAULT NULL,
  `rr_data` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`rr_id`),
  KEY `zone` (`zone`),
  KEY `zone_host` (`zone`(250),`host`(250)),
  KEY `rr_type` (`rr_type`),
  KEY `zone_host_type` (`zone`(245),`host`(245),`rr_type`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `dns_soa`
--

CREATE TABLE `dns_soa` (
  `zone` varchar(255) NOT NULL DEFAULT '',
  `ttl` mediumint(8) unsigned NOT NULL DEFAULT '86400',
  `primary_ns` varchar(255) NOT NULL DEFAULT 'ns0.example.com.',
  `resp_person` varchar(255) DEFAULT 'hostmaster',
  `serial` bigint(20) unsigned DEFAULT '1',
  `refresh` mediumint(8) unsigned DEFAULT '3600',
  `retry` mediumint(8) unsigned DEFAULT '600',
  `expire` int(10) unsigned DEFAULT '2419200',
  `minimum` mediumint(8) unsigned DEFAULT '1800',
  `owner` varchar(30) NOT NULL DEFAULT '',
  `active` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`zone`),
  KEY `owner` (`owner`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

If you plan on running bind chroot bear in mind that if you connect to
MySQL with a unix socket, you need the socket to be in the chroot. An
alternative, with slightly more overhead, is to connect with TCP. But
don't forget that if you specify the host as 'localhost' the mysql
client will try to use unix sockets - you need to specify it as
127.0.0.1 to force TCP.

Spend some time tuning MySQL. In particular, make sure the query cache
is adequate. Query responses are quite small, so I've found reducing
query_cache_min_res_unit to 1k useful.

It may be worth considering running a separate instance of MySQL just
for bind so that it can be tuned to that specific purpose and the query
cache isn't filled with non bind related queries. Bear in mind that
storing your zone data in MySQL is considerably slower than bind's
default in memory system, so on a busy system you need to get every
ounce of performance out of MySQL.

Don't use a bind-dlz installation as a caching nameserver. Apart from
the usual reasons to keep caching resolvers and authoritative name
servers apart, firing recursive queries at bind massively increases the
number of sql queries - bind-dlz will have to execute queries in order
to work out that it is not authoritative for the requested domain. All
these queries on random domains will fill MySQL's query cache with stuff
that might not be asked again and you'll see a reduction in query cache
hit rate.

HTH

-- 
Chris Hastie
Find tree care advice at http://www.tree-care.info/