Debian 7 PowerDNS Server ( PowerDNS Authoritive Server ) with MariaDB ( MySQL ) with DNSSEC

Assumptions

Minimal Debian 7 installation
Completed Proxmox / OpenVZ Container Debian 7 wheezy Server Preparation

#Hostname
dbnode1 11.11.11.11

#Ports<
TCP 3306 : MySQL

TCP/UDP 53 : DNS

TCP 8081 : 127.0.0.1 : PowerDNS website

TCP 8004 : 127.0.0.1 : PowerAdmin

Set the timezone

Set the system timezone to UTC

Remove conflicting solutions and configs

Install the latest mariadb

add the repository

Import the GP key
If this command fails, disable your firewall eg. csf -x

Apt-Pinning the packages

Install MariaDB

New password for the MariaDB “root” user: yousecurepassword

Secure MariaDB

Enter current password for root (enter for none): yoursecurepassword
Change the root password? N
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

Stop MariaDB (mysql server)

Force Innodb logfile to 64mb

Start MariaDB (mysql server)

Confirm MariaDB is running

tcp 0 0 localhost:mysql *:* LISTEN 11878/mysqld

Install the latest xtrabackup

The main advantage of using xtrabackup to synchronize the nodes, is that the Donor is writeable during the synchronization process.
add the repository

Import the GP key

Install Xtrabackup and Percona-toolkit
Note: install netcat-openbsd fixes the error: nc: invalid option — ‘d’

Note: I have decided to use the SolusVM PowerDNS Tables, this adds an extra the column solusvm_cid to the domains table.
This was done to allow support for SolusVM users, it will not affect you if you are not using SolusVM.

The database will automatically create all the required tables

Prepare the Database ( PowerDNS MySQL + DNSSEC MySQL + PowerAdmin )

Only run this once, and only on one of the nodes.
Create a the PowerDNS userDBNODE1 only

Create a the PowerDNS user

Install PowerDNS Authoritive Server with MySQL

Configure database for pdns-backend-mysql with dbconfig-common? [N]o

Add PowerDNS MySQL Config

Enable DNS Caching

Optional: Enable PowerDNS built in webserver

Start PowerDNS Server

Use the powerdns server as a local dns server

Optional: Enable PowerDNS DNSSEC

enable dnssec

Restart PowerDNS Server

Rectify the existing zonesdbnode1 only

Reload all zones

Optional: Disable PowerDNS DNSSEC

Disable dnssec

Restart PowerDNS Server

Reload all zones

Optional: Secure the DNS Server with CSF

Configure Allowed CSF Ports for CSF Firewall

Restart CSF

For reference

Install Monit to monitor and correct any issues with the DNS servers: https://extremeshok.com/category/monitoring/

PowerAdmin : https://github.com/stbuehler/poweradmin/tree/master/sql

5 replies
  1. dns4me
    dns4me says:

    Thanks! I got this error:

    ERROR 1064 (42000) at line 13: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”cryptokeys’ (
    ‘id’ int(11) NOT NULL AUTO_INCREMENT,
    ‘domain_id’ int(11) NOT’ at line 1

    Reply
  2. anonymous contributor
    anonymous contributor says:

    Nice writeup.  2 things:

    I get the same error mentioned in a previous commentERROR 1064 (42000) at line 13: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”cryptokeys’ (
    ‘id’ int(11) NOT NULL AUTO_INCREMENT,
    ‘domain_id’ int(11) NOT’ at line 1

    You have an error under “Add PowerDNS MySQL Config” section:

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

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

    Cheers

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *