This type of engine allows you to access data remotely without the use of a replication or cluster implementation. To use the Federated Storage Engine in MySQL, you must create a server definition in the MySQL database.
In this tutorial, we will discuss how to use the CREATE SERVER statement in MySQL to create an entry in the servers table of the MySQL database.
Statement Syntax
The syntax for the server definition is as shown below:
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)
option: {
HOST character-literal
| DATABASE character-literal
| USER character-literal
| PASSWORD character-literal
| SOCKET character-literal
| OWNER character-literal
| PORT numeric-literal
}
The server_name specifies the name of the server to create in the table. This should be a unique value that does not exist in the table. Ensure that the name follows the MySQL naming rules. As a convention, the server_name should be specified as a quoted string literal.
The wrapper_name allows you to set the identifier for the server.
Once you run the CREATE SERVER command, the query creates an entry in the mysql.servers table allowing you to use the server to create a FEDERATED table.
Practical Example
The following example shows how to use the CREATE SERVER command to create a server in the mysql.servers table.
foreign data wrapper mysql
options (user 'root',
host '192.168.0.100',
database 'sakila');
The statement above should create an entry ‘fd_server’ in the servers table. We can confirm this by querying the table as shown:
The query above should return the output as:
| Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
+-------------+---------------+--------+----------+----------+------+--------+---------+-------+
| fd_server | 192.168.0.100 | sakila | root | | 0 | | mysql | |
+-------------+---------------+--------+----------+----------+------+--------+---------+-------+
1 row in set (0.00 sec)
Finally, we can create a federated table using the created server as shown:
Conclusion
In this post, you learned how to use the CREATE SERVER command to create an entry in the mysql.server table allowing you to create FEDERATED tables.
