HOWTO Setup Bind with DLZ, MySQL and replication

From Gentoo Linux Wiki

[edit] Introduction

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

[edit] Why?

Because 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 can write a webinterface 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?

[edit] Prerequisites

2 Servers (preferably in 2 different networks), with Bind (compiled with "dlz" and "mysql" USE-Flags) and MySQL

[edit] 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.

[edit] Emerging

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

emerge mysql

[edit] Replication

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

...
log-bin            = binlog
server-id          = 1
...

"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.

...
log-bin
server-id          = 2
replicate-do-db    = dns
...

"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 | NULL             | 
+------+-------------+-----------------+---------+---------+--------+-----------------------------------------------------------------------+------------------+

[edit] 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

[edit] 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`) VALUE (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`) VALUE (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`) VALUE (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`) VALUE (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`) VALUE (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`) VALUE (105, 'sample.com', 86400, 'A', 'www', NULL, '123.12.12.1', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

[edit] Bind setup

[edit] 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. Also, Bind won't compile with both the "berkdb" and "dlz" USE-Flags. Further information about Bind and the DLZ patch can be found at the bind dlz project on sourceforge.

...
net-dns/bind dlz mysql -threads -berkdb
...

emerge bind

[edit] DLZ setup

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

...
dlz "Mysql zone" {
   database "mysql
   {host=localhost dbname=dns user=dns pass=YourPasswordHere}
   {SELECT zone FROM records WHERE zone = '%zone%'}
   {SELECT ttl, type, mx_priority, IF(type = 'TXT', CONCAT('\"',data,'\"'), data) AS data
    FROM records
    WHERE zone = '%zone%' AND host = '%record%' AND type <> 'SOA' AND type <> 'NS'}
   {SELECT ttl, type, data, primary_ns, resp_contact, serial, refresh, retry, expire, minimum
    FROM records
    WHERE zone = '%zone%' AND (type = 'SOA' OR type='NS')}
   {SELECT ttl, type, host, mx_priority, IF(type = 'TXT', CONCAT('\"',data,'\"'), data) AS data, resp_contact, serial, refresh, retry, expire, minimum
    FROM records
    WHERE zone = '%zone%' AND type <> 'SOA' AND type <> 'NS'}
   {SELECT zone FROM xfr where zone='%zone%' AND client = '%client%'}";
};
...

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.

[edit] init.d

MySQL needs to be started prior to Bind since Bind tries to connect to the MySQL-Server at Startup. Startup fails if Bind can't connect to the MySQL-Server. You can solve this by adding mysql to the dependency in /etc/init.d/named:

depend() {
     need net mysql
     [...]
}