Re: Bind with mysql backend

I am personally using a non-standard DB Structure, although its closely
modelled off the sample one.
Reason for doing this alternate version is to bring the database
structure in line with naming conventions for CakePHP, to ensure we can
manage the data within the database easily, and in a useful manner.
 
mysql> show tables;
+---------------------+
| Tables_in_dns       |
+---------------------+
| dns_queries         |
| dns_query_summaries |
| dns_records         |
| dns_transfers       |
| dns_zones           |
+---------------------+
8 rows in set (0.00 sec)
 
mysql> desc dns_zones;
+-------------+------------------+------+-----+---------+---------------
-+
| Field       | Type             | Null | Key | Default | Extra
|
+-------------+------------------+------+-----+---------+---------------
-+
| id          | int(11) unsigned | NO   | PRI | NULL    | auto_increment
|
| domain_id   | int(11)          | NO   |     | NULL    |
|
| domain_name | varchar(255)     | NO   | MUL | NULL    |
|
+-------------+------------------+------+-----+---------+---------------
-+
3 rows in set (0.00 sec)
 
mysql> desc dns_records;
+-------------+---------------------+------+-----+---------------------+
----------------+
| Field       | Type                | Null | Key | Default             |
Extra          |
+-------------+---------------------+------+-----+---------------------+
----------------+
| id          | int(11) unsigned    | NO   | PRI | NULL                |
auto_increment |
| dns_zone_id | int(11) unsigned    | NO   | MUL | NULL                |
|
| ttl         | int(11) unsigned    | NO   |     | 21600               |
|
| type        | varchar(10)         | NO   |     | NULL                |
|
| host        | varchar(255)        | NO   |     | @                   |
|
| mx_priority | int(11) unsigned    | YES  |     | NULL                |
|
| data        | text                | YES  |     | NULL                |
|
| contact     | varchar(255)        | YES  |     | NULL                |
|
| serial      | bigint(20) unsigned | YES  |     | NULL                |
|
| refresh     | int(11) unsigned    | YES  |     | NULL                |
|
| retry       | int(11) unsigned    | YES  |     | NULL                |
|
| expire      | int(11) unsigned    | YES  |     | NULL                |
|
| minimum     | int(11) unsigned    | YES  |     | NULL                |
|
| updated     | timestamp           | NO   |     | 0000-00-00 00:00:00 |
|
+-------------+---------------------+------+-----+---------------------+
----------------+
14 rows in set (0.00 sec)
 
mysql> desc dns_queries;
+-------------+------------------+------+-----+-------------------+-----
-----------+
| Field       | Type             | Null | Key | Default           |
Extra          |
+-------------+------------------+------+-----+-------------------+-----
-----------+
| id          | int(11) unsigned | NO   | PRI | NULL              |
auto_increment |
| domain_name | varchar(255)     | NO   |     | NULL              |
|
| timestamp   | timestamp        | NO   |     | CURRENT_TIMESTAMP |
|
+-------------+------------------+------+-----+-------------------+-----
-----------+
3 rows in set (0.00 sec)
 
mysql> desc dns_transfers;
+-------------+------------------+------+-----+---------+---------------
-+
| Field       | Type             | Null | Key | Default | Extra
|
+-------------+------------------+------+-----+---------+---------------
-+
| id          | int(11) unsigned | NO   | PRI | NULL    | auto_increment
|
| dns_zone_id | int(11) unsigned | NO   | MUL | NULL    |
|
| client      | int(11) unsigned | NO   | MUL | NULL    |
|
+-------------+------------------+------+-----+---------+---------------
-+
3 rows in set (0.01 sec)

 
And finally, my DLZ config... (coloured to match the coloring used on
the MySQL section of the DLZ website
---------------------------
dlz "MySQL Zone" {
        database "mysql
        {host=localhost dbname=dns_db user=dns_user pass=dns_pass}
        {SELECT DISTINCT 'DOMAIN_EXISTS' FROM `dns_zones` WHERE
`domain_name` = '%zone%'}
        {SELECT `ttl`,`type`,`mx_priority`,IF (`type` = 'TXT',
concat('\"', `data`, '\"'), `data`) FROM `dns_records` LEFT JOIN
`dns_zones` ON `dns_records`.`dns_zone_id` = `dns_zones`.`id` WHERE
`domain_name`='%zone%' AND `host`='%record%' AND NOT (`type`='SOA' OR
`type`='NS')}
        {SELECT
`ttl`,`type`,`mx_priority`,`data`,`contact`,`serial`,`refresh`,`retry`,`
expire`,`minimum` FROM `dns_records` LEFT JOIN `dns_zones` ON
`dns_records`.`dns_zone_id`=`dns_zones`.`id` WHERE
`domain_name`='%zone%' AND (`type`='SOA' OR `type`='NS') ORDER BY `type`
DESC}
        {SELECT `ttl`,`type`,`mx_priority`,IF (`type` = 'TXT',
concat('\"', `data`, '\"'),
`data`),`contact`,`serial`,`refresh`,`retry`,`expire`,`minimum` FROM
`dns_records` LEFT JOIN `dns_zones` ON
`dns_records`.`dns_zone_id`=`dns_zones`.`id` WHERE
`domain_name`='%zone%' AND NOT (`type`='SOA' OR `type`='NS')}
        {SELECT DISTINCT 'ALLOW_TRANSFER' FROM `dns_transfers` LEFT JOIN
`dns_zones` ON `dns_transfers`.`dns_zone_id` = `dns_zones`.`id` WHERE
`domain_name`='%zone%' AND `client`='%client%'}
        {INSERT INTO `dns_queries` SET `domain_name`='%zone%'}";
};
---------------------------


 
Cheers,
 
Graham Weldon
 
Fluid Lino
(02) 4927 5337
(0407) 017 293
[email protected] <mailto:[email protected]> 
www.fluidlino.com.au <http://www.fluidlino.com.au/> 
 

________________________________

From: [email protected]
[mailto:[email protected]] On Behalf Of
Mike Ragusa
Sent: Wednesday, 5 March 2008 3:25 AM
To: [email protected]; [email protected]
Subject: [Bind-dlz-testers] Bind with mysql backend


I have been using the default schema with bind-dlz and mysql as noted on
their website. I was wondering what people use to manage this
information and if they used another database schema that offered better
performance or ablity to manage the data better. 

Default Schema:
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| zone        | text       | YES  | MUL | NULL    |       | 
| host        | text       | YES  | MUL | NULL    |       | 
| type        | text       | YES  | MUL | NULL    |       | 
| data        | text       | YES  |     | NULL    |       | 
| ttl         | int(11)    | NO   |     | 3600    |       | 
| 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    |       | 
+-------------+------------+------+-----+---------+-------+


Default MySQL for named.conf:

dlz "Mysql zone" {
   database "mysql
   {host=localhost dbname=dns user=dns pass=bind-dlz-test}
   {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%'}";
};

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/

_______________________________________________
Bind-dlz-testers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bind-dlz-testers