MySQL Tipps und Tricks: Difference between revisions
From Lolly's Wiki
Jump to navigationJump to search
No edit summary |
|||
| Line 17: | Line 17: | ||
<source lang=mysql> | <source lang=mysql> | ||
mysql> select table_schema as database_name,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_name,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb; | ||
</source> | |||
==Logging== | |||
===Enable/disable logging=== | |||
<source lang=mysql> | |||
mysql> SET GLOBAL general_log = 'ON'; | |||
Query OK, 0 rows affected (0.00 sec) | |||
mysql> SET GLOBAL general_log = 'OFF'; | |||
Query OK, 0 rows affected (0.00 sec) | |||
</source> | </source> | ||
Revision as of 10:31, 12 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_name, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema order by total_size_mb;
Per table
mysql> select table_schema as database_name,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb;
Logging
Enable/disable logging
mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)
