Infiniroot Blog: We sometimes write, too.

Of course we cannot always share details about our work with customers, but nevertheless it is nice to show our technical achievements and share some solutions.

PowerDNS Authoritative DNS Server 4.1 with MySQL backend on Ubuntu 18.04

Published on March 26th 2019

In a previous article (New authoritative DNS server setup with user interface in 2019 (a comparison)), I mentioned that I will write an article series about PowerDNS and this is the article first of that series. It will cover the basic installation of PowerDNS using a MySQL (or MariaDB) database as backend.

Some basic questions

Why PowerDNS?

I have been happy with BIND over the last decade (and I still am), but in a larger environment with around 1000 domains and multiple administrators, BIND on the command line can be tricky. The more people are working in the zone files, the more errors can happen, which may affect the whole DNS server. That's why a user interface for less cli-aware users is necessary. And PowerDNS also comes with an API, so this allows (in theory) programmatic DNS changes, e.g. from a CI/CD script. See article New authoritative DNS server setup with user interface in 2019 (a comparison) for more information.

Why MySQL Backend?

My initial test installation was PowerDNS with the Bind backend. Which means PowerDNS is a management layer on top of classic Bind Zone files. However there is one major problem:

root@pdns:~# pdnsutil create-zone
Jan 23 16:06:56 [bindbackend] Done parsing domains, 0 rejected, 0 new, 0 removed
Creating empty zone ''
Domain '' was not created!

Yep, the domain/zone was not created. PowerDNS is not able to create the new zones on its own when using the Bind backend. However it is required that this command works for the user interface I chose (more on that in a follow-up article).

See and .

Why PowerDNS 4.1? 4.2 is available!

As of the current end of life statements in the official documentation (March 2019), 4.1 is still the currently supported release.

 The currently supported release train of PowerDNS Authoritative Server is 4.1.

4.2 is available yes, but I'm not certain if this is still considered an early release. In my first tests I installed 4.2 which basically worked fine but the Opera DNS User Interface was not compatible with it. So I decided for 4.1.

Prepping Ubuntu

PowerDNS is a DNS server (d'uh!) and DNS runs on udp/53 (and also on tcp/53). But in Ubuntu 18.04 something is already using that port. What could it be? systemd!

root@pdns:~# netstat -lntup | grep 53
tcp        0      0*    LISTEN      622/systemd-resolve
udp        0      0*                622/systemd-resolve 

Yep, systemd-resolved runs by default as local DNS resolver so we must get rid of it first.

root@pdns:~# systemctl stop systemd-resolved
root@pdns:~# systemctl disable systemd-resolved
root@pdns:~# systemctl mask systemd-resolved

You also need to fix your /etc/resolv.conf now because it is a symlink which still points to the systemd-resolved service:

root@pdns:~# ll /etc/resolv.conf
lrwxrwxrwx 1 root root 39 May 22  2018 /etc/resolv.conf -> ../run/systemd/resolve/stub-resolv.conf

Example /etc/resolv.conf with public resolvers:

root@pdns:~# cat < /etc/resolv.conf

PowerDNS package installation

As this HowTo is about PowerDNS 4.1, this package can be found in the official Ubuntu repositories. However it makes sense to use the dedicated repository directly from PowerDNS itself for faster upstream fixes.

root@pdns:~# echo "deb bionic-auth-41 main" >> /etc/apt/sources.list.d/powerdns.list

root@pdns:~# cat < /etc/apt/preferences.d/powerdns
Package: pdns-*
Pin: origin
Pin-Priority: 600

root@pdns:~# curl -s | sudo apt-key add -

As you can see I also pinned the pdns-* packages to use PowerDNS's repositories.

The installation of the core package (pdns-server) and the MySQL backend (pdns-backend-mysql) is quickly done:

root@pdns:~# apt-get update
root@pdns:~# apt-get install pdns-server pdns-backend-mysql

MySQL (or MariaDB)

We could argue about good vs evil here but to PowerDNS it doesn't matter whether the MySQL backend is really a MySQL or a MariaDB. As we're on an Ubuntu 18.04 here, we could install either easily from the official repositories. I went for mysql-server here.

root@pdns:~# apt-get install mysql-server
root@pdns:~# mysql_secure_installation

Yep, don't forget to set MySQL root password, etc. using mysql_secure_installation!

Add the following InnoDB related configuration into /etc/mysql/mysql.conf.d/mysqld.cnf:

# InnoDB
innodb_log_file_size = 64M
innodb_buffer_pool_instances = 2
innodb_autoinc_lock_mode = 2
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 60
innodb_locks_unsafe_for_binlog = 1
innodb_stats_on_metadata = 0

Then restart MySQL:

root@pdns:~# service mysql restart

Create the database for PowerDNS and create an application user:

mysql> create database powerdns;
mysql> grant all on powerdns.* to 'pdns'@'localhost' identified by 'secret';

Next you need to load the PowerDNS schemas (the empty tables) into the database. Download the schema directly from the official PowerDNS repository on GitHub:

root@pdns:~# wget

Note: You have to make sure you are using the correct schema for PowerDNS 4.1! I made the mistake and loaded the 4.2 schema into the database on my test install. That will cause you headaches later... so verify this right now.

For completeness' sake, the schema file currently looks like this:

root@pdns:~# cat schema.mysql.sql
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) CHARACTER SET 'utf8' DEFAULT NULL,
) Engine=InnoDB CHARACTER SET 'latin1';

CREATE UNIQUE INDEX name_index ON domains(name);

CREATE TABLE records (
  id                    BIGINT 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,
) Engine=InnoDB CHARACTER SET 'latin1';

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

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

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) CHARACTER SET 'utf8' DEFAULT NULL,
  comment               TEXT CHARACTER SET 'utf8' NOT NULL,
) Engine=InnoDB CHARACTER SET 'latin1';

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,
) Engine=InnoDB CHARACTER SET 'latin1';

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,
) Engine=InnoDB CHARACTER SET 'latin1';

CREATE INDEX domainidindex ON cryptokeys(domain_id);

CREATE TABLE tsigkeys (
  id                    INT AUTO_INCREMENT,
  name                  VARCHAR(255),
  algorithm             VARCHAR(50),
  secret                VARCHAR(255),
) Engine=InnoDB CHARACTER SET 'latin1';

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

Load the schema into the database:

root@pdns:~# mysql -u root -p powerdns < schema.mysql.sql

PowerDNS configuration

First the default configurations inside /etc/powerdns/pdns.d/ should be removed. Everything seems to be relating to a default bind backend.

root@pdns:~# rm /etc/powerdns/pdns.d/*

Now create a new config file for the MySQL backend /etc/powerdns/pdns.d/pdns.local.gmysql.conf:

root@pdns:~# cat /etc/powerdns/pdns.d/pdns.local.gmysql.conf
# MySQL Configuration file



Enter the MySQL credentials you've used before when you created the database.

Restart PowerDNS:

root@pdns:~# systemctl restart pdns

First zone and test

You are now able to create a zone and add some DNS records using the PowerDNS commands:

root@pdns:~# pdnsutil create-zone
Creating empty zone ''

root@pdns:~# pdnsutil add-record www A
New rrset: IN A 3600

root@pdns:~# dig -t A @localhost

; <<>> DiG 9.11.3-1ubuntu1.5-Ubuntu <<>> -t A @localhost
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 19205
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available

; EDNS: version: 0, flags:; udp: 1680
;        IN    A

;; ANSWER SECTION:    3600    IN    A

;; Query time: 0 msec
;; WHEN: Tue Mar 26 16:30:26 CET 2019
;; MSG SIZE  rcvd: 60

Bravo, your first zone is up and can be resolved on the local PowerDNS.

Other config changes

The SOA serial

By default PowerDNS starts the zone's serial from 1 and increases it by +1 every time the zone is told to increase the serial. For example:

root@pdns:~# pdnsutil increase-serial

This will increase the serial of the zone If this is the first time increase-serial was called on that zone, it will now have a serial of 2. 

However I'm more a fan of the serial in format of the current date YYYYMMDDNN, e.g. 2019032701 where the last two fields (NN) stand for the revision within the same day. From RFC1912:

 The recommended syntax is YYYYMMDDnn (YYYY=year, MM=month, DD=day, nn=revision number.  This won't overflow until the year 4294.

To tell PowerDNS it should use this format, adjust /etc/powerdns/pdns.conf and set the default-soa-edit to INCEPTION-INCREMENT:

# default-soa-edit      Default SOA-EDIT value
# default-soa-edit=

Primary DNS Server (DNS Master)

Usually you only create and edit zones on the primary DNS server or DNS master. You may want to fix this in the config file, too:

# default-soa-name      name to insert in the SOA record if none set in the backend
# default-soa-name=a.misconfigured.powerdns.server

DNS Contact

Same as for the primary server, you may want to set a default contact e-mail address:

# default-soa-mail      mail address to insert in the SOA record if none set in the backend
# default-soa-mail=

After saving pdns.conf, restart PowerDNS:

root@pdns:~# systemctl restart pdns

Next up

The next article in this series will cover a DNS Master-Slave replication using PowerDNS.