Qualche volta si ha la necessità di connettersi a dati (tabelle) che si trovano su di un server MySQL remoto, MySQL ci mette a disposizione un tipo federated che fa proprio al caso nostro.
Il tipo FEDERATED ci da la possibilità di accedere ai dati che risiedono su di un'altro database remoto senza dover replicare il database. Quando utilizziamo una tabella definita FEDERATED, le query eseguite sul server locale vengono eseguite automaticamente sulle tabelle remote questo ci consente di non avere alcun dato salvato sulla tabella locale.
Per prima cosa si deve verificare che il protocollo federated sia abilitato utilizzando il seguente comando da riga di comando Mysql.
mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
| CSV | YES | CSV storage engine |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| FEDERATED | YES | Federated MySQL storage engine |
| ARCHIVE | YES | Archive storage engine |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
+------------+---------+----------------------------------------------------------------+
se nella colonna support non dovesse essere abilitato si deve aggiungere federated=ON nella sezione [mysqld] del file di configurazione my.cnf e riaviarre il server
Per creare una tabella FEDERATED si devono seguire i seguenti passi:
- Creare la tabella sul server remoto. In alternativa si prenda nota della definizione della tabella esistente che si può vedere tramite il comando
SHOW CREATE TABLE .
- Creare la tabella sul server locale utilizzando la stessa definizione aggiungendo però le informazioni di connessione che collegano alla tabella remota.
il collegamento alla tabella remota si può eseguire in 2 modi usando una connection string (contente il nome del server, utente, password) oppure creando precedentemente una connessione al server remoto
vediamo le due alternative.
Per esempio creiamo la seguente tabella sul server remoto:
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),
INDEX name (name),
INDEX other_key (other)
)
Creazione tabella FEDERATED usando CONNECTION
Per usare questo metodo si deve specificare la CONNECTION string dopo aver speficato il parametro ENGINE nel
CREATE TABLE .
Per esempio:
CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
Note
La CONNECTION string contiene tutte le informazioni che servono per collegarsi al computer remoto ovvero specifica il nome del server, le credenziali di accesson il numero della porta e le informazioni relative al database e la tabella di riferimento.
Nell'esempio la tabella remota è sul server
remote_host, usa la porta
9306 (il nome server e porta possono essere sostituiti anche dall'indirizzo IP)
l'utenet è fed_user,
il dabase è federated e la tabella remota si chiama
test_table
La sintassi della CONNECTION STRING è:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
Dove:
-
scheme: Protocollo di connessione conosciuto. Solo mysql è supportato come valore di questo attributo.
-
user_name: Il nome utente per la connessione che deve essere creato sul server remoto e deve avere i privilegi per eseguire le azioni richieste (SELECT,
INSERT,
UPDATE) sulla tabella remota.
-
password: (facoltativo) la password corrispondente all'utente
user_name.
-
host_name: Il nome dell'host o l'indirizzo IP dello stesso.
-
port_num: (facoltativo) Il numero di porta del server. Di default è 3306.
-
db_name: Il nome del database che contiene la tabella che ci interessa.
-
tbl_name: Il nome della tabella remota. Il nome della tabella remota e quella locale non devono necessariamente coincidere.
Esempi di connection strings:
CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'
Creazione tabella FEDERATED usando
CREATE SERVER
Se si devono creare più di una tabella FEDERATED sullo stesso server o semplicemente se si vuole semplificare il processo di creazione si può usare CREATE SERVER per definire i parametri di connesione al server,
Il formato di CREATE SERVER è:
CREATE SERVERserver_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)
per esempio per creare un server identico alla
CONNECTION string:
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
Si usa il seguente codice:
CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');
Per creare un tabella
FEDERATED che usa questa connesionne si deve utilizzare la keyword
CONNECTION specofocando il nome che si ha utilizzato nella creazione del server
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),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';
Nel parametro CONNECTION dell'esempio si trova il nome della connessione al server (fedlink) e il nome della tabella remota (test_table)separate da /.
Se si specifica solo il nome della connessione senza il nome della tabella viene preso
Per maggiori informazioni
“CREATE SERVER Syntax”.
CREATE SERVER accetta gli stessi parametri della connction string.
Queate informazioni vengono inserite nella tabella mysql.servers, nella tabella seguente ci sono le corrispondenze con i valori inseriti nella connection string e quelli inseriti nella tabella.
la connection string di rifermineto è :
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
| Description |
CONNECTION string |
CREATE SERVER option |
mysql.servers column |
| Connection scheme |
scheme |
wrapper_name |
Wrapper |
| Remote user |
user_name |
USER |
Username |
| Remote password |
password |
PASSWORD |
Password |
| Remote host |
host_name |
HOST |
Host |
| Remote port |
port_num |
PORT |
Port |
| Remote database |
db_name |
DATABASE |
Db |
Alcune cose importanti da ricordare :
- Le tabelle FEDERATED possono essere replicate sui server slave previo verifica che il server slave possa utilizzzare la combinazion euser/password definite nella connection string (o nel record della tabella mysql.servers ) per connettersi al server remoto.
- il server remoto deve essere un server MySQL .
- La tabella remota a cui si fa riferimento deve esistere prima che si tenti l' accesso via tabella FEDERATED.
- una tabella FEDERATED può puntare ad un'altra tabella dello stesso tipo facendo attenzione a non creare un loop tra le connessioni.
-
Una tabella FEDERATED non supporta gli indice perchè l'accesso alla tabella è definito in remoto, e deve essere la tabella remota che ha il supporto per gli indici.
Si deve prestare attenzione quando si crea una tabella FEDERATED in
quanto la definizione dell'indice da un equivalente MyISAM o di altra
tabella potrebbero non essere supportate
Ad esempio, la creazione di una tabella FEDERATED con indice
su una colonna VARCHAR, TEXT o BLOB avrà esito negativo. La seguente
definizione in MyISAM è valida
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;
La definizione della chiave in questo esempio non è compatibile con il motore
FEDERATED, e la dichiarazione equivalente avrà esito negativo:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED
CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';
Se possibile, si dovrebbe cercare di separare la definizione di colonna e indice per creare tabelle sia sul server remoto e il server locale
per evitare questi problemi di indice.
- Il protocollo FEDERATED supporta SELECT, INSERT, UPDATE, DELETE,
TRUNCATE TABLE, e gli indici. Non supporta ALTER TABLE, o qualsiasi
istruzione DDL che modifichi la struttura della tabella come DROP TABLE.
La corrente implementazione non supporta l'uso di funzioni preparate e
nemmeno le transizioni sono supportate.
il protocollo FEDERATED accetta inserimento
... con chiavi duplicate , ma se vi èuna violazione di chiave duplicata il la chiarazione fallisce con un errore - La velocità di una tabella
FEDERATED per inserimenti massivi (per esempio, INSERT INTO ...
SELECT ...) è più lenta di ogni altro tipo di tabella perche ogni riga selezionata viene trattata come un INSERT individuale sulla tabella FEDERATED.
- Prima di MySQL 5.1.21, per un inserimento di righe multiple in una tablella FEDERATED, se l'inserimento creava un errore su di una riga dovuto ad una violazione di un vincolo, la tabella remota contenteva un aggiornamento parziale contenente le righe precendi a quella che ha causato l'errore invece di una cancellazione totale questo è dovuto al fatto che ogni INSERT è un'operazione a se stante.
DopoMySQL 5.1.21, il protocollo
FEDERATED consente inserimenti di massa che vengono spediti in remoto.Questo permette miglioramenti di prestazioni e se la tabella remota e di tipo transactional, è possibile il ripristino completo al verificarsi di un errore.
Questa possibilità ha le seguenti limitazioni:
-
La dimensione degli inserimenti non deve superare la dimensione massima di un pacchetto per il server altrimenti verrà diviso in più pacchetti che renderebbero il ripristino problematico.
-
l'inserimento massivo non avviene per
INSERT
... ON DUPLICATE KEY UPDATE.
- Non c'è modo per il protocollo FEDERATED di sapere se la tabella remota ha subito cambiamenti.Il motivo è che queste tabelle sono utilizzate come file di dati che non possono essere modificate dal sistema. l'integrità dei dati nel database locale può rompersi se ci fossero cambiamenti al database remoto.
(There is no way for the FEDERATED engine to
know if the remote table has changed. The reason for this is
that this table must work like a data file that would never be
written to by anything other than the database system. The
integrity of the data in the local table could be breached if
there was any change to the remote database.)
- Se usi una
CONNECTION string, non puoi usare il carattere '@' nella password. Si può ovviare al problema creando una connessione via CREATE SERVER.
- le opzioni
insert_id e
timestamp non vengono riportate sul server
- Qualsiasi
DROP TABLE su una tabella FEDERATED cancella solo la tabella locale e non la remota.
le tabelle FEDERATED non funzionano con le query nella cache - User-defined partitioning is not supported for
FEDERATED tables. Beginning with MySQL
5.1.15, it is no longer possible to create such tables at all.
