How To Install and Configure PowerDNS with a MariaDB Backend on Ubuntu 14.04

Introduction

PowerDNS is an advanced, high performance authoritative nameserver compatible with a number of backends. PowerDNS can use BIND configuration files, read information from MariaDB, MySQL, Oracle, PostgreSQL, and many other databases. Backends can easily be written in any language. In this case we will use MariaDB to store our zone file records.

MariaDB is a fork of MySQL, a relational database management system. Being a fork of a leading open source software system, it is notable for being led by its original developers. MariaDB retains full drop-in replacement capability with MySQL APIs and commands.

At the end of this tutorial, you will have a working PowerDNS nameserver that you can use to host DNS for any number of domains.

Prerequisites

Before you start the tutorial, please follow these prerequisites.

Droplet Requirements

• 512MB Droplet or larger
• Ubuntu 14.04 64-bit

PowerDNS is designed to be high performance, and low on resource usage. A 512MB Droplet should be plenty to run a PowerDNS server with a moderate amount of zones/records. This Droplet will be running Ubuntu 14.04 64bit.

Root Access

The rest of this tutorial will assume you are connected to your server with the root user account, or a user account with sudo privileges.

To enter the root shell from another account:

sudo su

Register Your Nameservers, Set Nameservers for Other Domains

You can do this before or after completing the technical setup, but for your new nameserver to be able to process real DNS requests, you have to register the nameserver domain or subdomain(s) as a nameserver at your registrar, using a glue record. Glue records are discussed in the tutorial linked below, although you will likely want to look up the process for registering nameservers / creating glue records at your registrar.

• How To Create Vanity or Branded Nameservers with DigitalOcean Cloud Servers

Note: When you’re setting up a DNS server, it helps to keep your domain names straight. You’ll most likely pick three subdomains for use with the nameserver itself. This tutorial uses hostmaster.example-dns.com, ns1.example-dns.com, and ns2.example-dns.com.

We’ll also present a domain that uses this nameserver as its SOA. In this tutorial, we’ll set up a zone file for example.com on your new PowerDNS nameserver.

This tutorial uses the following domain names as examples.

These three subdomains should have glue records that point to your PowerDNS Droplet’s IP address:

• hostmaster.example-dns.com

• ns1.example-dns.com

• ns2.example-dns.com

• Then, you should set example.com’s nameservers to the three nameservers shown above

Step 1 — Install Updates

It is always a good idea to make sure you have the latest updates installed.

Install updates:

apt-get update && apt-get upgrade -y

Step 2 — Install MariaDB

First we will import a key for the MariaDB repository:

apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

Next we will add the MariaDB APT repository:

add-apt-repository 'deb http://ftp.kaist.ac.kr/mariadb/repo/5.5/ubuntu trusty main'

Now we can install the MariaDB packages and dependencies (primarily libraries) using apt-get:

apt-get -y install libaio1 libdbd-mysql-perl libdbi-perl libmariadbclient18 libmysqlclient18 libnet-daemon-perl libplrpc-perl mariadb-client-5.5 mariadb-client-core-5.5 mariadb-common mysql-common mariadb-server mariadb-server-5.5 mariadb-server-core-5.5

During the installation, you will be prompted to set a password for the MariaDB root user.

Please enter a strong password for the database root user, and press ENTER.

You will be asked to confirm the new password. Enter the password again, and press ENTER to finish the setup process.

Step 3 — Secure and Configure MariaDB

By default MariaDB allows anonymous users and root access from remote clients. We will run the secure installation utility to disable those features.

Run this secure installation wizard:

mysql_secure_installation

You will be prompted to authenticate with the MariaDB root user password you created during the MariaDB setup. Then, press ENTER to continue. Entries are shown in red.

You already have a root password set, so you can safely answer **n**.

Change the root password? [Y/n] n

In our example we do not want to change the root password; although, if you did not set a password when installing MariaDB, now would be a good time to do so. Otherwise enter N and press ENTER.

Remove anonymous users? [Y/n]

It is recommended anonymous users be disabled. Press ENTER to accept the default answer of Y.

Disallow root login remotely? [Y/n] 

It is recommended that root not be used to administrate a remote database server. Press ENTER to accept the default answer of Y.

Remove test database and access to it? [Y/n]

You can keep the test database if you would like to experiment with MariaDB. In our example we decided to remove it. Press ENTER to accept the default answer of Y.

Reload privilege tables now? [Y/n] 

Reloading the privilege tables within the wizard will save us a step. Press ENTER to accept the default answer of Y.

Next we will increase the InnoDB log file size to 64MB. This will help if you have to debug issues in the future.

First we need to stop the MariaDB service:

service mysql stop

Remove any existing log files (if this isn’t a fresh MariaDB installation, you may want to back them up instead):

rm -f /var/lib/mysql/ib_logfile*

Open the config file with nano:

nano /etc/mysql/my.cnf

Press CTRL+W to search the file. Enter InnoDB into the search field, then press ENTER to continue. You will be taken to the InnoDB portion of the config file. You will need to add the line highlighted in red below.

# * InnoDB
# 
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

innodb_log_file_size = 64M

# 
# * Security Features

Press CTRL+X, press Y to save the file, and press ENTER to overwrite.

Finally, start the MariaDB service again:

service mysql start

If the startup script returns the status [OK], the log file size has been updated successfully and you are ready to proceed to the next section.

Step 4 — Create the PowerDNS Database and User Account in MariaDB

Throughout this section and the rest of the tutorial, we will use recommended names like “powerdns” and “powerdns_user”. Feel free to substitute your own database and database user names, and make sure you use the updated names throughout.

You should definitely change the password. Be sure to replace text highlighted in red with your own information.

Note: The MySQL shell will not process a command until you end the line with ;. You will notice our table commands use multiple lines; this is normal.

First, authenticate with the MariaDB root user:

mysql -u root -p

Enter the root database password, then press ENTER to access the database server.

Create the database. You can use whatever name you want, but we will use powerdns:

CREATE DATABASE powerdns;

Create a new user called “powerdns_user” and grant access to the database. You should replace powerdns_user_password with a unique password:

GRANT ALL ON powerdns.* TO 'powerdns_user'@'localhost' IDENTIFIED BY 'powerdns_user_password';

Flush the privileges to update the user settings:

FLUSH PRIVILEGES;

Use the new powerdns database:

USE powerdns;

Next, we will add some tables to the database that PowerDNS can use to store its zone file entries.

Create the domains table:

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

Set the unique index:

CREATE UNIQUE INDEX name_index ON domains(name);

Create the records table:

CREATE TABLE records (
id INT auto_increment,
domain_id INT DEFAULT NULL,
name VARCHAR(255) DEFAULT NULL,
type VARCHAR(6) DEFAULT NULL,
content VARCHAR(255) DEFAULT NULL,
ttl INT DEFAULT NULL,
prio INT DEFAULT NULL,
change_date INT DEFAULT NULL,
primary key(id)
);

Set the indexes:

CREATE INDEX rec_name_index ON records(name);
CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);

Create the supermasters table:

CREATE TABLE supermasters (
ip VARCHAR(25) NOT NULL,
nameserver VARCHAR(255) NOT NULL,
account VARCHAR(40) DEFAULT NULL
);

Now we can exit the MySQL shell:

quit;

Step 5 — Install PowerDNS

As mentioned earlier, MariaDB is a drop-in replacement for MySQL. So, we’ll install the main PowerDNS module, as well as the corresponding MySQL backend module.

Install PowerDNS:

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

Note: If you are prompted with dependency errors regarding mysql-client, the following command will remove the conflicting package and force the installation of PowerDNS packages.

apt-get -f purge -y mysql-client

You will be prompted to configure the MySQL backend. We will perform this process manually in a moment, so use the arrow keys to select <No>, and press ENTER to finish the installation.

Step 6 — Configure PowerDNS

We have to configure PowerDNS to use our new database.

First, remove the existing configuration files:

rm /etc/powerdns/pdns.d/*

Now we can create the MariaDB configuration file:

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

Enter the following data into the file. Remember to add your own database settings for gmysql-dbname, gmysql-user, and especially gmysql-password.

# MySQL Configuration file

launch=gmysql

gmysql-host=localhost
gmysql-dbname=powerdns
gmysql-user=powerdns_user
gmysql-password=powerdns_user_password

Restart PowerDNS to apply changes:

service pdns restart

Step 7 — Test PowerDNS

These steps are a good sanity check to make sure PowerDNS is installed and can connect to the database. If you do not pass the following tests, then something is wrong with your database configuration. Repeat Steps 4 and 6 to resolve the problem.

Check if PowerDNS is listening:

netstat -tap | grep pdns

You should see an output similar to:

root@ns1:~# netstat -tap | grep pdns
tcp        0      0 *:domain                *:*                     LISTEN      5525/pdns_server-in

Check if PowerDNS responds correctly:

dig @127.0.0.1

You should see an output similar to:

root@ns1:~# dig @127.0.0.1

; <<>> DiG 9.9.5-3-Ubuntu <<>> @127.0.0.1
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 27248
;; flags: qr rd; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 2800
;; QUESTION SECTION:
;.				IN	NS

;; Query time: 1 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Sun Nov 02 18:58:20 EST 2014
;; MSG SIZE  rcvd: 29

Did everything check out? Great! Let’s keep going.

Step 8 — Install Poweradmin

Poweradmin is a web-based DNS administration tool for PowerDNS. It has full support for all zone types (master, native, and slave). It has full supermaster support for automatic provisioning of slave zones, full support for IPv6, and multiple languages. You can view the feature list for more details.

Install Apache and the required dependencies for Poweradmin:

apt-get install -y apache2 gettext libapache2-mod-php5 php5 php5-common php5-curl php5-dev php5-gd php-pear php5-imap  php5-ming php5-mysql php5-xmlrpc php5-mhash php5-mcrypt 

Install the required PEAR modules:

pear install DB

pear install pear/MDB2#mysql

Enable Mcrypt:

php5enmod mcrypt

Restart Apache to apply the changes:

service apache2 restart

Change to your home directory:

cd ~

Download the compressed Poweradmin files:

wget https://github.com/downloads/poweradmin/poweradmin/poweradmin-2.1.6.tgz

Extract the archive:

tar xvzf poweradmin-2.1.6.tgz

Move the poweradmin directory to the Apache web directory:

mv poweradmin-2.1.6 /var/www/html/poweradmin

Create the configuration file:

touch /var/www/html/poweradmin/inc/config.inc.php

Give the Apache user ownership of the directory:

chown -R www-data:www-data /var/www/html/poweradmin/

Step 9 — Configure Poweradmin

To finish the installation of Poweradmin we will use the web-based configuration wizard.

Open your web browser and visit the URL below, substituting your own IP address or server hostname:

• http://your_server_ip/poweradmin/install/

Select your preferred language and click the Go to step 2 button.

There is some valuable information on the step 2 page, especially for multiple installations of Poweradmin. This information does not directly apply to this tutorial. When you are done reading the page, click the Go to step 3 button.

On the Installation step 3 page, we will need to enter the following information:

• Username: powerdns_user, or whatever username you created for MariaDB
• Password: powerdns_user_password, the database password you created earlier
• Database type: Select MySQL from the dropdown menu; remember that MariaDB acts like MySQL
• Hostname: 127.0.0.1 because we are connecting from localhost
• DB Port: 3306; leave the default
• Database: powerdns, or the database name you created earlier
• Poweradmin administrator password: Please set a unique password that you will use to log into the Poweradmin control panel later on; the username will be admin

Click the Go to step 4 button.

On the Installation step 4 page you have a choice between convenience and security. You can reuse the same database settings, or create a new less-privileged database user for Poweradmin. This example shows the same database user settings. You’ll also choose your nameserver domains.

• Username: Use a new or existing database user; in this case we’re using powerdns_user
• Password: Set a new password or use the existing database password of powerdns_user_password
• Hostmaster: Set the default hostmaster, such as hostmaster.example-dns.com
• Primary nameserver: Set the primary nameserver, such as ns1.example-dns.com
• Secondary nameserver: Set the secondary nameserver, such as ns2.example-dns.com

Click the Go to step 5 button.

Verify that the database information is correct. If you chose to create a new user and password, then you should log into your MariaDB database and add the new user by copying and pasting the code block shown on the screen, starting with GRANT. Then click the Go to step 6 button.

You should see a message like The installer was able to write to the file “…/inc/config.inc.php” . . .. If you have issues writing to the configuration file, that means you missed a step during the installation process.

If this step failed, go back to your server and create the file:

touch /var/www/html/poweradmin/inc/config.inc.php

Then restart the installation process again by refreshing the page.

Otherwise, click the Go to step 7 button to finish the installation.

You’ll be given the username admin and your Poweradmin control panel password.

We are done with the configuration of Poweradmin.

To clean up, go back to your server and delete the installation directory. Poweradmin requires us to do this before we can log in:

rm -rf /var/www/html/poweradmin/install/

Poweradmin Configuration Changes

If you need to make changes to the Poweradmin settings after finishing the installation, edit this file:

nano /var/www/html/poweradmin/inc/config.inc.php

Here you can update the database connection settings and other configuration settings for Poweradmin.

Step 10 — Create Your First DNS Record

Access the Poweradmin control panel:

• http://your_server_ip/poweradmin/

Log in to your Poweradmin control panel using the credentials you set up during the configuration. The username is admin and the password is the Poweradmin administrator password from the Installation step 3.

Click the Add Master Zone link.

Enter the domain name in the Zone name field. This domain should be one that for which you want to host a zone file. You can leave all other settings with their default entries. Click the Add zone button.

Click the List zones link from the top menu.

Click the edit button for your zone file, which looks like a small pencil on the left of the zone entry.

Add a DNS record for your domain.

• You can add a subdomain in the Name field, or leave it blank for the primary domain.
• Choose the Type of record from the dropdown menu.
• Add the IP address, domain name, or other entry in the Content field.
• Set the Priority if needed.
• Set the TTL in seconds.

Click the Add record button.

You can add additional records, or go back to the List zones page and the edit button for your domain to view all the current records for that domain.

Remember that for this record to actually function, you need to:

• Register the nameserver domains with glue records
• Set the nameservers for this domain to be the new PowerDNS nameserver domains
• Wait for propagation

However, we can check that the records are correct locally right away.

Step 11 — Test Your DNS Record

Note: Substitute example.com with your own domain or subdomain record.

On your server, look up the record for your domain:

dig example.com A @127.0.0.1

You should see an output similar to:

root@ns1:~# dig example.com A @127.0.0.1

; <<>> DiG 9.9.5-3-Ubuntu <<>> example.com A @127.0.0.1
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 20517
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 2800
;; QUESTION SECTION:
;example.com.			IN	A

;; ANSWER SECTION:
example.com.		86400	IN	A	104.131.174.136

;; Query time: 4 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Sun Nov 02 19:14:48 EST 2014
;; MSG SIZE  rcvd: 56

You can check all the other records as well, if you added multiple zone entries.

If these are correct, this means that this nameserver has the correct information!

However, it doesn’t mean that the nameserver domains are registered, that this domain is using your new nameservers as SOAs, or that the change has propagated globally yet.

Conclusion

We set up a PowerDNS server with a MariaDB backend. We set up the Poweradmin control panel to manage the backend. We created our first DNS zone, and created an A record for that zone.

Where do we go from here

If you have not done so already, you need to register your nameservers.

You also need to choose these nameservers as the SOAs for any domains for which you want to host DNS.

If you need assistance configuring your domain(s), the tutorials below will help you get you started. You may also need to check for instructions from your registrar.

• How To Create Vanity or Branded Nameservers with DigitalOcean Cloud Servers
• How To Set Up and Test DNS Subdomains with DigitalOcean’s DNS Panel
• How to Point to DigitalOcean Nameservers From Common Domain Registrars (Keep in mind that you would want to set these to ns1.example-dns.com, etc., NOT the DigitalOcean nameservers.)