MySQL – Automate Creation of Multiple FEDERATED Tables

MySQL

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?

Best Answer

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.

Related Question