MySQL Tipps und Tricks

Tipps und Tricks

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

If you use SET GLOBAL it is just for the moment.

Don't forget to add it in your my.cnf to make it permanent!

What can I log?

The interesting variables here are:

• log_queries_not_using_indexes
• log_slave_updates
• log_slow_queries
• general_log

Choose logging destination FILE/TABLE/NONE

This affects general_log and slow_query_log.

• Log to the table mysql.slow_log and mysql.general_log

mysql> SET GLOBAL log_output=TABLE;

• Log to the table mysql.slow_log and mysql.general_log

mysql> SET GLOBAL log_output=TABLE;

• Both: tables and files

mysql> SET GLOBAL log_output = 'TABLE,FILE';

• None, if NONE appears in the log_output destinations there is no logging

mysql> SET GLOBAL log_output = 'TABLE,FILE,NONE';

is equal to

mysql> SET GLOBAL log_output = 'NONE';

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)

Enable/disable logging of slow queries

mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

Filesystems for MySQL

ext3/ext4

Mountoptions are:

• noatime
• data=writeback (best performance , only metadata is logged)
• data=ordered (ok performance , recording metadata and grouping metadata related to the data changes)
• data=journal (worst performance, but best data protection, ext3 default mode, recording metadata and all data)