The General Query Log

曾经终败给现在 2022-09-21 01:28 22阅读 0赞

本文转载自：http://dev.mysql.com/doc/refman/5.5/en/query-log.html

### The General Query Log ###

The general query log is a general record of what [mysqld][] is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to [mysqld][].

[mysqld][] writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order is in contrast with that of the binary log, for which statements are written after they are executed but before any locks are released. In addition, the query log may contain statements that only select data while such statements are never written to the binary log.

When using statement-based logging all statements are written to the query log, but when using row-based logging, updates are sent as row changes rather than SQL statements, and thus these statements are never written to the query log when [`binlog_format`][binlog_format] is `ROW`. A given update also might not be written to the query log when this variable is set to `MIXED`, depending on the statement used. See [Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”][Section_17.1.2.1_ _Advantages and Disadvantages of Statement-Based and Row-Based Replication], for more information.

By default, the general query log is disabled. To specify the initial general query log state explicitly, use [`--general_log[={0|1}]`][--general_log_0_1]. With no argument or an argument of 1, [`--general_log`][--general_log_0_1] enables the log. With an argument of 0, this option disables the log. To specify a log file name, use [`--general_log_file=file_name`][--general_log_file_file_name]. To specify the log destination, use [`--log-output`][--log-output] (as described in [Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”][Section_5.2.1_ _Selecting General Query and Slow Query Log Output Destinations]). The older options to enable the general query log, [`--log`][--log] and `-l`, are deprecated.

If you specify no name for the general query log file, the default name is `host_name.log`. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To disable or enable the general query log or change the log file name at runtime, use the global [`general_log`][--general_log_0_1]and [`general_log_file`][--general_log_file_file_name] system variables. Set [`general_log`][--general_log_0_1] to 0 (or `OFF`) to disable the log or to 1 (or `ON`) to enable it. Set [`general_log_file`][--general_log_file_file_name] to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

When the general query log is enabled, the server writes output to any destinations specified by the [`--log-output`][--log-output] option or [`log_output`][log_output] system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the `FILE` log destination is selected. If the destination is `NONE`, the server writes no queries even if the general log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain `FILE`.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). To rename the file and create a new one, use the following commands:

shell> mv host_name.log host_name-old.log
    shell> mysqladmin flush-logs
    shell> mv host_name-old.log backup-directory

On Windows, use rename rather than mv.

You can also rename the general query log file at runtime by disabling the log:

SET GLOBAL general_log = 'OFF';

With the log disabled, rename the log file externally; for example, from the command line. Then enable the log again:

SET GLOBAL general_log = 'ON';

This method works on any platform and does not require a server restart.

The session [`sql_log_off`][sql_log_off] variable can be set to `ON` or `OFF` to disable or enable general query logging for the current connection.

The general query log should be protected because logged statements might contain passwords. See[Section 6.1.2.3, “Passwords and Logging”][Section_6.1.2.3_ _Passwords and Logging].

[Previous][] /  [Next][] /  [Up][] /  [Table of Contents][]

# User Comments #

<table style="margin:0px 0px 10px; padding:4px 0px 8px; border:0px; outline:0px; font-size:14px; vertical-align:baseline; background-color:transparent; border-spacing:0px; border-collapse:collapse; font-weight:bold; width:1040px"> 
 <tbody style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:top; background-color:transparent"> 
   <td style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> Posted by Jason Bibbings on January 14 2011 3:34pm</td> 
   <td align="right" style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=11668&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html&amp;action=delete" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=11668&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Edit</a>]</td> 
  </tr> 
 </tbody> 
</table>

On Windows, I could not get the Log Files to work using the default path of "C:/Program Files (x86)/MySQL/MySQL Server 5.1"   
  
This was on Windows Server 2008 using 32-bit MySQL version 5.1.49  
  
Moving the logs out of that directory and into the root C: fixed the issue for me. All I can figure is the logs don't like the parentheses in (x86), I've seen this a couple times before.   
  
Anyway if it helps anyone, my current working my.ini file on Windows now looks like this:  
  
\[mysqld\]  
\# Set Slow Query Log  
long\_query\_time = 1  
slow\_query\_log = 1  
slow\_query\_log\_file = "C:/slowquery.log"  
  
\#Set General Log  
log = "C:/genquery.log"

<table style="margin:0px 0px 10px; padding:4px 0px 8px; border:0px; outline:0px; font-size:14px; vertical-align:baseline; background-color:transparent; border-spacing:0px; border-collapse:collapse; font-weight:bold; width:1040px"> 
 <tbody style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:top; background-color:transparent"> 
   <td style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> Posted by Greg Schretter on November 16 2011 10:52pm</td> 
   <td align="right" style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12075&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html&amp;action=delete" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12075&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Edit</a>]</td> 
  </tr> 
 </tbody> 
</table>

Correct most system do not like "()" also the "." might cause issues, but it might be a good idea to have the files written some where else besides root. Maybe C:\\MySql\\Logs\\(different sub-directory names base on category). Root should be locked down on Windows servers. Hope this helps..

<table style="margin:0px 0px 10px; padding:4px 0px 8px; border:0px; outline:0px; font-size:14px; vertical-align:baseline; background-color:transparent; border-spacing:0px; border-collapse:collapse; font-weight:bold; width:1040px"> 
 <tbody style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:top; background-color:transparent"> 
   <td style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> Posted by Jakob Langgaard on April 21 2012 11:56pm</td> 
   <td align="right" style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12269&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html&amp;action=delete" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12269&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Edit</a>]</td> 
  </tr> 
 </tbody> 
</table>

The reason why the log file won't work in "C:/Program Files (x86)/MySQL/MySQL Server 5.1" is because programs need to be run with administrator privileges to be able to write or modify files within the program files folder.

<table style="margin:0px 0px 10px; padding:4px 0px 8px; border:0px; outline:0px; font-size:14px; vertical-align:baseline; background-color:transparent; border-spacing:0px; border-collapse:collapse; font-weight:bold; width:1040px"> 
 <tbody style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent"> 
  <tr style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:top; background-color:transparent"> 
   <td style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> Posted by Programmer Old on April 3 2013 5:46pm</td> 
   <td align="right" style="margin:0px; padding:0px 5px; border:0.1em outset gray; outline:0px; font-size:13px; vertical-align:baseline; background-color:transparent; font-family:Helvetica,Arial,sans-serif"> [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12614&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html&amp;action=delete" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12614&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Fquery-log.html" style="margin:0px; padding:0px; border:0px; outline:0px; vertical-align:baseline; background-color:transparent; text-decoration:none; color:rgb(0,117,159)" rel="nofollow">Edit</a>]</td> 
  </tr> 
 </tbody> 
</table>

Find "datadir" in "my.ini": for me the line is  
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"  
  
Likly that something along this path is what you want

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文章与“日志系统”主题相关。

[mysqld]: http://dev.mysql.com/doc/refman/5.5/en/mysqld.html
[binlog_format]: http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_binlog_format
[Section_17.1.2.1_ _Advantages and Disadvantages of Statement-Based and Row-Based Replication]: http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html
[--general_log_0_1]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_general_log
[--general_log_file_file_name]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_general_log_file
[--log-output]: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-output
[Section_5.2.1_ _Selecting General Query and Slow Query Log Output Destinations]: http://dev.mysql.com/doc/refman/5.5/en/log-destinations.html
[--log]: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log
[log_output]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_log_output
[sql_log_off]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sql_log_off
[Section_6.1.2.3_ _Passwords and Logging]: http://dev.mysql.com/doc/refman/5.5/en/password-logging.html
[Previous]: http://dev.mysql.com/doc/refman/5.5/en/error-log.html
[Next]: http://dev.mysql.com/doc/refman/5.5/en/binary-log.html
[Up]: http://dev.mysql.com/doc/refman/5.5/en/server-logs.html
[Table of Contents]: http://dev.mysql.com/doc/refman/5.5/en/index.html