Previously have used a number of SQL commands to check the Plesk database as some times Plesk doesn’t quite work as it should, or, needed to get information manually.

The following URL had a number of typical task SQL queries for the psa database:

• Details of email ,ftp and database users from PSA database

Some of the commands (incase the remote site goes down in the future) are below:

Email account details

mysql> SELECT CONCAT(mail.mail_name,’@’,domains.name) AS Email_Address , accounts.password AS Password FROM mail, domains, accounts WHERE domains.id=mail.dom_id AND mail.account_id=accounts.id;

Database user details

mysql> SELECT domains.name, data_bases.name, data_bases.type , db_users.login,accounts.password FROM domains, data_bases, db_users, accounts WHERE domains.id=data_bases.dom_id AND data_bases.id=db_users.db_id AND db_users.account_id=accounts.id;

FTP/System user details

mysql> SELECT domains.name, sys_users.login, accounts.password, sys_users.home, sys_users.shell, sys_users.quota FROM domains, accounts, hosting, sys_users WHERE domains.id=hosting.dom_id AND hosting.sys_user_id=sys_users.id AND sys_users.account_id=accounts.id;

IP Address details

mysql> SELECT domains.name, IP_Addresses.ip_address, IP_Addresses.mask, IP_Addresses.iface, IP_Addresses.type FROM domains, IP_Addresses, hosting WHERE domains.id=hosting.dom_id AND hosting.ip_address_id=IP_Addresses.id;

Client details

Single:

mysql> SELECT clients.pname AS Client_Name, clients.cname AS Company_Name, clients.email, clients.login, accounts.password FROM clients, accounts WHERE pname=”CLIENT_NAME” AND clients.account_id=accounts.id;

All:

mysql> SELECT clients.pname AS Client_Name, clients.cname AS Company_Name, clients.email, clients.login, accounts.password FROM clients, accounts WHERE clients.account_id=accounts.id;

The site Praveen’s Matrix appears to have a number of other great articles regarding Plesk, MySQL etc.