<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">
<meta name=Generator content="Microsoft Word 12 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I think we actually had a license for a different version of TOAD at one point. I'll check it out.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Thanks John.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> John Miller [mailto:johnmill@brandeis.edu] <br><b>Sent:</b> Friday, May 08, 2015 12:11 PM<br><b>To:</b> Jordan Rieger<br><b>Cc:</b> pdns-users@mailman.powerdns.com<br><b>Subject:</b> Re: [Pdns-users] Easiest way to compare two PowerDNS MYSQL zones (or whole databases)<o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><div><div><div><p class=MsoNormal style='margin-bottom:12.0pt'>Hi Jordan,<o:p></o:p></p></div><p class=MsoNormal style='margin-bottom:12.0pt'>In the past, I've used a Windows-based tool called TOAD (<a href="http://software.dell.com/products/toad-for-mysql/">http://software.dell.com/products/toad-for-mysql/</a>). 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.<o:p></o:p></p></div><p class=MsoNormal style='margin-bottom:12.0pt'>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:<br><br><a href="https://wiki.powerdns.com/trac/wiki/Zone2SQLFAQ">https://wiki.powerdns.com/trac/wiki/Zone2SQLFAQ</a><o:p></o:p></p></div><p class=MsoNormal style='margin-bottom:12.0pt'>Best of luck!<o:p></o:p></p></div><p class=MsoNormal>John<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>On Thu, May 7, 2015 at 8:07 PM, Jordan Rieger <<a href="mailto:jordan@webnames.ca" target="_blank">jordan@webnames.ca</a>> wrote:<o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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".<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Do you know of a tool or API command that would make this easier? (Other than a generic database data comparison tool.)<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Jordan Rieger<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Senior Software Developer<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Webnames.ca Inc.<o:p></o:p></p></div></div><p class=MsoNormal style='margin-bottom:12.0pt'><br>_______________________________________________<br>Pdns-users mailing list<br><a href="mailto:Pdns-users@mailman.powerdns.com">Pdns-users@mailman.powerdns.com</a><br><a href="http://mailman.powerdns.com/mailman/listinfo/pdns-users" target="_blank">http://mailman.powerdns.com/mailman/listinfo/pdns-users</a><o:p></o:p></p></div><p class=MsoNormal><br><br clear=all><br>-- <o:p></o:p></p><div><p class=MsoNormal>John Miller<br>Systems Engineer<br>Brandeis University<br><a href="mailto:johnmill@brandeis.edu" target="_blank">johnmill@brandeis.edu</a><br>(781) 736-4619<o:p></o:p></p></div></div></div></body></html>