Applicable to:
- Plesk for Linux
- Plesk for Windows
Question
How to list databases with their subscription/domain/owner using a MySQL query in Plesk?
Answer
-
Run one of these commands:
-
This SQL query lists all databases with corresponding subscriptions and customers.
# plesk db "select db.name as 'Database',d.name as 'Subscription',c.pname as 'Owner',c.login as 'Customer Username' from data_bases db,domains d,clients c where d.cl_id=c.id and db.dom_id=d.id"
The output will look as follows:
MYSQL_LIN: +----------------+---------------+---------------+----------------+
| Database | Subscription | Owner | Owner Username |
+----------------+---------------+---------------+----------------+
| joomla_7 | joomla.tld | Administrator | admin |
| wordpress_a | wordpress.tld | Administrator | admin |
| db_example.com | example.com | John Doe | jdoe |
| db_example.net | example.com | John Doe | jdoe |
+----------------+---------------+---------------+----------------+ -
This SQL query lists all databases that have the option "Related to" defined in Plesk. If it is not set, database will not appear in the list. Please note that this query does not list databases that were created together with applications via the Plesk Applications menu.
# plesk db "select db.name as 'Database', d.name as 'Related to' from data_bases db, domains d, dom_param p where d.id=p.dom_id and p.val=db.id and p.param='lastDatabaseSelectedId'"
The output will look as follows:
MYSQL_LIN: +----------------+-------------+
| Database | Related to |
+----------------+-------------+
| db_example.com | example.com |
| db_example.net | example.net |
+----------------+-------------+
-
Comments
9 comments
Can we have similar Query for MSSQL to list the DBs and their subscription/domain/owner
Hello @Sreehari,
The data is gathered from the Plesk database. According to Plesk system requirements only MySQL is used for Plesk database.
To filter MSSQL databases from Plesk database you may use the following Mysql query:
select db.name as "Database",d.name as "Subscription",c.pname as "Owner",c.login as "Customer Username",db.type as "Server type" from data_bases db,domains d,clients c where d.cl_id=c.id and db.dom_id=d.id and db.type = 'mssql';
Thanks Vladimir,
I am able to figure out the MSSQL DBs by using the above query from MYSQL.
Is there a way to select database users as well ?
Gsamarguliani yes you proceed as follows:
plesk db "select db.name as 'Database',d.name as 'Subscription',c.pname as 'Owner',e.login as 'Databa User', c.login as 'Customer Username' from data_bases db,domains d,clients c, db_users e where d.cl_id=c.id and db.dom_id=d.id and e.db_id=db.id"
@Julian Bonpland Mignaquy thanks this works, how do i get plesk database user/password that is responsible for system databases ?
i mean db,domains , db_users etc.
Hello @Gsamarguliani,
Due to security reasons, only hashes of passwords are stored in Plesk database.
System user passwords cannot be viewed in plain text.
If it is necessary, passwords can be updated in Plesk: https://support.plesk.com/hc/en-us/articles/360000277114
Hi,
great query. However, we have several MSSQL servers for our customer's DBs. How can we get the same info per DB server so we can distinguish which databases are on which server?
Best regards,
Panos
Is it possible to set "Related to site" by plesk cli utility?
Please sign in to leave a comment.