1

From the MySQL documentation on creating tables for to access data on a remote MySQL server using the FEDERATED storage engine:

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.

I have a lot of existing tables that I want to federate against another server. Is there a way to make this process quicker? Or I must go, table-by-table, copying and editing the definitions and creating the federated version of each table locally?

I found the command to extract the existing table definition from the remote server:

mysql> SHOW CREATE TABLE wp_options;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                    |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=999648 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So it seems like all I have to do is modify the ENGINE and add a CONNECTION definition... still, having to create federated tables manually for each of the existing tables is a time-consuming task.

Does MySQL have a way to do this automatically for a group of tables, or all tables?

1 Answer 1

0

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.

6
  • This is what I call an answer ! Thank you so much. Do you know if I can federate on a local Mysql to RDS ? I know RDS does not support FEDERATED, but can you make calls from a loca federated table to a non-federated RDS table ? Oct 24, 2014 at 5:37
  • @PythPhytho yes, you can. I have a daily report runner on a "local" MySQL server that uses several federated connections to an RDS server to fill in some data from a different system. With a FEDERATED table, the remote server (where the data lives) has absolutely no clue that you're using it this way, because the federated storage engine makes an ordinary client connection to the remote system, and it writes and sends ordinary SQL queries to the far end, to get its work done. Oct 24, 2014 at 11:10
  • Thank you. I am new to federated tables, and was afraid i could not implement a solution based on mysql federated / rds. Mysql takes over so much CPU on regular machines. It is a pity that amazon can't offer the federated engine on RDS. Oct 24, 2014 at 12:09
  • @PythPhytho yes, the lack of FEDERATED support is one of several drawbacks to RDS, a decent platform overall if you can accept the limitations in functionality that are arguably necessary for a "managed" MySQL offering. Here's an extremely valuable reference that will help avoid some potential gotchas with the federated engine. It's an old article, but federated hasn't changed much in "official" MySQL (it was rewritten as "FederatedX" in MariaDB (and Percona?), but many of these principles still hold). onlamp.com/pub/a/databases/2006/08/10/… Oct 24, 2014 at 13:57
  • Version Caveat: If you are writing to the federated table, there is a fairly serious bug in MySQL versions prior to 5.5.36, 5.6.16, and 5.7.4, that could crash the local server due to an invalid pointer if an UPDATE ... JOIN joined a federated table to another table and exactly one row matched on the join. If 0 rows or more than 1 row matched, the issue did not occur, because the optimizer came up with a different query plan. This was fixed in the versions listed, above. If you are only reading from the federated side, this issue has no impact. Oct 24, 2014 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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