MySQL DB Logging – Watching in Realtime

Install mysqltuner.pl

https://github.com/major/MySQLTuner-perl

Has important info and stats:

Info:
[OK] Maximum reached memory usage: 6.4G (40.77% of installed RAM)
[OK] Maximum possible memory usage: 7.4G (47.70% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

stats
[OK] Slow queries: 0% (1/134M)
[OK] Highest usage of available connections: 10% (49/451)
[OK] Aborted connections: 0.00% (1/3223797)

Report generated by MySQLTuner has a section on InnoDB Metrics and
in that section some important information for tuning.
Examples:

[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size – buffer pool needs to be larger than data size.

[OK] InnoDB buffer pool instances: – needs to be 1 per Gig of buffer pool.

[OK] InnoDB Write Log efficiency:

If you have slow logging defined and turned on, you are making your DB server even slower. While you can and should define the slow log file, you don’t have to set slow logging on in the my.cnf. Rather, turn on slow query logging when you want to get a sampling, then turn it off.

In my.cnf, define the log files:

general_log_file=/var/log/mysql/mysql-general.log
slow_query_log_file=/var/log/mysql/mysql-slow.log

The above log files must belong to mysql user/group.

-rw-r–r–. 1 mysql mysql 416025 May 8 2015 mysql-general.log
-rw-r–r–. 1 mysql mysql 380 May 8 2015 mysql-slow.log

I use 2 bash shell scripts …
One to turn on MySQL slow query logs
The second to turn OFF MySQL slow query logs

Also use a thing called ‘multitail’ which allows watching logs in realtime.
https://www.vanheusden.com/multitail/

Watch MySQL Logs in realtime using multitail
(you replace []’s below)

# to turn on:
mysql -e 'set global log = 1' -u [SUPERUSER] -p'[PASSWORD]'
mysql -e 'set global log_slow_queries = 1' -u [SUPERUSER] -p'[PASSWORD]'
mysql -e 'set global slow_query_log = 1' -u [SUPERUSER] -p'[PASSWORD]'
multitail -i /var/log/mysql/mysql-general.log -i /var/log/mysql/mysql-slow.log;

The mysql-general.log file and the mysql-slow.log already exist and
have to be defined in my.cnf as well as belong to mysql user.

# to turn off
set global slow_query_log = 0
set global log_slow_queries = 0
set global log = 0;