Example
The General Query Log contains a listing of general information from client connects, disconnects, and queries. It is invaluable for debugging, yet it poses as a hindrance to performance (citation?).
An example view of a General Query Log is seen below:
To determine if the General Log is currently being captured:
SELECT @@general_log; -- 1 = Capture is active; 0 = It is not.
To determine the filename of the capture file:
SELECT @@general_log_file; -- Full path to capture file
If the fullpath to the file is not shown, the file exists in the datadir.
Windows example:
+----------------------------------------------------------+
| @@general_log_file |
+----------------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 5.7\Data\GuySmiley.log |
+----------------------------------------------------------+
Linux:
+-----------------------------------+
| @@general_log_file |
+-----------------------------------+
| /var/lib/mysql/ip-ww-xx-yy-zz.log |
+-----------------------------------+
When changes are made to the general_log_file GLOBAL variable, the new log is saved in the datadir. However, the fullpath may no longer be reflected by examining the variable.
In the case of no entry for general_log_file in the configuration file, it will default to @@hostname.log in the datadir.
Best practices are to turn OFF capture. Save the log file to a backup directory with a filename reflecting the begin/end datetime of the capture. Deleting the prior file if a filesystem move did not occur of that file. Establish a new filename for the log file and turn capture ON (all show below). Best practices also include a careful determination if you even want to capture at the moment. Typically, capture is ON for debugging purposes only.
A typical filesystem filename for a backed-up log might be:
/LogBackup/GeneralLog_20160802_1520_to_20160802_1815.log
where the date and time are part to the filename as a range.
For Windows note the following sequence with setting changes.
SELECT @@general_log; -- 0. Not being captured
SELECT @@general_log_file; -- C:\ProgramData\MySQL\MySQL Server 5.6\Data\GuySmiley.log
SELECT @@datadir; -- C:\ProgramData\MySQL\MySQL Server 5.7\Data\
SET GLOBAL general_log_file='GeneralLogBegin_20160803_1420.log'; -- datetime clue
SET GLOBAL general_log=1; -- Turns on actual log capture. File is created under `datadir`
SET GLOBAL general_log=0; -- Turn logging off
Linux is similar. These would represent dynamic changes. Any restart of the server would pick up configuration file settings.
As for the configuration file, consider the following relevant variable settings:
[mysqld]
general_log_file = /path/to/currentquery.log
general_log = 1
In addition, the variable log_output can be configured for TABLE output, not just FILE. For that, please see Destinations.
Please see the MySQL Manual Page The General Query Log.
