Log navigator a.k.a. lnav

Getting to love lnav. Just specify a directory path as argument and it will parse the logs.

lnav /var/log/mysql

A list of supported log formats is at http://lnav.readthedocs.io/en/latest/formats.html

Advertisements

New path for MySQL query log on OSX

Sometimes during development I need to see what SQL queries are executed and listing them in Console.app is maybe the most convenient solution on OSX.

console.app

To see queries in Console

mysqld query log OSX

I just created /etc/mysql/my.cnf with:

[mysqld]
general_log = 1
# general_log_file = /usr/local/mysql/data/mysqld.query.log
general_log_file = /var/log/mysql/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
# slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
slow_query_log_file = /var/log/mysql/mysqld.slow.log
log_queries_not_using_indexes = 0

MySQL server needs own permissions on the parent log dir so:

sudo mkdir /var/log/mysql
sudo chown -R _mysql:_mysql /var/log/mysql

Last step is server restart.

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

On a Linux machine I use multitail as an alternative.

Enabling MySQL 5.7 query logging on OSX

I got used to it that sometimes I need to check the queries run by MySQL. After clean OSX upgrade I forgot to enable it.

So I added the following to /etc/mysql/my.cnf:

[mysqld]

sql_mode=

general_log = 1
general_log_file = /usr/local/mysql/data/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
log_queries_not_using_indexes = 0

The default one that comes with the package is at /usr/local/mysql/support-files/my-default.cnf

There’s also an option to set logging via SQL queries.

Enable MySQL file logging

Wanted to know what queries are executed so I had to enable it for MariaDB.

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql.log';
SET GLOBAL slow_query_log = 'On';
SET GLOBAL slow_query_log_file = '/var/log/mysql-slow.log';

To see the changes:

SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'slow_query_log';

logrotate error: stat of /var/log/xferlog failed

In Arch Linux the logrotate service was failing:

user@host# systemctl start logrotate.service 
Job for logrotate.service failed. See "systemctl status logrotate.service" and "journalctl -xe" for details.

So I ran it by hand to debug:

user@host# logrotate /etc/logrotate.conf

And the following error appeared:

logrotate error: stat of /var/log/xferlog failed

I fired up a grep for xferlog in the /etc directory:

user@host:/etc# grep -r xferlog *
logrotate.d/proftpd:/var/log/xferlog

Solution was commenting the xferlog rule in logrotate.d/proftpd. I don’t need the transfer log.

Switched from Console.app to multitail

With Console.app I had the problem that when I switched to output /var/log/apache2/error_log I didn’t see
/var/log/system.log and yesterday I read http://kkovacs.eu/cool-but-obscure-unix-tools and found multitail so I played with it.

I’m currently running the following setup on OSX Mountain Lion


mike@mikembp:~$ cat bin/multitail-log.sh
#!/bin/bash

multitail -s 2 /tmp/lsof-net.log \
/var/log/apache2/error_log \
/var/log/system.log -I /var/log/wifi.log -I /var/log/mail.log \
/var/log/mysql.log

I had to make a crontab to get /tmp/lsof-net.log file, because multitail -R 2 -l “lsof lsof -RPi4 +c15” was crashing with “Operation not permitted”. I think the problem is that lsofon Mac is in /usr/sbin. Crons minimal execution is every minute so I had to call the desired command 29 times with 2 second sleep.


mike@mikembp:~$ cat bin/cron-netlog.sh
#!/bin/bash

# crontab -e
# * * * * * /Users/mike/bin/cron-netlog.sh

LOGFILE=/tmp/lsof-net.log

for (( i=1; i <= 29; i++ ))
do
/usr/sbin/lsof -RPi4 +c15 | grep -v -e rtorrent -e Mail -e Last | awk '{print $1,$2,$3,$4,$9,$10}' | column -t >> $LOGFILE
sleep 2
done

mike@mikembp:~$ cat .crontab
# ~/.crontab
#
# Run:
# crontab ~/.crontab

MAILTO=user@example.com

* * * * * ~/bin/cron-netlog.sh

mike@mikembp:~$ crontab .crontab