How to do bulk/mass changes on DNS records in PowerDNS with MySQL backend

Written by - 0 comments

Published on - Listed in DNS PowerDNS Linux Coding Shell MySQL Database

Updating a single DNS record in PowerDNS is easy. There are several possibilities to do that:

  • Use the PowerDNS CLI (which uses the PowerDNS API in the background)
  • Use a user interface, for example the Opera DNS UI for PowerDNS
  • Change the record in the MySQL database, then increase the serial and reload the zone(s)
  • probably more...

But the problem is: What if you know you have hundreds or thousands of domains and you need to update the same kind of record (for example a CNAME, A or TXT record) for all domains?

This article describes how PowerDNS (with a MySQL backend) stores DNS records and how they are assigned to a domain name (zone) and how to use a special script (pdns-search-replace.sh) to do bulk DNS changes, which searches for a specific string in a record and replaces this found string with a replacement string (which can be empty).

How DNS records are stored in PowerDNS

Luckily the data structure inside the powerdns database is very sane. The amount of tables are small and the names are well chosen:

mysql> use powerdns;
Database changed

mysql> show tables;
+--------------------+
| Tables_in_powerdns |
+--------------------+
| comments           |
| cryptokeys         |
| domainmetadata     |
| domains            |
| records            |
| supermasters       |
| tsigkeys           |
+--------------------+
7 rows in set (0.00 sec)

It is pretty self-explaining what kind of data is stored in which table. DNS records can therefore be found in the records table, domains (zones) can be found in the domains table.

A typical DNS record looks like this:

mysql> SELECT * FROM records LIMIT 0,1;
+----+-----------+---------------+------+---------------+------+------+-------------+----------+-----------+------+
| id | domain_id | name          | type | content       | ttl  | prio | change_date | disabled | ordername | auth |
+----+-----------+---------------+------+---------------+------+------+-------------+----------+-----------+------+
|  5 |         1 | infiniroot.com | A    | 212.103.71.210 | 38400 |    0 |        NULL |        0 | NULL      |    1 |
+----+-----------+---------------+------+---------------+------+------+-------------+----------+-----------+------+
1 row in set (0.00 sec)

The most important columns here are the following:

  • domain_id: The ID of the domain this record belongs to (this serves as the relational connect to the domain)
  • name: The DNS record itself
  • type: What kind of DNS record is this (A, CNAME, TXT, MX, ....)
  • ttl: The TTL of this record

Now that we know that for this particular DNS record, the related domain_id is 1, we can retrieve this value from the database, too:

mysql> SELECT * FROM domains WHERE id = 1;
+----+----------------+--------+------------+--------+-----------------+---------+
| id | name           | master | last_check | type   | notified_serial | account |
+----+----------------+--------+------------+--------+-----------------+---------+
|  1 | infiniroot.com |        |       NULL | MASTER |      2021021601 |         |
+----+----------------+--------+------------+--------+-----------------+---------+
1 row in set (0.00 sec)

Mass-change in MySQL using replace()

Now that we know how DNS records and their related domains are stored in the database, the records themselves can be tackled. A TXT record (for SPF) needs to be adjusted and a couple of entries removed from the list. But let's first search for the occurrence we need to replace (ip4:3.3.3.3):

# mysql -e "SELECT id,content FROM powerdns.records WHERE content LIKE '%ip4:3.3.3.3%'"
+-------+------------------------------------------------------------------------------------------------------------------+
| id    | content                                                                                                          |
+-------+------------------------------------------------------------------------------------------------------------------+
|   369 | "v=spf1 ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ip4:7.7.7.7/24 ~all"          |
|  1416 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  3728 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  3744 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  4558 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  8800 | "v=spf1 ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 include:spf.example.com ~all" |
|  8852 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  8925 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9868 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9873 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9876 | "v=spf1 mx ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 include:spf.mandrillapp.com ~all"      |
|  9881 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9884 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9888 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9898 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9905 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9908 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9913 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9918 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9923 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9928 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9933 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9936 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9949 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9957 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
|  9961 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
| 10015 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
| 10074 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
| 10080 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
| 10090 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
| 10115 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
| 10121 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
| 10127 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"                      |
+-------+------------------------------------------------------------------------------------------------------------------+

Yep, there are a couple SPF TXT record which hold that particular IP address. How can this IP be replaced with a new IP or be removed from the TXT record? If you know SQL, the first thought would probably be to UPDATE the record. However UPDATE requires that the full content is written back into the database. This means: First retrieve the existing record, manipulate the data (which is not exactly the same as you can see above), update the record with the manipulated data.

Luckily there's something easier: The MySQL built-in function replace(). The great advantage of this function is that it keeps the data of a field and works on string replacement, kind of comparable to a search/replace using sed on the command line. The other advantage is that it works not just on one database row but on the whole table. Let's try this:

mysql> UPDATE powerdns.records SET content = replace(content, 'ip4:3.3.3.3 ip4:4.4.4.4 ', '');
Query OK, 33 rows affected (0.05 sec)

The search string "ip4:3.3.3.3 ip4:4.4.4.4" was replaced by an empty string, therefore removing these two entries from the SPF TXT record. Verification:

mysql> SELECT id,content FROM records WHERE id = 10127;
+-------+---------------------------------------------------------------------+
| id    | content                                                             |
+-------+---------------------------------------------------------------------+
| 10127 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
+-------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

The SPF entries ip4:3.3.3.3 and ip4:4.4.4.4 were removed, the rest of the SPF TXT record is intact!

Now this domain must be identified (using domain_id column) and the zone's serial increased and then reloaded:

# pdnsutil increase-serial example.com
# pdns_control reload

And in case you have a PowerDNS master slave setup, launch a notify to inform the slaves to transfer the updated zone:

# pdns_control notify example.com

Now doing all this demands for internal understanding of the PowerDNS MySQL backend - and manual work. Wouldn't it be nice, if there were a script to do this?

Automatic bulk change of DNS records using pdns-search-replace.sh

All the steps described above are bundled together in a handy script pdns-search-replace.sh, which is publicly available on GitHub. It supports multiple parameters and allows to quickly run through the all DNS records matching the search string, defined using the -s parameter. All occurrences will be replaced by the replace string, defined using the -r parameter. Besides these two parameters, the database user (-u) is required, too.

Download the script from GitHub:

# wget https://raw.githubusercontent.com/Napsty/scripts/master/pdns/infoblox2pdns.sh

Or clone the full repository:

# git clone https://github.com/Napsty/scripts.git

Then make sure the permissions on the script are correct (they should already be set to 755) and execute the script with the required parameters:

root@powerdns:~/scripts# ./pdns-search-replace.sh -s "ip4:3.3.3.3 ip4:4.4.4.4 " -r "" -u root
Manipulating DNS records can cause severe damage in your zone. Would you like to create a backup first? Y/N ? Y
Saving MySQL dump of powerdns in /tmp/powerdns.1614693776.sql
Found 33 domains that will be affected of 'ip4:3.3.3.3 ip4:4.4.4.4 ' being replaced by ''
Increasing serial (2020062703) for domain example.com
SOA serial for zone example.com set to 2021030201
PowerDNS reloading zones
Ok
Sending notify to slave(s)
Added to queue
Increasing serial (2019110501) for domain example2.com
SOA serial for zone example2.com set to 2021030201
PowerDNS reloading zones
Ok
Sending notify to slave(s)
Added to queue
Increasing serial (2019110501) for domain example3.com
SOA serial for zone example3.com set to 2021030201
PowerDNS reloading zones
Ok
Sending notify to slave(s)
Added to queue

As you can see, the script also asks to create a backup (database dump) first. If the script is executed non-interactively, this prompt can be skipped using the -B (for batch) parameter.

The updated TXT record can now be queried on all the DNS servers:

ckadm@mintp ~ $ dig -t TXT example.com @ns1.example.com +short
"v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"

ckadm@mintp ~ $ dig -t TXT example.com @ns2.example.com +short
"v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"

ckadm@mintp ~ $ dig -t TXT example.com @ns3.example.com +short
"v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"

The SPF entries ip4:3.3.3.3 and ip4:4.4.4.4 were successfully removed.


Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder