Plesk: Viewing All Mailboxes on the Server

First connect to mysql:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa

This mysql query prints info on all mailboxes that exist on a server with a Plesk panel

SELECT CONCAT_WS('@',mail.mail_name,domains.name),mail.redir_addr, mail.redirect ,accounts.password
FROM domains,mail,accounts WHERE domains.id=mail.dom_id AND accounts.id=mail.account_id
ORDER BY domains.name ASC,mail.mail_name ASC;

The results will include so-called ‘email forwarders’ - addresses mail gets forwarded to for a given mailbox.

If you don’t need that info, you can use this query instead:

SELECT CONCAT_WS('@',mail.mail_name,domains.name),accounts.password FROM domains,mail,accounts WHERE domains.id=mail.dom_id AND accounts.id=mail.account_id ORDER BY domains.name ASC,mail.mail_name ASC;

A mailbox listing that includes the Plesk system user’s login:

SELECT domains.name, sys_users.login, mail.mail_name from domains,hosting, sys_users, mail WHERE domains.id=hosting.dom_id AND hosting.sys_user_id=sys_users.id and domains.id=mail.dom_id;

You can build a batch file for transferring mail files over to a WHM server:

echo "use psa; SELECT concat('/',domains.name, '/', mail.mail_name), concat('/home/',sys_users.login,'/mail/',domains.name, '/')  from domains,hosting, sys_users, mail WHERE domains.id=hosting.dom_id AND hosting.sys_user_id=sys_users.id and domains.id=mail.dom_id " |mysql |awk '{print "rsync -Hogva /var/qmail/mailnames"$1" root@server_ip:"$2}'