Multiple DNS servers with PowerDNS and MySQL replication

With DNS it is essential to have at least two and preferably more DNS servers for your domains in geographically separated locations. Putting all your DNS servers on the same server is asking for trouble: even if your server goes down for only a little while (like a reboot) some visitors may perceive your sites due to negative DNS caching (where a visitor’s ISP resolving DNS server will “remember” your site “does not exist” for a while).

There are of course several commercial DNS hosting providers that can solve this problem for you, but most of these charge by how much DNS traffic your domains generate. For certain types of very popular sites (like image & file hosting sites) that may be costly because of the level of DNS traffic they generate. Or perhaps you simply want to maintain your own DNS servers.

The solution is to have several DNS servers powered by PowerDNS using MySQL as a backend, and synchronizing the DNS servers not using any DNS specific mechanism but simply through MySQL replication.  As the main DNS server you could use your own server, and as secondary servers you can use other servers or cheap VPS servers.

As PowerDNS supports caching using MySQL as a backend is not going to be a performance issue unless you really have a lot of different domains you want to provider DNS for (and in that case, just get beefier hardware or more servers). For information on how to setup PowerDNS with MySQL, see the official documentation.

To setup MySQL replication I recommend this guide, although the part on the first page about getting a snapshot of the master server (using the lock/unlock commands) is a bit obsolete if you used InnoDB to create the tables for PowerDNS. With InnoDB you can get a snapshot without any interruption with a single command:

mysqldump --opt --single-transaction --flush-logs --master-data=1 pdns > pdns.sql

The “master-data=1” bit even includes the right CHANGE MASTER command in the dumped SQL file, so you don’t need to manually specify the master position and only need load the SQL dump and restart the slave. Be aware though that MySQL replication might sometimes break (for example if one of the servers was uncleanly reboot) so occasionally check if MySQL replication is still working from phpMyAdmin or using the SHOW SLAVE STATUS command. For DNS troubleshooting I highly recommend

I apologize this post is not a straight-forward how-to, but hopefully it will point you in the right direction.