Quickly Toggle MySQL Logging

MySQL logging is a lifesaver when trying to understand applications at a low level. However, since I use it infrequently, I always have to consult the Google doc I have with the details on how to toggle the setting, and where to find the output.

For Windows, I've switched to a BAT file to speed up the process (which I call from SlickRun).

BEFORE using the script, you will want to have the following in your MySQL config file (in Laragon, use the right button menu -> mysql -> my.ini) in the [mysqld] section.

log-output=FILE
general_log_file = "C:/whatever/mylog.txt"
general_log      = OFF

@echo off
set /p state="Set MySQL Logging state ON or OFF: "
c:\xampp\mysql\bin\mysql -u "root" -e "SET GLOBAL general_log = '%state%';
%SystemRoot%\explorer.exe "C:\whatever"

A better alternative is to output to a table. In your MySQL config file (in Laragon, use the right button menu -> mysql -> my.ini) in the [mysqld] section, add

log-output=TABLE (see docs (MySQL 5.7))
general_log      = OFF

Then your script would be

@echo off
set /p state="Set MySQL Logging state ON or OFF: "
c:\xampp\mysql\bin\mysql -u "root" -e "SET GLOBAL general_log = '%state%';

You will find the data in the mysql.general_log table. What I do to make is easier to grok is to create a table with queries that I want to ignore (e.g. the standard PrestaShop code at the beginning of a page load).

create table mysql.general_log_ignores
(
argument_to_ignore mediumblob null
);

and then select only the interesting queries with

select 
       event_time, 
       convert(argument using utf8)
from 
    general_log gl 
    left join general_log_ignores gli on gl.argument = gli.argument_to_ignore
where
    gli.argument_to_ignore is null
order by 
    gl.event_time;