MySQL : utilisation tables Federated(x)

Les tables "FEDERATED" sont des tables "distantes" sur un système différent.
Les tables de type "FEDERATED" permettent la communication (facile) entre deux bases MySQL (et même non-MySQL, par exemple MariaDB).
Ce plugin est relativement insensible aux versions des bases utilisées.
Remarque : pour MariaDB l'utilisation du plugin "ha_federated.so" est à déconseiller car ce plugin n'est plus maintenu ... mais remplacé par "ha_federatedx.so" qui, lui, est toujours maintenu et a un fonctionnement compatible. Même avec le plugin "federatedx" il faut ajouter "federated" dans votre fichier de configuration.
Pour MariaDB le "nec plus ultra" est d'utiliser un nouveau plugin "connect" dont je vous proposerais un test prochainement.

Un petit exemple d'utilisation de "federated" :

Je dispose d'une machine "A" qui gère les tables utilisées par NtopNG, je ne veux pas laisser dans cette machine un trop gros volume de donnnées car NtopNG est assez consommateur d'espace et j'ai limité a 30 jours la rétention des données.
Je voudrais disposer d'un historique plus "long" sur une machine "B" pour pouvoir m'amuser à faire des analyses à plus long terme.

La gestion du transfert des données de A vers B m'a posé quelques problèmes, car les solutions "classiques" ne sont pas faciles à gérer :

Extraction dans une table "tampon" des données de la dernière semaine,
Export de la table tampon par mysqldump,
Transfert du ficher export
Import dans une table tampon
Intégration dans la table définitive.

Les rangs de la table principale sont identifiés par un ID en auto-incrément et il est donc aisé de repérer ceux qui manquent dans la machine "B" et c'est là que se profile l'utilisation d'une table "FEDERATED" Mysql.
En effet il suffit de déclarer dans la machine "B" une table identique à la table principale de "A" avec "engine=FEDERATED" pour accéder de "B" vers "A". La création est simple et toutes les opérations se font sur la machine "B". Il faut quand même vérifier que "monuser" a bien accès à la base "A" depuis l'adresse de "B" :

Créer un "serveur" :

create server xxxxxxx
foreign data wrapper mysql
options (USER 'monuser', PASSWORD 'monpassword', HOST '192.168.x.y', DATABASE 'mabaseA');
Créer une "table" FEDERATED (IDX = PRIMARY KEY) :

create table table_distante
( ......
KEY (IDX),
....
) engine=FEDERATED connection='xxxxxxx/table_sur_serveur_A';

Ensuite récupérer les données sur B est hyper simple :

select max(idx) into @IDMAX from table_sur_B;

insert into table_sur_B select * from table_distante
where idx > @IDMAX;

commit;

Et en plus c'est assez rapide comme le montre la trace suivante :

--------------
select max(idx) into @IDMAX from table_sur_B,
--------------

Query OK, 1 row affected (0,03 sec)

--------------
select @IDMAX
--------------

+----------+
| @IDMAX |
+----------+
| 12775832 |
+----------+
1 row in set (0,00 sec)

--------------
insert into table_sur_B select * from table_distante
where idx > @IDMAX
--------------

Query OK, 532917 rows affected (7,30 sec)
Records: 532917 Duplicates: 0 Warnings: 0

Plus de 530 000 rangs en 7,3 secondes ... c'est bien plus rapide que la manip "old style" et surtout bien plus simple !
La base distante est ici une base Mysql mais cela marche aussi avec d'autres bases ... Oracle, Postgres ... selon les modules disponibles.

Attention toutefois, un bête "select" ne passant pas par un index entraînera le transport de toute la table en local (temporaire) et peut durer assez longtemps et être très consommateur de ressources (disque et mémoire), donc éviter comme la peste les ordres n'utilisant pas correctement un index, mon exemple utilise la Primary Key et est donc très rapide.

Version imprimable