Mass updating Bind zone file serial numbers with sed

If you’ve ever administered a DNS (name server), than you know that serial numbers mean a lot. But what happens when you have several [hundreds|thousands] of zone that you need to make a mass-change to. That’s easy enough, there’s tons of ways to do that with sed, awk, or simple tools like “replace”. However, once you’ve done the easy work, you still need to update all the serial numbers as well. Here’s how I saved myself hours of manual labor:

If you’re even reading this article, it’s expected that you at least know that a serial number is in the form YYYYMMDDNN where NN is the update number for that day. So the serial number from my post today would be 2013082100.

Now here’s how you tell sed what you want done (note, this formula assumes that your previous serial numbers were post-2000):

# sed -i ‘s/20[0-9][0-9]\{7\}/2013082100/g’ *.db

This says to replace any text starting with 20+[any two numbers 0-9] and followed by another 7 digits with the string: 2013082100

Problem solved, check out your zones now (provided they end in the standard format of domain.tld.db).


Incremental VALUE with MYSQL

SET @pos:=0;
LPAD(@pos:[email protected]+1,4,'0')

How long is “too long” for MySQL Connections to sleep?

mysqld will timeout DB Connections based on two(2) server options:


Both are 28800 seconds (8 hours) by default.

You can set these options in /etc/my.cnf

If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 min) or even 60 (1 min). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in my.cnf (takes effect after mysql restart):


If you do not want to restart mysql, then run these two commands:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 sec.

Add SPF records to all domains in Plesk

To add SPF records to every domain in Plesk, you can use this huge one-liner:

mysql -u admin -p`cat /etc/psa/.psa.shadow` psa -e "select dns_zone_id,displayHost from dns_recs GROUP BY dns_zone_id ORDER BY dns_zone_id ASC;" | awk '{print "INSERT INTO dns_recs (type,host,val,time_stamp,dns_zone_id,displayHost,displayVal) VALUES ('\''TXT'\'','\''"$2"'\'','\''v=spf1 a mx ~all'\'',NOW(),"$1",'\''"$2"'\'','\''v=spf1 a mx ~all'\'');"}' | mysql -u admin -p`cat /etc/psa/.psa.shadow` psa

Then you’ll need to make Plesk write these changes to the zone files:

# mysql -Ns -uadmin -p`cat /etc/psa/.psa.shadow` -D psa -e 'select name from domains' | awk '{print "/usr/local/psa/admin/sbin/dnsmng update " $1 }' | sh

You can check your work by viewing the new entries you made:

mysql -u admin -p`cat /etc/psa/.psa.shadow` psa -e "SELECT * FROM dns_recs WHERE type='TXT';"