List all databases and users from Plesk MySQL
Anthony Gee | Feb 16, 2010 | Comments 4
This tutorial is for these who want to gather or just see the databases and the users under Plesk using MySQL command.
For that you must have to login to the Plesk server as ‘root’ and there to execute MySQL query with ‘psa’ table active.
I presume you are already logged to your server via SSH or console.
Now execute this command:
|
1 |
~$ mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa |
If you have only ‘sudo’ access the above command will not work since the ‘.psa.shadow’ file will not be accessible, even that you are entering the ‘sudo’ password.
Instead you can use:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
~$ sudo mysql -uadmin -p psa [sudo] password for CurrentUser: Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 113 Server version: 5.0.51a-3ubuntu5.5 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> |
The next MySQL command will list all Plesk databases and assigned users.
|
1 2 3 4 5 6 7 |
mysql> SELECT domains.name AS domain_name, -> data_bases.name AS database_name, db_users.login, accounts.password -> FROM data_bases, db_users, domains, accounts -> WHERE data_bases.dom_id = domains.id -> AND db_users.db_id = data_bases.id -> AND db_users.account_id = accounts.id -> ORDER BY domain_name; |
The above command is paste from my command line which shows the consecutive MySQL query, so take in mind if you cannot copy it straight since there are some characters which will generate errors.
Here is the MySQL command showing Plesk databases easy to be copied.
|
1 |
SELECT domains.name AS domain_name, data_bases.name AS database_name, db_users.login, accounts.password FROM data_bases, db_users, domains, accounts WHERE data_bases.dom_id = domains.id AND db_users.db_id = data_bases.id AND db_users.account_id = accounts.id ORDER BY domain_name; |
The list of the Plesk databases and their users result should look like this:
|
1 2 3 4 5 6 7 8 9 |
+--------------+-----------------+------------------+-----------+ | domain_name | database_name | login | password | +--------------+-----------------+------------------+-----------+ | mydomain.com | one_mississippi | one_mississippi | asdasd | | mydomain.com | one_mississippi | one_mississippi2 | asdasd | | mydomain.com | two_mississippi | two_mississippi | qweasd | | mydomain.com | two_mississippi | two_mississippi2 | 123qweasd | +--------------+-----------------+------------------+-----------+ 4 rows in set (0.01 sec) |
In my example the domain is always �mydomain.com�, but that is because I have only one domain setup in plesk. If you have others they will be listed in the same table with all the Plesk database users.
If you receive errors, the most common issues could be either the Plesk database (psa) is not selected or you do not have access to the Plesk MySQL database.
In case the database is not changed and you are not really sure how to make it active you may relay to this tutorial MySQL commands.
Regarding the access to the server contact your hosting provider .
Tags
Filed Under: Plesk Control Panel
About the Author: Anthony G. is an IT specialist with more than 9 years of solid working experience in the Web Hosting industry. Currently works as server support administrator, involved in consultative discussions about Web Hosting and server administration. One of the first writers in the Onlinehowto.net website, now writing for Free Tutorials community - he is publishing tutorials and articles for the wide public, as well as specific technical solutions.
I was looking for a way to list the DB’s without logging into Plesk since I’m connected to a client’s site on my cell phone using ConnectBot’s SSH client for Android, and this was PERFECT!!! Thanks so much for this article! =0)
Thanks! This helped me when I was having problems connecting to the Plesk tables when using this guide:
(http://www.simplesteps.com/content/view/89/1/)
mysql> use plesk;
was not working but after using the command
mysql -uadmin -p
cat /etc/psa/.psa.shadowpsaI was able to select from the Plesk tables
:-)
You are welcome Denon . The reason is because the database is called ‘psa’ not ‘plesk’
One line execution
mysql -uadmin -p
cat /etc/psa/.psa.shadow-Dpsa -e”SELECT domains.name AS domain_name, data_bases.name AS database_name, db_users.login, accounts.password FROM data_bases, db_users, domains, accounts WHERE data_bases.dom_id = domains.id AND db_users.db_id = data_bases.id AND db_users.account_id = accounts.id ORDER BY domain_name;”