BIND DLZ scheduled.

The MySQL views based on a UNION query can be slow to respond.

mysql> select ttl, type, host, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum from dns_records where zone = 'myzone.example.com' and view in ('DEFAULT', 'ALL');
+--------+------+------------+-------------+--------------------------+-------------+-----------+---------+-------+---------+---------+
| ttl    | type | host       | mx_priority | data                     | resp_person | serial    | refresh | retry | expire  | minimum |
+--------+------+------------+-------------+--------------------------+-------------+-----------+---------+-------+---------+---------+
| 172800 | A    | dns1       |             | 192.168.2.1              |             |           |         |       |         |         |
| 172800 | NS   | @          |             | dns1.myzone.example.com. |             |           |         |       |         |         |
| 172800 | A    | test       |             | 192.168.2.2              |             |           |         |       |         |         |
| 172800 | SOA  | @          |             | dns1.myzone.example.com. | hostmaster. | 730170427 | 21600   | 3600  | 3600000 | 172800  |
+--------+------+------------+-------------+--------------------------+-------------+-----------+---------+-------+---------+---------+
7 rows in set (2.16 sec)

But the exact same query using 2 seperate and more simple views (no union to construct them) you get your results much quicker :

mysql> select ttl, type, host, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum from v_dns_clients where zone = 'myzone.example.com' and view in ('DEFAULT', 'ALL')
    -> UNION
    -> select ttl, type, host, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum from v_dns_zones where zone = 'myzone.example.com' and view in ('DEFAULT', 'ALL');
+--------+------+------------+-------------+--------------------------+-------------+-----------+---------+-------+---------+---------+
| ttl    | type | host       | mx_priority | data                     | resp_person | serial    | refresh | retry | expire  | minimum |
+--------+------+------------+-------------+--------------------------+-------------+-----------+---------+-------+---------+---------+
| 172800 | A    | dns1       |             | 192.168.2.1              |             |           |         |       |         |         |
| 172800 | NS   | @          |             | dns1.myzone.example.com. |             |           |         |       |         |         |
| 172800 | A    | test       |             | 192.168.2.2              |             |           |         |       |         |         |
| 172800 | SOA  | @          |             | dns1.myzone.example.com. | hostmaster. | 730170427 | 21600   | 3600  | 3600000 | 172800  |
+--------+------+------------+-------------+--------------------------+-------------+-----------+---------+-------+---------+---------+
7 rows in set (0.88 sec)

In order to avoid this kind of issue, you can build a "materialized view". It'll just consist of a table regularly updated with the data we need for BIND. The frequency for that table to refresh can be adjusted to your need.

1. Creation of a new table

USE ona_default;
DROP TABLE vm_dns_records;
CREATE TABLE vm_dns_records (
  ttl      int(11) unsigned,
  type     varchar(15)   ,
  host     varchar(255)  DEFAULT NULL,
  mx_priority   varbinary(11) ,
  data     longtext      DEFAULT NULL,
  resp_person   varchar(256)  ,
  serial   varbinary(11) ,
  refresh  varbinary(11) ,
  retry    varbinary(11) ,
  expire   varbinary(11) ,
  minimum  varbinary(11) ,
  zone     varchar(255)  ,
  view     varchar(255)  ,
  INDEX vm_dns_records_idx_host (host),
  INDEX vm_dns_records_idx_data (data(16))
);

2. Creation of the procedure that'll take care of refreshing the data

DROP PROCEDURE refresh_vm_dns_records;
DELIMITER $$
CREATE PROCEDURE refresh_vm_dns_records(OUT rc INT)
BEGIN
 
  TRUNCATE TABLE vm_dns_records;
 
  INSERT INTO vm_dns_records 
  SELECT (case when (dns.ttl > 0) then dns.ttl else domains.default_ttl end) AS ttl, dns.type AS type,
    cast((case when (dns.type = 'ptr') then trim(trailing concat('.',domains.name) from concat_ws('.',(interfaces.ip_addr % 256),((interfaces.ip_addr >> 8) % 256),((interfaces.ip_addr >> 16) % 256),((interfaces.ip_addr >> 24) % 256),'in-addr.arpa')) when (dns.name = '') then '@' else dns.name end) as char charset utf8) AS host,  
    (case when (dns.type = 'mx') then dns.mx_preference else '' end) AS mx_priority,
    cast((case when (dns.type = 'txt') then concat('"',dns.txt,'"') when (dns.type = 'srv') then concat_ws(' ',dns.srv_pri,dns.srv_weight,dns.srv_port,(select concat(dns2.name,'.',domains.name,'.') from (dns dns2 join domains on((domains.id = dns2.domain_id))) where (dns.dns_id = dns2.id))) when (dns.type in ('ptr','cname','mx','ns')) then (select concat(dns2.name,'.',domains.name,'.') from (dns dns2 join domains on((domains.id = dns2.domain_id))) where (dns.dns_id = dns2.id)) when (dns.type = 'a') then inet_ntoa(interfaces.ip_addr) collate utf8_general_ci end) as char charset utf8) AS data,
    '' AS resp_person, '' AS serial, '' AS refresh, '' AS retry, '' AS expire, '' AS minimum, domains.name AS zone, dns_views.name AS view 
  FROM (
         ((dns join domains on ((dns.domain_id = domains.id)) )
           join dns_views on ((dns.dns_view_id = dns_views.id)) )
           left join interfaces on ((interfaces.id = dns.interface_id)) )
  WHERE ((dns.ebegin > 0) and (now() >= dns.ebegin)) 
  UNION 
  SELECT 
    domains.default_ttl AS ttl, 'SOA' AS type, '@' AS host, '' AS mx_priority,
    concat(trim(trailing '.' from domains.primary_master),'.') AS data,
    concat(trim(trailing '.' from domains.admin_email),'.') AS resp_person,
    domains.serial AS serial, domains.refresh AS refresh, domains.retry AS retry, domains.expiry AS expire,
    domains.minimum AS minimum, domains.name AS zone, 'ALL' AS view
  FROM (domains join dns_server_domains on 
        (( (domains.id = dns_server_domains.domain_id) and (dns_server_domains.role in ('master','slave')))) );
 
  SET rc=0;
END;
$$
DELIMITER ;

3. Schedule the regular execution of the procedure.

It can even be issued every 30 seconds since the procedure takes about 3 seconds only, here it's done every 2 minutes.

-- The scheduler is only available since version 5.1.6 of MySQL
-- don’t forget to declare it in /etc/my.cnf so it gets activated on startup :
-- [mysqld]
-- event_scheduler=ON
 
SET GLOBAL event_scheduler = 1;
 
-- Here we schedule the procedure to be executed every 2 minutes from now + 1 second for the first execution.
DROP EVENT event_refresh_vm_dns_records;
CREATE EVENT event_refresh_vm_dns_records
  ON SCHEDULE EVERY 2 MINUTE
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 SECOND
  DO CALL refresh_vm_dns_records(@rc);

4. A few commands to control that everything is running fine

show variables like 'event_scheduler';
select count(*) from vm_dns_records;
show procedure status;
select name, body, execute_at, interval_value, interval_field, created, last_executed, starts, ends, status from mysql.event;

5. Finally, you just have to adapt your named.conf file

Just replace the view name dns_records by the name of our new table vm_dns_records

dlz "ONA Default" {
        database "mysql {host=localhost dbname=ona_default user=ona_sys pass=******** ssl=tRue}
        {select zone from vm_dns_records where zone = '$zone$' and type = 'SOA' limit 1}
        {select ttl, type, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum
                from vm_dns_records where zone = '$zone$' and host = '$record$' and view in ('DEFAULT', 'ALL')}
        {}
        {select ttl, type, host, mx_priority, data, resp_person, serial, refresh, retry, expire, minimum
                from vm_dns_records where zone = '$zone$' and view in ('DEFAULT', 'ALL')}
        {select zone from xfr_table where zone = '$zone$' and client = '$client$'}";
};

Before (bind using the dns_records view you might have had very slow replies :

[root@dns1 ~]# dig @localhost test.myzone.example.com
... 
;; ANSWER SECTION:
test.myzone.example.com. 172800  IN      A       192.168.2.2
...
;; Query time: 1383 msec
[root@dns1 ~]#

Now it should reply in less than a msec :

[root@dns1 ~]# dig @localhost test.myzone.example.com
... 
;; ANSWER SECTION:
test.myzone.example.com. 172800  IN      A       192.168.2.2
...
;; Query time: 1 msec
[root@dns1 ~]#