<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="log-file-maintenance">
      </a>
      7.4.6 Server Log Maintenance
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045257425344">
  </a>
  <a class="indexterm" name="idm46045257423888">
  </a>
  <a class="indexterm" name="idm46045257422400">
  </a>
  <p>
   As described in
   <a class="xref" href="server-logs.html" title="7.4 MySQL Server Logs">
    Section 7.4, “MySQL Server Logs”
   </a>
   , MySQL Server can
      create several different log files to help you see what activity
      is taking place. However, you must clean up these files regularly
      to ensure that the logs do not take up too much disk space.
  </p>
  <p>
   When using MySQL with logging enabled, you may want to back up and
      remove old log files from time to time and tell MySQL to start
      logging to new files. See
   <a class="xref" href="backup-methods.html" title="9.2 Database Backup Methods">
    Section 9.2, “Database Backup Methods”
   </a>
   .
  </p>
  <p>
   On a Linux (Red Hat) installation, you can use the
   <code class="literal">
    mysql-log-rotate
   </code>
   script for log maintenance. If
      you installed MySQL from an RPM distribution, this script should
      have been installed automatically. Be careful with this script if
      you are using the binary log for replication. You should not
      remove binary logs until you are certain that their contents have
      been processed by all replicas.
  </p>
  <p>
   On other systems, you must install a short script yourself that
      you start from
   <span class="command">
    <strong>
     cron
    </strong>
   </span>
   (or its equivalent) for
      handling log files.
  </p>
  <p>
   Binary log files are automatically removed after the server's
      binary log expiration period. Removal of the files can take place
      at startup and when the binary log is flushed. The default binary
      log expiration period is 30 days. To specify an alternative
      expiration period, use the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds">
    <code class="literal">
     binlog_expire_logs_seconds
    </code>
   </a>
   system
      variable. If you are using replication, you should specify an
      expiration period that is no lower than the maximum amount of time
      your replicas might lag behind the source. To remove binary logs
      on demand, use the
   <a class="link" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
    <code class="literal">
     PURGE BINARY
      LOGS
    </code>
   </a>
   statement (see
   <a class="xref" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
    Section 15.4.1.1, “PURGE BINARY LOGS Statement”
   </a>
   ).
  </p>
  <p>
   To force MySQL to start using new log files, flush the logs. Log
      flushing occurs when you execute a
   <a class="link" href="flush.html#flush-logs">
    <code class="literal">
     FLUSH
      LOGS
    </code>
   </a>
   statement or a
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin
      flush-logs
     </strong>
    </span>
   </a>
   ,
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin refresh
     </strong>
    </span>
   </a>
   ,
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   <a class="link" href="mysqldump.html#option_mysqldump_flush-logs">
    <code class="option">
     --flush-logs
    </code>
   </a>
   , or
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   <a class="link" href="mysqldump.html#option_mysqldump_source-data">
    <code class="option">
     --source-data
    </code>
   </a>
   command. See
   <a class="xref" href="flush.html" title="15.7.8.3 FLUSH Statement">
    Section 15.7.8.3, “FLUSH Statement”
   </a>
   ,
   <a class="xref" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    Section 6.5.2, “mysqladmin — A MySQL Server Administration Program”
   </a>
   , and
   <a class="xref" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    Section 6.5.4, “mysqldump — A Database Backup Program”
   </a>
   . In addition, the server flushes the
      binary log automatically when current binary log file size reaches
      the value of the
   <a class="link" href="replication-options-binary-log.html#sysvar_max_binlog_size">
    <code class="literal">
     max_binlog_size
    </code>
   </a>
   system variable.
  </p>
  <p>
   <a class="link" href="flush.html#flush-logs">
    <code class="literal">
     FLUSH LOGS
    </code>
   </a>
   supports optional
      modifiers to enable selective flushing of individual logs (for
      example,
   <a class="link" href="flush.html#flush-binary-logs">
    <code class="literal">
     FLUSH BINARY LOGS
    </code>
   </a>
   ). See
   <a class="xref" href="flush.html" title="15.7.8.3 FLUSH Statement">
    Section 15.7.8.3, “FLUSH Statement”
   </a>
   .
  </p>
  <p>
   A log-flushing operation has the following effects:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If binary logging is enabled, the server closes the current
          binary log file and opens a new log file with the next
          sequence number.
     </p>
    </li>
    <li class="listitem">
     <p>
      If general query logging or slow query logging to a log file
          is enabled, the server closes and reopens the log file.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the server was started with the
      <a class="link" href="server-options.html#option_mysqld_log-error">
       <code class="option">
        --log-error
       </code>
      </a>
      option to cause the
          error log to be written to a file, the server closes and
          reopens the log file.
     </p>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045257391104">
  </a>
  <a class="indexterm" name="idm46045257389616">
  </a>
  <p>
   Execution of log-flushing statements or commands requires
      connecting to the server using an account that has the
   <a class="link" href="privileges-provided.html#priv_reload">
    <code class="literal">
     RELOAD
    </code>
   </a>
   privilege. On Unix and
      Unix-like systems, another way to flush the logs is to send a
      signal to the server, which can be done by
   <code class="literal">
    root
   </code>
   or the account that owns the server process. (See
   <a class="xref" href="unix-signal-response.html" title="6.10 Unix Signal Handling in MySQL">
    Section 6.10, “Unix Signal Handling in MySQL”
   </a>
   .) Signals enable log
      flushing to be performed without having to connect to the server:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A
      <code class="literal">
       SIGHUP
      </code>
      signal flushes all the logs.
          However,
      <code class="literal">
       SIGHUP
      </code>
      has additional effects
          other than log flushing that might be undesirable.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SIGUSR1
      </code>
      causes the server to flush the
          error log, general query log, and slow query log. If you are
          interested in flushing only those logs,
      <code class="literal">
       SIGUSR1
      </code>
      can be used as a more
      <span class="quote">
       “
       <span class="quote">
        lightweight
       </span>
       ”
      </span>
      signal that does not have the
      <code class="literal">
       SIGHUP
      </code>
      effects that are unrelated to logs.
     </p>
    </li>
   </ul>
  </div>
  <p>
   As mentioned previously, flushing the binary log creates a new
      binary log file, whereas flushing the general query log, slow
      query log, or error log just closes and reopens the log file. For
      the latter logs, to cause a new log file to be created on Unix,
      rename the current log file first before flushing it. At flush
      time, the server opens the new log file with the original name.
      For example, if the general query log, slow query log, and error
      log files are named
   <code class="filename">
    mysql.log
   </code>
   ,
   <code class="filename">
    mysql-slow.log
   </code>
   , and
   <code class="filename">
    err.log
   </code>
   , you can use a series of commands
      like this from the command line:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa71930736"><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-terminal">cd <em class="replaceable">mysql-data-directory</em>
mv mysql<span class="token punctuation">.</span>log mysql<span class="token punctuation">.</span>log<span class="token punctuation">.</span>old
mv mysql-slow<span class="token punctuation">.</span>log mysql-slow<span class="token punctuation">.</span>log<span class="token punctuation">.</span>old
mv err<span class="token punctuation">.</span>log err<span class="token punctuation">.</span>log<span class="token punctuation">.</span>old
mysqladmin flush-logs</code></pre>
  </div>
  <p>
   On Windows, use
   <span class="command">
    <strong>
     rename
    </strong>
   </span>
   rather than
   <span class="command">
    <strong>
     mv
    </strong>
   </span>
   .
  </p>
  <p>
   At this point, you can make a backup of
   <code class="filename">
    mysql.log.old
   </code>
   ,
   <code class="filename">
    mysql-slow.log.old
   </code>
   , and
   <code class="filename">
    err.log.old
   </code>
   , then remove them from disk.
  </p>
  <p>
   To rename the general query log or slow query log at runtime,
      first connect to the server and disable the log:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3560754"><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 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">SET</span> <span class="token keyword">GLOBAL</span> slow_query_log <span class="token operator">=</span> <span class="token string">'OFF'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   With the logs disabled, rename the log files externally (for
      example, from the command line). Then enable the logs again:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15971180"><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 keyword">GLOBAL</span> general_log <span class="token operator">=</span> <span class="token string">'ON'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> slow_query_log <span class="token operator">=</span> <span class="token string">'ON'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This method works on any platform and does not require a server
      restart.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    For the server to recreate a given log file after you have
        renamed the file externally, the file location must be writable
        by the server. This may not always be the case. For example, on
        Linux, the server might write the error log as
    <code class="filename">
     /var/log/mysqld.log
    </code>
    , where
    <code class="filename">
     /var/log
    </code>
    is owned by
    <code class="literal">
     root
    </code>
    and not writable by
    <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
     <span class="command">
      <strong>
       mysqld
      </strong>
     </span>
    </a>
    . In this case, log-flushing operations
        fail to create a new log file.
   </p>
   <p>
    To handle this situation, you must manually create the new log
        file with the proper ownership after renaming the original log
        file. For example, execute these commands as
    <code class="literal">
     root
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa74908276"><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-terminal">mv /var/log/mysqld<span class="token punctuation">.</span>log /var/log/mysqld<span class="token punctuation">.</span>log<span class="token punctuation">.</span>old
install <span class="token property">-omysql</span> <span class="token property">-gmysql</span> <span class="token property">-m0644</span> /dev/null /var/log/mysqld<span class="token punctuation">.</span>log</code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
