<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="program-variables">
      </a>
      6.2.2.5 Using Options to Set Program Variables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045323675840">
  </a>
  <a class="indexterm" name="idm46045323674384">
  </a>
  <p>
   Many MySQL programs have internal variables that can be set at
        runtime using the
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   statement. See
   <a class="xref" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    Section 15.7.6.1, “SET Syntax for Variable Assignment”
   </a>
   , and
   <a class="xref" href="using-system-variables.html" title="7.1.9 Using System Variables">
    Section 7.1.9, “Using System Variables”
   </a>
   .
  </p>
  <p>
   Most of these program variables also can be set at server
        startup by using the same syntax that applies to specifying
        program options. For example,
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   has a
   <code class="literal">
    max_allowed_packet
   </code>
   variable that controls the
        maximum size of its communication buffer. To set the
   <code class="literal">
    max_allowed_packet
   </code>
   variable for
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   to a value of 16MB, use either of the
        following commands:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa94027082"><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">--max_allowed_packet</span><span class="token attr-value"><span class="token punctuation">=</span>16777216</span>
mysql <span class="token constant">--max_allowed_packet</span><span class="token attr-value"><span class="token punctuation">=</span>16M</span></code></pre>
  </div>
  <p>
   The first command specifies the value in bytes. The second
        specifies the value in megabytes. For variables that take a
        numeric value, the value can be given with a suffix of
   <code class="literal">
    K
   </code>
   ,
   <code class="literal">
    M
   </code>
   , or
   <code class="literal">
    G
   </code>
   to indicate a multiplier of 1024,
        1024
   <sup>
    2
   </sup>
   or
        1024
   <sup>
    3
   </sup>
   . (For example, when used to set
   <code class="literal">
    max_allowed_packet
   </code>
   , the suffixes indicate
        units of kilobytes, megabytes, or gigabytes.) As of MySQL
        8.0.14, a suffix can also be
   <code class="literal">
    T
   </code>
   ,
   <code class="literal">
    P
   </code>
   , and
   <code class="literal">
    E
   </code>
   to indicate a
        multiplier of 1024
   <sup>
    4
   </sup>
   ,
        1024
   <sup>
    5
   </sup>
   or
        1024
   <sup>
    6
   </sup>
   . Suffix letters can be
        uppercase or lowercase.
  </p>
  <p>
   In an option file, variable settings are given without the
        leading dashes:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa37156971"><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-ini"><span class="token selector">[mysql]</span>
<span class="token constant">max_allowed_packet</span><span class="token attr-value"><span class="token punctuation">=</span>16777216</span></code></pre>
  </div>
  <p>
   Or:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa97037320"><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-ini"><span class="token selector">[mysql]</span>
<span class="token constant">max_allowed_packet</span><span class="token attr-value"><span class="token punctuation">=</span>16M</span></code></pre>
  </div>
  <p>
   If you like, underscores in an option name can be specified as
        dashes. The following option groups are equivalent. Both set the
        size of the server's key buffer to 512MB:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa87570409"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>512M</span>

<span class="token selector">[mysqld]</span>
<span class="token constant">key-buffer-size</span><span class="token attr-value"><span class="token punctuation">=</span>512M</span></code></pre>
  </div>
  <p>
   Suffixes for specifying a value multiplier can be used when
        setting a variable at program invocation time, but not to set
        the value with
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   at
        runtime. On the other hand, with
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   , you
        can assign a variable's value using an expression, which is not
        true when you set a variable at server startup. For example, the
        first of the following lines is legal at program invocation
        time, but the second is not:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa2318503"><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"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token constant">--max_allowed_packet</span><span class="token attr-value"><span class="token punctuation">=</span>16M</span>
<span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token constant">--max_allowed_packet</span><span class="token attr-value"><span class="token punctuation">=</span>16*1024*1024</span></code></pre>
  </div>
  <p>
   Conversely, the second of the following lines is legal at
        runtime, but the first is not:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26361104"><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">SET</span> <span class="token keyword">GLOBAL</span> max_allowed_packet<span class="token operator">=</span>16M<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> max_allowed_packet<span class="token operator">=</span><span class="token number">16</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
