{"id":75,"date":"2012-09-21T08:30:27","date_gmt":"2012-09-21T08:30:27","guid":{"rendered":"https:\/\/www.nicktailor.com\/?p=75"},"modified":"2024-04-28T06:28:31","modified_gmt":"2024-04-28T06:28:31","slug":"plesk-mysql-queries","status":"publish","type":"post","link":"https:\/\/nicktailor.com\/tech-blog\/plesk-mysql-queries\/","title":{"rendered":"Plesk Mysql Queries Cheat Sheet"},"content":{"rendered":"
I decided to blog this for people who use parallels plesk with mysql. I got some Parallel Certifications a couple years ago, this stuff will help you mine mysql information that you might need if your running a business.
\n<\/strong><\/div>\n
    \n
  1. Domain Information<\/li>\n
  2. Domains and IP addresses<\/li>\n
  3. Domain Users accounts and passwords<\/li>\n
  4. Client usernames\/passwords<\/li>\n
  5. FTP accounts<\/li>\n
  6. ftp users(with domain)<\/li>\n
  7. Logrotate config for all domains<\/li>\n
  8. DNS records for a domain<\/li>\n
  9. DNS primary A-records for all domains<\/li>\n
  10. Statistics application per domain<\/li>\n
  11. SSL certificates installed under domains<\/li>\n
  12. SSL certificate files associated with default domain on IP<\/li>\n
  13. SSL certificate files associated with IP address<\/li>\n
  14. SSL certificate files not in use by any domain<\/li>\n
  15. Domains expiration in UNIX time<\/li>\n
  16. Domains expiration in human readable time<\/li>\n
  17. Bandwidth by service for the month(change date string accordingly)<\/li>\n
  18. Disk usage per service by domain<\/li>\n
  19. Mail Info<\/li>\n
  20. Mail accounts<\/li>\n
  21. All enabled mailboxes (local or redirect)<\/li>\n
  22. List bounces<\/li>\n
  23. List status of all mail to non-existent users:<\/li>\n
  24. All (singular) email info<\/li>\n
  25. List all Mail redirect\/forwards:<\/li>\n
  26. List all Mail redirect\/forwards to external domains:<\/li>\n
  27. Email Aliases<\/li>\n
  28. Email Groups<\/li>\n
  29. Email Autoresponders<\/li>\n
  30. Mailbox quota size per domain:<\/li>\n
  31. Databases<\/li>\n
  32. Show databases by domain<\/li>\n
  33. Show database users and passwords created in Plesk<\/li>\n
  34. User Accounts<\/li>\n
  35. ftp users(with domain):<\/li>\n
  36. ftp users with additional details(shell,quota):<\/li>\n
  37. database users(with domain):<\/li>\n
  38. web users:<\/li>\n
  39. subdomains usernames\/passwords:<\/li>\n
  40. protected directories (htpasswd):<\/li>\n
  41. One Time Use<\/li>\n
  42. Redirect update from previous install<\/li>\n<\/ol>\n

    Domain Information<\/strong><\/em>
    \nDomains and IP addresses
    \nselect 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;
    \n
    \nDomain Users accounts and passwords<\/strong><\/em>
    \nmysql 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;’<\/p>\n

    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;<\/p>\n

    Domain Users accounts and passwords and email.<\/strong><\/em>
    \nselect domains.name,sys_users.login,accounts.password,clients.email from domains,sys_users,hosting,accounts,clients where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id and clients.id=domains.cl_id order by domains.name;
    \n
    \nClient usernames\/passwords<\/strong><\/em>
    \nselect clients.login, accounts.password from clients,accounts where clients.account_id=accounts.id;
    \n
    \nFTP accounts<\/strong><\/em>
    \nmysql 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;’
    \n
    \nftp users(with domain)<\/strong><\/em>
    \nselect 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;
    \n
    \nLogrotate config for all domains<\/strong><\/em>
    \nselect 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;
    \n
    \nDNS records for a domain<\/strong><\/em>
    \nselect 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=’www.nicktailor.com’;
    \n
    \nDNS primary A-records for all domains<\/em><\/strong>
    \nselect 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;
    \n
    \nStatistics application per domain<\/strong><\/em>
    \nselect domains.name,hosting.webstat from domains, hosting where domains.id=hosting.dom_id;<\/p>\n

    List subdomains by domain<\/strong><\/em>
    \nselect subdomains.name,domains.name as domain from domains,sys_users,subdomains,accounts where domains.id=subdomains.dom_id and subdomains.sys_user_id=sys_users.id and sys_users.account_id=accounts.id;<\/p>\n

    select subdomains.name,domains.name as domain from domains,sys_users,subdomains,accounts where domains.id=subdomains.dom_id and subdomains.sys_user_id=sys_users.id and sys_users.account_id=accounts.id and domains.name = ‘test.com’;<\/p>\n

    SSL certificates installed under domains<\/strong><\/em>
    \nselect 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 and hosting.ip_address_id=IP_Addresses.id and domains.cert_rep_id=certificates.id;<\/p>\n

    SSL certificate files associated with default domain on IP<\/strong><\/em>
    \nselect 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 and IP_Addresses.default_domain_id=domains.id order by domains.name;<\/p>\n

    SSL certificate files associated with IP address<\/strong><\/em>
    \nselect IP_Addresses.ip_address,certificates.cert_file from certificates,IP_Addresses where IP_Addresses.ssl_certificate_id=certificates.id;<\/p>\n

    SSL certificate files not in use by any domain<\/strong><\/em>
    \nselect 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%”;
    \n
    \nDomains expiration in UNIX time<\/strong><\/em>
    \nselect 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;<\/p>\n

    Domains expiration in human readable time<\/strong><\/em>
    \nmysql 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;’
    \n
    \nBandwidth by service for the month(change date string accordingly)<\/strong><\/em>
    \nselect 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;<\/p>\n

    Disk usage per service by domain<\/strong><\/em>
    \nselect 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 by total;<\/p>\n

    Mail Info<\/strong><\/em>
    \nMail accounts<\/strong><\/em>
    \nmysql 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;’<\/p>\n

    mysql> select pname,email from clients;(list all clients name and emails)<\/p>\n

    All enabled mailboxes (local or redirect)<\/strong><\/em>
    \nSELECT 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’ or mail.redirect=’true’) ORDER BY domains.name,mail.mail_name;<\/p>\n

    List bounces\u00a0If checking for backscatter, be sure to check for autoresponders too.\u00a0<\/strong><\/em>
    \nselect 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 by domains.name;
    \n
    \nList status of all mail to non-existent users:<\/strong><\/em>
    \nselect 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 and DomainServices.parameters_id=Parameters.id order by Parameters.value,domains.name;
    \n
    \nAll (singular) email info<\/strong><\/em>
    \nSELECT 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;<\/p>\n

    List all Mail redirect\/forwards:<\/strong><\/em>
    \nSELECT 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;<\/p>\n

    List all Mail redirect\/forwards to external domains:<\/strong><\/em>
    \nSELECT 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;
    \n
    \nEmail Aliases<\/strong><\/em>
    \nselect 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;<\/p>\n

    Email Groups<\/strong><\/em>
    \nselect 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 by domains.name,mail.mail_name,mail_redir.address;<\/p>\n

    Email Autoresponders<\/strong><\/em>
    \nselect 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 and mail.id=mail_resp.mn_id and mail.autoresponder=’true’ and mail_resp.resp_on=’true’;<\/p>\n

    Mailbox quota size per domain:<\/strong><\/em>
    \nselect 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;
    \nDatabases<\/p>\n

    Show databases by domain<\/strong><\/em>
    \nselect domains.name as Domain, data_bases.name as DB from domains, data_bases where data_bases.dom_id=domains.id order by domains.name;<\/p>\n

    Show database users and passwords created in Plesk<\/strong><\/em>
    \nselect 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;
    \n
    \nUser Accounts<\/strong><\/em>
    \nftp users(with domain):
    \nselect 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;<\/p>\n

    ftp users with additional details(shell,quota):<\/strong><\/em>
    \nselect 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 and sys_users.account_id=accounts.id order by domains.name;
    \n
    \ndatabase users(with domain):<\/strong><\/em>
    \nselect 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 and data_bases.id=db_users.db_id order by domains.name;
    \n
    \nweb users:<\/strong><\/em>
    \nselect 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;
    \n
    \nsubdomains usernames\/passwords:<\/em><\/strong>
    \nselect 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 and sys_users.account_id=accounts.id;<\/p>\n

    protected directories (htpasswd):<\/strong><\/em>
    \nselect 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 and pd_users.account_id=accounts.id;<\/p>\n

    One Time Use<\/strong><\/em>
    \nRedirect update from previous install\u00a0This 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.\u00a0<\/strong><\/em><\/p>\n

    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!=”));<\/p>\n","protected":false},"excerpt":{"rendered":"

    I decided to blog this for people who use parallels plesk with mysql. I got some Parallel Certifications a couple years ago, this stuff will help you mine mysql information that you might need if your running a business. Domain Information Domains and IP addresses Domain Users accounts and passwords Client usernames\/passwords FTP accounts ftp users(with domain) Logrotate config forRead More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[58,138,8],"tags":[],"class_list":["post-75","post","type-post","status-publish","format-standard","hentry","category-centos","category-linux","category-parallels-plesk"],"_links":{"self":[{"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/posts\/75","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/comments?post=75"}],"version-history":[{"count":18,"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/posts\/75\/revisions"}],"predecessor-version":[{"id":1780,"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/posts\/75\/revisions\/1780"}],"wp:attachment":[{"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/media?parent=75"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/categories?post=75"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nicktailor.com\/tech-blog\/wp-json\/wp\/v2\/tags?post=75"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}