Re: Bind with mysql backend
"Mike Ragusa" <[email protected]>
| Newsgroups | gmane.network.dns.bind9.dlz |
|---|---|
| Message-ID | <[email protected]> |
This is the configuration I am using now. I finally got zone transfers
working and all of my data imported and working properly.
mysql> show tables;
+---------------+
| Tables_in_dns |
+---------------+
| data_count |
| dns_records |
| xfr_table |
+---------------+
3 rows in set (0.00 sec)
mysql> describe dns_records;
+-------------+----------+------+-----+---------+-------+
| 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 | char(4) | NO | | 3600 | |
| mx_priority | char(4) | YES | | NULL | |
| refresh | char(11) | YES | | NULL | |
| retry | char(11) | YES | | NULL | |
| expire | char(11) | YES | | NULL | |
| minimum | char(11) | YES | | NULL | |
| serial | char(20) | YES | | NULL | |
| resp_person | text | YES | | NULL | |
| primary_ns | text | YES | | NULL | |
+-------------+----------+------+-----+---------+-------+
13 rows in set (0.01 sec)
mysql> describe xfr_table;
+--------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| zone | text | YES | MUL | NULL | |
| client | text | YES | MUL | NULL | |
+--------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
This is my named.conf SQL statements which i grabbed from the DLZ site.
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%'}";
};
I am currently in the process of writing up a howto for freebsd and
including my scripts i used for data importation and what not. What i would
like to know right now is:
1. What GUI or command line interface do people use to manage the data
inside of bind-dlz?
2. Can bind-dlz take advantage of zone options?
3. Does bind-dlz understand views?
Thanks
Mike.
On Tue, Mar 4, 2008 at 4:14 PM, Graham Weldon <[email protected]>
wrote:
> 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]
> 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
>
>
-------------------------------------------------------------------------
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