Question How to see plesk database tables and records

[Jhow Plug]

Server operating system version

Windows 2019

Plesk version and microupdate number

Plesk Obsedian 18

Hello community, happy 2023 everyone
I wish everyone an amazing year in advance

I've been working extracting data with the pleskdb command
However, I still don't know how to see the database records and table

Doubt:

1) What is the most professional and secure way to be able to see all the records in the domain table?

2) How can I see any table

Do I install some bank reader in the same windows ?

 

[Peter Debik]

Please use the Plesk GUI and there
Tools & Settings > Applications & Databases > Database Servers > Click the phpMyAdmin logo



Then select the database named "psa" to see all tables. For each table you can click on the table name to see all fields.

 

[Kaspar]

@Peter Debik, on Plesk for Windows the PSA database runs on it's own database server. And therefor is not accessibel via phpMyAdmin trough Plesk.

@Jhow Plug if you're working on any kind of integration I strongly recommend to not access the database directly, but use the Plesk REST API, XML API or Plesk CLI instead. Those are the safest methodes to use.

Unfortunately I don't know how to list all database tables on a Plesk Windows installation. Best suggestion I can think of is to temporary use a Plesk linux server for your development project. That way you can access the PSA database as Peter described in his post.

EDIT: It's possible to use the plesk db utility to access the Plesk (PSA) database directly and run any SQL query. Or simply run plesk db tables which will return all tables in the PSA database.

 

[Peter Debik]

Ah, yes, Windows. Sorry for that. I overlooked that.

 

[scsa20]

This is the Linux forum for Plesk lol.

But anyways, this help article should help you out: https://support.plesk.com/hc/en-us/articles/213928465-How-to-access-the-Plesk-psa-database-in-MySQL
There's a part for Windows via RDP where you use the command line utility: plesk db

 

[Jhow Plug]

This is the Linux forum for Plesk lol.

But anyways, this help article should help you out: https://support.plesk.com/hc/en-us/articles/213928465-How-to-access-the-Plesk-psa-database-in-MySQL
There's a part for Windows via RDP where you use the command line utility: plesk db

I know the were is from linux.
My question is generic and I would like to inform you that there are almost no plesk windows professionals.

So let's be good guys, answering general questions that work for both systems. Look at minorities, they exist.

Thank you and those who sought a solution

 

[scsa20]

Actually it's not very generic considering the Windows version and the LInux version stores the PSA database differently. But like I said, if you look at the link provided it shows you how to do it.

 

[Kaspar]

@scsa20 actually the methode described in the link to access the Plesk (PSA) database doesn't allow for any SQL queries that show the whole database schema (like SHOW TABLES). So there is no way, as far as I know at least, on a Plesk Windows server to view the database schema or just list all tables.

 

[scsa20]

Why wouldn't you be able to?

C:\Windows\system32>plesk db
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 314
Server version: 10.6.9-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [psa]> show tables
    -> ;
+-----------------------------------+
| Tables_in_psa                     |
+-----------------------------------+
| accounts                          |
| actions                           |
| admin_aliases                     |
| admin_aliases_param               |
| anon_ftp                          |
| apirpccallsstat                   |
| apsapplicationitems               |
| apscatalogupdates                 |
| apscategories                     |
| apsclientapplicationitems         |
| apscontexts                       |
| apscontextsapplications           |
| apsinstancesparameters            |
| apslicenses                       |
| apslicensetypes                   |
| apsresources                      |
| apsresourcesparameters            |
| apstokens                         |
| avstatistics                      |
| backendcache                      |
| backupexcludefiles                |
| backupsscheduled                  |
| backupssettings                   |
| backuptasks                       |
| badmailfrom                       |
| bigparameters                     |
| blupgradeactions                  |
| blupgrades                        |
| certificaterepositories           |
| certificates                      |
| cl_param                          |
| clicallsstat                      |
| clients                           |
| clientstraffic                    |
| components                        |
| cp_access                         |
| custom_buttons                    |
| data_bases                        |
| databasecustomhosts               |
| databaseservers                   |
| databaseuserremoteaccessrules     |
| db_users                          |
| disk_usage                        |
| dns_recs                          |
| dns_recs_t                        |
| dns_zone                          |
| dom_param                         |
| domain_aliases                    |
| domainaliasesparameters           |
| domainoutgoingmessagespeaks       |
| domainoutgoingmessagesstats       |
| domains                           |
| domainservices                    |
| domainstraffic                    |
| dsn                               |
| dynamicipsecurity                 |
| emailactivations                  |
| event_handlers                    |
| exp_event                         |
| externalwebmails                  |
| forwarding                        |
| ftp_users                         |
| hosting                           |
| hotlink_friends                   |
| hotlink_prot                      |
| iisapppooldomains                 |
| iisapppools                       |
| iisapppoolspolicy                 |
| ip_addresses                      |
| ip_pool                           |
| ipaddressescollections            |
| ipcollections                     |
| key_history                       |
| key_history_params                |
| limits                            |
| limitsreservation                 |
| locales                           |
| log_actions                       |
| log_components                    |
| log_rotation                      |
| logos                             |
| longtaskparams                    |
| longtasks                         |
| mail                              |
| mail_aliases                      |
| mail_drweb                        |
| mail_redir                        |
| mail_resp                         |
| mailer_queries                    |
| maillists                         |
| mailmessagesstat                  |
| mailoutgoingmessagespeaks         |
| mailoutgoingmessagesstats         |
| massmail_templates                |
| misc                              |
| mn_param                          |
| modules                           |
| modulesettings                    |
| ms_jet_counter                    |
| notes                             |
| notifications                     |
| packageupdatelocks                |
| packageupdatenotifications        |
| panelnotificationmessages         |
| panelnotificationtemplatesettings |
| parameters                        |
| password_secrets                  |
| pd_users                          |
| permissions                       |
| persistentcache                   |
| phpsettings                       |
| phpsettingscustom                 |
| phpsettingsparameters             |
| planitemproperties                |
| planitems                         |
| planssubscriptions                |
| pleskstats                        |
| protected_dirs                    |
| report                            |
| report_auto                       |
| report_section                    |
| repository                        |
| resp_attach                       |
| resp_forward                      |
| resp_freq                         |
| restricteddomains                 |
| sa_conf                           |
| sa_list                           |
| sa_param                          |
| scheduledtasks                    |
| schemaversions                    |
| secret_keys                       |
| secretkeys                        |
| secretkeysipaddresses             |
| servicenodecache                  |
| servicenodecertificates           |
| servicenodeconfiguration          |
| servicenodeenvironment            |
| servicenodes                      |
| sessioncontexts                   |
| sessions                          |
| siteappshitsstat                  |
| sitepagesstat                     |
| skins                             |
| smb_apsbundlefilteritems          |
| smb_apsbundlefilters              |
| smb_apscategories                 |
| smb_apscontexts                   |
| smb_apsinstanceerrors             |
| smb_apsinstances                  |
| smb_apsmetas                      |
| smb_apspackages                   |
| smb_apspackagescategories         |
| smb_apspackageupdates             |
| smb_apsprovisionenvironments      |
| smb_apsprovisions                 |
| smb_apsresourceparameters         |
| smb_apsresources                  |
| smb_apssettings                   |
| smb_componentupdates              |
| smb_generalpermissions            |
| smb_locales                       |
| smb_productupgrades               |
| smb_rolegeneralpermissions        |
| smb_roles                         |
| smb_roleservicepermissions        |
| smb_serviceentrypoints            |
| smb_serviceinstances              |
| smb_servicepermissionaccounts     |
| smb_servicepermissions            |
| smb_serviceproviders              |
| smb_settings                      |
| smb_users                         |
| smb_userservicepermissions        |
| smtp_poplocks                     |
| smtpwhitelists                    |
| stat                              |
| subdomains                        |
| subscriptionoutgoingmessagespeaks |
| subscriptionoutgoingmessagesstats |
| subscriptionproperties            |
| subscriptions                     |
| suspend_handler_history           |
| sys_users                         |
| templates                         |
| tmpldata                          |
| tracefailedrequestsproviderareas  |
| tracefailedrequestsproviders      |
| tracefailedrequestsrules          |
| upgrade_history                   |
| usersettings                      |
| web_users                         |
| webserversettings                 |
| webserversettingsparameters       |
| websitesdiagnostic                |
| websitesdiagnosticdomains         |
+-----------------------------------+
196 rows in set (0.002 sec)

MariaDB [psa]> DESCRIBE accounts
    -> ;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| type     | varchar(32)      | NO   |     | plain   |                |
| password | text             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.008 sec)

MariaDB [psa]> select * from templates
    -> ;
+----+------------------+----------+----------+--------------------------------------+-------------+
| id | name             | owner_id | type     | uuid                                 | external_id |
+----+------------------+----------+----------+--------------------------------------+-------------+
|  1 | Admin Simple     |        1 | domain   | f09ac736-add3-c6bc-7311-a88aa658bd42 | NULL        |
|  2 | Default Reseller |        1 | reseller | 117aaec5-b818-52fc-2e6f-3103d2592c5d | NULL        |
|  3 | Default Domain   |        1 | domain   | aee30dca-4d8e-d34c-2b58-61f4c9b9f179 | NULL        |
|  4 | Unlimited        |        1 | domain   | 2b0d607c-0962-ef8f-0e1a-09b1d3686cde | NULL        |
|  5 | Default Simple   |        1 | domain   | ac21b67b-5fac-176a-2767-d9fc3953d857 | NULL        |
+----+------------------+----------+----------+--------------------------------------+-------------+
5 rows in set (0.000 sec)

MariaDB [psa]>

 

[Kaspar]

@scsa20, you're right. I somehow tough it was only possible to use the plesk db utility to run SQL queries directly. Like for example plesk db SELECT * FROM ModuleSettings. Reading the support article you linked to again I now see it clearly explains how to use the plesk db utility to access the PSA database.

I learned something new today. Thank you

 

[Jhow Plug]

Why wouldn't you be able to?

C:\Windows\system32>plesk db
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 314
Server version: 10.6.9-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [psa]> show tables
    -> ;
+-----------------------------------+
| Tables_in_psa                     |
+-----------------------------------+
| accounts                          |
| actions                           |
| admin_aliases                     |
| admin_aliases_param               |
| anon_ftp                          |
| apirpccallsstat                   |
| apsapplicationitems               |
| apscatalogupdates                 |
| apscategories                     |
| apsclientapplicationitems         |
| apscontexts                       |
| apscontextsapplications           |
| apsinstancesparameters            |
| apslicenses                       |
| apslicensetypes                   |
| apsresources                      |
| apsresourcesparameters            |
| apstokens                         |
| avstatistics                      |
| backendcache                      |
| backupexcludefiles                |
| backupsscheduled                  |
| backupssettings                   |
| backuptasks                       |
| badmailfrom                       |
| bigparameters                     |
| blupgradeactions                  |
| blupgrades                        |
| certificaterepositories           |
| certificates                      |
| cl_param                          |
| clicallsstat                      |
| clients                           |
| clientstraffic                    |
| components                        |
| cp_access                         |
| custom_buttons                    |
| data_bases                        |
| databasecustomhosts               |
| databaseservers                   |
| databaseuserremoteaccessrules     |
| db_users                          |
| disk_usage                        |
| dns_recs                          |
| dns_recs_t                        |
| dns_zone                          |
| dom_param                         |
| domain_aliases                    |
| domainaliasesparameters           |
| domainoutgoingmessagespeaks       |
| domainoutgoingmessagesstats       |
| domains                           |
| domainservices                    |
| domainstraffic                    |
| dsn                               |
| dynamicipsecurity                 |
| emailactivations                  |
| event_handlers                    |
| exp_event                         |
| externalwebmails                  |
| forwarding                        |
| ftp_users                         |
| hosting                           |
| hotlink_friends                   |
| hotlink_prot                      |
| iisapppooldomains                 |
| iisapppools                       |
| iisapppoolspolicy                 |
| ip_addresses                      |
| ip_pool                           |
| ipaddressescollections            |
| ipcollections                     |
| key_history                       |
| key_history_params                |
| limits                            |
| limitsreservation                 |
| locales                           |
| log_actions                       |
| log_components                    |
| log_rotation                      |
| logos                             |
| longtaskparams                    |
| longtasks                         |
| mail                              |
| mail_aliases                      |
| mail_drweb                        |
| mail_redir                        |
| mail_resp                         |
| mailer_queries                    |
| maillists                         |
| mailmessagesstat                  |
| mailoutgoingmessagespeaks         |
| mailoutgoingmessagesstats         |
| massmail_templates                |
| misc                              |
| mn_param                          |
| modules                           |
| modulesettings                    |
| ms_jet_counter                    |
| notes                             |
| notifications                     |
| packageupdatelocks                |
| packageupdatenotifications        |
| panelnotificationmessages         |
| panelnotificationtemplatesettings |
| parameters                        |
| password_secrets                  |
| pd_users                          |
| permissions                       |
| persistentcache                   |
| phpsettings                       |
| phpsettingscustom                 |
| phpsettingsparameters             |
| planitemproperties                |
| planitems                         |
| planssubscriptions                |
| pleskstats                        |
| protected_dirs                    |
| report                            |
| report_auto                       |
| report_section                    |
| repository                        |
| resp_attach                       |
| resp_forward                      |
| resp_freq                         |
| restricteddomains                 |
| sa_conf                           |
| sa_list                           |
| sa_param                          |
| scheduledtasks                    |
| schemaversions                    |
| secret_keys                       |
| secretkeys                        |
| secretkeysipaddresses             |
| servicenodecache                  |
| servicenodecertificates           |
| servicenodeconfiguration          |
| servicenodeenvironment            |
| servicenodes                      |
| sessioncontexts                   |
| sessions                          |
| siteappshitsstat                  |
| sitepagesstat                     |
| skins                             |
| smb_apsbundlefilteritems          |
| smb_apsbundlefilters              |
| smb_apscategories                 |
| smb_apscontexts                   |
| smb_apsinstanceerrors             |
| smb_apsinstances                  |
| smb_apsmetas                      |
| smb_apspackages                   |
| smb_apspackagescategories         |
| smb_apspackageupdates             |
| smb_apsprovisionenvironments      |
| smb_apsprovisions                 |
| smb_apsresourceparameters         |
| smb_apsresources                  |
| smb_apssettings                   |
| smb_componentupdates              |
| smb_generalpermissions            |
| smb_locales                       |
| smb_productupgrades               |
| smb_rolegeneralpermissions        |
| smb_roles                         |
| smb_roleservicepermissions        |
| smb_serviceentrypoints            |
| smb_serviceinstances              |
| smb_servicepermissionaccounts     |
| smb_servicepermissions            |
| smb_serviceproviders              |
| smb_settings                      |
| smb_users                         |
| smb_userservicepermissions        |
| smtp_poplocks                     |
| smtpwhitelists                    |
| stat                              |
| subdomains                        |
| subscriptionoutgoingmessagespeaks |
| subscriptionoutgoingmessagesstats |
| subscriptionproperties            |
| subscriptions                     |
| suspend_handler_history           |
| sys_users                         |
| templates                         |
| tmpldata                          |
| tracefailedrequestsproviderareas  |
| tracefailedrequestsproviders      |
| tracefailedrequestsrules          |
| upgrade_history                   |
| usersettings                      |
| web_users                         |
| webserversettings                 |
| webserversettingsparameters       |
| websitesdiagnostic                |
| websitesdiagnosticdomains         |
+-----------------------------------+
196 rows in set (0.002 sec)

MariaDB [psa]> DESCRIBE accounts
    -> ;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| type     | varchar(32)      | NO   |     | plain   |                |
| password | text             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.008 sec)

MariaDB [psa]> select * from templates
    -> ;
+----+------------------+----------+----------+--------------------------------------+-------------+
| id | name             | owner_id | type     | uuid                                 | external_id |
+----+------------------+----------+----------+--------------------------------------+-------------+
|  1 | Admin Simple     |        1 | domain   | f09ac736-add3-c6bc-7311-a88aa658bd42 | NULL        |
|  2 | Default Reseller |        1 | reseller | 117aaec5-b818-52fc-2e6f-3103d2592c5d | NULL        |
|  3 | Default Domain   |        1 | domain   | aee30dca-4d8e-d34c-2b58-61f4c9b9f179 | NULL        |
|  4 | Unlimited        |        1 | domain   | 2b0d607c-0962-ef8f-0e1a-09b1d3686cde | NULL        |
|  5 | Default Simple   |        1 | domain   | ac21b67b-5fac-176a-2767-d9fc3953d857 | NULL        |
+----+------------------+----------+----------+--------------------------------------+-------------+
5 rows in set (0.000 sec)

MariaDB [psa]>

WOW. Great. Now, to see the records contained in the 'domain' table, which command would you use?

 

[Jhow Plug]

@scsa20, you're right. I somehow tough it was only possible to use the plesk db utility to run SQL queries directly. Like for example plesk db SELECT * FROM ModuleSettings. Reading the support article you linked to again I now see it clearly explains how to use the plesk db utility to access the PSA database.

I learned something new today. Thank you

WOW. Great. Now, to see the records contained in the 'domain' table, which command would you use?

 

[scsa20]

It's just your normal SQL queries at that point.

So something like: SELECT * FROM domains WHERE what_youre_looking_for

 

[Jhow Plug]

It's just your normal SQL queries at that point.

So something like: SELECT * FROM domains WHERE what_youre_looking_for

Thank you very much

Do you make the queries directly at the command prompt or indicate a database manager to carry out the queries?

 

[Change Maker]

Sorry. I picked this thread up as I'm trying to make sure I type in the correct SSH Command to back up the Plesk DB, just in case I need to do a restore.

https://support.plesk.com/hc/en-us/articles/12377958344855-How-to-back-up-restore-a-system-database-in-Plesk

It needs a path so I'm trying to find out what the correct CLS would be to type this in.

I was looking at the DB on this thread, to see if I could find out where it was.

Can anyone advise. I'm not a seasoned Command Line person, but I'm always happy to try and learn as I go along.
Thanks

 

[Kaspar@Plesk]

@Robert Alexander, based on your previous forum posts I assume your using Plesk on a Linux OS.

To export (dump) the Plesk database you'd use:

plesk db dump psa > /root/psa_dump.sql

Where PSA is the name of the Plesk database, no need to change this. And /root/psa_dump.sql is the path and file for the database export. You can change this, but there is no need to.

To import the Plesk database you can use:

plesk db < /root/psa_dump.sql

Is this example /root/psa_dump.sql is the path and file for the previous made database export (dump). If you used a different path or file you would use that.

 

[Change Maker]

Thanks Kaspar. Appreciate that.