As a server admin you may run into a few problems with SQL from time to time – then it’s important that you can analyze your slow.log, which is a file that you can use to log all queries that take longer than X seconds to complete. Let’s get it done!
1. Open SSH via Putty or whatever tool you use
2. Enter pico /etc/my.cnf
or vim/nano to view the configuration file of your MySQL installation (cant find it? enter locate my.cnf
)
3. Alright, now go to the bottom of the file (CTRL + K) and paste this code
general_log=1#####LOGS#######
log_slow_queries=/var/log/slow.log
long_query_time=1
general_log_file=/var/log/sql.log
4. Hit CTRL + O to save and then exit via CTRL + X
5. Now, here’s the important part – you need to create those files in the exact path and assign the correct permissions
6. Enter cd /var/log
7. Enter touch slow.log
and touch sql.log
Assign Correct Permssions: This Is Necessary For SQL To Start Logging
8. Finally, assign the correct permissions: chown mysql:mysql sql.log
and chown mysql:mysql slow.log
9. Eventually, you may also want to make sure to run this either via PHPMyAdmin’s SQL Box or via mysql directly:
SET GLOBAL general_log = 'ON';