Applicable to:
- Plesk for Linux
- Plesk for Windows
Question
How to get a list of all databases/database tables and their size via a command-line interface on a Plesk server?
Answer
-
Connect to a Plesk via SSH.
-
Run one of the following commands:
-
to get a list of all databases sorted by size:
MYSQL_LIN: plesk db "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema ORDER BY ROUND(SUM(data_length + index_length)) DESC;"
-
to get a list of all database tables sorted by size:
MYSQL_LIN: plesk db "SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;"
-
to get a list of all database tables sorted by a database name:
MYSQL_LIN: plesk db "SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (table_schema) ASC;"
-
-
Connect to a Plesk server via RDP.
-
Run one of the following commands:
-
to get a list of all databases sorted by size:
C:\> "%plesk_dir%MySQL\bin\mysql.exe" -u admin -p -P 3306 -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema ORDER BY ROUND(SUM(data_length + index_length)) DESC;"
-
to get a list of all database tables sorted by size:
C:\> "%plesk_dir%MySQL\bin\mysql.exe" -u admin -p -P 3306 -e "SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;"
-
to get a list of all database tables sorted by a database name:
C:\> "%plesk_dir%MySQL\bin\mysql.exe" -u admin -p -P 3306 -e "SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (table_schema) ASC;"
Note: The password for MySQL admin user is required. Refer to the documentation Changing Database Administrator’s Credentials to change it if needed.
-
Comments
0 comments
Please sign in to leave a comment.