<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-read-only-instance">
      </a>
      17.8.2 Configuring InnoDB for Read-Only Operation
     </h3>
    </div>
   </div>
  </div>
  <p>
   You can query
   <code class="literal">
    InnoDB
   </code>
   tables where the MySQL
      data directory is on read-only media by enabling the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
    <code class="literal">
     --innodb-read-only
    </code>
   </a>
   configuration
      option at server startup.
  </p>
  <h4>
   <a name="idm46045163414080">
   </a>
   How to Enable
  </h4>
  <p>
   To prepare an instance for read-only operation, make sure all the
      necessary information is
   <a class="link" href="glossary.html#glos_flush" title="flush">
    flushed
   </a>
   to the data files before storing it on the read-only medium. Run
      the server with change buffering disabled
      (
   <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffering">
    <code class="literal">
     innodb_change_buffering=0
    </code>
   </a>
   ) and
      do a
   <a class="link" href="glossary.html#glos_slow_shutdown" title="slow shutdown">
    slow shutdown
   </a>
   .
  </p>
  <p>
   To enable read-only mode for an entire MySQL instance, specify the
      following configuration options at server startup:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
       <code class="literal">
        --innodb-read-only=1
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      If the instance is on read-only media such as a DVD or CD, or
          the
      <code class="filename">
       /var
      </code>
      directory is not writeable by
          all:
      <a class="link" href="server-system-variables.html#sysvar_pid_file">
       <code class="literal">
        --pid-file=
        <em class="replaceable">
         <code>
          path_on_writeable_media
         </code>
        </em>
       </code>
      </a>
      and
      <a class="link" href="server-system-variables.html#sysvar_event_scheduler">
       <code class="literal">
        --event-scheduler=disabled
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
       <code class="literal">
        --innodb-temp-data-file-path
       </code>
      </a>
      .
          This option specifies the path, file name, and file size for
      <code class="literal">
       InnoDB
      </code>
      temporary tablespace data files. The
          default setting is
      <code class="literal">
       ibtmp1:12M:autoextend
      </code>
      ,
          which creates the
      <code class="filename">
       ibtmp1
      </code>
      temporary
          tablespace data file in the data directory. To prepare an
          instance for read-only operation, set
      <a class="link" href="innodb-parameters.html#sysvar_innodb_temp_data_file_path">
       <code class="literal">
        innodb_temp_data_file_path
       </code>
      </a>
      to
          a location outside of the data directory. The path must be
          relative to the data directory. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa87429868"><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 constant">--innodb-temp-data-file-path</span><span class="token attr-value"><span class="token punctuation">=</span>../../../tmp/ibtmp1:12M:autoextend</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Enabling
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
    <code class="literal">
     innodb_read_only
    </code>
   </a>
   prevents table creation and drop operations for all storage
      engines. These operations modify data dictionary tables in the
   <code class="literal">
    mysql
   </code>
   system database, but those tables use the
   <code class="literal">
    InnoDB
   </code>
   storage engine and cannot be modified
      when
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
    <code class="literal">
     innodb_read_only
    </code>
   </a>
   is enabled.
      The same restriction applies to any operation that modifies data
      dictionary tables, such as
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE
      TABLE
    </code>
   </a>
   and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     ENGINE=
     <em class="replaceable">
      <code>
       engine_name
      </code>
     </em>
    </code>
   </a>
   .
  </p>
  <p>
   In addition, other tables in the
   <code class="literal">
    mysql
   </code>
   system
      database use the
   <code class="literal">
    InnoDB
   </code>
   storage engine in MySQL
      8.4. Making those tables read only results in
      restrictions on operations that modify them. For example,
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   ,
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   ,
   <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
    <code class="literal">
     REVOKE
    </code>
   </a>
   , and
   <a class="link" href="install-plugin.html" title="15.7.4.4 INSTALL PLUGIN Statement">
    <code class="literal">
     INSTALL PLUGIN
    </code>
   </a>
   operations are not
      permitted in read-only mode.
  </p>
  <h4>
   <a name="idm46045163380864">
   </a>
   Usage Scenarios
  </h4>
  <p>
   This mode of operation is appropriate in situations such as:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Distributing a MySQL application, or a set of MySQL data, on a
          read-only storage medium such as a DVD or CD.
     </p>
    </li>
    <li class="listitem">
     <p>
      Multiple MySQL instances querying the same data directory
          simultaneously, typically in a data warehousing configuration.
          You might use this technique to avoid
      <a class="link" href="glossary.html#glos_bottleneck" title="bottleneck">
       bottlenecks
      </a>
      that can
          occur with a heavily loaded MySQL instance, or you might use
          different configuration options for the various instances to
          tune each one for particular kinds of queries.
     </p>
    </li>
    <li class="listitem">
     <p>
      Querying data that has been put into a read-only state for
          security or data integrity reasons, such as archived backup
          data.
     </p>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    This feature is mainly intended for flexibility in distribution
        and deployment, rather than raw performance based on the
        read-only aspect. See
    <a class="xref" href="innodb-performance-ro-txn.html" title="10.5.3 Optimizing InnoDB Read-Only Transactions">
     Section 10.5.3, “Optimizing InnoDB Read-Only Transactions”
    </a>
    for ways to tune the
        performance of read-only queries, which do not require making
        the entire server read-only.
   </p>
  </div>
  <h4>
   <a name="idm46045163373584">
   </a>
   How It Works
  </h4>
  <p>
   When the server is run in read-only mode through the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
    <code class="literal">
     --innodb-read-only
    </code>
   </a>
   option,
      certain
   <code class="literal">
    InnoDB
   </code>
   features and components are
      reduced or turned off entirely:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      No
      <a class="link" href="glossary.html#glos_change_buffering" title="change buffering">
       change
          buffering
      </a>
      is done, in particular no merges from the
          change buffer. To make sure the change buffer is empty when
          you prepare the instance for read-only operation, disable
          change buffering
          (
      <a class="link" href="innodb-parameters.html#sysvar_innodb_change_buffering">
       <code class="literal">
        innodb_change_buffering=0
       </code>
      </a>
      )
          and do a
      <a class="link" href="glossary.html#glos_slow_shutdown" title="slow shutdown">
       slow
          shutdown
      </a>
      first.
     </p>
    </li>
    <li class="listitem">
     <p>
      There is no
      <a class="link" href="glossary.html#glos_crash_recovery" title="crash recovery">
       crash
          recovery
      </a>
      phase at startup. The instance must have
          performed a
      <a class="link" href="glossary.html#glos_slow_shutdown" title="slow shutdown">
       slow
          shutdown
      </a>
      before being put into the read-only state.
     </p>
    </li>
    <li class="listitem">
     <p>
      Because the
      <a class="link" href="glossary.html#glos_redo_log" title="redo log">
       redo log
      </a>
      is
          not used in read-only operation, you can set
      <a class="link" href="innodb-parameters.html#sysvar_innodb_log_file_size">
       <code class="literal">
        innodb_log_file_size
       </code>
      </a>
      to the
          smallest size possible (1 MB) before making the instance
          read-only.
     </p>
    </li>
    <li class="listitem">
     <p>
      Most background threads are turned off. I/O read threads
          remain, as well as I/O write threads and a page flush
          coordinator thread for writes to temporary files, which are
          permitted in read-only mode. A buffer pool resize thread also
          remains active to enable online resizing of the buffer pool.
     </p>
    </li>
    <li class="listitem">
     <p>
      Information about deadlocks, monitor output, and so on is not
          written to temporary files. As a consequence,
      <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
       <code class="literal">
        SHOW ENGINE
          INNODB STATUS
       </code>
      </a>
      does not produce any output.
     </p>
    </li>
    <li class="listitem">
     <p>
      Changes to configuration option settings that would normally
          change the behavior of write operations, have no effect when
          the server is in read-only mode.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="glossary.html#glos_mvcc" title="MVCC">
       MVCC
      </a>
      processing to
          enforce
      <a class="link" href="glossary.html#glos_isolation_level" title="isolation level">
       isolation
          levels
      </a>
      is turned off. All queries read the latest
          version of a record, because update and deletes are not
          possible.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="glossary.html#glos_undo_log" title="undo log">
       undo log
      </a>
      is not used.
          Disable any settings for the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_tablespaces">
       <code class="literal">
        innodb_undo_tablespaces
       </code>
      </a>
      and
      <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
       <code class="literal">
        innodb_undo_directory
       </code>
      </a>
      configuration options.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
