1

In my.ini I added

[mysqld]
federated

When I type in a shell show engines; I get this result: enter image description here

But when I try to change one of this tables which are on innodb engine to federated I get an error. When I try to create/alter table with sqlyog there is no federated engine

enter image description here

Can anyone help me? And if I change innodb/myisam table with data to federated table, will I lose any data? I dont use transactions in table

4
  • No offense in asking this, but do you actually know what the federated engine is? I recommend you read the MySQL manual about it. You don't convert a table to federated, you just create it. – tombom Oct 23 '14 at 8:25
  • @tombom Have 2 mysql databases on different hosts, want trigger after insert data to one database it inserted to another . I'm new in mysql , in sql server I can create linked server and do it . But how to do in mysql ? Both databases have similar table. Both tables are in innodb engine and I dont want to lose any data , that why I try change engine to ferderated – GeoVIP Oct 23 '14 at 8:30
  • Do you want to do this for all tables in a database? If yes, what you actually want is called replication. – tombom Oct 23 '14 at 8:32
  • @tombom I know about replication, but I want it only on one table not more, any advise? – GeoVIP Oct 23 '14 at 8:32
4

An example from the manual:

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

CREATE TABLE test_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=MyISAM
DEFAULT CHARSET=latin1;

The example uses a MyISAM table, but the table could use any storage engine.

Next, create a FEDERATED table on the local server for accessing the remote table:

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';

(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)

The basic structure of this table should match that of the remote table, except that the ENGINE table option should be FEDERATED and the CONNECTION table option is a connection string that indicates to the FEDERATED engine how to connect to the remote server.

9
  • 1
    @GeoVIP No, you can't. The concept behind federated is still not clear, isn't it? The federated engine is like a link to another table. You select something from the local federated table, the data comes from the remote whatever-engine-table. You can't use triggers to write data to a remote server. – tombom Oct 23 '14 at 8:40
  • I have answer here stackoverflow.com/questions/26503342/… It will not work ? – GeoVIP Oct 23 '14 at 8:45
  • Oh, yes, that's actually a fancy trick. But you have to create the table the other way round than I described in my answer. The federated table on the localhost, and the real table on the remote host, where you want to write data to. Nice. – tombom Oct 23 '14 at 8:53
  • I haven't used the federated engine much, cause from my experience it's painfully slow. But maybe just for my use case. – tombom Oct 23 '14 at 8:55
  • why slow ? federated engine not support index ? – GeoVIP Oct 23 '14 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.