To use the first method, you must specify the
CONNECTION
string after the engine type in a
CREATE TABLE
statement. For
example:
CREATE TABLE federated_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) NOT NULL DEFAULT '0', PRIMARY KEY (id), INDEX name (name), INDEX other_key (other) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
CONNECTION
replaces the
COMMENT
used in some previous versions of
MySQL.
The CONNECTION
string contains the
information required to connect to the remote server containing
the table that will be used to physically store the data. The
connection string specifies the server name, login credentials,
port number and database/table information. In the example, the
remote table is on the server remote_host
,
using port 9306. The name and port number should match the host
name (or IP address) and port number of the remote MySQL server
instance you want to use as your remote table.
The format the connection string is as follows:
scheme
://user_name
[:password
]@host_name
[:port_num
]/db_name
/tbl_name
Where:
scheme
— is a recognized
connection protocol. Only mysql
is
supported as the scheme
value at
this point.
user_name
— the user name
for the connection. This user must have been created on the
remote server, and must have suitable privileges to perform
the required actions (SELECT
,
INSERT
,
UPDATE
, and so forth) on the
remote table.
password
— (optional) the
corresponding password for
user_name
.
host_name
— the host name
or IP address of the remote server.
port_num
— (optional) the
port number for the remote server. The default is 3306.
db_name
— the name of the
database holding the remote table.
tbl_name
— the name of the
remote table. The name of the local and the remote table do
not have to match.
Sample connection strings:
CONNECTION='mysql://username:password@hostname:port/database/tablename' CONNECTION='mysql://username@hostname/database/tablename' CONNECTION='mysql://username:password@hostname/database/tablename'
User Comments
Add your own comment.