From owner-freebsd-questions@FreeBSD.ORG Tue Aug 12 07:59:41 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id EF5ED1065670 for ; Tue, 12 Aug 2008 07:59:40 +0000 (UTC) (envelope-from lists@oak-wood.co.uk) Received: from ash.oak-wood.co.uk (ash.oak-wood.co.uk [62.3.200.116]) by mx1.freebsd.org (Postfix) with ESMTP id 583B48FC16 for ; Tue, 12 Aug 2008 07:59:40 +0000 (UTC) (envelope-from lists@oak-wood.co.uk) Received: from localhost (localhost [127.0.0.1]) by ash.oak-wood.co.uk (Postfix) with ESMTP id CECA0BC5C6 for ; Tue, 12 Aug 2008 07:59:38 +0000 (GMT) Received: from ash.oak-wood.co.uk ([127.0.0.1]) by localhost (ash.oak-wood.co.uk [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 79604-02 for ; Tue, 12 Aug 2008 07:59:38 +0000 (GMT) Received: from [192.168.37.233] (bluebell.thegrove.oak-wood.co.uk [192.168.37.233]) by ash.oak-wood.co.uk (Postfix) with ESMTPA id 1F6E7BBFA4 for ; Tue, 12 Aug 2008 07:59:38 +0000 (GMT) Message-ID: <48A142E9.2090906@oak-wood.co.uk> Date: Tue, 12 Aug 2008 08:59:37 +0100 From: Chris Hastie User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.1.16) Gecko/20080724 Thunderbird/2.0.0.16 Mnenhy/0.7.5.666 MIME-Version: 1.0 To: freebsd-questions@freebsd.org References: <48A0B763.4020305@oak-wood.co.uk> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Subject: Re: mysql and BIND 9.4.2 X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 12 Aug 2008 07:59:41 -0000 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/