Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 3 > Tips/Tricks/Mods

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 1st November 2009, 19:15
SamTzu SamTzu is offline
HowtoForge Supporter
 
Join Date: Apr 2007
Location: Helsinki
Posts: 426
Thanks: 33
Thanked 55 Times in 38 Posts
Send a message via Skype™ to SamTzu
Default 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
Attached Files
File Type: zip test-ns.php.zip (2.0 KB, 251 views)
__________________

Sami Mattila
Internet-Content

Telephone:
00358942833310
Email: firstname.lastname@internet-content.org
Shop: http://shop.internet-content.net
Site: http://www.internet-content.net
Blog: http://www.internet-content.net/en/blog
FB: https://www.facebook.com/internetcontent


Last edited by SamTzu; 29th May 2010 at 13:44.
Reply With Quote
Sponsored Links
  #2  
Old 1st November 2009, 22:02
Franz Franz is offline
Senior Member
 
Join Date: Jul 2009
Location: Cakovec
Posts: 117
Thanks: 7
Thanked 24 Times in 18 Posts
Send a message via Skype™ to Franz
Default

First fetch data from master server then truncate and insert new data into table.

what if you can not connect to master database.
dns data will be deleted.
__________________
my PAGE
VoIP
my IT help blog
Reply With Quote
  #3  
Old 4th November 2009, 17:36
SamTzu SamTzu is offline
HowtoForge Supporter
 
Join Date: Apr 2007
Location: Helsinki
Posts: 426
Thanks: 33
Thanked 55 Times in 38 Posts
Send a message via Skype™ to SamTzu
Default

The code has been edited so that the personally created local Zones from 0-1000 would not be overwritten by the script.
__________________

Sami Mattila
Internet-Content

Telephone:
00358942833310
Email: firstname.lastname@internet-content.org
Shop: http://shop.internet-content.net
Site: http://www.internet-content.net
Blog: http://www.internet-content.net/en/blog
FB: https://www.facebook.com/internetcontent


Last edited by SamTzu; 4th November 2009 at 18:13.
Reply With Quote
  #4  
Old 9th November 2009, 21:30
Niels Niels is offline
Member
 
Join Date: Oct 2009
Posts: 52
Thanks: 5
Thanked 2 Times in 2 Posts
Default

Quote:
Originally Posted by SamTzu View Post
The code has been edited so that the personally created local Zones from 0-1000 would not be overwritten by the script.
How does the script tells the server that there are now entrys in the database? When are the entry visible?
Reply With Quote
  #5  
Old 15th November 2009, 01:57
SamTzu SamTzu is offline
HowtoForge Supporter
 
Join Date: Apr 2007
Location: Helsinki
Posts: 426
Thanks: 33
Thanked 55 Times in 38 Posts
Send a message via Skype™ to SamTzu
Default

The script does not tell anything to any server. The script can be run on ns3 every hour to check the ISPConfig3 MyDNS zones and records on serverX, serverY etc.

The script will simply collect that DNS data to ns3 where the script is run hourly by cron.

It seems to work quite nicely. There are some limits to how many servers can be included in the script but as it stands now every server can have up to a thousand records that can be collected from up to a thousand servers.
(I did not calculate that my self and confirmation from someone who can code would be nice.)
__________________

Sami Mattila
Internet-Content

Telephone:
00358942833310
Email: firstname.lastname@internet-content.org
Shop: http://shop.internet-content.net
Site: http://www.internet-content.net
Blog: http://www.internet-content.net/en/blog
FB: https://www.facebook.com/internetcontent


Last edited by SamTzu; 15th November 2009 at 02:01.
Reply With Quote
The Following User Says Thank You to SamTzu For This Useful Post:
adamassasda291 (28th June 2013)
  #6  
Old 15th November 2009, 02:08
Niels Niels is offline
Member
 
Join Date: Oct 2009
Posts: 52
Thanks: 5
Thanked 2 Times in 2 Posts
Default

Quote:
Originally Posted by SamTzu View Post
The script does not tell anything to any server. The script can be run on ns3 every hour to check the ISPConfig3 MyDNS zones and records on serverX, serverY etc.
So each server runs a job every hour and this job are using data form database? I would prefer if the master dns server copied data TO the other dns-servers.

Hm.... need to learn some php again
Reply With Quote
  #7  
Old 15th November 2009, 12:29
SamTzu SamTzu is offline
HowtoForge Supporter
 
Join Date: Apr 2007
Location: Helsinki
Posts: 426
Thanks: 33
Thanked 55 Times in 38 Posts
Send a message via Skype™ to SamTzu
Talking

Quote:
I would prefer if the master dns server copied data TO the other dns-servers.
That way you would have to do ALL the work on the Zones and Records.
Our way is much more Admin friendly. This script provides a change to collect ALL DNS data to central location AND delegate that work to the client.
Win/Win scenario.

PS. By definition of MASTER server I mean NS1, NS2 & NS3. Not serverX, serverY & serverZ.
__________________

Sami Mattila
Internet-Content

Telephone:
00358942833310
Email: firstname.lastname@internet-content.org
Shop: http://shop.internet-content.net
Site: http://www.internet-content.net
Blog: http://www.internet-content.net/en/blog
FB: https://www.facebook.com/internetcontent

Reply With Quote
  #8  
Old 15th November 2009, 13:38
Niels Niels is offline
Member
 
Join Date: Oct 2009
Posts: 52
Thanks: 5
Thanked 2 Times in 2 Posts
 
Default

Quote:
Originally Posted by SamTzu View Post
That way you would have to do ALL the work on the Zones and Records.
Our way is much more Admin friendly. This script provides a change to collect ALL DNS data to central location AND delegate that work to the client.
Win/Win scenario.

PS. By definition of MASTER server I mean NS1, NS2 & NS3. Not serverX, serverY & serverZ.
Got the point... I have decided to have ns1 and ns2 as small servers (5 GB) and have an application server with phpmyadmin, webmail and ispconfig. Then I need to copy all dns data from app-server to ns1 and ns2...
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
User unknown in relay recipient table Taxick Installation/Configuration 12 9th April 2013 12:31
Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail -Ubuntu 8.04 c4rdinal HOWTO-Related Questions 112 23rd August 2011 10:49
FTP cannot open remote folder!?! andysm849 Server Operation 23 16th October 2008 23:34
The Perfect Setup - Debian Etch (Debian 4.0) some trouble daniel80 HOWTO-Related Questions 26 1st February 2008 16:30
DNS Configuration Problems VMartins Installation/Configuration 10 24th July 2007 14:40


All times are GMT +2. The time now is 09:10.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.