<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-moving-data-files-offline">
      </a>
      17.6.3.6 Moving Tablespace Files While the Server is Offline
     </h4>
    </div>
   </div>
  </div>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
    <code class="literal">
     innodb_directories
    </code>
   </a>
   variable,
      which defines directories to scan at startup for tablespace files,
      supports moving or restoring tablespace files to a new location
      while the server is offline. During startup, discovered tablespace
      files are used instead those referenced in the data dictionary,
      and the data dictionary is updated to reference the relocated
      files. If duplicate tablespace files are discovered by the scan,
      startup fails with an error indicating that multiple files were
      found for the same tablespace ID.
  </p>
  <p>
   The directories defined by the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
    <code class="literal">
     innodb_data_home_dir
    </code>
   </a>
   ,
   <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
    <code class="literal">
     innodb_undo_directory
    </code>
   </a>
   , and
   <a class="link" href="server-system-variables.html#sysvar_datadir">
    <code class="literal">
     datadir
    </code>
   </a>
   variables are
      automatically appended to the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
    <code class="literal">
     innodb_directories
    </code>
   </a>
   argument
      value. These directories are scanned at startup regardless of
      whether an
   <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
    <code class="literal">
     innodb_directories
    </code>
   </a>
   setting is specified explicitly. The implicit addition of these
      directories permits moving system tablespace files, the data
      directory, or undo tablespace files without configuring the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
    <code class="literal">
     innodb_directories
    </code>
   </a>
   setting.
      However, settings must be updated when directories change. For
      example, after relocating the data directory, you must update the
   <a class="link" href="server-system-variables.html#sysvar_datadir">
    <code class="literal">
     --datadir
    </code>
   </a>
   setting before
      restarting the server.
  </p>
  <p>
   The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
    <code class="literal">
     innodb_directories
    </code>
   </a>
   variable
      can be specified in a startup command or MySQL option file. Quotes
      are used around the argument value because a semicolon (;) is
      interpreted as a special character by some command interpreters.
      (Unix shells treat it as a command terminator, for example.)
  </p>
  <p>
   Startup command:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa31270760"><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">mysqld <span class="token constant">--innodb-directories</span>=<span class="token atrule">"<em class="replaceable">directory_path_1</em>;<em class="replaceable">directory_path_2</em>"</span></code></pre>
  </div>
  <p>
   MySQL option file:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa26245907"><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">innodb_directories</span><span class="token attr-value"><span class="token punctuation">=</span>"<em class="replaceable">directory_path_1</em>;<em class="replaceable">directory_path_2</em>"</span></code></pre>
  </div>
  <p>
   The following procedure is applicable to moving individual
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   and
   <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
    general tablespace
   </a>
   files,
   <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
    system
      tablespace
   </a>
   files,
   <a class="link" href="glossary.html#glos_undo_tablespace" title="undo tablespace">
    undo
      tablespace
   </a>
   files, or the data directory. Before moving
      files or directories, review the usage notes that follow.
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Stop the server.
     </p>
    </li>
    <li class="listitem">
     <p>
      Move the tablespace files or directories to the desired
          location.
     </p>
    </li>
    <li class="listitem">
     <p>
      Make the new directory known to
      <code class="literal">
       InnoDB
      </code>
      .
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         If moving individual
         <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
          file-per-table
         </a>
         or
         <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
          general
              tablespace
         </a>
         files, add unknown directories to the
         <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
          <code class="literal">
           innodb_directories
          </code>
         </a>
         value.
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: circle; ">
          <li class="listitem">
           <p>
            The directories defined by the
            <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
             <code class="literal">
              innodb_data_home_dir
             </code>
            </a>
            ,
            <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
             <code class="literal">
              innodb_undo_directory
             </code>
            </a>
            ,
                  and
            <a class="link" href="server-system-variables.html#sysvar_datadir">
             <code class="literal">
              datadir
             </code>
            </a>
            variables
                  are automatically appended to the
            <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
             <code class="literal">
              innodb_directories
             </code>
            </a>
            argument value, so you need not specify these.
           </p>
          </li>
          <li class="listitem">
           <p>
            A file-per-table tablespace file can only be moved to
                  a directory with same name as the schema. For example,
                  if the
            <code class="literal">
             actor
            </code>
            table belongs to the
            <code class="literal">
             sakila
            </code>
            schema, then the
            <code class="filename">
             actor.ibd
            </code>
            data file can only be
                  moved to a directory named
            <code class="filename">
             sakila
            </code>
            .
           </p>
          </li>
          <li class="listitem">
           <p>
            General tablespace files cannot be moved to the data
                  directory or a subdirectory of the data directory.
           </p>
          </li>
         </ul>
        </div>
       </li>
       <li class="listitem">
        <p>
         If moving system tablespace files, undo tablespaces, or
              the data directory, update the
         <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
          <code class="literal">
           innodb_data_home_dir
          </code>
         </a>
         ,
         <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
          <code class="literal">
           innodb_undo_directory
          </code>
         </a>
         ,
              and
         <a class="link" href="server-system-variables.html#sysvar_datadir">
          <code class="literal">
           datadir
          </code>
         </a>
         settings, as
              necessary.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Restart the server.
     </p>
    </li>
   </ol>
  </div>
  <h5>
   <a name="idm46045165033760">
   </a>
   Usage Notes
  </h5>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Wildcard expressions cannot be used in the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
       <code class="literal">
        innodb_directories
       </code>
      </a>
      argument
          value.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
       <code class="literal">
        innodb_directories
       </code>
      </a>
      scan
          also traverses subdirectories of specified directories.
          Duplicate directories and subdirectories are discarded from
          the list of directories to be scanned.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
       <code class="literal">
        innodb_directories
       </code>
      </a>
      supports
          moving
      <code class="literal">
       InnoDB
      </code>
      tablespace files. Moving
          files that belong to a storage engine other than
      <code class="literal">
       InnoDB
      </code>
      is not supported. This restriction
          also applies when moving the entire data directory.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
       <code class="literal">
        innodb_directories
       </code>
      </a>
      supports
          renaming of tablespace files when moving files to a scanned
          directory. It also supports moving tablespaces files to other
          supported operating systems.
     </p>
    </li>
    <li class="listitem">
     <p>
      When moving tablespace files to a different operating system,
          ensure that tablespace file names do not include prohibited
          characters or characters with a special meaning on the
          destination system.
     </p>
    </li>
    <li class="listitem">
     <p>
      When moving a data directory from a Windows operating system
          to a Linux operating system, modify the binary log file paths
          in the binary log index file to use backward slashes instead
          of forward slashes. By default, the binary log index file has
          the same base name as the binary log file, with the extension
          '
      <code class="filename">
       .index
      </code>
      '. The location of the binary log
          index file is defined by
      <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
       <code class="option">
        --log-bin
       </code>
      </a>
      . The default location
          is the data directory.
     </p>
    </li>
    <li class="listitem">
     <p>
      If moving tablespace files to a different operating system
          introduces cross-platform replication, it is the database
          administrator's responsibility to ensure proper replication of
          DDL statements that contain platform-specific directories.
          Statements that permit specifying directories include
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE ...
          DATA DIRECTORY
       </code>
      </a>
      and
      <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
       <code class="literal">
        CREATE
          TABLESPACE ... ADD DATAFILE
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Add the directories of file-per-table and general tablespaces
          created with an absolute path or in a location outside of the
          data directory to the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
       <code class="literal">
        innodb_directories
       </code>
      </a>
      setting.
          Otherwise,
      <code class="literal">
       InnoDB
      </code>
      is not able to locate the
          files during recovery. For related information, see
      <a class="xref" href="innodb-recovery.html#innodb-recovery-tablespace-discovery" title="Tablespace Discovery During Crash Recovery">
       Tablespace Discovery During Crash Recovery
      </a>
      .
     </p>
     <p>
      To view tablespace file locations, query the Information
          Schema
      <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
       <code class="literal">
        FILES
       </code>
      </a>
      table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa98761713"><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> TABLESPACE_NAME<span class="token punctuation">,</span> FILE_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES \G</code></pre>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
