Plesk MySQL Queries

- admin

Posted by admin on Ara 3, 2015 in MySQL, Plesk | Plesk MySQL Queries için yorumlar kapalı

1 Domain Information
1.1 Domains and IP addresses
1.2 Domain Users accounts and passwords
1.3 Client usernames/passwords
1.4 FTP accounts
1.5 ftp users(with domain)
1.6 Logrotate config for all domains
1.7 DNS records for a domain
1.8 DNS primary A-records for all domains
1.9 Statistics application per domain
1.10 SSL certificates installed under domains
1.11 SSL certificate files associated with default domain on IP
1.12 SSL certificate files associated with IP address
1.13 SSL certificate files not in use by any domain
1.14 Domains expiration in UNIX time
1.15 Domains expiration in human readable time
1.16 Bandwidth by service for the month(change date string accordingly)
1.17 Disk usage per service by domain
2 Mail Info
2.1 Mail accounts
2.2 All enabled mailboxes (local or redirect)
2.3 List bounces
2.4 List status of all mail to non-existent users:
2.5 All (singular) email info
2.6 List all Mail redirect/forwards:
2.7 List all Mail redirect/forwards to external domains:
2.8 Email Aliases
2.9 Email Groups
2.10 Email Autoresponders
2.11 Mailbox quota size per domain:
3 Databases
3.1 Show databases by domain
3.2 Show database users and passwords created in Plesk
4 User Accounts
4.1 ftp users(with domain):
4.2 ftp users with additional details(shell,quota):
4.3 database users(with domain):
4.4 web users:
4.5 subdomains usernames/passwords:
4.6 protected directories (htpasswd):
5 One Time Use
5.1 Redirect update from previous install

Domain Information

Domains and IP addresses

select domains.name,IP_Addresses.ip_address from domains,hosting,IP_Addresses where domains.id=hosting.dom_id and hosting.ip_address_id=IP_Addresses.id order by IP_Addresses.ip_address,domains.name;

Domain Users accounts and passwords

mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e 'select domains.name, accounts.password from domains, accounts, dom_level_usrs where domains.id=dom_level_usrs.dom_id and accounts.id=dom_level_usrs.account_id order by domains.name;'

Client usernames/passwords

select clients.login, accounts.password from clients,accounts where clients.account_id=accounts.id;

FTP accounts

mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e 'select sys_users.home,sys_users.login,accounts.password from sys_users,accounts where sys_users.account_id=accounts.id order by home;'

ftp users(with domain)

select domains.name,sys_users.login,accounts.password from domains,sys_users,hosting,accounts where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id order by domains.name;

Logrotate config for all domains

select domains.name,log_rotation.period_type,log_rotation.period,log_rotation.max_number_of_logfiles,log_rotation.turned_on from domains,dom_param,log_rotation where domains.id=dom_param.dom_id and dom_param.param="logrotation_id"and dom_param.val=log_rotation.id;

DNS records for a domain

select domains.name,dns_recs.host,dns_recs.type,dns_recs.val from domains,dns_recs where domains.dns_zone_id=dns_recs.dns_zone_id and domains.name='aznb.net';

DNS primary A-records for all domains

select dns_recs.host,dns_recs.type,dns_recs.val from domains,dns_recs where domains.dns_zone_id=dns_recs.dns_zone_id and dns_recs.type='A' and domains.name=substring_index(dns_recs.host,'.',2) order by domains.name;

Statistics application per domain

select domains.name,hosting.webstat from domains, hosting where domains.id=hosting.dom_id;

SSL certificates installed under domains

select domains.name as domain_name,IP_Addresses.ip_address,certificates.name as cert_name,certificates.cert_file from domains,IP_Addresses,certificates,hosting where domains.cert_rep_id != "NULL" and domains.id=hosting.dom_id andhosting.ip_address_id=IP_Addresses.id and domains.cert_rep_id=certificates.id;

SSL certificate files associated with default domain on IP

select domains.name as domain,IP_Addresses.ip_address,certificates.name,certificates.cert_file from domains,certificates,IP_Addresses where IP_Addresses.ssl_certificate_id=certificates.id andIP_Addresses.default_domain_id=domains.id order by domains.name;

SSL certificate files associated with IP address

select IP_Addresses.ip_address,certificates.cert_file from certificates,IP_Addresses where IP_Addresses.ssl_certificate_id=certificates.id;

SSL certificate files not in use by any domain

select IP_Addresses.ip_address,certificates.name,certificates.cert_file from certificates,IP_Addresses where IP_Addresses.ssl_certificate_id=certificates.id and IP_Addresses.default_domain_id < 1 and certificates.name not like"%default%";

Domains expiration in UNIX time

select domains.name, Limits.limit_name, Limits.value from domains, Limits where domains.limits_id=Limits.id and Limits.limit_name="expiration" and Limits.value != -1;

Domains expiration in human readable time

mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e 'select domains.name, Limits.limit_name, from_unixtime(Limits.value) from domains, Limits where domains.limits_id=Limits.id and Limits.limit_name="expiration" and Limits.value != -1;'

Bandwidth by service for the month(change date string accordingly)

select domains.name as domain, SUM(DomainsTraffic.http_out)/1024/1024 as HTTP_out_MB, SUM(DomainsTraffic.ftp_out)/1024/1024 as FTP_out_MB, SUM(DomainsTraffic.smtp_out)/1024/1024 as SMTP_out_MB,SUM(DomainsTraffic.pop3_imap_out)/1024/1024 as POP_IMAP_out_MB from domains,DomainsTraffic where domains.id=DomainsTraffic.dom_id and date like "2009-10%" group by domain;

Disk usage per service by domain

select domains.name,disk_usage.*,httpdocs+httpsdocs+subdomains+web_users+anonftp+logs+dbases+mailboxes+webapps+maillists+domaindumps+configs+chroot as total from domains,disk_usage where domains.id=disk_usage.dom_id order bytotal;

Mail Info

Mail accounts

mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e 'select concat(mail.mail_name,"@",domains.name) as address,accounts.password from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id order by address;'

All enabled mailboxes (local or redirect)

SELECT mail.mail_name,domains.name,accounts.password,mail.postbox, mail.redirect, mail.redir_addr FROM mail,domains,accounts WHERE mail.dom_id=domains.id AND mail.account_id=accounts.id and (mail.postbox='true' ormail.redirect='true') ORDER BY domains.name,mail.mail_name;

List bounces

If checking for backscatter, be sure to check for autoresponders too.

select domains.name from domains,Parameters,DomainServices where DomainServices.type='mail' and Parameters.value = 'bounce' and domains.id = DomainServices.dom_id and DomainServices.parameters_id=Parameters.id order bydomains.name;

List status of all mail to non-existent users:

select domains.name,Parameters.value from domains,Parameters,DomainServices where DomainServices.type='mail' and Parameters.value in ('catch','reject','bounce') and domains.id=DomainServices.dom_id andDomainServices.parameters_id=Parameters.id order by Parameters.value,domains.name;

All (singular) email info

SELECT mail.mail_name,domains.name,accounts.password,mail.redir_addr FROM mail,domains,accounts WHERE mail.dom_id=domains.id AND mail.account_id=accounts.id ORDER BY domains.name,mail.mail_name;

List all Mail redirect/forwards:

SELECT mail.mail_name,domains.name,mail.redir_addr FROM mail,domains WHERE mail.redirect='true' AND mail.dom_id=domains.id AND mail.redir_addr!='' ORDER BY mail.mail_name;

List all Mail redirect/forwards to external domains:

SELECT mail.mail_name,domains.name,mail.redir_addr FROM mail,domains WHERE mail.redirect='true' AND mail.dom_id=domains.id AND mail.redir_addr!='' AND SUBSTRING_INDEX(mail.redir_addr,'@',-1) NOT IN (SELECT name from domains) ORDER BY domains.name,mail.mail_name;

Email Aliases

select mail.mail_name, domains.name, mail_aliases.alias from mail, domains, mail_aliases where mail.dom_id=domains.id and mail.id=mail_aliases.mn_id;

Email Groups

select mail.mail_name as group_mailbox,domains.name,mail_redir.address as group_member from mail,domains,mail_redir where mail.dom_id=domains.id and mail.id=mail_redir.mn_id and mail.mail_group='true' order bydomains.name,mail.mail_name,mail_redir.address;

Email Autoresponders

select mail.mail_name, domains.name as domain, mail_resp.resp_name, mail_resp.resp_on, mail_resp.key_where as filter, mail_resp.subject, mail_resp.reply_to from mail,domains,mail_resp where mail.dom_id=domains.id andmail.id=mail_resp.mn_id and mail.autoresponder='true' and mail_resp.resp_on='true';

Mailbox quota size per domain:

select domains.name,Limits.limit_name,Limits.value/1024/1024 as "quota MB" from domains,Limits where Limits.limit_name='mbox_quota' and domains.limits_id=Limits.id;

Databases

Show databases by domain

select domains.name as Domain, data_bases.name as DB from domains, data_bases where data_bases.dom_id=domains.id order by domains.name;

Show database users and passwords created in Plesk

select name,login,password from psa.db_users, psa.accounts, psa.data_bases where psa.db_users.account_id=psa.accounts.id and psa.data_bases.id=psa.db_users.db_id;

User Accounts

ftp users(with domain):

select domains.name,sys_users.login,accounts.password from domains,sys_users,hosting,accounts where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id order by domains.name;

ftp users with additional details(shell,quota):

select domains.name,sys_users.login,accounts.password,sys_users.shell,sys_users.quota from domains,sys_users,hosting,accounts where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id andsys_users.account_id=accounts.id order by domains.name;

database users(with domain):

select domains.name as domain_name, data_bases.name as DB_name,db_users.login,password from db_users, accounts, data_bases,domains where domains.id=data_bases.dom_id and db_users.account_id=accounts.id anddata_bases.id=db_users.db_id order by domains.name;

web users:

select domains.name, sys_users.login, web_users.sys_user_id from domains,sys_users,web_users where domains.id=web_users.dom_id and web_users.sys_user_id=sys_users.id;

subdomains usernames/passwords:

select subdomains.name,domains.name as domain, sys_users.login, accounts.password from domains,sys_users,subdomains,accounts where domains.id=subdomains.dom_id and subdomains.sys_user_id=sys_users.id andsys_users.account_id=accounts.id;

protected directories (htpasswd):

select domains.name, protected_dirs.path, pd_users.login, accounts.password from domains, protected_dirs, pd_users, accounts where domains.id=protected_dirs.dom_id and protected_dirs.id=pd_users.pd_id andpd_users.account_id=accounts.id;

One Time Use

Redirect update from previous install

This was for an instance where redirects were brought over from a previous installation, but the previous migration failed to check if the redirects were active or not. This compares the two, and only updates the differences.

UPDATE mail SET redirect='false' WHERE id IN (SELECT mail_copy.id FROM mail_copy,domains WHERE mail_copy.redirect='true' AND mail_copy.dom_id=domains.id AND mail_copy.redir_addr!='' and CONCAT(mail_copy.mail_name,'@',domains.name)IN (SELECT CONCAT(mail.mail_name,'@',domains.name) AS address FROM psa_orig.mail,psa_orig.domains WHERE mail.redirect='false' AND mail.dom_id=domains.id AND mail.redir_addr!=''));