PowerDNS export from MySQL to csv using command line

PowerDNS Authoritative Server uses the MariaDB database. Data can be exported from the database using the command line. There are two options to do it: 1. run commands on the mysql command line or 2. mysqldump.

Image without description

Let's start, set a directory for csv-file

sudo mkdir /tmp/mysql
sudo chown -R mysql:mysql /tmp/mysql/

This eliminates an error message such as "ERROR 1 (HY000): Can't create/write to file '/home/exsample_domainparkki_com.csv' (Errcode: 13 "Permission denied")"

1. Run the following command on the mysql command line

SELECT * FROM domains
INTO OUTFILE '/tmp/mysql/exsample_domainparkki_com.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

2. Using mysqldump

mysqldump -u [username] -p -t -T/path/to/directory [database] [tableName] --fields-terminated-by=,

The csv file can be imported into Airtable or Baserow for further processing, for example.