PowerDNS Authoritative DNS Server 4.1 with MySQL backend on Ubuntu 18.04

Written by - 2 comments

Published on - last updated on April 1st 2019 - Listed in DNS Linux Ubuntu PowerDNS

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 example.com ns1.example.com
Jan 23 16:06:56 [bindbackend] Done parsing domains, 0 rejected, 0 new, 0 removed
Creating empty zone 'example.com'
Domain 'example.com' 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 https://github.com/PowerDNS/pdns/issues/5783 and https://github.com/PowerDNS/pdns/issues/6954 .

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 http://repo.powerdns.com/ubuntu bionic-auth-41 main" >> /etc/apt/sources.list.d/powerdns.list

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

root@pdns:~# curl -s https://repo.powerdns.com/FD380FBB-pub.asc | 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 https://raw.githubusercontent.com/PowerDNS/pdns/rel/auth-4.1.x/modules/gmysqlbackend/schema.mysql.sql

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 example.com
Creating empty zone 'example.com'

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

root@pdns:~# dig -t A www.example.com @localhost

; <<>> DiG 9.11.3-1ubuntu1.5-Ubuntu <<>> -t A www.example.com @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
;www.example.com.        IN    A

www.example.com.    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 example.com

This will increase the serial of the zone example.com. 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.

Add a comment

Show form to leave a comment

Comments (newest first)

ck from Switzerland wrote on Feb 26th, 2020:

Jim, basically all you need to do is to follow the correct documentation on the official PowerDNS website. This is sometimes kind of confusing (this is where I made the error and imported the wrong schema). As long as you use the correct schema version, you should be good to go.

Jim from wrote on Feb 26th, 2020:

Hi Claudio,

I would like to use this guide but PowerDNS 4.2 seems to be the standard release now.

As you write in your tutorial: 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."

Any specifics to look out for if I want to install 4.2?

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder