MySQL in a Nutshell, 2nd Edition

Name

CREATE SERVER

Synopsis

CREATE SERVER 'server'
   FOREIGN DATA WRAPPER mysql
   OPTIONS 
    ({ HOST host
     | DATABASE database
     | USER user
     | PASSWORD password
     | SOCKET socket
     | OWNER owner
     | PORT port_number }, ...)

This statement creates a server for use by the FEDERATED storage engine. The server created is registered in the server table in the mysql database. The server name given cannot exceed 63 characters and is case-insensitive. The only acceptable wrapper name is mysql. Multiple options may be given, separated by commas. The PORT option requires a numeric literal, whereas the other options require character literals. So don’t put the port number within quotes. SUPER privilege is required to be able to use this statement. Here is an example of this statement:

CREATE SERVER testing
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'test_user', HOST '10.1.1.100', 
DATABASE 'test', PORT 3307);

SELECT * FROM mysql.servers 
WHERE Server_name = 'testing' \G
 
*************************** 1. row ***************************
Server_name: testing
       Host: 10.1.1.100
         Db: test
   Username: test_user
   Password: 
       Port: 3307
     Socket: 
    Wrapper: mysql
      Owner: 

CREATE TABLE table1 (col_id INT, col_1 VARCHAR(25)) 
ENGINE=FEDERATED CONNECTION='testing';

A server created with this statement can be altered with the ALTER SERVER statement. Once created, servers can be accessed by setting the ENGINE clause in either the CREATE TABLE statement or the same clause of the ALTER TABLE statement for existing tables.