<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="federated-create-server">
      </a>
      18.8.2.2 Creating a FEDERATED Table Using CREATE SERVER
     </h4>
    </div>
   </div>
  </div>
  <p>
   If you are creating a number of
   <code class="literal">
    FEDERATED
   </code>
   tables on the same server, or if you want to simplify the
        process of creating
   <code class="literal">
    FEDERATED
   </code>
   tables, you can
        use the
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE SERVER
    </code>
   </a>
   statement
        to define the server connection parameters, just as you would
        with the
   <code class="literal">
    CONNECTION
   </code>
   string.
  </p>
  <p>
   The format of the
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE SERVER
    </code>
   </a>
   statement is:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34086101"><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">CREATE</span> <span class="token keyword">SERVER</span>
<em class="replaceable">server_name</em>
<span class="token keyword">FOREIGN</span> <span class="token keyword">DATA</span> <span class="token keyword">WRAPPER</span> <em class="replaceable">wrapper_name</em>
<span class="token keyword">OPTIONS</span> <span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">option</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">option</em></span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   The
   <em class="replaceable">
    <code>
     server_name
    </code>
   </em>
   is used in the
        connection string when creating a new
   <code class="literal">
    FEDERATED
   </code>
   table.
  </p>
  <p>
   For example, to create a server connection identical to the
   <code class="literal">
    CONNECTION
   </code>
   string:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa64174702"><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">CONNECTION</span><span class="token operator">=</span><span class="token string">'mysql://fed_user@remote_host:9306/federated/test_table'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You would use the following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28990826"><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">CREATE</span> <span class="token keyword">SERVER</span> fedlink
<span class="token keyword">FOREIGN</span> <span class="token keyword">DATA</span> <span class="token keyword">WRAPPER</span> mysql
<span class="token keyword">OPTIONS</span> <span class="token punctuation">(</span><span class="token keyword">USER</span> <span class="token string">'fed_user'</span><span class="token punctuation">,</span> <span class="token keyword">HOST</span> <span class="token string">'remote_host'</span><span class="token punctuation">,</span> <span class="token keyword">PORT</span> <span class="token number">9306</span><span class="token punctuation">,</span> <span class="token keyword">DATABASE</span> <span class="token string">'federated'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To create a
   <code class="literal">
    FEDERATED
   </code>
   table that uses this
        connection, you still use the
   <code class="literal">
    CONNECTION
   </code>
   keyword, but specify the name you used in the
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE SERVER
    </code>
   </a>
   statement.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62045270"><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">CREATE</span> <span class="token keyword">TABLE</span> test_table <span class="token punctuation">(</span>
    id     <span class="token datatype">INT</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    <span class="token keyword">name</span>   <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">,</span>
    other  <span class="token datatype">INT</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span>  <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> <span class="token keyword">name</span> <span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> other_key <span class="token punctuation">(</span>other<span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">ENGINE</span><span class="token operator">=</span>FEDERATED
<span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4
<span class="token keyword">CONNECTION</span><span class="token operator">=</span><span class="token string">'fedlink/test_table'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The connection name in this example contains the name of the
        connection (
   <code class="literal">
    fedlink
   </code>
   ) and the name of the
        table (
   <code class="literal">
    test_table
   </code>
   ) to link to, separated by a
        slash. If you specify only the connection name without a table
        name, the table name of the local table is used instead.
  </p>
  <p>
   For more information on
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE
        SERVER
    </code>
   </a>
   , see
   <a class="xref" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    Section 15.1.18, “CREATE SERVER Statement”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE SERVER
    </code>
   </a>
   statement
        accepts the same arguments as the
   <code class="literal">
    CONNECTION
   </code>
   string. The
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE SERVER
    </code>
   </a>
   statement updates the rows in the
   <code class="literal">
    mysql.servers
   </code>
   table. See the following table
        for information on the correspondence between parameters in a
        connection string, options in the
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE
        SERVER
    </code>
   </a>
   statement, and the columns in the
   <code class="literal">
    mysql.servers
   </code>
   table. For reference, the
        format of the
   <code class="literal">
    CONNECTION
   </code>
   string is as
        follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa15090453"><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"><em class="replaceable">scheme</em>://<em class="replaceable">user_name</em>[:<em class="replaceable">password</em>]@<em class="replaceable">host_name</em>[:<em class="replaceable">port_num</em>]/<em class="replaceable">db_name</em>/<em class="replaceable">tbl_name</em></code></pre>
  </div>
  <div class="informaltable">
   <table summary="The correspondence between parameters in a connection string, options in the CREATE SERVER statement, and the columns in the mysql.servers table.">
    <colgroup>
     <col style="width: 25%"/>
     <col style="width: 25%"/>
     <col style="width: 25%"/>
     <col style="width: 25%"/>
    </colgroup>
    <thead>
     <tr>
      <th scope="col">
       Description
      </th>
      <th scope="col">
       <code class="literal">
        CONNECTION
       </code>
       string
      </th>
      <th scope="col">
       <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
        <code class="literal">
         CREATE SERVER
        </code>
       </a>
       option
      </th>
      <th scope="col">
       <code class="literal">
        mysql.servers
       </code>
       column
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th scope="row">
       Connection scheme
      </th>
      <td>
       <em class="replaceable">
        <code>
         scheme
        </code>
       </em>
      </td>
      <td>
       <code class="literal">
        wrapper_name
       </code>
      </td>
      <td>
       <code class="literal">
        Wrapper
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Remote user
      </th>
      <td>
       <em class="replaceable">
        <code>
         user_name
        </code>
       </em>
      </td>
      <td>
       <code class="literal">
        USER
       </code>
      </td>
      <td>
       <code class="literal">
        Username
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Remote password
      </th>
      <td>
       <em class="replaceable">
        <code>
         password
        </code>
       </em>
      </td>
      <td>
       <code class="literal">
        PASSWORD
       </code>
      </td>
      <td>
       <code class="literal">
        Password
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Remote host
      </th>
      <td>
       <em class="replaceable">
        <code>
         host_name
        </code>
       </em>
      </td>
      <td>
       <code class="literal">
        HOST
       </code>
      </td>
      <td>
       <code class="literal">
        Host
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Remote port
      </th>
      <td>
       <em class="replaceable">
        <code>
         port_num
        </code>
       </em>
      </td>
      <td>
       <code class="literal">
        PORT
       </code>
      </td>
      <td>
       <code class="literal">
        Port
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       Remote database
      </th>
      <td>
       <em class="replaceable">
        <code>
         db_name
        </code>
       </em>
      </td>
      <td>
       <code class="literal">
        DATABASE
       </code>
      </td>
      <td>
       <code class="literal">
        Db
       </code>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
 </div>
 <br/>
</div>
