I may have mentioned before that I’m doing a LAMP project these days (among other things). As I go along I’m taking notes on various tasks, etc. so I figured I could post some of them to my blog. Currently the site is hosted on a virtual dedicated server running Fedora Core 4. Anyhow…
Logging MySQL Queries
You may need to turn on query logging for mysql from time to time in order to see what SQL is being passed to the database. Here’s how to do so in Linux. This makes some assumptions about directories, so you may need to execute the commands in different places depending on your setup. FYI, I did the following on a Fedora Core 4 distro. Also, if you’re running a busy server, your query log will probably get really huge, so be forewarned! I wouldn’t leave query logging running all the time, or at least not on a production machine.
Anyhow, shell into your server, log in with an administratively enabled account, and follow the below steps.
Create the MySQL logging file
If you haven’t created the MySQL logging file before, you’ll need to do so.
First create an empty file with
touch /var/log/mysqlq.log
That makes an empty file. Now change the ownership to the mysql account to it via
chown mysql /var/log/mysqlq.log
Now the mysql user account can write to that file.
Enable MySQL logging
Now you need to stop mysql
/usr/bin/mysqladmin -u root -p shutdown
you’ll be prompted for the root password, enter it and mysql will shut down. You may need to hit ENTER after you get the shutdown message in order to get your prompt back.
Now start mysql with logging enabled.
/usr/bin/mysqld_safe --log="/var/log/mysqlq.log" &
The trailing ampersand is important, otherwise you won’t get your shell prompt back.
You should see a message about logging and mysql starting. Hit ENTER to get your shell prompt.
Using the Log
You can now run some web sites or do some things that query the MySQL database and the results should be logged.
You can look at the log using vim or other editors. You can look at the last 100 lines of the log via
tail -100 /var/log/mysqlq.log
Note that your log will get pretty big, so to turn off logging, stop MySQL using the shutdown command above, and then start it back up with
/usr/bin/mysqld_safe &
Clearing the Log
You may want to clear your log from time to time if it gets too big. To do first, first make sure that MySQL is not currently logging to the file. You can see if any process is accessing the log by running this command & seeing if anything is returned. If no lines return then nothing is using the log.
fuser /var/log/mysqlq.log
Before clearing the log you could make a backup of it using
cp /var/log/mysqlq.log "$(date +%Y%m%d)-mysqlq.log"
That will create a copy of the log file with today’s date in the filename, e.g. 20070408-mysqlq.log
You can then clear the log file using
> /var/log/mysqlq.log
Yes, you type the > in that command. 🙂
Troubleshooting
If you don’t see commands being written to your log, look at the /var/log/mysqld.log log file to see what the problem is.
