<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mysql-logging">
      </a>
      6.5.1.3 mysql Client Logging
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045317485376">
  </a>
  <a class="indexterm" name="idm46045317484288">
  </a>
  <a class="indexterm" name="idm46045317482800">
  </a>
  <a class="indexterm" name="idm46045317481712">
  </a>
  <a class="indexterm" name="idm46045317480224">
  </a>
  <a class="indexterm" name="idm46045317479136">
  </a>
  <a class="indexterm" name="idm46045317477648">
  </a>
  <a class="indexterm" name="idm46045317476576">
  </a>
  <a class="indexterm" name="idm46045317475088">
  </a>
  <p>
   The
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client can do these types of
        logging for statements executed interactively:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      On Unix,
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      writes the statements to a
            history file. By default, this file is named
      <code class="filename">
       .mysql_history
      </code>
      in your home directory.
            To specify a different file, set the value of the
      <code class="literal">
       MYSQL_HISTFILE
      </code>
      environment variable.
     </p>
    </li>
    <li class="listitem">
     <p>
      On all platforms, if the
      <code class="option">
       --syslog
      </code>
      option is
            given,
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      writes the statements to the
            system logging facility. On Unix, this is
      <code class="literal">
       syslog
      </code>
      ; on Windows, it is the Windows
            Event Log. The destination where logged messages appear is
            system dependent. On Linux, the destination is often the
      <code class="filename">
       /var/log/messages
      </code>
      file.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following discussion describes characteristics that apply to
        all logging types and provides information specific to each
        logging type.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-logging.html#mysql-logging-how-logging-occurs" title="How Logging Occurs">
       How Logging Occurs
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-logging.html#mysql-logging-history-file" title="Controlling the History File">
       Controlling the History File
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-logging.html#mysql-logging-syslog" title="syslog Logging Characteristics">
       syslog Logging Characteristics
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="mysql-logging-how-logging-occurs">
   </a>
   How Logging Occurs
  </h5>
  <p>
   For each enabled logging destination, statement logging occurs
        as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Statements are logged only when executed interactively.
            Statements are noninteractive, for example, when read from a
            file or a pipe. It is also possible to suppress statement
            logging by using the
      <a class="link" href="mysql-command-options.html#option_mysql_batch">
       <code class="option">
        --batch
       </code>
      </a>
      or
      <a class="link" href="mysql-command-options.html#option_mysql_execute">
       <code class="option">
        --execute
       </code>
      </a>
      option.
     </p>
    </li>
    <li class="listitem">
     <p>
      Statements are ignored and not logged if they match any
            pattern in the
      <span class="quote">
       “
       <span class="quote">
        ignore
       </span>
       ”
      </span>
      list. This list is
            described later.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      logs each nonignored, nonempty
            statement line individually.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a nonignored statement spans multiple lines (not
            including the terminating delimiter),
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      concatenates the lines to form the
            complete statement, maps newlines to spaces, and logs the
            result, plus a delimiter.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Consequently, an input statement that spans multiple lines can
        be logged twice. Consider this input:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa91029123"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
<span class="token prompt">    -&gt;</span> <span class="token string">'Today is'</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span> <span class="token function">CURDATE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this case,
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   logs the
   <span class="quote">
    “
    <span class="quote">
     SELECT
    </span>
    ”
   </span>
   ,
   <span class="quote">
    “
    <span class="quote">
     'Today is'
    </span>
    ”
   </span>
   ,
   <span class="quote">
    “
    <span class="quote">
     ,
    </span>
    ”
   </span>
   ,
   <span class="quote">
    “
    <span class="quote">
     CURDATE()
    </span>
    ”
   </span>
   , and
   <span class="quote">
    “
    <span class="quote">
     ;
    </span>
    ”
   </span>
   lines as it reads them. It also logs the complete statement,
        after mapping
   <code class="literal">
    SELECT\n'Today
        is'\n,\nCURDATE()
   </code>
   to
   <code class="literal">
    SELECT 'Today is' ,
        CURDATE()
   </code>
   , plus a delimiter. Thus, these lines appear
        in logged output:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27217445"><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">SELECT</span>
<span class="token string">'Today is'</span>
<span class="token punctuation">,</span>
<span class="token function">CURDATE</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token string">'Today is'</span> <span class="token punctuation">,</span> <span class="token function">CURDATE</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   ignores for logging purposes statements
        that match any pattern in the
   <span class="quote">
    “
    <span class="quote">
     ignore
    </span>
    ”
   </span>
   list. By
        default, the pattern list is
   <code class="literal">
    "*IDENTIFIED*:*PASSWORD*"
   </code>
   , to ignore
        statements that refer to passwords. Pattern matching is not
        case-sensitive. Within patterns, two characters are special:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ?
      </code>
      matches any single character.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       *
      </code>
      matches any sequence of zero or more
            characters.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To specify additional patterns, use the
   <a class="link" href="mysql-command-options.html#option_mysql_histignore">
    <code class="option">
     --histignore
    </code>
   </a>
   option or set the
   <code class="literal">
    MYSQL_HISTIGNORE
   </code>
   environment variable. (If
        both are specified, the option value takes precedence.) The
        value should be a list of one or more colon-separated patterns,
        which are appended to the default pattern list.
  </p>
  <p>
   Patterns specified on the command line might need to be quoted
        or escaped to prevent your command interpreter from treating
        them specially. For example, to suppress logging for
   <code class="literal">
    UPDATE
   </code>
   and
   <code class="literal">
    DELETE
   </code>
   statements in addition to statements that refer to passwords,
        invoke
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa96948568"><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">mysql <span class="token constant">--histignore</span>=<span class="token atrule">"*UPDATE*:*DELETE*"</span></code></pre>
  </div>
  <h5>
   <a name="mysql-logging-history-file">
   </a>
   Controlling the History File
  </h5>
  <p>
   The
   <code class="filename">
    .mysql_history
   </code>
   file should be protected
        with a restrictive access mode because sensitive information
        might be written to it, such as the text of SQL statements that
        contain passwords. See
   <a class="xref" href="password-security-user.html" title="8.1.2.1 End-User Guidelines for Password Security">
    Section 8.1.2.1, “End-User Guidelines for Password Security”
   </a>
   .
        Statements in the file are accessible from the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client when the
   <span class="keycap">
    <strong>
     up-arrow
    </strong>
   </span>
   key is used to recall the history. See
   <a class="xref" href="mysql-tips.html#mysql-history" title="Disabling Interactive History">
    Disabling Interactive History
   </a>
   .
  </p>
  <p>
   If you do not want to maintain a history file, first remove
   <code class="filename">
    .mysql_history
   </code>
   if it exists. Then use
        either of the following techniques to prevent it from being
        created again:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Set the
      <code class="literal">
       MYSQL_HISTFILE
      </code>
      environment
            variable to
      <code class="filename">
       /dev/null
      </code>
      . To cause this
            setting to take effect each time you log in, put it in one
            of your shell's startup files.
     </p>
    </li>
    <li class="listitem">
     <p>
      Create
      <code class="filename">
       .mysql_history
      </code>
      as a symbolic
            link to
      <code class="filename">
       /dev/null
      </code>
      ; this need be done
            only once:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa90384572"><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">ln <span class="token property">-s</span> /dev/null $HOME/<span class="token punctuation">.</span>mysql_history</code></pre>
     </div>
    </li>
   </ul>
  </div>
  <h5>
   <a name="mysql-logging-syslog">
   </a>
   syslog Logging Characteristics
  </h5>
  <p>
   If the
   <code class="option">
    --syslog
   </code>
   option is given,
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   writes interactive statements to the
        system logging facility. Message logging has the following
        characteristics.
  </p>
  <p>
   Logging occurs at the
   <span class="quote">
    “
    <span class="quote">
     information
    </span>
    ”
   </span>
   level. This
        corresponds to the
   <code class="literal">
    LOG_INFO
   </code>
   priority for
   <code class="literal">
    syslog
   </code>
   on Unix/Linux
   <code class="literal">
    syslog
   </code>
   capability and to
   <code class="literal">
    EVENTLOG_INFORMATION_TYPE
   </code>
   for the Windows
        Event Log. Consult your system documentation for configuration
        of your logging capability.
  </p>
  <p>
   Message size is limited to 1024 bytes.
  </p>
  <p>
   Messages consist of the identifier
   <code class="literal">
    MysqlClient
   </code>
   followed by these values:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       SYSTEM_USER
      </code>
     </p>
     <p>
      The operating system user name (login name) or
      <code class="literal">
       --
      </code>
      if the user is unknown.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MYSQL_USER
      </code>
     </p>
     <p>
      The MySQL user name (specified with the
      <a class="link" href="mysql-command-options.html#option_mysql_user">
       <code class="option">
        --user
       </code>
      </a>
      option) or
      <code class="literal">
       --
      </code>
      if the user is unknown.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CONNECTION_ID
      </code>
      :
     </p>
     <p>
      The client connection identifier. This is the same as the
      <a class="link" href="information-functions.html#function_connection-id">
       <code class="literal">
        CONNECTION_ID()
       </code>
      </a>
      function
            value within the session.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DB_SERVER
      </code>
     </p>
     <p>
      The server host or
      <code class="literal">
       --
      </code>
      if the host is
            unknown.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DB
      </code>
     </p>
     <p>
      The default database or
      <code class="literal">
       --
      </code>
      if no database
            has been selected.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       QUERY
      </code>
     </p>
     <p>
      The text of the logged statement.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Here is a sample of output generated on Linux by using
   <code class="option">
    --syslog
   </code>
   . This output is formatted for
        readability; each logged message actually takes a single line.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa93889284"><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-none">Mar  7 12:39:25 myhost MysqlClient[20824]:
  SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
  DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
Mar  7 12:39:28 myhost MysqlClient[20824]:
  SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
  DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'</code></pre>
  </div>
 </div>
 <br/>
</div>
