Checking size on multiple mysql tables and schemas

The space on my database server ran out, and I want a quick way to view all the tab开发者_Python百科les on all schemas, and how much size each table takes. How do I do that?

If I could find the fattest schemas and work my way from there, that's also good.

You could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this:

show table status like '%';

you'll find a column called Data_length which tells you "the length of the data file" for each table. If you simply add the size of each table in the database together, you should have the size of the whole database.

If you run the query which is given below in MySQL Query Browser then you will get the two columns first will display the Data Base Name and the second will display the Data Base Size in MB.

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ; 

simply cd PATH_TO_DATABASE_DATA;

for db in `find -type d `; do du -shc $db/*; done | less

or using maxdepth

for db in `find -type d -maxdepth 1 `; do du -shc $db/*.{MYI,MYD,frm}; done | less

if want to sort

for db in `find -type d -maxdepth 1 `; do ls -alS $db/*.{MYI,MYD,frm}; done | less

*.MYI = index size
*.MYD = data size
*.frm = table schema size

You should use SHOW TABLE STATUS: https://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

SHOW TABLE STATUS works likes SHOW TABLES, but provides a lot of information about each non-TEMPORARY table.