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

Berkeley's Internet Name Daemon (BIND) ability to store DNS zones in the mysql database is not very well known and extremely poorly documented. Documentation is frozen at the time of the 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, having laid out the workers at the moment with instructions and examples under the habrakat. My description does not claim to be complete, but it will allow to register the simplest zone.
Separately, I want to note that DLZ supports not only mysql, the list of supported repositories is also under habrakat.


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

1. File system - all data is stored in the names of files and directories, a certain way structured
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 appropriate 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 (threads), on some operating systems, including linux. The list of OS in which the mysql driver can be used in multi-thread 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 up to you).

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 variant of the structure, since in the BIND config we will specify specific sql queries for receiving data. Here I bring what is taken from official documentation and refined to current realities (BIND 9.7.4_p1).

My version of the records table structure is:

 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; 

Sample 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);

We register in named.conf:

 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 just the name of a piece of the config file, it has no relation to the name of the zone itself.

$ zone $ and $ record $ are written this way, these are the variables to which DLZ itself will substitute the name of the zone and the records upon request.

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, except that it is a boolean parameter, nothing is said about it.

Requests will describe briefly. The documentation describes them in more detail.

• select zone from records where zone = '$ zone $' is a request with the help of 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 an entry for 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 the authoritative records about the zone.

The most unpleasant and incomprehensible problem that I encountered, focusing on the documents available on the Internet, is the constant appearance in the logs of records 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 non-working 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 other 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 was dug out of the mail archive ).

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

It really helps to debug, oddly enough, the BIND debug mode (-d 9). In gentoo, write OPTIONS = "- d 9" in /etc/conf.d/named.

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