HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials - DNS data from Standalone servers to Primary DNS server

Description:
Here is a short PHP script that allows a 'master' DNS server to collect DNS data from other StandAlone ISPConfig3 servers.

Intent:
To setup a system that allows 1 (one) or more stripped down ISPConfig3/MyDNS servers to act as 'primary' DNS servers. This way clients can keep their own DNS up-to-date them selves while the Primary DNS server (in our case NS3) collects that DNS data from different ISPConfig3 (standalone) servers.

You can also add other DNS servers to make sure the data replicated to primary NS1 and NS2 servers is 'whole.' (NS3 wipes all DNS data from it's tables before it collects the new DNS data from other stand alone servers - so using it as a NS1 is not a good idea.) This collected data can then be 'replicated' to NS1 and NS2 using simple MySQL script or 'normal' ISPConfig3 methods. You can run this script on ns3 using command php -q dns.php or by simply adding it to cron with command crontab -e

Edit: Code edited 2010.05.29
Changelog:
Script no longer stops if one of the queried servers is down but just skips that server.

PHP DNS sync code to run in NS3:

Quote:

#!/usr/bin/php -q
<?
error_reporting(0);
$db_name = 'dbispconfig';
$user="root";
$pass="MYSQLPASSWORD";
$server="localhost";
$link = mysql_connect($server, $user, $pass) or die("Could not connect!");
mysql_select_db($db_name) or die("Could not select database!");
$sql="delete from dns_soa where id >=1000";
mysql_query($sql);
$sql="delete from dns_rr where zone>=1000";
mysql_query($sql);

$serverlist=array('serverX.internet-content.net','serverY.internet-content.net','serverZ.internet-content.net');
$i=1000;
//print_r($serverlist);
foreach($serverlist as $server){
$i=$i+1000;
echo $server."<br>";
$user='dnsauto';
$pass='DNSPASSWORD';
$db_name = 'dbispconfig';
$link = mysql_connect($server, $user, $pass);
if(!$link) {
$mymsg= "can't connect to ".$server;
if(mail("zzz@internet-content.org",$mymsg,$mymsg)) echo "mail sent";
}
if(!mysql_select_db($db_name)){
$mymsg="can't select db on ".$server;
mail("yyy@internet-content.org",$mymsg,$mymsg);
}else{
$sql="SELECT id,`origin` , `ns` , `mbox` , `serial` , `refresh` , `retry` , `expire` , `minimum` , `ttl` , `active` , `xfer`
FROM `dns_soa`";
$result=mysql_query($sql);
while($row=mysql_fetch_assoc($result)){
// print_r($row);
// boolean
$id=$i+$row[id];
$sqli.="INSERT INTO `dbispconfig`.`dns_soa` (
`id` ,`sys_userid` ,`sys_groupid` ,`sys_perm_user` ,`sys_perm_group` ,`sys_perm_other` ,`server_id` ,`origin` ,`ns` ,`mbox` ,`serial` ,`refresh` ,`retry` ,`expire` ,`minimum` ,`ttl` ,`active` ,`xfer`)
VALUES (
$id , '2', '1', 'ruid', 'ruid', '', '1', '$row[origin]', '$row[ns]', '$row[mbox]', '$row[serial]', '$row[refresh]', '$row[retry]', '$row[expire]', '$row[minimum]', '$row[ttl]', '$row[active]', '$row[xfer]')|";
$sql2="SELECT `zone` , `name` , `type` , `data` , `aux` , `ttl` , `active` FROM `dns_rr` where zone='$row[id]'";
$result2=mysql_query($sql2) or die(mysql_error());
while($row2=mysql_fetch_assoc($result2)){
$sqlrr.="INSERT INTO `dbispconfig`.`dns_rr` (
`id` ,`sys_userid` ,`sys_groupid` ,`sys_perm_user` ,`sys_perm_group` ,`sys_perm_other` ,`server_id` ,`zone` ,`name` ,`type` ,`data` ,`aux` ,`ttl` ,`active`)
VALUES (
NULL , '2', '1', 'ruid', 'ruid', '', '1', '$id', '$row2[name]', '$row2[type]', '$row2[data]', '$row2[aux]', '$row2[ttl]', '$row2[active]')|";
}
}
$db_name = 'dbispconfig';
$user="root";
$pass="MYSQLPASSWORD";
$server="localhost";
$link = mysql_connect($server, $user, $pass) or die("Could not connect!");
mysql_select_db($db_name) or die("Could not select database!");
$sqla=explode("|",$sqli);
foreach($sqla as $insert){
@mysql_query($insert);
}
$sqla=explode("|",$sqlrr);
foreach($sqla as $insert){
@mysql_query($insert);
}
}
}
mail("xxx@internet-content.org","DNS Scrip Run","DNS OK ns3");
mysql_close();

//shell_exec('/etc/init.d/apache2 restart');
shell_exec('/etc/init.d/apache2 restart');
?>

SQL command to run in standalone ISPConfig3 serverX and/or serverY, etc:

Quote:

grant select on dbispconfig.* to dnsuser@'ns3.domain.net' identified by 'MYSQLPASSWORD'

Known problems:
1. If client in serverX enters a domain that belongs to serverY client, they can possibly cause problems with the 'original' domains name resolution.
FIXED - 2. If the serverX has not run the SQL command to enable ns3 to read it's DNS related tables the script will be unable to complete.

Optional scripts to help run the PHP DNS sync code and keep DNS script backup and logfile:

This is script is run hourly by crontab: (Or you can test the script manually with this command.)
php -q /etc/dns/test-ns.php > /dev/null 1>> /var/log/dns-script.log

test-ns.php

Quote:

<?php
error_reporting(1);
//shell_exec('/etc/dns/dns.sh');
shell_exec('/etc/dns/dns.sh');
?>

dns.sh

Quote:

#!/bin/bash
echo "DNS Script START"
echo "=== DNS Script START ===" >> /var/log/dns-script.log
echo `date` >> /var/log/dns-script.log
echo "List table sizes before dump" >> /var/log/dns-script.log
ls -hal /etc/dns/dns* >> /var/log/dns-script.log
echo "Backing up database" >> /var/log/dns-script.log
mysqldump -uroot -pMYSQLPASSWORD -hlocalhost --all-databases | gzip -9 > /backup/mysql-`date -I`-backup.sql.gz
echo "Importing new tables from ISPConfig3 servers." >> /var/log/dns-script.log
php -q /etc/dns/dns.php >> /var/log/dns-script.log
echo "" >> /var/log/dns-script.log
echo "Dumping table dns_rr from ns3" >> /var/log/dns-script.log
mysqldump --single-transaction --no-create-info --no-create-db -u root -pMYSQLPASSWORD -h localhost dbispconfig dns_rr > /etc/dns/dns_rr.sql
echo "Dumping table dns_soa from ns3" >> /var/log/dns-script.log
mysqldump --single-transaction --no-create-info --no-create-db -u root -pMYSQLPASSWORD -h localhost dbispconfig dns_soa > /etc/dns/dns_soa.sql
echo `date` >> /var/log/dns-script.log
echo "List table sizes after import" >> /var/log/dns-script.log
ls -hal /etc/dns/dns* >> /var/log/dns-script.log
/etc/init.d/apache2 restart >> /var/log/dns-script.log
echo "=== DNS Script END ===" >> /var/log/dns-script.log
echo "DNS Script END"
echo "" >> /var/log/dns-script.log