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.

Advertisements

Getting ‘doesn’t have a default value’ from MySQL (MariaDB)?

Solution for me from ideas at https://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values and https://www.farbeyondcode.com/Solution-for-MariaDB-Field–xxx–doesn-t-have-a-default-value-5-2720.html was to remove ‘STRICT_TRANS_TABLES’ from my.cnf.

So I ended up with the following in /usr/local/mysql/my.cnf:
[mysqlnd]
sql_mode=NO_ENGINE_SUBSTITUTION

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

WakeOnLan z Macu

Po upgrade na nový OSX som bol nútený znova spojazdniť zaslanie Magic Packetu môjmu backup notebooku.  Po úspešnom nainštalovaní macports podľa návodu na oficiálnej stránke som inštaloval utilitu wakeonlan:

$sudo port install wakeonlan

Ako ďalší krok nasledovalo zisťit MAC adresu backup notebooku, na ktorom beží Linux, tak z výstupu príkazu ifconfig som skopíroval HWaddr.

$ifconfig
eth0      Link encap:Ethernet  HWaddr 00:1a:6b:6c:08:e0  
          inet addr:192.168.2.2  Bcast:192.168.2.255  Mask:255.255.255.0
          inet6 addr: fe80::21a:6bff:fe6c:8e0/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:208 errors:0 dropped:0 overruns:0 frame:0
          TX packets:251 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100 
          RX bytes:30099 (30.0 KB)  TX bytes:34089 (34.0 KB)
          Memory:ee000000-ee020000

MAC adresu som pridal do bash skriptu, ktorý som nazval wake_jarvis-om 🙂

#!/bin/bash
wakeonlan 00:1a:6b:6c:08:e0

Už len uložiť a spraviť skript spustiteľný.

$chmod +x ~/Dropbox/bin/wake_jarvis

Po spustení by mal byť výstup vyzerať nejako takto:

Sending magic packet to 255.255.255.255:9 with 00:1a:6b:6c:08:e0

A mašina s danou MAC adresou by sa mala “zobudiť”. K fungovaniu je predpoklad, že daná mašina podporuje WakeOnLan. Tiež som musel v BIOSe zapnúť.