How ToLinuxPlesk

Plesk How-To: Export List of all Mail Aliases

Note: you'll need access to the psa database. Tested on Plesk 18.0.30.

Plesk stores most of its information in the psa database, so getting information out can usually be done via an SQL query. To export a list of all mail aliases sorted alphabetically grouped by domain, run the SQL query below:

select concat(mail_aliases.alias,'@',domains.name) as aliases from mail,mail_aliases,domains where mail.dom_id=domains.id and mail_aliases.mn_id=mail.id order by domains.name;

If you want to store the output in a text file, you can add INTO OUTFILE ‘/location’ into the query:

select concat(mail_aliases.alias,'@',domains.name) as aliases INTO OUTFILE '/tmp/output-aliaseslist.txt' from mail,mail_aliases,domains where mail.dom_id=domains.id and mail_aliases.mn_id=mail.id order by domains.name;

Lastly, if you wanted to put this into a Bash script (e.g; to send over a list of mail-enabled domains to a spam filtering server) you can run:

plesk db "select concat(mail_aliases.alias,'@',domains.name) as address INTO OUTFILE '/tmp/output-aliaseslist.txt' from mail,mail_aliases,domains where mail.dom_id=domains.id and mail_aliases.mn_id=mail.id order by domains.name;"

The above command can be run by anybody with root privileges and will output a list of all mail-enabled domains to /tmp/output.txt. Note that MySQL cannot overwrite existing files, so you will need to add a line into your script to remove the file before running the command above.

Jon Procter

Linux, Unix, and Windows server sysadmin with strong experience in security and compliance.

Related Articles

Back to top button