It’s good idea to monitor or log all SQL Queries to your MySQL Database. There is a simple way and tool to trace all MySQL activities similar to Microsoft SQL Server Profiler. This is great stuff for both developers and database administrators to debug, troubleshoot and deeper understand which SQL commands are submitted to your MySQL database.
It’s the log option in the my.cnf (on Unix) file or my.ini (on Windows) file which be specified to log every single query to a query log file. By default, this option is off and you have to turn it on.
1. Turn on the log option in the MySQL config file
Find and open the my.cnf file usually on /etc/mysql/my.cnf on Linux or my.ini usually on C:\xampp\mysql\bin on Windows system.
If there is a line looks like:
//log = /var/log/mysql/mysql.log
just uncomment that line to turn on the logging or if it’s not existed, just add that line to your config file. Remember to adjust the path.
Here is my.ini file for your reference:[text highlight=”25″] # The MySQL server
[mysqld] basedir = "C:/xampp/mysql/"
datadir = "D:/dev/db/Mysql/data/"
port = 3306
socket = "MySQL"
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-time-zone = "Asia/Krasnoyarsk"
log_error = "C:/xampp/mysql/data/mysql.err"
pid_file = "mysql.pid"
general_log = 0
general_log_file = "C:/xampp/mysql/data/mysql.log"
slow_query_log = 0
slow_query_log_file = "C:/xampp/mysql/data/mysql-slow.log"
log = "C:/xampp/mysql/query.log"
2. Restart the MySQL server
After update and save the my.ini or my.cnf file, you need to restart MySQLto make it affected.
Restart the MySQL service by going to Control Panel => Administrative Tools => Services
Or if you’re on Linux system, simply type the command below:
sudo /etc/init.d/mysql restart
3. Start monitoring all MySQL activities
There is a tool named LogMonitor written in Java by Silviu Lita. It’s user interface program and very easy to use. It monitors the query log file produced by the MySQL server and displays the latest activity on the screen. Note: to run the application, the JRE (Java Runtime Environment) 1.5 or above must be available on your system.
First, download the ProjectLogMonitor, unzip then run LogMonitor.jar located at \ProjectLogMonitor\ProjectLogMonitor\Jar.
For the first time of running, you need to specify location of the query log file which is config in your my.cnf or my.ini in step 1. Go to Options menu, click on Log file path then browse to the query.log file. After that, click Open button.
It seems nothing happen because there is no query to your Mysql Server. Let start a website which is running MySQL database and visit some pages, MySQL activities will be displayed on your screen.
You will get more information as well as more configurations in the ReadMe.doc (\ProjectLogMonitor\ProjectLogMonitor\) after you download the ProjectLogMonitor, unzip then run LogMonitor.jar file.
Or if you’re on Linux, open a new terminal and run command below:
tail -f /var/log/mysql/query.log
You will see all your database activities appear in the terminal.
Note: the log option should be only turned on on your staging environment for logging, debugging or troubleshooting because it may slow down your server by recording all your queries to a log file.