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:
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.