You should not put any faith in triggers when it comes to federated tables. You are better off creating a temporary trigger on the real MyISAM table on the source side.

Picture this:

On Server S1 - T1 is MyISAM table - T2 is FEDERATED table to T1 over on S2

Do the following:

• Create the Trigger on T1 to insert a row into T2
• Insert Data into T1
• Drop the Trigger

ALTERNATIVE

You would be way better off using MySQL Replication as follows:

• S1 is the Master
• S2 is the Slave
• Set replicate-do-table=db1.T1 in my.cnf on S2

This would automatically do all DML against that table provided you replicate to the same named table.

There's not an internal way to do this, but it's easy enough to automate with a script, like the following:

#!/usr/bin/env perl
use strict;
use warnings;
use DBI;

my $sleep_between = 1; # artificial delay; set to zero to remove

my $origin = 'origin_server_hostname';
my $oport = 3306; # origin server port
my $ou = 'origin_server_username';
my $op = 'origin_server_password';

my $fu = $ou; # or change, if federation needs a different user
my $fp = $op; # or change, if federation needs a different user

my $target = 'target_server_hostname';
my $tport = 3306; # target server port
my $tu = 'target_server_username';
my $tp = 'target_server_password';

warn "connecting to origin server";
my $odbh = DBI->connect("DBI:mysql:information_schema;host=$origin;port=$oport","$ou","$op",{RaiseError => 1, mysql_auto_reconnect => 0});
warn "connecting to target server";
my $tdbh = DBI->connect("DBI:mysql:information_schema;host=$target;port=$tport","$tu","$tp",{RaiseError => 1, mysql_auto_reconnect => 0});

warn "fetching origin tables...";
# change the query to select only the tables you want to federate; existing tables won't be overwritten
my $otables = $odbh->selectall_arrayref(qq{
        SELECT table_name, table_schema
          FROM information_schema.tables
         WHERE table_type = 'BASE TABLE'
           AND table_schema NOT IN ('mysql','information_schema','performance_schema')
},{Slice => {}}
);

my $prev_db = '';
foreach (@$otables)
{
        my ($os,$ot) = ($_->{table_schema}, $_->{table_name});
        my ($ts,$tt) = ($os,$ot);

        my $sct = $odbh->selectrow_arrayref(qq{SHOW CREATE TABLE `$os`.`$ot`})->[1];
        $sct =~ s/^CREATE\sTABLE\s`$ot`/CREATE TABLE IF NOT EXISTS `$ts`.`$tt`/ || die "regex replacement failed: $sct";
        $sct =~ s/\)\sENGINE=\S+\s/) ENGINE=FEDERATED / || die "regex replacement failed: $sct";
        my $fcs = "CONNECTION='mysql://$fu:$fp\@$origin:$oport/$os/$ot'";
        $sct .= ' ' . $fcs;
        warn "$sct\n\n";
        if($prev_db ne $ts)
        {
                my $dbdef = $odbh->selectrow_arrayref(qq{SHOW CREATE DATABASE IF NOT EXISTS `$os`})->[1];
                print STDERR "$dbdef ... ";
                print STDERR ($tdbh->do($dbdef) ? 'ok' : 'error') . "\n\n";
                $prev_db = $ts;
                sleep $sleep_between;
        }
        print STDERR "creating table ${ts}.${tt}... ";
        print STDERR ($tdbh->do($sct) ? 'ok' : 'error' ) . "\n\n";
        sleep $sleep_between;
}

$odbh->disconnect;
$tdbh->disconnect;

warn "done";
# # #

Save the script to a file, e.g. federation-automate.pl.

Modify the variables at the top to specify the origin (server with the base tables) and target (server where you want to create federated tables) hostnames, ports, usernames, and passwords. Modify the information_schema query to select fewer tables, if you don't want to federate the whole server.

All modern systems have Perl, but you need DBD::mysql for Perl, if you don't have it. On Ubuntu, that's...

$ sudo apt-get install libdbd-mysql-perl

Then run the script.

$ perl federation-automate.pl

It loads the table definitions from the origin, and rewrites the SHOW CREATE TABLE statements to build federated tables. It will also attempt to create the database containing each table, if the database doesn't already exist on the target server.

Note that federated tables don't actually support foreign key constraints, ROW_FORMAT=COMPRESSED, and some other common table options... however, table definitions containing these elements are still valid, and will be accepted, by the federated engine, so there's no need for this code to scrub them out.

Minor modifications to the code would allow you to change the database and/or table names on the target server if you didn't want them to be the same as the origin, for some reason.