<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="log-destinations">
      </a>
      7.4.1 Selecting General Query Log and Slow Query Log Output Destinations
     </h3>
    </div>
   </div>
  </div>
  <p>
   MySQL Server provides flexible control over the destination of
      output written to the general query log and the slow query log, if
      those logs are enabled. Possible destinations for log entries are
      log files or the
   <code class="literal">
    general_log
   </code>
   and
   <code class="literal">
    slow_log
   </code>
   tables in the
   <code class="literal">
    mysql
   </code>
   system database. File output, table output, or both can be
      selected.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="log-destinations.html#log-destinations-startup" title="Log Control at Server Startup">
       Log Control at Server Startup
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="log-destinations.html#log-destinations-runtime" title="Log Control at Runtime">
       Log Control at Runtime
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="log-destinations.html#log-destinations-tables" title="Log Table Benefits and Characteristics">
       Log Table Benefits and Characteristics
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="log-destinations-startup">
       </a>
       Log Control at Server Startup
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_log_output">
     <code class="literal">
      log_output
     </code>
    </a>
    system variable
        specifies the destination for log output. Setting this variable
        does not in itself enable the logs; they must be enabled
        separately.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_log_output">
        <code class="literal">
         log_output
        </code>
       </a>
       is not
            specified at startup, the default logging destination is
       <code class="literal">
        FILE
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_log_output">
        <code class="literal">
         log_output
        </code>
       </a>
       is specified
            at startup, its value is a list one or more comma-separated
            words chosen from
       <code class="literal">
        TABLE
       </code>
       (log to tables),
       <code class="literal">
        FILE
       </code>
       (log to files), or
       <code class="literal">
        NONE
       </code>
       (do not log to tables or files).
       <code class="literal">
        NONE
       </code>
       , if present, takes precedence over
            any other specifiers.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_general_log">
     <code class="literal">
      general_log
     </code>
    </a>
    system variable
        controls logging to the general query log for the selected log
        destinations. If specified at server startup,
    <a class="link" href="server-system-variables.html#sysvar_general_log">
     <code class="literal">
      general_log
     </code>
    </a>
    takes an optional
        argument of 1 or 0 to enable or disable the log. To specify a
        file name other than the default for file logging, set the
    <a class="link" href="server-system-variables.html#sysvar_general_log_file">
     <code class="literal">
      general_log_file
     </code>
    </a>
    variable.
        Similarly, the
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
     <code class="literal">
      slow_query_log
     </code>
    </a>
    variable controls logging to the slow query log for the selected
        destinations and setting
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log_file">
     <code class="literal">
      slow_query_log_file
     </code>
    </a>
    specifies a
        file name for file logging. If either log is enabled, the server
        opens the corresponding log file and writes startup messages to
        it. However, further logging of queries to the file does not
        occur unless the
    <code class="literal">
     FILE
    </code>
    log destination is
        selected.
   </p>
   <p>
    Examples:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       To write general query log entries to the log table and the
            log file, use
       <a class="link" href="server-system-variables.html#sysvar_log_output">
        <code class="option">
         --log_output=TABLE,FILE
        </code>
       </a>
       to
            select both log destinations and
       <a class="link" href="server-system-variables.html#sysvar_general_log">
        <code class="option">
         --general_log
        </code>
       </a>
       to enable the
            general query log.
      </p>
     </li>
     <li class="listitem">
      <p>
       To write general and slow query log entries only to the log
            tables, use
       <a class="link" href="server-system-variables.html#sysvar_log_output">
        <code class="option">
         --log_output=TABLE
        </code>
       </a>
       to select
            tables as the log destination and
       <a class="link" href="server-system-variables.html#sysvar_general_log">
        <code class="option">
         --general_log
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
        <code class="option">
         --slow_query_log
        </code>
       </a>
       to enable
            both logs.
      </p>
     </li>
     <li class="listitem">
      <p>
       To write slow query log entries only to the log file, use
       <a class="link" href="server-system-variables.html#sysvar_log_output">
        <code class="option">
         --log_output=FILE
        </code>
       </a>
       to select
            files as the log destination and
       <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
        <code class="option">
         --slow_query_log
        </code>
       </a>
       to enable
            the slow query log. In this case, because the default log
            destination is
       <code class="literal">
        FILE
       </code>
       , you could omit the
       <a class="link" href="server-system-variables.html#sysvar_log_output">
        <code class="literal">
         log_output
        </code>
       </a>
       setting.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="log-destinations-runtime">
       </a>
       Log Control at Runtime
      </h4>
     </div>
    </div>
   </div>
   <p>
    The system variables associated with log tables and files enable
        runtime control over logging:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <a class="link" href="server-system-variables.html#sysvar_log_output">
        <code class="literal">
         log_output
        </code>
       </a>
       variable
            indicates the current logging destination. It can be
            modified at runtime to change the destination.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="server-system-variables.html#sysvar_general_log">
        <code class="literal">
         general_log
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
        <code class="literal">
         slow_query_log
        </code>
       </a>
       variables
            indicate whether the general query log and slow query log
            are enabled (
       <code class="literal">
        ON
       </code>
       ) or disabled
            (
       <code class="literal">
        OFF
       </code>
       ). You can set these variables at
            runtime to control whether the logs are enabled.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="server-system-variables.html#sysvar_general_log_file">
        <code class="literal">
         general_log_file
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_slow_query_log_file">
        <code class="literal">
         slow_query_log_file
        </code>
       </a>
       variables indicate the names of the general query log and
            slow query log files. You can set these variables at server
            startup or at runtime to change the names of the log files.
      </p>
     </li>
     <li class="listitem">
      <p>
       To disable or enable general query logging for the current
            session, set the session
       <a class="link" href="server-system-variables.html#sysvar_sql_log_off">
        <code class="literal">
         sql_log_off
        </code>
       </a>
       variable to
       <code class="literal">
        ON
       </code>
       or
       <code class="literal">
        OFF
       </code>
       . (This
            assumes that the general query log itself is enabled.)
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="log-destinations-tables">
       </a>
       Log Table Benefits and Characteristics
      </h4>
     </div>
    </div>
   </div>
   <p>
    The use of tables for log output offers the following benefits:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Log entries have a standard format. To display the current
            structure of the log tables, use these statements:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55802873"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mysql<span class="token punctuation">.</span>general_log<span class="token punctuation">;</span>
<span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mysql<span class="token punctuation">.</span>slow_log<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Log contents are accessible through SQL statements. This
            enables the use of queries that select only those log
            entries that satisfy specific criteria. For example, to
            select log contents associated with a particular client
            (which can be useful for identifying problematic queries
            from that client), it is easier to do this using a log table
            than a log file.
      </p>
     </li>
     <li class="listitem">
      <p>
       Logs are accessible remotely through any client that can
            connect to the server and issue queries (if the client has
            the appropriate log table privileges). It is not necessary
            to log in to the server host and directly access the file
            system.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The log table implementation has the following characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       In general, the primary purpose of log tables is to provide
            an interface for users to observe the runtime execution of
            the server, not to interfere with its runtime execution.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
       ,
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       , and
       <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
        <code class="literal">
         DROP TABLE
        </code>
       </a>
       are valid
            operations on a log table. For
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER
            TABLE
        </code>
       </a>
       and
       <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
        <code class="literal">
         DROP
            TABLE
        </code>
       </a>
       , the log table cannot be in use and must be
            disabled, as described later.
      </p>
     </li>
     <li class="listitem">
      <p>
       By default, the log tables use the
       <code class="literal">
        CSV
       </code>
       storage engine that writes data in comma-separated values
            format. For users who have access to the
       <code class="filename">
        .CSV
       </code>
       files that contain log table data,
            the files are easy to import into other programs such as
            spreadsheets that can process CSV input.
      </p>
      <p>
       The log tables can be altered to use the
       <code class="literal">
        MyISAM
       </code>
       storage engine. You cannot use
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       to alter a log
            table that is in use. The log must be disabled first. No
            engines other than
       <code class="literal">
        CSV
       </code>
       or
       <code class="literal">
        MyISAM
       </code>
       are legal for the log tables.
      </p>
      <p>
       <a name="log-destinations-tables-open-files">
       </a>
       <b>
        Log Tables and
        <span class="quote">
         “
         <span class="quote">
          Too many open files
         </span>
         ”
        </span>
        Errors.
       </b>
       <a class="indexterm" name="idm46045259328384">
       </a>
       If you select
       <code class="literal">
        TABLE
       </code>
       as a log
              destination and the log tables use the
       <code class="literal">
        CSV
       </code>
       storage engine, you may find that
              disabling and enabling the general query log or slow query
              log repeatedly at runtime results in a number of open file
              descriptors for the
       <code class="filename">
        .CSV
       </code>
       file,
              possibly resulting in a
       <span class="quote">
        “
        <span class="quote">
         Too many open files
        </span>
        ”
       </span>
       error. To work around this issue, execute
       <a class="link" href="flush.html" title="15.7.8.3 FLUSH Statement">
        <code class="literal">
         FLUSH
              TABLES
        </code>
       </a>
       or ensure that the value of
       <a class="link" href="server-system-variables.html#sysvar_open_files_limit">
        <code class="literal">
         open_files_limit
        </code>
       </a>
       is
              greater than the value of
       <a class="link" href="server-system-variables.html#sysvar_table_open_cache_instances">
        <code class="literal">
         table_open_cache_instances
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       To disable logging so that you can alter (or drop) a log
            table, you can use the following strategy. The example uses
            the general query log; the procedure for the slow query log
            is similar but uses the
       <code class="literal">
        slow_log
       </code>
       table
            and
       <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
        <code class="literal">
         slow_query_log
        </code>
       </a>
       system
            variable.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66804419"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@old_log_state</span> <span class="token operator">=</span> <span class="token variable">@@GLOBAL.general_log</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> general_log <span class="token operator">=</span> <span class="token string">'OFF'</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> mysql<span class="token punctuation">.</span>general_log <span class="token keyword">ENGINE</span> <span class="token operator">=</span> MyISAM<span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> general_log <span class="token operator">=</span> <span class="token variable">@old_log_state</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
        <code class="literal">
         TRUNCATE TABLE
        </code>
       </a>
       is a valid
            operation on a log table. It can be used to expire log
            entries.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
        <code class="literal">
         RENAME TABLE
        </code>
       </a>
       is a valid
            operation on a log table. You can atomically rename a log
            table (to perform log rotation, for example) using the
            following strategy:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58989856"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">USE</span> mysql<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> general_log2<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> general_log2 <span class="token operator">LIKE</span> general_log<span class="token punctuation">;</span>
<span class="token keyword">RENAME</span> <span class="token keyword">TABLE</span> general_log <span class="token keyword">TO</span> general_log_backup<span class="token punctuation">,</span> general_log2 <span class="token keyword">TO</span> general_log<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
        <code class="literal">
         CHECK TABLE
        </code>
       </a>
       is a valid
            operation on a log table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
        <code class="literal">
         LOCK TABLES
        </code>
       </a>
       cannot be used on
            a log table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       ,
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       , and
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       cannot be used on a
            log table. These operations are permitted only internally to
            the server itself.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="flush.html#flush-tables-with-read-lock">
        <code class="literal">
         FLUSH TABLES WITH READ LOCK
        </code>
       </a>
       and the state of the
       <a class="link" href="server-system-variables.html#sysvar_read_only">
        <code class="literal">
         read_only
        </code>
       </a>
       system variable
            have no effect on log tables. The server can always write to
            the log tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       Entries written to the log tables are not written to the
            binary log and thus are not replicated to replicas.
      </p>
     </li>
     <li class="listitem">
      <p>
       To flush the log tables or log files, use
       <a class="link" href="flush.html#flush-tables">
        <code class="literal">
         FLUSH TABLES
        </code>
       </a>
       or
       <a class="link" href="flush.html#flush-logs">
        <code class="literal">
         FLUSH LOGS
        </code>
       </a>
       , respectively.
      </p>
     </li>
     <li class="listitem">
      <p>
       Partitioning of log tables is not permitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       A
       <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
        <span class="command">
         <strong>
          mysqldump
         </strong>
        </span>
       </a>
       dump includes statements to
            recreate those tables so that they are not missing after
            reloading the dump file. Log table contents are not dumped.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
