Version 5.1.6 and higher:
1. Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';
2. Turn on any other parameters you need. Here are some common examples:
Log details for queries that are expected to retrieve all rows instead of using an index:
set global log_queries_not_using_indexes = 'ON'
Set the path to the slow query log:
set global slow_query_log_file ='/var/log/mysql/slow-query.log';
Set the time during which the request must be run before recording:
set global long_query_time = '20'; (default is 10 seconds)
3. Confirm that the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';
Version below 5.1.6:
Edit the /etc/my.cnf file with your favorite vi / etc / my.cnf text editor
Add the following line to the "[mysqld]" section. Feel free to update the path to the log file to what you want:
log-slow-queries=/var/log/mysql/slow-query.log
3. If necessary, add additional parameters. Here are the same most commonly used examples from above:
Set the time during which the request must be run before recording:
`long_query_time=20 (default is 10 seconds)`
Log details for queries that are expected to retrieve all rows instead of using an index:
`log-queries-not-using-indexes`
4. Restart the MySQL service:
service mysqld restart
5. Confirm that the change is active by entering the MySQL shell and doing the following:
show variables like '%slow%';
Update: 1
According to MySQL docs, error # 1193 occurs when you use the wrong code for SQLSTATE.
Message: Unknown system variable %s
And, as you can see on the same page, SQLSTATE 99003 is undefined.
link:
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html