How to use Zone2SQL to migrate from bind,named to a new DNS server

Please feel free to contribute more to this FAQ! The static docs are not complete.

First, tar and copy to the new server your named.conf and all included zone files from /var/lib/named, /etc/bind, or wherever your distro saves your zone files. In this example, the conf and zone files were copied into local folder 'namedfiles.' Modify the path specified in the named.conf to point to this local folder.

Next, create your zones manually in Poweradmin. Go into mysql and identify the domain_id/id for each:

# Example:
mysql -u poweradmin -p powerdns
select * from domains;
+----+-------------------------+--------+------------+--------+-----------------+---------+
| id | name                      | master | last_check | type   | notified_serial | account |
|  9 | domain1.test.edu          |        |       NULL | MASTER |      2009051201 | NULL    |
| 10 | 4.3.2.in-addr.arpa        | NULL   |       NULL | MASTER |      2009051201 | NULL    |

Next, prepare zone2sql options for conversion. You will want to specify the start-id number.

zone2sql --mysql --named-conf=namedfiles/named.conf --verbose --start-id=9 > import.sql

Examine your import.sql file and make any necessary changes, such as replacing the new SOA name servers.

Finally, import the sql:

mysql -u poweradmin -p powerdns < import.sql

You should now see the domains and records populated in Poweradmin.

Some additional utilities

domains import

You can generate an domains import sql script from the generated import file using awk: create import2zone.awk

#!/usr/bin/awk -f
/'SOA'/ {
    sub(/\(/,"",$7)
    sub(/,/,"",$7)
    gsub(/ /,"",$7)
    gsub(/'/,"",$8)
    sub(/,/,"",$8)
    gsub(/ /,"",$8)
    printf "INSERT INTO domains (id,name,type) VALUES (%s,'%s','NATIVE');\n", $7,$8
    printf "INSERT INTO zones (id,domain_id,owner,comment) VALUES (%s,%s,1,'Zone Import from zone2dns output - zone: %s');\n",$7,$7,$8
}

make it executable:

chmod +x import2zone.awk

then just run it upon the previously generated import.sql file:

./import2zone.awk import.sql > insdomains.sql

and import into db:

mysql -u poweradmin -p powerdns < insdomains.sql

This will insert a record in 'domains' table for each SOA record generated by zone2sql and a record in 'zones' table with same zone id and domain id as domain_id generated from zone2sql.

file format problems

Zone files edited with Windows programs may contain some weird chars that zones2sql doesn't like. I wrote a simple php script in order to clean zone files. Create rewritezones.php:

#!/usr/bin/php5
<?php
  /*
   * suppose original zone files copied to namedfile/oldzone.include and cleaned zone files 
   * (those which will feed zones2sql) stored in namedfiles/zone.include
   */
  $basedir = 'namedfiles';
  $dir = dir($basedir . '/oldzone.include');
  while ($entry = $dir->read()) {
    if ($entry != '.' && $entry != '..') {
        echo $entry ."\n";
        $lines = file($basedir.'/oldzone.include/'.$entry);
        
        $handle = fopen($basedir .'/zone.include/'.$entry,'w');
        foreach ($lines as $line) {      
          fwrite($handle,trim($line)."\n");
        }
        fclose($handle);
    }
  }
  /*
   * suppose original db files copied to namedfile/olddb and cleaned zone files 
   * (those which will feed zones2sql) stored in namedfiles/db
   */

  $dir = dir($basedir . '/olddb');
  while ($entry = $dir->read()) {
    if ($entry != '.' && $entry != '..') {
        echo $entry ."\n";
        $lines = file($basedir.'/olddb/'.$entry);
        
        $handle = fopen($basedir.'/db/'.$entry,'w');
        foreach ($lines as $line) {      
          fwrite($handle,trim($line)."\n");
        }
        fclose($handle);
    }
  }
?>

make it executable and run it:

chmod +x rewritezones.php
./rewritezones.php

or just run it via php:

php5 ./rewritezones.php

What about tables cleaning ?

Since manual table cleaning after every unsuccesfull import is quite boring, you may clean all interested tables with this simple sql:

DELETE FROM domains WHERE 1;
DELETE FROM zones WHERE 1;
DELETE FROM records WHERE 1;
ALTER TABLE domains AUTO_INCREMENT = 1;
ALTER TABLE zones AUTO_INCREMENT = 1;
ALTER TABLE records AUTO_INCREMENT = 1;

which will delete all entries and reset auto-increment fields.