BIND: store zones in mysql (Dynamically Loadable Zones - BIND DLZ)

The ability of Berkeley Internet Name Daemon (BIND) to store DNS zones in the mysql database is not very well known and extremely poorly documented. The documentation is frozen at the time of inclusion of a separate DLZ patch in the main BIND branch, and this is BIND 9.4. * And 2005-2006. I will try to at least partially fill this gap by laying out the currently working instructions with examples under the habrakat. My description does not claim to be complete at all, but the simplest zone will allow you to register.
Separately, I want to note that DLZ does not only support mysql, the list of supported repositories is also under habrakat.


I'll start with the list of repositories that DLZ supports :

1. File system - all data is stored in file and directory names, structured in a certain way
2. Berkeley db
3. PostgreSQL
4. MySQL
5. ODBC (Firebird, Oracle, DB2, Sybase, SAPDB, MS SQL Server, etc.)
6. LDAP

To work with DLZ, bind must be compiled with DLZ support and the corresponding storage drivers. How to achieve this - see the manual for your OS.

Specifically, the mysql driver does not work with BIND compiled with support for threads on some operating systems, including linux. The list of operating systems in which the mysql driver can be used in multithreaded BIND is in the description of the mysql driver on the official DLZ website .

In gentoo we include the following USE for BIND with the following USE = dlz mysql -threads (the rest is your choice).

We create a database, for example, “dns”, in it a table, for example, “records” and a user who has the right to SELECT from this database. In general, dlz supports any version of the structure, since in the BIND config we will specify specific sql requests for receiving data. Here I give what is taken from official documentation and finalized to current realities (BIND 9.7.4_p1).

My version of the records table structure:

CREATE TABLE IF NOT EXISTS `records` (
  `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
  `zone` varchar (255) NOT NULL,
  `ttl` int (11) NOT NULL DEFAULT '86400',
  `type` varchar (255) NOT NULL,
  `host` varchar (255) NOT NULL DEFAULT '@',
  `mx_priority` int (11) DEFAULT NULL,
  `data` text,
  `resp_person` varchar (255) DEFAULT NULL,
  `serial` bigint (20) DEFAULT NULL,
  `refresh` int (11) DEFAULT NULL,
  `retry` int (11) DEFAULT NULL,
  `expire` int (11) DEFAULT NULL,
  `minimum` int (11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (` type`),
  KEY `host` (` host`),
  KEY `zone` (` zone`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT = 10;

Example zone data:

INSERT INTO `records` (` id`, `zone`,` ttl`, `type`,` host`, `mx_priority`,` data`, `resp_person`,` serial`, `refresh` , `retry`,` expire`, `minimum`) VALUES
(1, 'example.com', 86400, 'SOA', '@', NULL, 'ns1.example.com.', 'admin.example.com . ', 2011013101, 10800, 7200, 604800, 86400),
(2,' example.com ', 86400,' NS ',' @ ', NULL,' ns1.example.com. ', NULL, NULL, NULL, NULL, NULL, NULL),
(3, 'example.com', 86400, 'NS', '@', NULL, 'ns2.example.com.', NULL, NULL, NULL, NULL, NULL, NULL),
(4, 'example.com', 86400, 'A', '@', NULL, '192.168.0.35', NULL, NULL, NULL, NULL, NULL, NULL),
(5, 'example.com', 86400 , 'A','ns1', NULL, '192.168.0.36', NULL, NULL, NULL, NULL, NULL, NULL),
(6, 'example.com', 86400, 'A', 'ns2', NULL, '192.168.0.37', NULL, NULL, NULL, NULL, NULL, NULL);

In named.conf, write:

dlz "Mysql zone" {
   database "mysql
   {dbname = dns user = username pass = password socket = / var / run / mysqld / mysqld.sock}
   {select zone from records where zone = '$ zone $'}
   {select ttl, type, mx_priority, case when lower (type) = 'txt' then concat ('\ "', data, '\"')
        else data end from records where zone = '$ zone $' and host = '$ record $'
        and not (type = 'SOA' or type = 'NS')}
   {select ttl, type, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum
        from records where (type = 'SOA' or type = 'NS') and zone = '$ zone $'} ";
};

"Mysql zone" is simply the name of a piece of the config; it has nothing to do with the name of the zone itself.

We specify $ zone $ and $ record $ in this way, these are variables in the place of which DLZ itself will substitute the name of the zone and the record when requested.

In the third line you can write the following parameters:
dbname =
port =
host =
user =
pass =
socket =
compress =
ssl =
space =

The names seem to speak for themselves. Well, maybe, except for "space =", but what it is - I do not know. In the documentation, in addition to being a Boolean parameter, nothing is said about it.

I will briefly describe the requests. In the documentation they are described in more detail.

• select zone from records where zone = '$ zone $' - a request by which BIND understands whether it supports this zone at all.
• select ttl, type, mx_priority, case when lower (type) = 'txt' then concat ('\ "', data, '\"')
  else data end from records where zone = '$ zone $' and host = '$ record $ '
  and not (type =' SOA 'or type =' NS ')} - request to get a record about a specific host in the zone.
• select ttl, type, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum
  from records where (type = 'SOA' or type = 'NS') and zone = '$ zone $' as seen from the request - here we get all authoritarian records about the zone.

The most unpleasant and incomprehensible problem that I encountered when focusing on documents available on the Internet is the constant appearance in the logs of entries like:

Jun 22 19: 51: 10.142 dns_rdata_fromtext: buffer-0xbfffe390: 1: near eof:
unexpected end of input
Jun 22 19: 51: 10.143 dns_sdlz_putrr returned error. Error code was:
unexpected end of input
Jun 22 19: 51: 10.146 dns_rdata_fromtext: buffer-0xbfffe0d0: 1: near eof:
unexpected end of input
Jun 22 19: 51: 10.147 dns_sdlz_putrr returned error. Error code was:
unexpected end of input

and inactive zone.

These lines indicate that the DLZ receives data in the wrong format or order as it expects. A and NS records must have ttl, type and data, in that order and only them (the remaining fields are NULL). MX - all of the above + mx_priority, SOA - ttl, type, data, responsible_person, refresh, retry, expire, minimum. (The information in this paragraph is dug from the mail archive ).

There are two more query options related to transferring (xfer) zones, I did not use them, so I refer to the official documentation.

It helps a lot for debugging, strangely enough, the BIND debugging mode (-d 9). In gentoo, write OPTIONS = "- d 9" in /etc/conf.d/named.

I will be very glad if in the comments, users of the Habr share their experience and configs for working with BIND DLZ. I am only at the initial stage of tuning, I have a lot to do. For example, reverse zones.