MySQL Tipps und Tricks: Difference between revisions
From Lolly's Wiki
Jump to navigationJump to search
| Line 11: | Line 11: | ||
===Per database=== | ===Per database=== | ||
<source lang=mysql> | <source lang=mysql> | ||
mysql> select table_schema, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema; | mysql> select table_schema as database, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema; | ||
</source> | </source> | ||
===Per table=== | ===Per table=== | ||
<source lang=mysql> | <source lang=mysql> | ||
mysql> select table_schema as database,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb; | mysql> select table_schema as database,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb; | ||
</source> | </source> | ||
Revision as of 09:29, 11 August 2015
Oneliner
All grants
# mysql --skip-column-names --batch --execute 'select concat_ws("@",user,host) from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}'
InnoDB space
Per database
mysql> select table_schema as database, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema;
Per table
mysql> select table_schema as database,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb;
