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

[John Miller]

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:

https://wiki.powerdns.com/trac/wiki/Zone2SQLFAQ

Best of luck!

John

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.
>
>
>
> Thanks,
>
>
>
> Jordan Rieger
>
> Senior Software Developer
>
> Webnames.ca Inc.
>
> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> http://mailman.powerdns.com/mailman/listinfo/pdns-users
>
>


-- 
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/dfa70af5/attachment-0001.html>