BIND DLZ

This same database schema is used for the PostgreSQL, MySQL and ODBC examples. It is simple, but very capable.

Below is a description of the "dns_records" table. This output was obtained by executing "describe dns_records;" from within the mysql utility after it connected to the database.

mysql> describe dns_records;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| zone        | text       | YES  |     | NULL    |       |
| host        | text       | YES  |     | NULL    |       |
| type        | text       | YES  |     | NULL    |       |
| data        | text       |      |     |         |       |
| ttl         | int(11)    | YES  |     | NULL    |       |
| mx_priority | text       | YES  |     | NULL    |       |
| refresh     | int(11)    | YES  |     | NULL    |       |
| retry       | int(11)    | YES  |     | NULL    |       |
| expire      | int(11)    | YES  |     | NULL    |       |
| minimum     | int(11)    | YES  |     | NULL    |       |
| serial      | bigint(20) | YES  |     | NULL    |       |
| resp_person | text       | YES  |     | NULL    |       |
| primary_ns  | text       | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

Below is a description of the indexes for the dns_records table. This output was obtained by executing "show index from dns_records;" from within the mysql utility.

mysql> show index from dns_records;
+-----------+----------+----------+------------+-----------+---------+-----------+--------+------+-------+
|Table      |Non_unique|Key_name  |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Comment|
+-----------+----------+----------+------------+-----------+---------+-----------+--------+------+-------+
|dns_records|        1 |host_index|          1 | host      |A        |         7 |     20 |NULL  |       |
|dns_records|        1 |zone_index|          1 | zone      |A        |         2 |     30 |NULL  |       |
|dns_records|        1 |type_index|          1 | type      |A        |         4 |      8 |NULL  |       |
+-----------+----------+----------+------------+-----------+---------+-----------+--------+------+-------+
3 rows in set (0.00 sec)

Below is a description of the "xfr_table" table. This output was obtained by executing "describe xfr_table;" from within the mysql utility. This table is only necessary if you will be supporting zone transfers from DLZ. See the Worst Practices section of the site for reasons you should not use zone transfers when using DLZ.

mysql> describe xfr_table;
+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| zone   | text |      |     |         |       |
| client | text |      |     |         |       |
+--------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Below is a description of the indexes for the xfr_table table. This output was obtained by executing "show index from xfr_table;" from within the mysql utility.

mysql> show index from xfr_table;
+---------+----------+-----------------+------------+-----------+---------+-----------+--------+------+-------+
| Table   |Non_unique|Key_name         |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Comment|
+---------+----------+-----------------+------------+-----------+---------+-----------+--------+------+-------+
|xfr_table|        1 |zone_client_index|          1 | zone      | A       |         2 |     30 | NULL |       |
|xfr_table|        1 |zone_client_index|          2 | client    | A       |         2 |     20 | NULL |       |
+---------+----------+-----------------+------------+-----------+---------+-----------+--------+------+-------+
2 rows in set (0.00 sec)

This configuration will provide the best performance because it uses the fewest queries to obtain the information it needs.

When setting up your configuration, pay very close attention to spaces! If you include a space where you shouldn't, or don't include a space where you should, either your query will not work with the database, or BIND may not be able to properly parse the query results. If in doubt, copy the SQL queries directly from this page (copy / paste).

dlz "Mysql zone" {
   database "mysql
   {host=localhost dbname=dns_data ssl=tRue}
   {select zone from dns_records where zone = '%zone%'}
   {select ttl, type, mx_priority, case when lower(type)='txt' then concat('\"', data, '\"')
        when lower(type) = 'soa' then concat_ws(' ', data, resp_person, serial, refresh, retry, expire, minimum)
        else data end from dns_records where zone = '%zone%' and host = '%record%'}";
};

If you must support zone transfers with DLZ, use the configuration below with this example schema. Notice the section of the configuration in GREEN. It is a pair of brackets with NO SPACES between it. It is important that the brackets have no spaces. We are telling the driver not to use an SQL query there. If there are any spaces between the brackets, the driver will think there is an SQL query to execute and then fail when it does try to run it.

dlz "Mysql zone" {
   database "mysql
   {host=localhost dbname=dns_data ssl=tRue}
   {select zone from dns_records where zone = '%zone%'}
   {select ttl, type, mx_priority, case when lower(type)='txt' then concat('\"', data, '\"')
        when lower(type) = 'soa' then concat_ws(' ', data, resp_person, serial, refresh, retry, expire, minimum)
        else data end from dns_records where zone = '%zone%' and host = '%record%'}
   {}
   {select ttl, type, host, mx_priority, case when lower(type)='txt' then
        concat('\"', data, '\"') else data end, resp_person, serial, refresh, retry, expire,
        minimum from dns_records where zone = '%zone%'}
   {select zone from xfr_table where zone = '%zone%' and client = '%client%'}";
};