Hey,

I thought I would give the PowerDNS / pdns Setup a try with MySQL as backend. Its been too many years suffering from BIND oddities. So what you read here is a draft for setting up PowerDNS with MySQL as storage backend.

To get an idea what pdns can do for you, you may want to read e.g. the tricks you can do with LUA here https://doc.powerdns.com/authoritative/lua-records/index.html
As well you may want to read about the concept of having one hidden master pdns with database connected. Then have several smaller pdns setups, called pdns-recursor that only cache the data from the one central instance. That makes up a resilient setup with minimal maintenance as all pdns-recursor learn dynamically about the central data.

You may want the read the generic PowerDNS documentation to get all details. In this blog article I'll just dump what I've done to get a test setup in my lab.
Unfortunately the powerdns setup webpages do not talk a lot about mysql based backend initial setup, so I combined snippets from other guides into this article here.

Install the packages PowerDNS and MySQL
pfexec pkg install -v pdns mysql-80. (or if you only want the mysql client on this machine, then use mysql-80/client@8. Your MySQL server may run elsewhere)

Read about the very first time setup for MySQL Sever here: https://sfe.opencsw.org/SFEmysql80
have the MySQL root password setup and the server running. (in case you start this database instance the very first time)
svcadm enable mysql:version_80
you find the initial password in clear text in the log: (assume root user)
root-shell# grep password /var/mysql/8.0/data/${HOSTNAME}.err

And MySQL set the bar to access the data even higher, as you are forced to change the initial password with only this command:
shell> mysql -u root -p
(enter the password seen above, and replace the string below PASSWORD_HERE with what _you_ private password should read.)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';
Query OK, 0 rows affected

For convenience, I'll place the password in the local $HOME/my.cnf
[client]
user=root
password=PASSWORD_HERE

After that, the user may call the mysql client and get a password-less login:
shell> mysql -u root
prompt>
(use \q for quit)

If you want a smaller footprint of your MySQL Server, you may want to run this script and adjust the values in the MySQL Server config:
https://tech.labelleassiette.com/how-to-reduce-the-memory-usage-of-mysql...
On my OmniOS Install it is 630MB virtual and 430MB non swappable (without modification and without any usage of the Database).
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/LWP
20379 mysql 638M 427M sleep 4 0 0:00:06 0.1% mysqld/20

Important! In case you do not change the ZFS storage location to some place *outside* of your bootenvironment, then you *may* loose updates to your Database in case you do a snapshot+upgrade of your Operating System or simply switch your boot-environments and reboot.
The Setup-Guide to MySQL above gives you some ideas on how to place the ZFS Filesystems outside the Bootenvironments, so you always have only one data store shared across all bootenvironments.

User and Database setup:
create the mysql database and a user powerdns:
shell> mysql -u root
> CREATE DATABASE powerdns;

create the powerdns user in MySQL:
shell> mysql -u root
> USE powerdns;
> CREATE USER 'powerdns'@'localhost IDENTIFIED BY 'ANOTHER_PASSWORD_HERE';

Create the database tables with the SQL-script provided in the package:
The path to the script is: /usr/share/doc/pdns/schema.mysql.sql (this is the sql code shown on the pdns website for gmysql schema pdns v4.7)

To read in the sql script use this: (specify the name of the database, here: powerdns)
mysql -u root powerdns < /usr/share/doc/pdns/schema.mysql.sql
(there is no screen output if successful!)

Grant rights to user powerdns on all the tables:
shell> mysql -u root powerdns
> GRANT ALL PRIVILEGES ON powerdns.* TO 'powerdns'@'localhost';
Query OK, 0 rows affected
> FLUSH PRIVILEGES;

Tell PowerDNS where the database is
Now tell the pdns config where to find the database and the backend module for mysql. There is a sample pdns.conf, copy this:
cp -p /etc/pdns.conf-dist /etc/pdns.conf

Edit /etc/pdns.conf
Add these lines: (e.g. at the very top of the file)

#################################
# launch Which backends to launch and order to query them in
#
# launch=
launch=gmysql
gmysql-host=127.0.0.1
#gmysql-socket=/tmp/mysql.sock # (or /tmp/mysqlx.sock)
gmysql-user=powerdns
gmysql-password=ANOTHER_PASSWORD_HERE
gmysql-dbname=powerdns
#################################

Then start the pdns_server
#> pdns_server

Apr 07 22:07:18 This is a standalone pdns
Apr 07 22:07:18 Created local state directory '/var/run/pdns/'
Apr 07 22:07:18 Listening on controlsocket in '/var/run/pdns/pdns.controlsocket'
Apr 07 22:07:18 UDP server bound to 0.0.0.0:53
Apr 07 22:07:18 UDP server bound to [::]:53
Apr 07 22:07:18 TCP server bound to 0.0.0.0:53
Apr 07 22:07:18 TCP server bound to [::]:53
Apr 07 22:07:18 PowerDNS Authoritative Server 4.7.3 (C) 2001-2022 PowerDNS.COM BV
Apr 07 22:07:18 Using 64-bits mode. Built using gcc 11.2.0 on Apr 7 2023 15:45:41 by sfe@omnios..
Apr 07 22:07:18 PowerDNS comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to redistribute it according to the terms of the GPL version 2.
Apr 07 22:07:18 Polled security status of version 4.7.3 at startup, no known issues reported: OK
Apr 07 22:07:18 Creating backend connection for TCP
Apr 07 22:07:18 About to create 3 backend threads for UDP
Apr 07 22:07:18 Done launching threads, ready to distribute questions

For another check if the mysql connect has happened, do this:

shell> sudo pfiles `pgrep pdns` | grep 3306
peername: AF_INET 127.0.0.1 port: 3306
peername: AF_INET 127.0.0.1 port: 3306
peername: AF_INET 127.0.0.1 port: 3306
peername: AF_INET 127.0.0.1 port: 3306

In case you didn't manage to connect to mysql, the pdns_server might output errors like this:
Apr 07 22:11:16 PDNSException while filling the zone cache: Unable to launch gmysql connection: Unable to connect to database: ERROR 1045 (28000): Access denied for user 'powerdns'@'localhost' (using password: YES)

Next TODO for the SFEpdns package is to add a manifest.xml to get a service in SMF defined. Maybe it is possible to run as non-root user and assign privilege to bind to lower ports by SMF.

You should be able to add records now, see e.g. this guide:

to-be-continued!

references used:
https://www.howtoforge.com/installing-powerdns-with-mysql-on-centos
https://tech.labelleassiette.com/how-to-reduce-the-memory-usage-of-mysql...
https://doc.powerdns.com/authoritative/lua-records/index.html