Plesk How-To: Export List of all Mail Aliases

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.