Lead Image © Sergey Nivens, 123RF.com

Lead Image © Sergey Nivens, 123RF.com

PowerDNS Authoritative server high availability with MariaDB Galera

Power Up

Article from ADMIN 70/2022
Combining the PowerDNS Authoritative server daemon with MariaDB's multiprimary Galera cluster allows a simple yet robust solution for your DNS needs.

Recently, I found myself in the need for a trio of Authoritative nameservers to disperse between my company's data centers. Having used a PowerDNS Recursive server for years, I was anxious to give their Authoritative version a heaping helping of DNS records.

Unlike PowerDNS Recursive, the Authoritative server requires a back-end system to store records. The list of supported back ends is rather lengthy, including but not limited to MySQL, PostgreSQL, Berkeley Internet Name Domain (BIND), and even Lightweight Directory Access Protocol (LDAP). I consider myself rather skilled at MariaDB, and because the Authoritative server supports MySQL, I knew that MariaDB would be a non-issue.

I've set up dozens of MySQL replication servers over the years, but I wanted to investigate something different, something a bit better suited to this project's specific needs. MySQL Replication uses a primary server to update one or more replicas, and because the transactions are committed sequentially, a slow transaction can cause replicas to trail behind the primary server. If the primary fails, it is entirely possible that the replica might not have recorded the last few transactions. With a transaction-safe engine, such as InnoDB, a transaction will either be completed on replica nodes or not at all. That just won't do.

Enter MariaDB's Galera Cluster. Galera is a virtually synchronous multiprimary cluster for MariaDB that is only available on Linux and only supports the InnoDB engine for storage (although MyISAM and Aria are in the works). With Galera, you get virtually synchronous replication, active-active multiprimary topology, read/write to any node, automatic membership control, automatic node joining, true parallel replication, and direct client connections. Those features translate to no replica lag, no lost transactions, read scalability, and smaller client latencies – perfect for keeping DNS records happy and healthy across data centers.


PowerDNS installation and setup has been covered at length in previous articles [1] [2]. I highly recommend both articles to get your Authoritative server into a solid configuration.

Assuming PowerDNS is installed and configured with the MySQL back end, you should crunch away on installing MariaDB. The first step is to download and run the MariaDB repo setup script:

wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup --mariadb-server-version="mariadb-10.5"

MariaDB documentation recommends installing dependencies separately to avoid conflicting packages from your OS vendor:

sudo dnf install perl-DBI libaio libsepol lsof boost-program-options
sudo dnf install --repo="mariadb-main" MariaDB-server

Once dependencies have been addressed, run the installation command for MariaDB and start the MariaDB server with systemctl:

sudo mysql_install_db
sudo systemctl start mariadb.service
sudo mysql_secure_installation

The final line ensures the use of basic security best practices.

Creating and Populating

A quick edit of your pristine MariaDB server's configuration file /etc/my.cnf.d/server.cnf (1) binds the MariaDB service to the localhost and (2) connects and (3) creates the database:

Bind-address =
mysql -h localhost -u root -p
create database pdns;

Listing 1 shows a quick user addition to the database and the granting of permissions. To exit from the MariaDB shell, use the quit; command.

Listing 1

User Creation

GRANT ALL ON pdns.* TO 'pdnsadmin'@'localhost' IDENTIFIED BY 'CreateAnAwesomePassword';
GRANT ALL ON pdns.* TO 'pdnsadmin'@'localhost.localdomain' IDENTIFIED BY 'CreateAnAwesomePassword';

For PowerDNS to work as intended, you need to add the default schema (Listing 2 for PowerDNS 4.3). Schemas for PowerDNS version 4.2 or 4.1 can be found on the PowerDNS documentation website [3] [4].

Listing 2

Populate a Database

01 use pdns;
03 CREATE TABLE domains (
04   id                    INT AUTO_INCREMENT,
05   name                  VARCHAR(255) NOT NULL,
06   master                VARCHAR(128) DEFAULT NULL,
07   last_check            INT DEFAULT NULL,
08   type                  VARCHAR(6) NOT NULL,
09   notified_serial       INT UNSIGNED DEFAULT NULL,
10   account               VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL,
11   PRIMARY KEY (id)
12 ) Engine=InnoDB CHARACTER SET 'latin1';
14 CREATE UNIQUE INDEX name_index ON domains(name);
16 CREATE TABLE records (
17   id                    BIGINT AUTO_INCREMENT,
18   domain_id             INT DEFAULT NULL,
19   name                  VARCHAR(255) DEFAULT NULL,
20   type                  VARCHAR(10) DEFAULT NULL,
21   content               VARCHAR(64000) DEFAULT NULL,
22   ttl                   INT DEFAULT NULL,
23   prio                  INT DEFAULT NULL,
24   disabled              TINYINT(1) DEFAULT 0,
25   ordername             VARCHAR(255) BINARY DEFAULT NULL,
26   auth                  TINYINT(1) DEFAULT 1,
27   PRIMARY KEY (id)
28 ) Engine=InnoDB CHARACTER SET 'latin1';
30 CREATE INDEX nametype_index ON records(name,type);
31 CREATE INDEX domain_id ON records(domain_id);
32 CREATE INDEX ordername ON records (ordername);
34 CREATE TABLE supermasters (
35   ip                    VARCHAR(64) NOT NULL,
36   nameserver            VARCHAR(255) NOT NULL,
37   account               VARCHAR(40) CHARACTER SET 'utf8' NOT NULL,
38   PRIMARY KEY (ip, nameserver)
39 ) Engine=InnoDB CHARACTER SET 'latin1';
40 CREATE TABLE comments (
41   id                    INT AUTO_INCREMENT,
42   domain_id             INT NOT NULL,
43   name                  VARCHAR(255) NOT NULL,
44   type                  VARCHAR(10) NOT NULL,
45   modified_at           INT NOT NULL,
46   account               VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL,
47   comment               TEXT CHARACTER SET 'utf8' NOT NULL,
48   PRIMARY KEY (id)
49 ) Engine=InnoDB CHARACTER SET 'latin1';
51 CREATE INDEX comments_name_type_idx ON comments (name, type);
52 CREATE INDEX comments_order_idx ON comments (domain_id, modified_at);
54 CREATE TABLE domainmetadata (
55   id                    INT AUTO_INCREMENT,
56   domain_id             INT NOT NULL,
57   kind                  VARCHAR(32),
58   content               TEXT,
59   PRIMARY KEY (id)
60 ) Engine=InnoDB CHARACTER SET 'latin1';
62 CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind);
64 CREATE TABLE cryptokeys (
65   id                    INT AUTO_INCREMENT,
66   domain_id             INT NOT NULL,
67   flags                 INT NOT NULL,
68   active                BOOL,
69   published             BOOL DEFAULT 1,
70   content               TEXT,
71   PRIMARY KEY(id)
72 ) Engine=InnoDB CHARACTER SET 'latin1';
74 CREATE INDEX domainidindex ON cryptokeys(domain_id);
76 CREATE TABLE tsigkeys (
77   id                    INT AUTO_INCREMENT,
78   name                  VARCHAR(255),
79   algorithm             VARCHAR(50),
80   secret                VARCHAR(255),
81   PRIMARY KEY (id)
82 ) Engine=InnoDB CHARACTER SET 'latin1';
84 CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm);

The basic configuration is complete, but you have a bit more to do. MariaDB needs to know that you intend to use Galera to cluster your PowerDNS database. To do that, edit /etc/my.cnf.d/server.cnf and add or modify the variables shown in Listing 3. Pay special attention to the wsrep_cluster_address variable because it is the list of IP addresses of all nodes in the cluster. To add more nodes, simply add their IP address separated by a comma.

Listing 3

Galera Config


Take note that the variables shown in Listing 3 are the minimal, mandatory variables to make Galera a happy camper, but many more tunable goodies can be found in Galera Variables documentation [5].

Additionally, the new cluster nodes will attempt to connect to other nodes listed in wsrep_cluster_address in search of a Primary Component, which will be the first node you bootstrap with the galera_new_cluster script. To bootstrap a new cluster and create a new Primary Component, run the command

sudo galera_new_cluster

only on the first node. This command identifies the first node as a "seed" to populate the databases of newly added nodes. Therefore, all nodes added to the cluster will automatically copy the complete schema and data without user intervention. How cool is that?!

More Power

Once the initial node is set up, simply follow the steps above two more times to create secondary and tertiary nodes. Remember to bootstrap only the first node in your cluster and verify that /etc/my.cnf.d/server.cnf is identical on all three. The systemctl command starts the other nodes after the Primary Component node.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus
Subscribe to our ADMIN Newsletters
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs

Support Our Work

ADMIN content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.