This tutorial was made for Debian 8 (jessie). I use PowerDNS 4 from the repository of the developer.

Installing MySQL

apt-get install mysql-server mysql-client

In the examples I use the following command for connection.

mysql -u root -p

Installing and configuring PowerDNS 4

echo "deb http://repo.powerdns.com/debian jessie-auth-40 main" >> /etc/apt/sources.list.d/pdns.list
wget -O - https://repo.powerdns.com/FD380FBB-pub.asc | sudo apt-key add -
echo "Package: pdns-*
Pin: origin repo.powerdns.com
Pin-Priority: 600" > /etc/apt/preferences.d/pdns
apt-get update
apt-get install pdns-server pdns-backend-mysql

https://doc.powerdns.com/md/authoritative/installation/

Creation of the database user.

mysql>
create database pdns;
grant all on pdns.* to pdnsusr@localhost identified by 'pdnspw';
grant usage on *.* to pdnsusr@localhost;

https://doc.powerdns.com/md/authoritative/howtos/#basic-setup-configuring-database-connectivity

Loading the database schema to the database.

mysql>
CREATE TABLE domains (
 id                    INT AUTO_INCREMENT,
 name                  VARCHAR(255) NOT NULL,
 master                VARCHAR(128) DEFAULT NULL,
 last_check            INT DEFAULT NULL,
 type                  VARCHAR(6) NOT NULL,
 notified_serial       INT DEFAULT NULL,
 account               VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (id)
) Engine=InnoDB;

CREATE UNIQUE INDEX name_index ON domains(name);

CREATE TABLE records (
 id                    INT AUTO_INCREMENT,
 domain_id             INT DEFAULT NULL,
 name                  VARCHAR(255) DEFAULT NULL,
 type                  VARCHAR(10) DEFAULT NULL,
 content               VARCHAR(64000) DEFAULT NULL,
 ttl                   INT DEFAULT NULL,
 prio                  INT DEFAULT NULL,
 change_date           INT DEFAULT NULL,
 disabled              TINYINT(1) DEFAULT 0,
 ordername             VARCHAR(255) BINARY DEFAULT NULL,
 auth                  TINYINT(1) DEFAULT 1,
 PRIMARY KEY (id)
) Engine=InnoDB;

CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);
CREATE INDEX recordorder ON records (domain_id, ordername);

CREATE TABLE supermasters (
 ip                    VARCHAR(64) NOT NULL,
 nameserver            VARCHAR(255) NOT NULL,
 account               VARCHAR(40) NOT NULL,
 PRIMARY KEY (ip, nameserver)
) Engine=InnoDB;

CREATE TABLE comments (
 id                    INT AUTO_INCREMENT,
 domain_id             INT NOT NULL,
 name                  VARCHAR(255) NOT NULL,
 type                  VARCHAR(10) NOT NULL,
 modified_at           INT NOT NULL,
 account               VARCHAR(40) NOT NULL,
 comment               VARCHAR(64000) NOT NULL,
 PRIMARY KEY (id)
) Engine=InnoDB;

CREATE INDEX comments_domain_id_idx ON comments (domain_id);
CREATE INDEX comments_name_type_idx ON comments (name, type);
CREATE INDEX comments_order_idx ON comments (domain_id, modified_at);

CREATE TABLE domainmetadata (
 id                    INT AUTO_INCREMENT,
 domain_id             INT NOT NULL,
 kind                  VARCHAR(32),
 content               TEXT,
 PRIMARY KEY (id)
) Engine=InnoDB;

CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind);

CREATE TABLE cryptokeys (
 id                    INT AUTO_INCREMENT,
 domain_id             INT NOT NULL,
 flags                 INT NOT NULL,
 active                BOOL,
 content               TEXT,
 PRIMARY KEY(id)
) Engine=InnoDB;

CREATE INDEX domainidindex ON cryptokeys(domain_id);

CREATE TABLE tsigkeys (
 id                    INT AUTO_INCREMENT,
 name                  VARCHAR(255),
 algorithm             VARCHAR(50),
 secret                VARCHAR(255),
 PRIMARY KEY (id)
) Engine=InnoDB;

CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm);

PowerDNS configuration to use the MySQL backend.

echo "launch=gmysql
gmysql-host=127.0.0.1
gmysql-user=pdnsusr
gmysql-dbname=pdns
gmysql-password=pdnspw" > /etc/powerdns/pdns.d/pdns.mysettings.conf

systemctl restart pdns

Importing zones to the PowerDNS database

zone2sql --named-conf=/etc/bind/named.conf --gmysql --slave | mysql -u pdnsusr -p pdns

https://doc.powerdns.com/md/authoritative/migration/#from-zonefiles-to-powerdns

Setting up DNSSEC, migrating keys

I export the keys from the SoftHSM. I need the CKA_ID for the softhsm export.

$ ods-ksmutil key list --verbose
SQLite database set to: /var/lib/opendnssec/kasp.db
Keys:
Zone:         Keytype:  State:   Date of next transition (to):  Size:  Algorithm:  CKA_ID:     Repository:  Keytag:
example.com   ZSK       active   2016-10-09 07:32:33 (retire)   1024   8           hf78o4h...  SoftHSM      64812
example.com   KSK       active   2017-06-01 11:51:39 (retire)   2048   8           3892zhr...  SoftHSM      53675

I need the Token label from the list of the slots for the next step.

softhsm --show-slots
Available slots:
Slot 0
          Token present: yes
          Token initialized: yes
          User PIN initialized: yes
          Token label: OpenDNSSEC

The id comes from the ods-ksmutil key list --verbose command example.com KSK line (CKA_ID).

softhsm --export ksk.pem --slot 0 --label "OpenDNSSEC" --pin 12345678 --id 3892zhr...

https://wiki.opendnssec.org/display/DOCS/Migrating+to+OpenDNSSEC

I convert the PEM format to BIND format and I import the key.

softhsm-keyconv --tobind --in ksk.pem --name example.com --ksk --algorithm RSASHA256
pdnsutil import-zone-key example.com Kksk+008+53675.private active ksk

Other active KSKs can be deactivated or deleted. The id number 12 is just an example.

pdnsutil list-keys example.com
pdnsutil remove-zone-key example.com 12

The NSEC3 settings migration to PowerDNS begins by reading the <Denial> section of the /etc/opendnssec/kasp.xml file. The elements <OptOut> and <Iterations> can be migrated easily. Concerning the other elements see RFC 5155 and section 10.3 of the RFC 5155.

Part of the /etc/opendnssec/kasp.xml file:

...
       <Denial>
           <NSEC3>
               <TTL>PT3600S</TTL>
               <!-- <OptOut/> -->
               <Resalt>P100D</Resalt>
               <Hash>
                   <Algorithm>1</Algorithm>
                   <Iterations>5</Iterations>
                   <Salt length="8"/>
               </Hash>
           </NSEC3>
       </Denial>
...

A possible PowerDNS setup based on the above example (see man pdnsutil):

pdnsutil set-nsec3 1 0 5 abcdef01

Two PowerDNS with MySQL master-slave replication and SSL connection

The native replication mode of PowerDNS is the database system own replication.

https://doc.powerdns.com/md/authoritative/modes-of-operation/#native-replication

Creation of the CA and SSL certificates

It is needed to create a certificate chain for the MySQL servers.

Creating CA.

mkdir mysqlcerts
cd mysqlcerts/
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

Creation of the pdns1 server's certificate.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout pdns1-key.pem -out pdns1-req.pem
openssl rsa -in pdns1-key.pem -out pdns1-key.pem
openssl x509 -req -in pdns1-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out pdns1-cert.pem

Creation of certificate for the pdns2 server.

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout pdns2-key.pem -out pdns2-req.pem
openssl rsa -in pdns2-key.pem -out pdns2-key.pem
openssl x509 -req -in pdns2-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out pdns2-cert.pem

Verifying certificates.

openssl verify -CAfile ca.pem pdns1-cert.pem pdns2-cert.pem

https://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html

MySQL replication master setup

Setting up the master server.

echo "[mysqld]
bind-address            = 0.0.0.0
server-id               = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = pdns
binlog_format = MIXED

ssl-ca=/etc/mysql/ca.pem
ssl-cert=/etc/mysql/pdns1-cert.pem
ssl-key=/etc/mysql/pdns1-key.pem" >> /etc/mysql/conf.d/mymaster.cnf

systemctl restart mysql
mysql>
GRANT REPLICATION SLAVE ON *.* TO 'pdnsusrslave'@'%' IDENTIFIED BY 'pdnspwslave' require ssl;
FLUSH PRIVILEGES;
use pdns;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

New shell:

mysqldump -u root -p --opt pdns > pdns.sql

Back to MySQL:

mysql>
UNLOCK TABLES;
QUIT;

MySQL replication slave setup

mysql>
CREATE DATABSE pdns;
EXIT;
mysql -u root -p pdns < pdns.sql
echo "[mysqld]
bind-address = 0.0.0.0
server-id = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = pdns
binlog_format = mixed

ssl-ca=/etc/mysql/ca.pem
ssl-cert=/etc/mysql/pdns2-cert.pem
ssl-key=/etc/mysql/pdns2-key.pem" >> /etc/mysql/conf.d/myslave.cnf

systemctl restart mysql
mysql>
CHANGE MASTER TO MASTER_HOST='<MASTER_HOST>',MASTER_USER='pdnsusrslave', MASTER_PASSWORD='pdnspwslave', MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=790, MASTER_SSL=1, MASTER_SSL_CA='/etc/mysql/ca.pem', MASTER_SSL_CERT='/etc/mysql/pdns2-cert.pem', MASTER_SSL_KEY='/etc/mysql/pdns2-key.pem';
START SLAVE;
SHOW SLAVE STATUS\G

If this does not work:

mysql>
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE STOP;
SLAVE START;

https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-secure-connections.html