The FederatedX storage engine was first released in MariaDB 5.1.
The FederatedX storage engine is a fork of MySQL's Federated storage engine, which is no longer being developed by Oracle. The original purpose of FederatedX was to keep this storage engine's development progressing-- to both add new features as well as fix old bugs.
Since MariaDB 10.0, the CONNECT storage engine also allows access to a remote database via MySQL or ODBC connection (table types: MYSQL, ODBC). However, in the current implementation there are several limitations.
The FederatedX Storage Engine is a storage engine that works with both MariaDB and MySQL. Where other storage engines are built as interfaces to lower-level file-based data stores, FederatedX uses libmysql to talk to the data source, the data source being a remote RDBMS. Currently, since FederatedX only uses libmysql, it can only talk to another MySQL RDBMS. The plan is of course to be able to use other RDBMS systems as a data source. There is an existing project Federated ODBC which was able to use PostgreSQL as a remote data source, and it is this type of functionality which will be brought to FederatedX in subsequent versions.
The history of FederatedX is derived from the History of Federated. Cisco needed a MySQL storage engine that would allow them to consolidate remote tables on some sort of routing device, being able to interact with these remote tables as if they were local to the device, but not actually on the device, since the routing device had only so much storage space. The first prototype of the Federated Storage Engine was developed by JD (need to check on this- Brian Aker can verify) using the HANDLER interface. Brian handed the code to Patrick Galbraith and explained how it needed to work, and with Brian and Monty's tutelage and Patrick had a working Federated Storage Engine with MySQL 5.0. Eventually, Federated was released to the public in a MySQL 5.0 release.
When MySQL 5.1 became the production release of MySQL, Federated had more features and enhancements added to it, namely:
In MariaDB 10.0.2 FederatedX got support for assisted table discovery.
Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing INSTALL SONAME
or INSTALL PLUGIN
. For example:
INSTALL SONAME 'ha_federatedx';
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the --plugin-load
or the --plugin-load-add
options. This can be specified as a command-line argument to mysqld
or it can be specified in a relevant server option group in an option file. For example:
[mariadb] ... plugin_load_add = ha_federatedx
You can uninstall the plugin dynamically by executing UNINSTALL SONAME
or UNINSTALL PLUGIN
. For example:
UNINSTALL SONAME 'ha_federatedx';
If you installed the plugin by providing the --plugin-load
or the --plugin-load-add
options in a relevant server option group in an option file, then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.
Every storage engine has to implement derived standard handler class API methods for a storage engine to work. FederatedX is no different in that regard. The big difference is that FederatedX needs to implement these handler methods in such as to construct SQL statements to run on the remote server and if there is a result set, process that result set into the internal handler format so that the result is returned to the user.
Normal database files are local and as such: You create a table called 'users', a file such as 'users.MYD' is created. A handler reads, inserts, deletes, updates data in this file. The data is stored in particular format, so to read, that data has to be parsed into fields, to write, fields have to be stored in this format to write to this data file.
With the FederatedX storage engine, there will be no local files for each table's data (such as .MYD). A foreign database will store the data that would normally be in this file. This will necessitate the use of MySQL client API to read, delete, update, insert this data. The data will have to be retrieve via an SQL call "SELECT * FROM users
". Then, to read this data, it will have to be retrieved via mysql_fetch_row
one row at a time, then converted from the column in this select into the format that the handler expects.
The basic functionality of how FederatedX works is:
The create table will simply create the .frm file, and within the CREATE TABLE
SQL statement, there SHALL be any of the following :
connection=scheme://username:password@hostname:port/database/tablename connection=scheme://username@hostname/database/tablename connection=scheme://username:password@hostname/database/tablename connection=scheme://username:password@hostname/database/tablename
Or using the syntax introduced in MySQL versions 5.1 for a Federated server (SQL/MED Spec xxxx)
connection="connection_one" connection="connection_one/table_foo"
An example of a connect string specifying all the connection parameters would be:
connection=mysql://username:password@hostname:port/database/tablename
Or, using a Federated server, first a server is created:
create server 'server_one' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db1', USER 'root', PASSWORD '', PORT 3306, SOCKET '', OWNER 'root');
Then the FederatedX table is created specifying the newly created Federated server:
CREATE TABLE federatedx.t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='server_one';
(Note that in MariaDB, the original Federated storage engine is replaced with the new FederatedX storage engine. And for backward compatibility, the old name "FEDERATED" is used in create table. So in MariaDB, the engine type should be given as "FEDERATED" without an extra "X", not "FEDERATEDX").
The equivalent of above, if done specifying all the connection parameters
CONNECTION="mysql://[email protected]:3306/db1/t1"
You can also change the server to point to a new schema:
ALTER SERVER 'server_one' options(DATABASE 'db2');
All subsequent calls to any FederatedX table using the 'server_one' will now be against db2.t1! Guess what? You no longer have to perform an alter table in order to point one or more FederatedX tables to a new server!
This connection="connection string"
is necessary for the handler to be able to connect to the foreign server, either by URL, or by server name.
One way to see how the FederatedX storage engine works is to compile a debug build of MariaDB and turn on a trace log. Using a two column table, with one record, the following SQL statements shown below, can be analyzed for what internal methods they result in being called.
If the query is for instance "SELECT * FROM foo
", then the primary methods you would see with debug turned on would be first:
ha_federatedx::info ha_federatedx::scan_time: ha_federatedx::rnd_init: share->select_query SELECT * FROM foo ha_federatedx::extra
Then for every row of data retrieved from the foreign database in the result set:
ha_federatedx::rnd_next ha_federatedx::convert_row_to_internal_format ha_federatedx::rnd_next
After all the rows of data that were retrieved, you would see:
ha_federatedx::rnd_end ha_federatedx::extra ha_federatedx::reset
If the query was "INSERT INTO foo (id, ts) VALUES (2, now());
", the trace would be:
ha_federatedx::write_row ha_federatedx::reset
If the query was "UPDATE foo SET ts = now() WHERE id = 1;
", the resultant trace would be:
ha_federatedx::index_init ha_federatedx::index_read ha_federatedx::index_read_idx ha_federatedx::rnd_next ha_federatedx::convert_row_to_internal_format ha_federatedx::update_row ha_federatedx::extra ha_federatedx::extra ha_federatedx::extra ha_federatedx::external_lock ha_federatedx::reset
To use this handler, it's very simple. You must have two databases running, either both on the same host, or on different hosts.
First, on the foreign database you create a table, for example:
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), KEY name (name), KEY other_key (other)) DEFAULT CHARSET=latin1;
Then, on the server that will be connecting to the foreign host (client), you create a federated table without specifying the table structure:
CREATE TABLE test_table ENGINE=FEDERATED CONNECTION='mysql://[email protected]:9306/federatedx/test_federatedx';
Notice the "ENGINE" and "CONNECTION" fields? This is where you respectively set the engine type, "FEDERATED" and foreign host information, this being the database your 'client' database will connect to and use as the "data file". Obviously, the foreign database is running on port 9306, so you want to start up your other database so that it is indeed on port 9306, and your FederatedX database on a port other than that. In my setup, I use port 5554 for FederatedX, and port 5555 for the foreign database.
Alternatively (or if you're using MariaDB before version 10.0.2) you specify the federated table structure explicitly:
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), KEY name (name), KEY other_key (other)) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://[email protected]:9306/federatedx/test_federatedx';
In this case the table structure must match exactly the table on the foreign server.
When developing this handler, I compiled the FederatedX database with debugging:
./configure --with-federatedx-storage-engine \ --prefix=/home/mysql/mysql-build/federatedx/ --with-debug
Once compiled, I did a 'make install' (not for the purpose of installing the binary, but to install all the files the binary expects to see in the directory I specified in the build with
--prefix=/home/code-dev/maria
Then, I started the foreign server:
/usr/local/mysql/bin/mysqld_safe \ --user=mysql --log=/tmp/mysqld.5555.log -P 5555
Then, I went back to the directory containing the newly compiled mysqld <builddir>/sql/
, started up gdb:
gdb ./mysqld
Then, within the (gdb) prompt:
(gdb) run --gdb --port=5554 --socket=/tmp/mysqld.5554 --skip-innodb --debug
Next, I open several windows for each:
I would create a table on the client to the foreign server on port 5555, and then to the FederatedX server on port 5554. At this point, I would run whatever queries I wanted to on the FederatedX server, just always remembering that whatever changes I wanted to make on the table, or if I created new tables, that I would have to do that on the foreign server.
Another thing to look for is 'show variables' to show you that you have support for FederatedX handler support:
show variables like '%federat%'
and:
show storage engines;
Both should display the federatedx storage handler.
A federated server is a way to have a foreign data source defined-- with all connection parameters-- so that you don't have to specify explicitly the connection parameters in a string.
For instance, say if you wanted to create a table, t1, that you would specify with
connection="mysql://[email protected]/first_db/t1"
You could instead create this with a server:
create server 'server_one' foreign data wrapper 'mysql' options (HOST '192.168.1.123', DATABASE 'first_db', USER 'patg', PASSWORD '', PORT 3306, SOCKET '', OWNER 'root');
You could now instead specify the server instead of the full URL connection string
connect="server_one"
FederatedX from a user point of view is the same for the most part. What is different with FederatedX and Federated is the following:
FederatedX is part of MariaDB 5.1 and later. MariaDB merged with the latest FederatedX when there is a need to get a bug fixed. You can get the latest code/follow/participate in the project from the FederatedX home page.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/about-federatedx/