Connettersi ad un database remoto con mysql

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:

1. 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 .
2. 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)
) 

1. 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'

1. 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.