[Pdns-users] Easiest way to compare two PowerDNS MYSQL zones (or whole databases)

Jordan Rieger jordan at webnames.ca
Fri May 8 19:16:56 UTC 2015

I think we actually had a license for a different version of TOAD at one point. I'll check it out.


Thanks John.



From: John Miller [mailto:johnmill at brandeis.edu] 
Sent: Friday, May 08, 2015 12:11 PM
To: Jordan Rieger
Cc: pdns-users at mailman.powerdns.com
Subject: Re: [Pdns-users] Easiest way to compare two PowerDNS MYSQL zones (or whole databases)


Hi Jordan,

In the past, I've used a Windows-based tool called TOAD (http://software.dell.com/products/toad-for-mysql/).  It allows you to do a direct comparison between two different databases: just log into your MySQL server from TOAD and you should be on your way.

If you're worried about duplicate database records or other db strangeness, you can use the zone2sql tool to convert between SQL and zone files:


Best of luck!



On Thu, May 7, 2015 at 8:07 PM, Jordan Rieger <jordan at webnames.ca> wrote:

What do you guys think would be the best approach for comparing two PowerDNS MYSQL databases that are supposed to hold the same domains and records? I want to produce a list of discrepancies between the two databases, e.g. domains missing from one but not the other, records missing from one but not the other, etc.


So far I'm thinking that I could dump both databases at the same time, import them into a temporary scratch database, and then write a SQL script to compare the records directly between database X and Y. (I have a lot of SQL experience, but not much in MySQL, and I have never looked at the PowerDNS schema.) I could start by checking if each domain is present in both X and Y. And then, since I assume the primary keys wouldn't match, I would have to compare the count of records in each domain, and if the counts matched, I would have to compare the actual record content, e.g. "does X.domain1.record1 match a record in Y.domain1?" and so forth for each record in X. And since the order of records is not significant, and duplicates are possible, I would have to repeat the same comparison from the opposite direction, Y to X, to catch everything. And of course when I say "match" I mean "all publically-visible DNS content is the same between the two records".


Do you know of a tool or API command that would make this easier? (Other than a generic database data comparison tool.)


Background: we already have a PowerDNS server synced with our existing Bind master, and I am confident that there are no discrepancies. We will soon be migrating all servers to PowerDNS, but this requires rewriting a lot of our backend code to use the PowerDNS API. For the first few months, we want to run both our old Bind API wrapper and our new PowerDNS API wrapper in parallel to evaluate. The new PowerDNS server will then be potentially out of sync if there are any bugs in our new wrapper. That is why I would like a comparison tool to find the discrepancies.




Jordan Rieger

Senior Software Developer

Webnames.ca Inc.

Pdns-users mailing list
Pdns-users at mailman.powerdns.com


John Miller
Systems Engineer
Brandeis University
johnmill at brandeis.edu
(781) 736-4619

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20150508/fc3c699b/attachment-0001.html>

More information about the Pdns-users mailing list