automating powerdns mysql with bcfg2

Briefly I will describe how I automate DNS updates to PowerDNS (with mysql backend) with bcfg2;
This pattern of using bcfg2 to verify contents of and automate updates to a mysql database could be applied to many different applications. I use it for PowerDNS.

the generic pattern

• write a TGenshi template which generates a correct mysql dump
• write a Bcfg2 probe to dump the mysql data in place of the aforementioned templated dump
• to test data: run bcfg2 in dry-run mode; tests if the mysql dump generated by the probe differs from the TGenshi templated mysql dump
• to update the mysql database: run bcfg2 in non-dry-run mode to update the mysql database via a BoundAction in the Bcfg2 Bundle

Here is what our Power DNS Bundle looks like:

<Bundle name='pdns-server' version='2.0'>

    <BoundPackage name='pdns-server' version='2.9.22-1~bpo50+1' type='deb'/>
    <BoundPackage name='pdns-backend-mysql' version='2.9.22-1~bpo50+1' type='deb'/>
    <BoundService name="pdns" status="on" type="deb"/>

    <Path name='/etc/powerdns/pdns.conf'/>
    <Path name='/etc/powerdns/pdns.d/pdns.local'/>
    <Path name='/etc/powerdns/pdns.d/master.conf'/>

    <Path name='/etc/powerdns/powerdns_reload.sh'/>
    <BoundAction name="reload_powerdns" timing="post" when="modified" command="bash /etc/powerdns/powerdns_reload.sh" status="ignore" />
    <Path name='/etc/powerdns/powerdns.sql'/>

</Bundle>

And here we have the reload_powerdns action script:

#!/bin/bash
mysql powerdns < /etc/powerdns/powerdns.sql
echo 'DROP TABLE IF EXISTS `domains_old`; DROP TABLE IF EXISTS `records_old`; RENAME TABLE domains TO domains_old, records TO records_old, domains_new TO domains, records_new TO records;'|mysql powerdns

As you can see from this reload script, our TGenshi template (/etc/powerdns/powerdns.sql) which generates the PowerDNS mysqldump has two tables; domains_new and records_new… which this reload script renames to doamins and records. Simple!

Now let’s take a look at our Powerdns probe:

#!/bin/bash
if [ -d /var/lib/mysql/powerdns ]; then
    mysqldump powerdns domains records | grep -v 'Server version' | grep -v '^-- Dump completed on' \
        | sed -e 's/`domains`/`domains_new`/g' | sed -e 's/`records`/`records_new`/g' | sed -e 's/),(/),\n(/g' > /etc/powerdns/powerdns.sql
fi

As you can see from this probe that I in fact lied about the TGenshi template creating an exact mysqldump. It does so with a newline character at the end of each tuple so that when we run the bcfg2 client in interactive mode (e.g. bcfg2 -qedI) we can see a readable diff showing us which line in the dump has changed…

And finally our TGenshi template which generates the mysqldump loads most of it’s data from several XML properties files as well as bcfg2 metadata…