MySQL Tipps und Tricks: Difference between revisions
From Lolly's Wiki
Jump to navigationJump to search
No edit summary |
|||
| Line 21: | Line 21: | ||
==Logging== | ==Logging== | ||
===Enable/disable logging=== | ===Enable/disable general logging=== | ||
<source lang=mysql> | <source lang=mysql> | ||
mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log'; | |||
Query OK, 0 rows affected (0.00 sec) | |||
mysql> SET GLOBAL general_log = 'ON'; | mysql> SET GLOBAL general_log = 'ON'; | ||
Query OK, 0 rows affected (0.00 sec) | Query OK, 0 rows affected (0.00 sec) | ||
Revision as of 10:32, 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 general logging
mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
Query OK, 0 rows affected (0.00 sec)
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)
