So, a fun one happened the other day. I needed to remove all the old domains from our nameserver cluster which is open to customers who were no longer pointing there domains to our nameservers. Easy eh?
Well, unless you have access to a unrestricted IP capable of doing that many lookups on Ausreg (most of the domains are com.au’s) you would need some other method.I just did a dig to get the nameservers but a lot returned without NS records and thus didn’t know where they were pointing.
Turns out some of our customers didn’t use our nice templates when they made the domains, so it didn’t create a NS record in our system. So I thought better fix that. Anyway, the SQL is
<pre>SELECT domains.id, domains.name FROM domains WHERE domains.id NOT IN (SELECT domain_id FROM records WHERE type = “NS”);</pre>
You could also substitue this for any record type really. Someone with mad SQL skilz could probably optimise that, but it does what i need it to 🙂
If you save the above to a CSV file (into outfile ‘/tmp/domains.txt’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;)
and then you can use something like this to automate the creating of the records
<pre>
#!/bin/bash
INPUT=domains.csv
OLDIFS=$IFS
IFS=,
[ ! -f $INPUT ] && { echo “$INPUT file not found”; exit 99; }
while read ID DOMAIN
do
mysql -u pdnsuser -p ‘pass’ powerdns -e “insert into records (domain_id,name,type,content,ttl,prio,change_date) values ($ID,’$DOMAIN’,’NS’,’ns1.yourdomain’,3600,0,unix_timestamp());”
mysql -u pdnsuser -p ‘pass’ powerdns -e “insert into records (domain_id,name,type,content,ttl,prio,change_date) values ($ID,’$DOMAIN’,’NS’,’ns2.yourdomain’,3600,0,unix_timestamp());”
mysql -u pdnsuser -p ‘pass’ powerdns -e “insert into records (domain_id,name,type,content,ttl,prio,change_date) values ($ID,’$DOMAIN’,’NS’,’ns3.yourdomain’,3600,0,unix_timestamp());”
done < $INPUT
IFS=$OLDIFS
</pre>
