Monday, September 22, 2008

MySQL Query Logging on CentOS5 and external Moodle Authentication DB's

When I'm not blogging about two-bit Alaska mayors/governors, most of the point is to jot down things that aren't necessarily profound but that are useful, and it did not up easily in one minute of googling.

I knew I'd done this before, but like so many things you don't use every day, it is easy to forget. Fortunately I discovered the nice Windows GUI admin tools for MySQL so I wouldn't have to write command-line PHP. Something else I do like every 3-4 years.

So my goal is to enable database queries so you can debug a web app. In my case I'm trying to enable Moodle to use and external authentication database and of course it fails the first time.

So the command line argument to enable query logging is "--log=/var/log/mysql.queries"

That is easy enough, but where to put it in /etc/init.d/mysqld?

Ideally I'd like to put in the global MySQL options file (/etc/my.cnf) which gets read in by get_mysql_option() but this doesn't work although I did get it to show up with my_print_defaults (a new one for me) so I'm not sure what is up. Tried both under [mysqld] and [mysqld_safe] so I did it the old fashioned way and added it to the line that starts up mysql_safe

/usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \
--log=/var/log/mysql.queries \
--log-error="$errlogfile" \
>/dev/null 2>&1 &

Not pretty but good enough and discovered that I had not granted by local user access to the database.

1 comment:

Matt Franz said...

Something obvious to remember is the the perms must allow mysql to write to the logfile (doh!)

[root@localhost log]# ls -al mysql.queries
-rw-r--r-- 1 mysql mysql 11341 Dec 29 10:11 mysql.queries