Mysql plesk shortcuts

Deleting a domain from plesk directly from mysql:

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

select * from domains where id = 234
delete from domains where id = 234
delete from mail where id = 234
delete from domains where name =’domainname’;
delete from subdomains where id = 234
delete from dns_recs where id = 234
select * from hosting where dom_id = 234
delete from sys_users where id = 123 [From the above query, you can get the sys_users]
delete from hosting where dom_id = 234

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

use psa;

select d.id, d.name, R.rep_id, R.component_id, c.id from domains d, Repository R left join certificates c on c.id=R.component_id where d.cert_rep_id=R.rep_id and c.id is NULL;

select d.id, d.name, R.rep_id, R.component_id, c.id from domains d, Repository R left join certificates c on c.id=R.component_id where d.cert_rep_id=R.rep_id ;

Listing all FTP users in the database:

SELECT REPLACE(sys_users.home,’/home/httpd/vhosts/’,”) AS domain, sys_users.login,accounts.password FROM sys_users LEFT JOIN accounts on sys_users.account_id=accounts.id ORDER BY sys_users.home ASC;

Listing all mail accounts:

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;

Listing of all user accounts (‘reseller/client’ type accounts rather than “domain owner” type)

mysql -u admin -p`cat /etc/psa/.psa.shadow` -Dpsa -e”SELECT accounts.id,clients.account_id,clients.cr_date,clients.cname,clients.pname,clients.login,accounts.password,clients.email FROM accounts,clients WHERE accounts.id=clients.account_id”

Leave a Reply

Your email address will not be published. Required fields are marked *

*