Skip to content

Instantly share code, notes, and snippets.

@itxx00

itxx00/dns.sql

Created May 14, 2014 15:16
Show Gist options
  • Save itxx00/aa94502bf7148977467c to your computer and use it in GitHub Desktop.
Save itxx00/aa94502bf7148977467c to your computer and use it in GitHub Desktop.
Download ZIP
dns bind mysql
Raw
dns.sql
-- MySQL dump 10.13 Distrib 5.1.61, for redhat-linux-gnu (x86_64)
--
-- Host: localhost Database: dnsdata
-- ------------------------------------------------------
-- Server version 5.1.61
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `dns_records`
--
DROP TABLE IF EXISTS `dns_records`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dns_records` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`zone` varchar(255) NOT NULL,
`host` varchar(255) NOT NULL DEFAULT '@',
`type` enum('MX','CNAME','NS','SOA','A','PTR') NOT NULL,
`data` varchar(255) DEFAULT NULL,
`ttl` int(11) NOT NULL DEFAULT '800',
`view` enum('TEL','ANY') NOT NULL,
`mx_priority` int(11) DEFAULT NULL,
`refresh` int(11) NOT NULL DEFAULT '28800',
`retry` int(11) NOT NULL DEFAULT '14400',
`expire` int(11) NOT NULL DEFAULT '86400',
`minimum` int(11) NOT NULL DEFAULT '86400',
`serial` bigint(20) NOT NULL DEFAULT '2009122300',
`resp_person` varchar(64) NOT NULL DEFAULT 'wdlinux.cn',
`primary_ns` varchar(64) NOT NULL DEFAULT 'ns1.wdlinux.cn.',
`second_ns` varchar(64) NOT NULL DEFAULT 'ns2.wdlinux.cn.',
`data_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `host` (`host`),
KEY `zone` (`zone`)
) ENGINE=MyISAM AUTO_INCREMENT=215 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dns_records`
--
LOCK TABLES `dns_records` WRITE;
/*!40000 ALTER TABLE `dns_records` DISABLE KEYS */;
INSERT INTO `dns_records` VALUES (202,'wdlinux.cn','@','SOA','wdlinux.cn.',86400,'TEL',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(203,'wdlinux.cn','@','SOA','wdlinux.cn.',86400,'ANY',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(204,'wdlinux.cn','@','NS','ns1.wdlinux.cn.',86400,'TEL',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(205,'wdlinux.cn','@','NS','ns1.wdlinux.cn.',86400,'ANY',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(206,'wdlinux.cn','@','NS','ns2.wdlinux.cn.',86400,'TEL',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(207,'wdlinux.cn','@','NS','ns2.wdlinux.cn.',86400,'ANY',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(208,'wdlinux.cn','ns1','A','192.168.1.91',86400,'TEL',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(209,'wdlinux.cn','ns1','A','192.168.1.91',86400,'ANY',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(210,'wdlinux.cn','ns2','A','192.168.1.92',86400,'TEL',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(211,'wdlinux.cn','ns2','A','192.168.1.92',86400,'ANY',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(212,'wdlinux.cn','www','A','192.168.1.240',86400,'TEL',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0),(213,'wdlinux.cn','www','A','192.168.1.240',86400,'ANY',NULL,28800,14400,86400,86400,2009122300,'root.wdlinux.cn.','ns1.wdlinux.cn.','ns2.wdlinux.cn.',0);
/*!40000 ALTER TABLE `dns_records` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `domain_list`
--
DROP TABLE IF EXISTS `domain_list`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domain_list` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`domain` varchar(100) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `domain_list`
--
LOCK TABLES `domain_list` WRITE;
/*!40000 ALTER TABLE `domain_list` DISABLE KEYS */;
INSERT INTO `domain_list` VALUES (7,'wdlinux.cn'),(1,'xingxing.com');
/*!40000 ALTER TABLE `domain_list` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `telecom`
--
DROP TABLE IF EXISTS `telecom`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `telecom` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`zone` varchar(255) NOT NULL,
`host` varchar(255) NOT NULL DEFAULT '@',
`type` enum('MX','NS','CNAME','SOA','A','PTR') NOT NULL DEFAULT 'A',
`data` varchar(255) DEFAULT NULL,
`ttl` int(11) NOT NULL DEFAULT '800',
`mx_priority` int(11) DEFAULT NULL,
`refresh` int(11) NOT NULL DEFAULT '28800',
`retry` int(11) NOT NULL DEFAULT '14400',
`expire` int(11) NOT NULL DEFAULT '86400',
`minimum` int(11) NOT NULL DEFAULT '86400',
`serial` bigint(20) NOT NULL DEFAULT '2012080700',
`resp_person` varchar(64) NOT NULL DEFAULT 'root.cache.51web.com.',
`primary_ns` varchar(64) NOT NULL DEFAULT 'ns1.cache.51web.com.',
`second_ns` varchar(64) NOT NULL DEFAULT 'ns2.cache.51web.com.',
`data_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `zone` (`zone`),
KEY `type` (`type`),
KEY `host` (`host`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `telecom`
--
LOCK TABLES `telecom` WRITE;
/*!40000 ALTER TABLE `telecom` DISABLE KEYS */;
INSERT INTO `telecom` VALUES (1,'cache.51web.com','@','SOA','cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(2,'cache.51web.com','@','NS','ns1.cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(3,'cache.51web.com','@','NS','ns2.cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(4,'cache.51web.com','ns1','A','118.123.12.62',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(5,'cache.51web.com','ns2','A','118.123.12.63',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(6,'cache.51web.com','xingxing','A','123.123.123.123',25,NULL,28800,14400,86400,86400,2012080700,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0);
/*!40000 ALTER TABLE `telecom` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `unicom`
--
DROP TABLE IF EXISTS `unicom`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `unicom` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`zone` varchar(255) NOT NULL,
`host` varchar(255) NOT NULL DEFAULT '@',
`type` enum('MX','NS','CNAME','SOA','A','PTR') NOT NULL DEFAULT 'A',
`data` varchar(255) DEFAULT NULL,
`ttl` int(11) NOT NULL DEFAULT '800',
`mx_priority` int(11) DEFAULT NULL,
`refresh` int(11) NOT NULL DEFAULT '28800',
`retry` int(11) NOT NULL DEFAULT '14400',
`expire` int(11) NOT NULL DEFAULT '86400',
`minimum` int(11) NOT NULL DEFAULT '86400',
`serial` bigint(20) NOT NULL DEFAULT '2012080700',
`resp_person` varchar(64) NOT NULL DEFAULT 'root.cache.51web.com.',
`primary_ns` varchar(64) NOT NULL DEFAULT 'ns1.cache.51web.com.',
`second_ns` varchar(64) NOT NULL DEFAULT 'ns2.cache.51web.com.',
`data_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `zone` (`zone`),
KEY `type` (`type`),
KEY `host` (`host`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `unicom`
--
LOCK TABLES `unicom` WRITE;
/*!40000 ALTER TABLE `unicom` DISABLE KEYS */;
INSERT INTO `unicom` VALUES (1,'cache.51web.com','@','SOA','cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(2,'cache.51web.com','@','NS','ns1.cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(3,'cache.51web.com','@','NS','ns2.cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(4,'cache.51web.com','ns1','A','118.123.12.62',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(5,'cache.51web.com','ns2','A','118.123.12.63',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(6,'cache.51web.com','xingxing','A','234.234.234.234',25,NULL,28800,14400,86400,86400,2012080700,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0);
/*!40000 ALTER TABLE `unicom` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `unkownnet`
--
DROP TABLE IF EXISTS `unkownnet`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `unkownnet` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`zone` varchar(255) NOT NULL,
`host` varchar(255) NOT NULL DEFAULT '@',
`type` enum('MX','NS','CNAME','SOA','A','PTR') NOT NULL DEFAULT 'A',
`data` varchar(255) DEFAULT NULL,
`ttl` int(11) NOT NULL DEFAULT '800',
`mx_priority` int(11) DEFAULT NULL,
`refresh` int(11) NOT NULL DEFAULT '28800',
`retry` int(11) NOT NULL DEFAULT '14400',
`expire` int(11) NOT NULL DEFAULT '86400',
`minimum` int(11) NOT NULL DEFAULT '86400',
`serial` bigint(20) NOT NULL DEFAULT '2012080700',
`resp_person` varchar(64) NOT NULL DEFAULT 'root.cache.51web.com.',
`primary_ns` varchar(64) NOT NULL DEFAULT 'ns1.cache.51web.com.',
`second_ns` varchar(64) NOT NULL DEFAULT 'ns2.cache.51web.com.',
`data_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `zone` (`zone`),
KEY `type` (`type`),
KEY `host` (`host`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `unkownnet`
--
LOCK TABLES `unkownnet` WRITE;
/*!40000 ALTER TABLE `unkownnet` DISABLE KEYS */;
INSERT INTO `unkownnet` VALUES (1,'cache.51web.com','@','SOA','cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(2,'cache.51web.com','@','NS','ns1.cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(3,'cache.51web.com','@','NS','ns2.cache.51web.com.',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(4,'cache.51web.com','ns1','A','118.123.12.62',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(5,'cache.51web.com','ns2','A','118.123.12.63',86400,NULL,28800,14400,86400,86400,2009122300,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0),(6,'cache.51web.com','xingxing','A','21.21.12.12',25,NULL,28800,14400,86400,86400,2012080700,'root.cache.51web.com.','ns1.cache.51web.com.','ns2.cache.51web.com.',0);
/*!40000 ALTER TABLE `unkownnet` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2014-05-14 23:09:21
Raw
named.conf
options {
directory "/usr/local/named"; // Working directory
version "cdn.v1.0";
};
key "rndc-key" {
algorithm hmac-md5;
secret "65HhJbhm02J/meAMOnwHLQ==";
};
controls {
inet 127.0.0.1 port 953
allow { 127.0.0.1; } keys { "rndc-key"; };
};
include "/usr/local/named/etc/telecom_acl.conf";
include "/usr/local/named/etc/unicom_acl.conf";
include "/usr/local/named/etc/cache_acl.conf";
include "/usr/local/named/etc/view.conf";
Raw
view.conf
#cache server
view "cache_view" {
match-clients { CACHE; };
allow-query-cache { CACHE; };
allow-recursion { none; };
allow-transfer { none; };
recursion no;
dlz "Mysql zone" {
database "mysql
{host=127.0.0.1 dbname=dnsdata ssl=false port=3306 user=dnsuser pass=dnspass }
{select zone from dns_records where zone = '$zone$' and view='CACHE' limit 1}
{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 as mydata from dns_records where zone = '$zone$' and host = '$record$' and view='CACHE'}
{}
{select ttl, type, host, mx_priority, case when lower(type)='txt' then concat('\"', data, '\"') else data end as mydata, resp_person, serial, refresh, retry, expire, minimum from dns_records where zone = '$zone$' and view='CACHE'}";
};
};
#telecom-view
view "telecom_view" {
match-clients { TEL; };
allow-query-cache { TEL; };
allow-recursion { TEL; };
allow-transfer { none; };
# recursion no;
dlz "Mysql zone" {
database "mysql
{host=127.0.0.1 dbname=dnsdata ssl=false port=3306 user=dnsuser pass=dnspass }
{select zone from telecom where zone = '$zone$' limit 1}
{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 telecom where zone = '$zone$' and host = '$record$' and not (type = 'SOA' or type = 'NS')}
{}
{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 telecom where zone = '$zone$'}";
#{update data_count set count = count + 1 where zone = '$zone$'}";
};
zone "." {
type hint;
file "named.root";
};
};
#unicom view
view "unicom_view" {
match-clients { UNI; };
allow-query-cache { UNI; };
allow-recursion { none; };
allow-transfer { none; };
# recursion yes;
zone "." {
type hint;
file "named.root";
};
dlz "Mysql zone" {
database "mysql
{host=127.0.0.1 dbname=dnsdata ssl=false port=3306 user=dnsuser pass=dnspass }
{select zone from unicom where zone = '$zone$' limit 1}
{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 unicom where zone = '$zone$' and host = '$record$' and not (type = 'SOA' or type = 'NS')}
{}
{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 unicom where zone = '$zone$'}";
};
};
#any_view
view "any_view" {
match-clients { any; };
allow-query-cache { any; };
allow-recursion { none; };
allow-transfer { none; };
recursion no;
dlz "Mysql zone" {
database "mysql
{host=127.0.0.1 dbname=dnsdata ssl=false port=3306 user=dnsuser pass=dnspass }
{select zone from unkownnet where zone = '$zone$' limit 1}
{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 unkownnet where zone = '$zone$' and host = '$record$' and not (type = 'SOA' or type = 'NS')}
{}
{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 unkownnet where zone = '$zone$'}";
};
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment