Setting Up PowerDNS with MySQL Database Replication on Ubuntu 18.04

I recently had to setup DNS servers for a project, so I figured that today’s as good a time as any to start documenting what I do.

The goal of this guide is to have PowerDNS configured with a MySQL backend, and then use MySQL replication to sync information between slave servers.

Prerequisites

  • At least two servers. I’ve been using DigitalOcean since 2014 and I always recommend them, their $5/month offering works great for DNS. But of course, any host supporting Ubuntu 18.04 will do just nicely.

Installing MySQL (All Servers)

First, we need to install MySQL on both the master and slave servers. This is an easy step: Just check for updates and install MySQL:

apt update
apt install mysql-server

A new feature in MySQL 5.7 and up on Ubuntu servers: MySQL is setup to use the auth_socket plugin for the root user by default, rather than password logins. This makes it easier to manage and more secure overall. We also won’t have to configure things like the root password.

Installing PowerDNS (All Servers)

Now we can install PowerDNS, and the MySQL backend plugin.

apt install pdns-server pdns-backend-mysql

During this installation, you’ll be prompted to configure a database for PowerDNS automatically. Do not do this. This generally doesn’t work in Ubuntu 18.04, as the root MySQL user no longer has a password, and is buggy overall. We’ll configure the database manually later.

The installation process will end with some messages from systemctl that look like an error, because without the database setup, pdns-server failed to start. This is expected, and we’ll fix that next.

Configuring MySQL (All Servers)

First things first, we will need to create a database for PowerDNS. Login to MySQL:

mysql

Then, create the database, and a user for PowerDNS to use:

CREATE DATABASE pdns;
GRANT ALL PRIVILEGES ON pdns.* TO 'pdns'@'localhost' IDENTIFIED BY '<YOUR PASSWORD HERE>';
FLUSH PRIVILEGES;

Of course, replacing <YOUR PASSWORD HERE> with your own password, preferably randomly generated. If you don’t yet use a password manager, now is probably the time to get started.

Now, we’ll need to create a bunch of tables. Just paste the following schema into the MySQL prompt.

USE pdns;

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                    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,
  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);

This creates the default schema for PowerDNS, all the tables and records it needs to operate. Now, exit MySQL:

exit;

Configuring PowerDNS (All Servers)

With MySQL all setup, we’ll need to change PowerDNS’ configuration. Open the MySQL configuration file first:

nano /etc/powerdns/pdns.d/pdns.local.gmysql.conf

And add the password for the pdns MySQL user we created earlier to the gmysql-password line. Save and exit.

Now, make a backup of the default configuration:

mv /etc/powerdns/pdns.conf /etc/powerdns/pdns.conf-orig

And create a new configuration file:

nano /etc/powerdns/pdns.conf

Pasting in the following configuration:

# Replace ns1.example.com with your master nameserver's hostname
default-soa-name=ns1.example.com
include-dir=/etc/powerdns/pdns.d
launch=
security-poll-suffix=
setgid=pdns
setuid=pdns

Additionally, on the master server only, include the following:

api=yes
# Replace <RANDOM STRING> with a randomly generated key for API access
api-key=<RANDOM STRING>

We’re enabling API access here, but it will only be accessible via localhost. This is for PowerDNS-Admin, the web client we’ll be installing later.

Disable systemd-resolved (All Servers)

Ubuntu 18.04 has some built-in resolver running on port 53 that will interfere with PowerDNS. Just disable it now.

systemctl stop systemd-resolved
systemctl disable systemd-resolved

Now, we should be able to start PowerDNS:

systemctl start pdns

MySQL Replication Setup

These steps are very important and somewhat complicated, but once it’s setup you should never have to touch it again. This setup allows for near instant DNS updates across all your nameservers, and has some benefits over PowerDNS’ other replication method, the “supermaster” setup, which can be unstable at times and doesn’t remove deleted zones across every server automatically.

Master Server Configuration

On the master server only, edit the MySQL configuration file:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Changing the following settings:

bind-address = 0.0.0.0

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = pdns

Then, restart MySQL:

systemctl restart mysql

Now, we need to create a user for the slave servers, so they can access the database to replicate it. Login to MySQL

mysql

And create a user for your slave server. An important thing I noticed: I had to set the slave’s IP address in the MySQL user (as shown below), I couldn’t get replication working with a wildcard. If you want to try using a wildcard host for the user, YMMV.

GRANT REPLICATION SLAVE ON *.* TO 'pdnsslave'@'<IP OF YOUR SLAVE SERVER>' IDENTIFIED BY '<REPLACE PASSWORD HERE>';
FLUSH PRIVILEGES;

If you have more than one slave server, the way I tested it, I repeated this step to create a new replication slave user for each server, changing the username each time. You may be able to use the same user for all servers, but I didn’t test that configuration.

Now, we need some important information from MySQL before we can configure our slave servers:

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      606 | pdns         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Note the file and position values here, in this case mysql-bin.000001 and 606. We’ll need those numbers later.

Slave Server Configuration

On the slave server(s) only, open the MySQL configuration file:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

And make the following changes:

server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=pdns

The server-id variable needs to be unique for each server, so if you have more than one slave server increase it accordingly, i.e. server-id=2, server-id=3, etc.

Restart MySQL:

systemctl restart mysql

Now, we will configure the slave server to login to the master MySQL database and replicate it locally. Enter MySQL:

mysql

Enter the following. Remember the file and position we noted earlier? You’ll be entering those here, as well as the password we created for the slave user.

change master to
master_host='<MASTER SERVER'S IP>',
master_user='pdnsslave',
master_password='<PASSWORD>', master_log_file='mysql-bin.000001', master_log_pos=606;
start slave;

Now, check the slave status to see if it worked:

show slave status;

Conclusion

That should be it! If you see any error messages following that last command, some additional troubleshooting may be required. But otherwise, you should now have MySQL replication.

In the next few days, I’ll be posting a guide on setting up PowerDNS-Admin on your master server for web-based zone configuration. It’s really a great tool for zone editing. Stay tuned!

I hope you found this guide helpful! If there’s something you feel I missed, please feel free to reach out and let me know.