• Sie befinden sich hier: Startseite > SQL-Backstube > MySQL FEDERATED ENGINE

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

20.03.2011: MySQL FEDERATED ENGINE

Eigentlich hat sie ja jeder MySQL-Nutzer - die FEDERATED ENGINE - nur ist sie per Default nicht aktiviert. Mit Hilfe der FEDERATED Engine kann auf Tabellen von "fernen" Datenbanken zugegriffen werden. Die "ferne" Datenbank kann aber auch eine zweite MySQL Version auf dem lokalen Rechner sein.

Getestet mit folgenden Datenbanken:

* MySQL 5.5.9
* MySQL 5.1.55

MySQL FEDERATED ENGINE aktivieren

Listing 1:

1. /* my.ini / my.cnf auf MySQL 5.5.9 */
2. [mysqld]
3. ...
4. # siehe auch (http://www.mysqlfanboy.com/2010/07/federated-tables/)
5. federated=On

Ob die FEDERATED Engine aktiv ist, läßt sich zum Beispiel mit SHOW ENGINES herausfinden.

Listing 2:

1. mysql> show engines;
2. +------------+---------+----------------------------------------------------------------+--------------+-----+------------+
3. | Engine | Support | Comment | Transactions | XA | Savepoints |
4. +------------+---------+----------------------------------------------------------------+--------------+-----+------------+
5. | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
6. | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
7. | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
8. | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
9. | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
10. | CSV | YES | CSV storage engine | NO | NO | NO |
11. | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
12. | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
13. +------------+---------+----------------------------------------------------------------+--------------+-----+------------+
14. 8 rows in set (0.03 sec)

Die FEDERATED Engine kann bei mir (trotz gegenteiliger Meinung) mit (=ON oder =On oder =1 ) in der Konfigurationsdatei aktiviert werden. Ein Neustart der MySQL Datenbank ist allerdings nötig, damit die Konfiguration neu eingelesen wird.

Tabellenstrukturen und Testdaten

Zuerst muss die Originaltabelle auf der "fernen" Datenbank (in meinem Fall die MySQL 5.1.55) mit einigen Daten erzeugt werden.

Listing 3:

1. /* mysql 5.1.55-community */
2. CREATE TABLE city(
3. id INT(11) NOT NULL AUTO_INCREMENT,
4. name VARCHAR(35) NOT NULL,
5. countrycode VARCHAR(3) NOT NULL,
6. district VARCHAR(20) NOT NULL,
7. population INT NOT NULL,
8. PRIMARY KEY (id)
9. ) ENGINE = INNODB;
11. INSERT INTO city VALUES
12. ( NULL, 'Name-1', 'N-1', 'District-1', 0),
13. ( NULL, 'Name-2', 'N-2', 'District-2', 0),
14. ( NULL, 'Name-3', 'N-3', 'District-3', 0),
15. ( NULL, 'Name-4', 'N-4', 'District-4', 0),
16. ( NULL, 'Name-5', 'N-5', 'District-5', 0);

Nun kann die "lokale" Tabelle mit der FEDERATED ENGINE erzeugt werden. Ganz wichtig ist, es muss eine absolut identische Tabellenstruktur sein. Daten brauchen "lokal" keine eingefügt werden. Die holen wir uns ja aus der "fernen" Datenbank.

Listing 4:

1. /* mysql 5.5.9 MySQL Community Server (GPL) */
2. CREATE TABLE city_remote(
3. id INT(11) NOT NULL AUTO_INCREMENT,
4. name VARCHAR(35) NOT NULL,
5. countrycode VARCHAR(3) NOT NULL,
6. district VARCHAR(20) NOT NULL,
7. population INT NOT NULL,
8. PRIMARY KEY (id)
9. ) ENGINE = FEDERATED
10. connection='mysql://username:passwort@localhost:3306/testdatenbank/city';
12. /* noch eine leere "lokale" city Tabelle die später gefüllt wird */
13. CREATE TABLE city(
14. id INT(11) NOT NULL AUTO_INCREMENT,
15. name VARCHAR(35) NOT NULL,
16. countrycode VARCHAR(3) NOT NULL,
17. district VARCHAR(20) NOT NULL,
18. population INT NOT NULL,
19. PRIMARY KEY (id)
20. ) ENGINE = INNODB;

Lesen der "fernen" Daten

Auf der MySQL 5.5.9 Datenbank führen wird nun folgende Abfrage aus.

Listing 5:

1. /* MySQL 5.5.9 */
2. mysql>SELECT * FROM city_remote;
3. ERROR 1430 (HY000): : 2003 : Can't connect to MySQL server on 'localhost' (10061)

So (Listing 5:) sieht es aus, wenn keine Verbindung zur Remotedatenbank hergestellt werden kann. Sei es weil sie "down" ist oder die Verbindungsdaten falsch sind. Und so sieht das Ganze aus, wenn es funktioniert..(Listing 6:)

Listing 6:

1. /* MySQL 5.5.9 */
2. mysql>SELECT * FROM city_remote;
3. +----+--------+-------------+------------+------------+
4. | id | name | countrycode | district | population |
5. +----+--------+-------------+------------+------------+
6. | 1 | Name-1 | N-1 | District-1 | 0 |
7. | 2 | Name-2 | N-2 | District-2 | 0 |
8. | 3 | Name-3 | N-3 | District-3 | 0 |
9. | 4 | Name-4 | N-4 | District-4 | 0 |
10. | 5 | Name-5 | N-5 | District-5 | 0 |
11. +----+--------+-------------+------------+------------+
12. 5 rows in set (1.01 sec)
14. /* MySQL 5.5.9 */
15. mysql>SELECT * FROM city_remote;
16. +----+--------+-------------+------------+------------+
17. | id | name | countrycode | district | population |
18. +----+--------+-------------+------------+------------+
19. | 1 | Name-1 | N-1 | District-1 | 0 |
20. | 2 | Name-2 | N-2 | District-2 | 0 |
21. | 3 | Name-3 | N-3 | District-3 | 0 |
22. | 4 | Name-4 | N-4 | District-4 | 0 |
23. | 5 | Name-5 | N-5 | District-5 | 0 |
24. +----+--------+-------------+------------+------------+
25. 5 rows in set (0.00 sec)

Der erste Select "dauert" relativ lang, der zweite identische Select ist schnell (ein Query Cache wird aber nicht laut Doku unterstützt).

EXPLAIN auf "ferne" Tabellen

Listing 7:

1. mysql> explain select * from city_remote;
2. +----+-------------+-------------+------+---------------+------+---------+------+------+-------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------------+------+---------------+------+---------+------+------+-------+
5. | 1 | SIMPLE | city_remote | ALL | NULL | NULL | NULL | NULL | 5 | |
6. +----+-------------+-------------+------+---------------+------+---------+------+------+-------+
7. 1 row in set (0.00 sec)

Wie man sieht, ist auch ein EXPLAIN auf die ferne Datenbank möglich. Dies ist eine extrem praktische Möglichkeit bei einer Fernbetreuung.

Datenübernahme aus "ferner" Datenbank zur "lokalen" Datenbank

Listing 8:

1. INSERT INTO city
2. SELECT * FROM city_remote;
3. Query OK, 5 rows affected (0.00 sec)
4. Records: 5 Duplicates: 0 Warnings: 0

Mit dieser doch sehr einfachen Methode haben wir uns die Daten aus der "fernen" Datenbank (Tabelle: city_remote) in unsere "lokale" Datenbank (Tabelle: city) kopiert.

JOIN über Datenbankgrenzen hinweg

Listing 9:

1. SELECT COUNT(*)
2. FROM city_remote cr
3. JOIN city c
4. ON c.id = cr.id
5. WHERE cr.id = 5;
6. +----------+
7. | count(*) |
8. +----------+
9. | 1 |
10. +----------+
11. 1 row in set (0.00 sec)

Auch ein JOIN über Datenbankgrenzen hinweg ist möglich. Sicherlich wird dies - je nach Datenmenge - nicht performant sein. Da für den JOIN einiges an Daten über das Netzwerk transportiert werden muss.

Beschränkungen der FEDERATED-Speicher-Engine

Die FEDERATED Engine bietet nicht die gleichen Möglichkeiten wie InnoDB oder MyISAM. Normale SELECT, INSERT, UPDATE und DELETE sind erlaubt, aber ALTER TABLE, Transaktionen, Cache etc. werden nicht unterstützt. Details dazu bitte in der Dokumentation unter Kapitel 14.7.3. der jeweiligen MySQL-Version nachlesen.

Resumee

Die FEDERATED Engine bietet interessante Möglichkeiten, aber die Doku dazu ist dünn und ob diese Speicher-Engine "produktiv" eingesetzt werden sollte ist unklar. Nichts desto trotz bietet sie praktische Möglichkeiten für die Administration.

Link Tipp

Creating FEDERATED tables with a stored procedure
Developed In: SQL — Contributed by: Roland Bouman
http://forge.mysql.com/tools/tool.php?id=54

https://twiedmann.de