MySQL Federated Tables: The Missing Manual
Pages: 1, 2, 3, 4, 5

Creating Federated Tables

Your first task is to create two simple tables on server1, one with a key and one without. The reason for these two tables will become apparent later.

create database if not exists test;
use test;

drop table if exists tkey;
create table tkey ( 
    id int not null auto_increment primary key, 
    word varchar(30) not null, 
    key (word)
    );

drop table if exists tnokey;
create table tnokey ( 
    id int not null primary key, 
    word varchar(30) not null
    );

load data local infile '/usr/share/dict/words'
into table tkey (word);

insert into tnokey select * from tkey;

select count(*) from tkey;
select count(*) from tnokey;

Save this script to fed1.mysql and feed it to server1:

$ ~/server1/use.sh -t < fed1.mysql
+----------+
| count(*) |
+----------+
|   518584 |
+----------+
+----------+
| count(*) |
+----------+
|   518584 |
+----------+

(These numbers may be different in your system, depending on the size of your dictionary.)

On server2, create two federated tables that point to the ones just built on server1.

drop database if exists test;
create database if not exists test;
use test;

drop table if exists tkey_fed;
create table tkey_fed ( 
    id int not null auto_increment primary key, 
    word varchar(30) not null, 
    key (word)
)
engine=federated 
connection='mysql://server2usr:datacharmer@127.0.0.1:5001/test/tkey';

drop table if exists tnokey_fed;
create table tnokey_fed ( 
    id int not null primary key, 
    word varchar(30) not null
)
engine=federated 
connection='mysql://server2usr:datacharmer@127.0.0.1:5001/test/tnokey';

Save this to fed2.mysql and load it:

$ ~/server2/use.sh -t < fed2.mysql

If you don't get any output, your federated tables were created without problems. If you get any errors, go back to the previous section and make sure that you have done everything, including creating the appropriate user on server1.

Pages: 1, 2, 3, 4, 5

Next Page