MySQL in a Nutshell, 2nd Edition

Name

CREATE SERVER

Synopsis

CREATE SERVER server
FOREIGN DATA WRAPPER wrapper
OPTIONS (  
  { DATABASE database, |
    HOST host, |
    USER user, |
    PASSWORD password, |
    SOCKET socket, |
    OWNER character, |
    PORT port }
 )

Use this SQL statement with the FEDERATED storage engine to set the connection parameters. The values given are stored in the mysql database, in the server table, in a new row. The server name given cannot exceed 63 characters, and it’s not case-sensitive. The only wrapper permitted at this time is mysql. Options are given in a comma-separated list. You’re not required to specify all options listed in the example syntax. If an option is not given, the default will be an empty string. To change options after a server has been created, use the ALTER SERVER statement, described earlier in this chapter. For option values, character or numeric literals (UTF-8; maximum length of 64 characters) must be given. This statement was introduced in version 5.1.15 of MySQL and requires SUPER privileges.

The host may be a hostname or an IP address. The username and password given are those that are required for accessing the server. Provide either the name of the socket or the port to use for connecting to the server. The owner is the filesystem username to use for accessing the server:

CREATE SERVER server1
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'russell', HOST 'dyerhouse.com', DATABASE 'db1', PORT 3306, 
   OWNER 'root');

CREATE TABLE table1 (col1 INT) 
ENGINE = FEDERATED CONNECTION='server1';