Setting up PowerDNS with working MariaDB Replication
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-serverNow 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.confAdd 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-COMMITTEDThen restart MariaDB with
systemctl restart mariadbNext, log into MariaDB as root with
mysql -u root -pand 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-resolvedNow 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-mysqlOnce 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.confThen create and open a new file:
/etc/powerdns/pdns.d/pdns.local.gmysql.confPut 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=yesNext 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-origNow 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=5300I 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=5300Now restart PowerDNS:
systemctl restart pdnsIt 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-origThen 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=8082Make 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.cnfmake 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 = powerdnsThen restart MariaDB with:
systemctl restart mariadb
Next we need to add the slave user:
mysql -u root -pGRANT 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.cnfThis time, editing to look like this:
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
replicate-do-db=powerdnsIf you are adding more serveres, you can bump the server-id on each one.
Now restart MariaDB with:
systemtctl restart mariadbNow 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 -pEnter 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\GIt 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 -yNext 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 -yNow we grab PowerDNS Admin:
wget https://github.com/ngoduykhanh/PowerDNS-Admin/archive/master.tar.gzCreate new directory for it:
mkdir /opt/webExtract the archive into that directory:
tar -C /opt/web -xvf master.tar.gzRename the directory to make it a little cleaner:
mv /opt/web/PowerDNS-Admin-master /opt/web/powerdns-adminFinally, change into that directory and setup the virtualenv.
virtualenv -p python3 flaskNow we activate that virtualenv:
. ./flask/bin/activateand install the PowerDNS Admin requirements:
pip install -r requirements.txtA 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-dotenvNow we need to rename the default config:
cp config_template.py config.pyand 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 = TrueNow we need to create the DB Schema:
export FLASK_APP=app/__init__.py
flask db upgradeand initialise the DB.
flask db migrate -m "Init DB"Now we need to generate the assets:
yarn install --pure-lockfileand then build them:
flask assets buildAgain this may take a little time.
Once that is done, you can check that the app runs without errors with:
./run.pyYou should get an output like the following:
(flask) root@dnsadmin:/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-408You can stop it with CTRL + C.
Next we need to create the sytsemd service file:
Create the following file:
/etc/systemd/system/powerdns-admin.serviceAnd 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.targetNow, run a daemon-reload, enable and start:
systemctl daemon-reload
systemctl start powerdns-admin
systemctl enable powerdns-adminA ‘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 insatll NGINX:
apt-get install nginxCreate 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 -tThen reload NGINX
systemctl reload nginxThat 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/registerThen 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.