Setting PowerDNS with MariaDB Replication

by jon · Published 31st October 2019 · Updated 13th January 2020

I’ve been wanting to change to PowerDNS for a little but havnt got around to it. After taking some PTO from work and buying a bunch of new Raspberry Pi’s i figured now is as good a time as any to build this out.

If you google this topic, there are loads of guides out there, all of them seem to be a rehash of the same source instructions which…doesnt work!

If you do follow those instructions you will get working DNS servers but you will NOT get working replication. At first glance it may look like it is but as soon as you try and push an update replication will fail. After several hours of hair pulling and googling i have managed to get this working!

This will be split into 4 parts, setup for both servers, setup for ns1 (master), setup for ns2 (slave) and finally the PowerDNS Admin page setup.

Both PowerDNS servers

Firstly, lets install MariaDB

apt-get install mariadb-server

Now lets run through some configuration for MariaDB before we start creating databases, tables and users:

Firstly, run through ‘mysql_secure_installation’ to set your root password etc

Then In:

/etc/mysql/mariadb.conf.d/50-server.conf

Add in the following, under the MariaDB heading:

# InnoDB

innodb_log_file_size = 64M

default-storage-engine=INNODB

innodb_buffer_pool_size=1G

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

transaction-isolation=READ-COMMITTED

Then restart MariaDB with

systemctl restart mariadb

Next, log into MariaDB as root with

mysql -u root -p

and add enter the following:

SET GLOBAL binlog_format = 'ROW';

I think that is the golden bit that a lot of guides have been missing! As once i did this on my test network, and reset up replication it started working.

While we are still logged in, lets create our database, user and tables:

CREATE DATABASE powerdns;

Then create the user:

GRANT ALL ON powerdns.* TO 'powerdns'@'localhost' \

IDENTIFIED BY '<SECRET_PASSWORD>';

FLUSH PRIVILEGES;

USE powerdns;

Now we need to create the tables:

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 UNSIGNED DEFAULT NULL,

  account               VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL,

  PRIMARY KEY (id)

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

  PRIMARY KEY (id)

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

  PRIMARY KEY (id)

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

  PRIMARY KEY (id)

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

  PRIMARY KEY(id)

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

  PRIMARY KEY (id)

) Engine=InnoDB CHARACTER SET 'latin1';

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

and finally, quit:

quit;

Next we need to stop and disable the sytemd resolver running on machine.

systemctl disable systemd-resolved

systemctl stop systemd-resolved

Now we need to install pdns, the mysql backend and also pdns-recursor if we are running both a recursive and Authoritive DNS server. If you are just running an Authoritive DNS server then dont add in pdns-recursor.

apt-get install pdns-server pdns-recursor pdns-backend-mysql

Once that lot is installed we can go about configuring it to use MariaDB for the backend.

First i’d suggest removing the current ‘bind’ config:

rm -rf /etc/powerdns/pdns.d/bind.conf

Then create and open a new file:

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

Put in the database details you setup earlier, like this:

# SQL Configuration (MariaDB)

# Launch gmysql backend

launch=gmysql

# gmysql parameters

gmysql-host=localhost

gmysql-port=3306

gmysql-dbname=powerdns

gmysql-user=powerdns

gmysql-password=pasword

gmysql-dnssec=yes

Next lets rename the default config, we dont want to remove it as its a good resource for checking out diferent settings:

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

Now create a new pdns.conf with the following settings:

# PowerDNS configuration file

# Replace ns1.example.com with your primary nameserver's hostname

default-soa-name=ns1.example.com

include-dir=/etc/powerdns/pdns.d

launch=

security-poll-suffix=

setgid=pdns

setuid=pdns

# Starts the API service

api=yes

# Sets webserver ad API to listen on specific address

webserver-address=192.168.0.70

# Allows access to the web server and api server to only these IP ranges.

webserver-allow-from=192.168.0.0/24,172.16.22.0/24

# Sets the port for the webserver

webserver-port=8081

# Enables the webserver

webserver=yes

# Replace <RANDOM_KEY> with a randomly generated key for API access

api-key=stupid-random-api-KEY

# Sets this server as the master server

master=yes

# Sets this server to NOT be a slave

slave=no

# Sets the serial of zones to default

default-soa-edit=INCEPTION-INCREMENT

# Defualt zone admin email

default-soa-mail=admin.haynet.host

# Only include the following 3 if you are running a recursive AND authoritive DNS server on the same machine

local-ipv6=

# Set the authoritive server to listen only on loopback interface

local-address=127.0.0.1

# Sets the authoritive servers listening port.

local-port=5300

I have commented on each section as to what they do.

*NOTE*

On the master server, leave ‘Master=yes’ and ‘slave=no’

On the secondary server, change it to ‘master=no’ slave=yes’

Also, if you arent going to run a recursive server, remove the last 3 settings:

# Only include the following 3 if you are running a recursive AND authoritive DNS server on the same machine

local-ipv6=

# Set the authoritive server to listen only on loopback interface

local-address=127.0.0.1

# Sets the authoritive servers listening port.

local-port=5300

Now restart PowerDNS:

systemctl restart pdns

It should restart fine, but likely wont be working properly yet.

Now we need to do the same for recursor.conf, rename it so we can reference it later if we need to:

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

Then create a new recursor.conf with the following entries:

# allow-from    If set, only allow these comma separated netmasks to recurse

allow-from=127.0.0.0/8, 10.0.0.0/8, 100.64.0.0/10, 169.254.0.0/16, 192.168.0.0/16, 172.16.0.0/12, ::1/128, fc00::/7, fe80::/10

# api-key   Static pre-shared authentication key for access to the REST API

api-key=testing123

# config-dir    Location of configuration directory (recursor.conf)

config-dir=/etc/powerdns

# forward-zones Zones for which we forward queries, comma separated domain=ip pairs

forward-zones=example.com=127.0.0.1:5300

# hint file If set, load root hints from this file

hint-file=/usr/share/dns/root.hints

# include-dir   Include *.conf files from this directory

include-dir=/etc/powerdns/recursor.d

# local-address IP addresses to listen on, separated by spaces or commas. Also accepts ports.

local-address=0.0.0.0

# lua-config-file   More powerful configuration options

lua-config-file=/etc/powerdns/recursor.lua

# quiet Suppress logging of questions and answers

quiet=yes

# security-poll-suffix  Domain name from which to query security update notifications

# security-poll-suffix=secpoll.powerdns.com.

security-poll-suffix=

# setgid    If set, change group id to this gid for more security

setgid=pdns

# setuid    If set, change user id to this uid for more security

setuid=pdns

# webserver Start a webserver (for REST API)

webserver=yes

# webserver-address IP Address of webserver to listen on

webserver-address=0.0.0.0

# webserver-allow-from  Webserver access is only allowed from these subnets

webserver-allow-from=192.168.0.0/24, 172.16.22.0/24

# webserver-password    Password required for accessing the webserver

# webserver-password=

# webserver-port    Port of webserver to listen on

webserver-port=8082

Make sure you change the ‘example.com’ to the domain you are authoritive for. You can add extra lines underneath for me.

Restart pdns-recursor with:

systemctl restart pdns-recursor

and you should now be able to get replies from your DNS servers.

MariaDB Replication – Master only

These settings apaply to the master only.

In this file:

/etc/mysql/mariadb.conf.d/50-server.cnf

make the following changes:

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 = powerdns

Then restart MariaDB with:

systemctl restart mariadb

Next we need to add the slave user:

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'pdns-secondary'@'<SECONDARY_SERVER_IP>' IDENTIFIED BY '<SECRET_PASSWORD>';

Remember to set the secondary IP and the secondary password.

FLUSH PRIVILEGES;

You can see if its working by running:

show master status;

Which will give you something like this:

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      634 | powerdns     |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.01 sec)

The ‘File’ and ‘Position’ are important, we will come back to them shortly.

MariaDB Replication – Slave server

On the slave server we are again editing the same file:

/etc/mysql/mariadb.conf.d/50-server.cnf

This time, editing to look like this:

server-id=2

relay-log=slave-relay-bin

relay-log-index=slave-relay-bin.index

replicate-do-db=powerdns

If you are adding more serveres, you can bump the server-id on each one.

Now restart MariaDB with:

systemtctl restart mariadb

Now is the time we need those two details from the ‘show master status’ so it would be wise to run it again. Take note of the ‘File’ and the ‘Position’.

On the secondary, log into MariaDB.

mysql -u root -p

Enter the following, its all one line so copy it into notepad, edit it, then paste it into the MariaDB console.

change master to

master_host='<PRIMARY_SERVER_IP>',

master_user='pdns-secondary',

master_password='<SECRET_PASSWORD>', master_log_file='mysql-bin.000001', master_log_pos=634;

Here, change master_host to the IP of the master server, set the msater_password to the password of the secondary user you created earlier, set the master_log to the ‘file’ from master status, same as the position.

Then start the slave:

start slave;

You can see the status of the slave with:

show slave status\G

It will show you an output a bit like this:

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.253
Master_User: pdns-secondary
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 363730
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 208937
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: powerdns
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 363730
Relay_Log_Space: 209246
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 182
1 row in set (0.001 sec)

That is it for the PowerDNS config. You should now have 2 DNS servers in-sync which (if you chose) are both recursive and authoritive.

PowerDNS Admin setup

The final part of this is to setup PowerDNS Admin. I have opted to run this on a seperate server, so somethings like install mariadb you dont need to so if you are installing it on your NS1.

Firstly, install MariaDB:

apt-get install mariadb-server

Log into MariaDB and create the database and user:

mysql -u root -p

CREATE DATABASE pdnsadmin CHARACTER SET utf8 COLLATE utf8_general_ci;

GRANT ALL PRIVILEGES ON pdnsadmin.* TO 'pdnsadminuser'@'%' IDENTIFIED BY '<SECRET_PASSWORD>';

FLUSH PRIVILEGES;

quit;

Now install the dependencies:

apt-get install python3-dev libmariadb-dev-compat libmariadb-dev python-mysqldb libsasl2-dev libffi-dev libldap2-dev libssl-dev libxml2-dev libxslt1-dev libxmlsec1-dev pkg-config virtualenv -y

Next we need to install ‘yarn’

curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -

echo "deb https://dl.yarnpkg.com/debian/ stable main" > /etc/apt/sources.list.d/yarn.list

apt update && apt install yarn -y

Now we grab PowerDNS Admin:

wget https://github.com/ngoduykhanh/PowerDNS-Admin/archive/master.tar.gz

Create new directory for it:

mkdir /opt/web

Extract the archive into that directory:

tar -C /opt/web -xvf master.tar.gz

Rename the directory to make it a little cleaner:

mv /opt/web/PowerDNS-Admin-master /opt/web/powerdns-admin

Finally, change into that directory and setup the virtualenv.

virtualenv -p python3 flask

Now we activate that virtualenv:

. ./flask/bin/activate

and install the PowerDNS Admin requirements:

pip install -r requirements.txt

A quick note that it may look like the process has locked up on

"Collecting xmlsec>=0.6.0"

It hasnt, this just seems to take a while to install.

Finally, we install our last dependency:

pip install python-dotenv

Now we need to rename the default config:

cp config_template.py config.py

and configure the databse with details you createed earlier in this section:

SQLA_DB_USER = 'pdnsadminuser'

SQLA_DB_PASSWORD = '<SECRET_PASSWORD>'

SQLA_DB_HOST = '127.0.0.1'

SQLA_DB_NAME = 'pdnsadmin'

SQLALCHEMY_TRACK_MODIFICATIONS = True

Now we need to create the DB Schema:

export FLASK_APP=app/__init__.py

flask db upgrade

and initialise the DB.

flask db migrate -m "Init DB"

Now we need to generate the assets:

yarn install --pure-lockfile

and then build them:

flask assets build

Again this may take a little time.

Once that is done, you can check that the app runs without errors with:

./run.py

You should get an output like the following:

(flask) [email protected]:/opt/web/powerdns-admin# ./run.py

 * Serving Flask app "app" (lazy loading)

 * Environment: production

   WARNING: Do not use the development server in a production environment.

   Use a production WSGI server instead.

 * Debug mode: on

[2019-10-29 20:44:01,051] [INFO] |  * Running on http://127.0.0.1:9191/ (Press CTRL+C to quit)

[2019-10-29 20:44:01,053] [INFO] |  * Restarting with stat

[2019-10-29 20:44:02,798] [WARNING] |  * Debugger is active!

[2019-10-29 20:44:02,799] [INFO] |  * Debugger PIN: 355-347-408

You can stop it with CTRL + C.

Next we need to create the sytsemd service file:

Create the following file:

/etc/systemd/system/powerdns-admin.service

And add the following into it:

[Unit]

Description=PowerDNS-Admin

After=network.target

[Service]

User=root

Group=root

WorkingDirectory=/opt/web/powerdns-admin

ExecStart=/opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerdns-admin/powerdns-admin.sock app:app

[Install]

WantedBy=multi-user.target

Now, run a daemon-reload, enable and start:

systemctl daemon-reload

systemctl start powerdns-admin

systemctl enable powerdns-admin

A ‘systemctl status powerdns-admin’ should look like this:

● powerdns-admin.service - PowerDNS-Admin

   Loaded: loaded (/etc/systemd/system/powerdns-admin.service; enabled; vendor preset: enabled)

   Active: active (running) since Tue 2019-10-29 20:55:01 GMT; 21s ago

 Main PID: 5317 (gunicorn)

    Tasks: 3 (limit: 4915)

   Memory: 69.3M

   CGroup: /system.slice/powerdns-admin.service

           ├─5317 /opt/web/powerdns-admin/flask/bin/python3 /opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerd

           ├─5320 /opt/web/powerdns-admin/flask/bin/python3 /opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerd

           └─5321 /opt/web/powerdns-admin/flask/bin/python3 /opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerd

Oct 29 20:55:01 dnsadmin.haynet.host systemd[1]: Started PowerDNS-Admin.

Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5317] [INFO] Starting gunicorn 19.7.1

Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5317] [INFO] Listening at: unix:/opt/web/powerdns-admin/powe

Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5317] [INFO] Using worker: sync

Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5320] [INFO] Booting worker with pid: 5320

Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5321] [INFO] Booting worker with pid: 5321

The final part of the setup is to instll NGINX:

apt-get install nginx

Create a new config file here:

/etc/nginx/conf.d/powerdns-admin.conf

And add the following into it, remembering to change the domain name:

server {

  listen *:80;

  server_name               <YOUR_DOMAIN_NAME>;

  index                     index.html index.htm index.php;

  root                      /opt/web/powerdns-admin;

  access_log                /var/log/nginx/powerdns-admin.local.access.log combined;

  error_log                 /var/log/nginx/powerdns-admin.local.error.log;

  client_max_body_size              10m;

  client_body_buffer_size           128k;

  proxy_redirect                    off;

  proxy_connect_timeout             90;

  proxy_send_timeout                90;

  proxy_read_timeout                90;

  proxy_buffers                     32 4k;

  proxy_buffer_size                 8k;

  proxy_set_header                  Host $host;

  proxy_set_header                  X-Real-IP $remote_addr;

  proxy_set_header                  X-Forwarded-For $proxy_add_x_forwarded_for;

  proxy_headers_hash_bucket_size    64;

  location ~ ^/static/  {

    include  /etc/nginx/mime.types;

    root /opt/web/powerdns-admin/app;

    location ~*  \.(jpg|jpeg|png|gif)

{

expires 365d;

}

location ~* ^.+.(css|js)

{

expires 7d;

}

location / {

proxy_pass http://unix:/opt/web/powerdns-admin/powerdns-admin.sock;

proxy_read_timeout 120;

proxy_connect_timeout 120;

proxy_redirect off;

}

Get NGINX to check the config is sane with:

nginx -t

Then reload NGINX

systemctl reload nginx

That should be it. You should now be able to hit your PowerDNS Admin interface by hostname.

All you need to do now is put in 3 details in PowerDNS Admin to connect to your NS1 via its API.

First create an account on PowerDNS Admin by going to:

http://yourdomain/register

Then login, you will get a box asking you for:

PDNS API URL
PDNS API KEY
PDNS VERSION

Because i have PDNS listening on the outside interface, rather than the loopback adapter, i need to set the URL to the public IP of the master server.

The API key is set in the master’s PDNS.conf

The veresion i used was 4.1.6.

You can find the version with pdns –version on the master.

That should be it. You can now add domain via the interface and they get replicated out to slaves via MariaDB replication.

Setting PowerDNS with MariaDB Replication

MariaDB Replication – Slave server

PowerDNS Admin setup

Leave a Reply Cancel reply

Recent Posts

Top Posts & Pages

Categories

Setting PowerDNS with MariaDB Replication

Both PowerDNS servers

MariaDB Replication – Master only

MariaDB Replication – Slave server

PowerDNS Admin setup

Share this:

Related

Leave a Reply Cancel reply

Recent Posts

Top Posts & Pages

Categories