MySQL Tipps und Tricks: Difference between revisions
From Lolly's Wiki
Jump to navigationJump to search
No edit summary |
No edit summary |
||
| Line 2: | Line 2: | ||
== | ==Oneliner== | ||
=== | ===All grants=== | ||
<source lang=bash> | <source lang=bash> | ||
# mysql --skip-column-names --batch --execute 'select concat_ws("@",user,host) from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}' | # mysql --skip-column-names --batch --execute 'select concat_ws("@",user,host) from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}' | ||
</source> | |||
==InnoDB space== | |||
===Per database=== | |||
<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; | |||
</source> | |||
===Per table=== | |||
<source lang=mysql> | |||
mysql> select table_schema,table_name,concat_ws(" ",round(data_length/1024/1024,2),"MB") as size_mb from information_schema.tables order by size_mb; | |||
</source> | </source> | ||
Revision as of 09:28, 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, 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,table_name,concat_ws(" ",round(data_length/1024/1024,2),"MB") as size_mb from information_schema.tables order by size_mb;
