Bind with DLZ, MySQL and replication

This article describes a Master/Slave setup of 2 Bind nameservers using DLZ (Dynamically Loadable Zones) with MySQL as zone-record source.

The advantage of using it is that it can make managing your zones much easier: If you are running two nameservers just for a fistful of domains it may be acceptable to manage your zone updates manually by editing your zone-files. But what if your nameservers have to handle hundreds of zones?

Sure, you could write a web interface that handles your zone-files for you. But with a growing number of domains, you will notice that the easiest way for managing your zone, even if you still use zone-files, is to put your zone-records in a SQL database an generate the zonefiles from there.

So why not use your database without detour?

Prerequisites
2 Servers (preferably in 2 different networks)

MySQL setup
The MySQL setup is quite simple: One master MySQL server for the master Bind server and one slave MySQL server for the slave Bind server.

Emerging
Start with emerging MySQL on both, the master and the slave server:

Replication
To allow replication, you first need to set some config variables in both servers:

"log-bin" tells the Master the name of the binary logfile where all changes made to its databases are written to.

The Master doesn't need "server-id" "1", you only have to make sure that both servers uses different server-ids.

"replicate-do-db" tells the Slave to ignore changes to all databases but "dns"

Now create a new database "dns" and a user "dns" on both MySQL servers CREATE DATABASE dns; GRANT ALL ON dns.* TO 'dns'@'localhost' IDENTIFIED BY 'YourPasswordHere'; On the Master, create a user "dns_rep" which will later be used by the slave to replicate the database. GRANT REPLICATION SLAVE ON *.* to 'dns_rep'@'YourSlaveHostHere' identified by 'YourPasswordHere'; And now tell the Slave to start replication CHANGE MASTER TO                 master_host='YourMastersHostnameHere', master_user='dns_rep', master_password='YourPasswordHere'; START SLAVE;

If your Slave has successfully connected to the Master your MySQL processlist on the Slave should look like this: SHOW PROCESSLIST; +--+-++-+-++---+-+ +--+-++-+-++---+-+ +--+-++-+-++---+-+
 * Id  | User        | Host       | db      | Command | Time   | State                                                                 | Info        |
 * 13 | system user |           | NULL    | Connect | 197946 | Waiting for master to send event                                      | NULL        |
 * 14 | system user |           | NULL    | Connect | 197632 | Has read all relay log; waiting for the slave I/O thread to update it |             |

Table Layout
Since the replication is up and running, you can now create all needed tables on the Master. Remember: All changes made in the database "dns" on the Master will now be replicated on the Slave.

You will create two tables, "records" and "xfr": CREATE TABLE `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,  `primary_ns` varchar(255) default NULL,  `resp_contact` 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`) ); CREATE TABLE `xfr` ( `zone` varchar(255) NOT NULL,  `client` varchar(255) NOT NULL,  KEY `zone` (`zone`),  KEY `client` (`client`) ); The table "records" holds all information about your zones, "xfr" can be used to allow AXFR/IXFR zone transfers per zone and host

Sample
A zone with SOA,NS,A and MX records could look like this: +-++---+--+--+-+---++---+++--+---++ +-++---+--+--+-+---++---+++--+---++ +-++---+--+--+-+---++---+++--+---++
 * id | zone       | ttl   | type | host | mx_priority | data          | primary_ns | resp_contact      | serial     |refresh |retry |expire |minimum |
 * 100 | sample.com | 86400 | SOA | @    |        NULL | NULL          | ns1.ns.com.| hostmaster.ns.com.| 2007080601 |  10800 | 7200 |604800 |  86400 |
 * 101 | sample.com | 86400 | NS  | @    |        NULL | ns1.ns.com.   | NULL       | NULL              |       NULL |   NULL | NULL |  NULL |   NULL |
 * 102 | sample.com | 86400 | NS  | @    |        NULL | ns2.ns.com.   | NULL       | NULL              |       NULL |   NULL | NULL |  NULL |   NULL |
 * 103 | sample.com | 86400 | MX  | @    |          10 | mail.mail.com.| NULL       | NULL              |       NULL |   NULL | NULL |  NULL |   NULL |
 * 104 | sample.com | 86400 | A   | @    |        NULL | 123.12.12.1   | NULL       | NULL              |       NULL |   NULL | NULL |  NULL |   NULL |
 * 105 | sample.com | 86400 | A   | www  |        NULL | 123.12.12.1   | NULL       | NULL              |       NULL |   NULL | NULL |  NULL |   NULL |

Equivalent SQL-Code of the table above:

INSERT INTO `records` (`id`, `zone`, `ttl`, `type`, `host`, `mx_priority`, `data`, `primary_ns`, `resp_contact`, `serial`, `refresh`, `retry`, `expire`, `minimum`) VALUES (100, 'sample.com', 86400, 'SOA', '@', NULL, NULL, 'ns1.ns.com.', 'hostmaster.ns.com.', 2007080601, 10800, 7200, 604800, 86400);

INSERT INTO `records` (`id`, `zone`, `ttl`, `type`, `host`, `mx_priority`, `data`, `primary_ns`, `resp_contact`, `serial`, `refresh`, `retry`, `expire`, `minimum`) VALUES (101, 'sample.com', 86400, 'NS', '@', NULL, 'ns1.ns.com.', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

INSERT INTO `records` (`id`, `zone`, `ttl`, `type`, `host`, `mx_priority`, `data`, `primary_ns`, `resp_contact`, `serial`, `refresh`, `retry`, `expire`, `minimum`) VALUES (102, 'sample.com', 86400, 'NS', '@', NULL, 'ns2.ns.com.', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

INSERT INTO `records` (`id`, `zone`, `ttl`, `type`, `host`, `mx_priority`, `data`, `primary_ns`, `resp_contact`, `serial`, `refresh`, `retry`, `expire`, `minimum`) VALUES (103, 'sample.com', 86400, 'MX', '@', 10, 'mail.mail.com.', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

INSERT INTO `records` (`id`, `zone`, `ttl`, `type`, `host`, `mx_priority`, `data`, `primary_ns`, `resp_contact`, `serial`, `refresh`, `retry`, `expire`, `minimum`) VALUES (104, 'sample.com', 86400, 'A', '@', NULL, '123.12.12.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

INSERT INTO `records` (`id`, `zone`, `ttl`, `type`, `host`, `mx_priority`, `data`, `primary_ns`, `resp_contact`, `serial`, `refresh`, `retry`, `expire`, `minimum`) VALUES (105, 'sample.com', 86400, 'A', 'www', NULL, '123.12.12.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

Emerging
Start with emerging Bind on both, the Master and the Slave server, remember to set the "dlz" and "mysql" USE-Flags. In addition, you should remove the "thread"-USE flag since threads are not usable when you compile Bind with DLZ and MySQL support. Further information can be found here

DLZ setup
The DLZ configurations for the Master and the Slave are the same. Just edit your Bind config-file and add the following:

The 1. line holds the parameters needed to connect to your MySQL server

The 2. line is the query used to determine if your DNS server is authoritative for the respective domain.

The 3. line is used to get the SOA and NS records.

The 4. line is the query used to get all zone records besides the SOA and NS records.

The 5. line queries the table "xfr" for zone/client-IP combinations allowed to initiate zone transfers.