Esegui la migrazione da MySQL a Spanner

Questo articolo spiega come eseguire la migrazione del database di elaborazione transazionale online (OLTP) da MySQL a Spanner.

Vincoli di migrazione

Spanner utilizza alcuni concetti in modo diverso rispetto ad altri strumenti di gestione dei database aziendali, quindi potrebbe essere necessario regolare l'architettura della tua applicazione per sfruttarne appieno le funzionalità. Potresti anche dover integrare Spanner con altri servizi di Google Cloud per soddisfare le tue esigenze.

Stored procedure e trigger

Spanner non supporta l'esecuzione del codice utente a livello di database, quindi, nell'ambito della migrazione, la logica di business implementata da procedure e trigger archiviati a livello di database deve essere spostata nell'applicazione.

Sequenze

Spanner consiglia di utilizzare UUID versione 4 come metodo predefinito per generare coppie chiave primaria-valore. La funzione GENERATE_UUID() (GoogleSQL, PostgreSQL) restituisce i valori UUID 4 della versione 4 rappresentati come tipo STRING.

Se devi generare valori interi, Spanner supporta sequenze positive invertite a bit (GoogleSQL, PostgreSQL), che producono valori che distribuiscono uniformemente nello spazio numerico positivo a 64 bit. Puoi usare questi numeri per evitare problemi di hotspot.

Per ulteriori informazioni, consulta le strategie per il valore predefinito della chiave principale.

Controlli di accesso

Spanner supporta un controllo dell'accesso granulare a livello di tabella e colonna. Il controllo granulare dell'accesso per le viste non è supportato. Per ulteriori informazioni, consulta Informazioni sul controllo dell'accesso granulare.

Vincoli di convalida dei dati

Spanner supporta un set limitato di vincoli di convalida dei dati nel livello del database. Se hai bisogno di vincoli per i dati più complessi, devi implementarli a livello di applicazione.

La tabella seguente illustra i tipi di vincoli comunemente presenti nei database MySQL e come implementarli con Spanner.

Vincolo Implementazione con Spanner
Non null Vincolo di colonna NOT NULL
Univoco Indice secondario con vincolo UNIQUE
Foreign key (per tabelle normali) Vedi Creare e gestire relazioni di chiave esterna.
Azioni chiave esterna ON DELETE/ON UPDATE Possibile solo per le tabelle con interleaving; altrimenti, implementato nel livello di applicazione
Controllo dei valori e convalida tramite vincoli CHECK Consulta la sezione Creazione e gestione dei vincoli di controllo.
Controllo dei valori e convalida tramite trigger Implementata nel livello dell'applicazione

Colonne generate

Spanner supporta le colonne generate dove il valore della colonna verrà sempre generato da una funzione fornita come parte della definizione della tabella. Come in MySQL, le colonne generate non possono essere impostate espressamente su un valore fornito in un'istruzione DML.

Le colonne generate sono definite come parte della definizione della colonna durante un'istruzione DDL (Data Definition Language) CREATE TABLE o ALTER TABLE. La parola chiave AS è seguita da una funzione SQL valida e dalla parola chiave suffisso richiesta STORED. La parola chiave STORED fa parte della specifica ANSI SQL e indica che i risultati della funzione verranno archiviati insieme ad altre colonne della tabella.

La funzione SQL, l'espressione di generazione, può includere qualsiasi espressione, funzione e operatore deterministici e può essere utilizzata in indici secondari o come chiave esterna.

Scopri di più su come gestire questo tipo di colonna, consulta Creare e gestire le colonne generate.

Tipi di dati supportati

MySQL e Spanner supportano diversi set di tipi di dati. La seguente tabella elenca i tipi di dati MySQL e il loro equivalente in Spanner. Per definizioni dettagliate di ogni tipo di dati di Spanner, consulta Tipi di dati.

Potresti dover trasformare ulteriormente i dati come descritto nella colonna Note per adattare i dati MySQL al tuo database Spanner. Ad esempio, puoi archiviare un BLOB di grandi dimensioni come oggetto in un bucket Cloud Storage anziché nel database, quindi puoi archiviare il riferimento URI all'oggetto Cloud Storage nel database come STRING.

Tipo di dati MySQL Equivalente Spanner Note
INTEGER, INT, MEDIUMINT BIGINT e SMALLINT INT64
TINYINT, BOOL, BOOLEAN BOOL, INT64 I valori TINYINT(1) vengono utilizzati per rappresentare i valori booleani di "true" (non zero) o "false" (0).
FLOAT, DOUBLE FLOAT64
DECIMAL, NUMERIC NUMERIC, STRING In MySQL,i tipi di dati NUMERIC e DECIMAL supportano fino a un totale di 65 cifre di precisione e scalabilità, come definito nella dichiarazione della colonna. Il tipo di dati NUMERIC di Spanner supporta fino a 38 cifre di precisione e 9 cifre decimali di scala.
Se hai bisogno di una maggiore precisione, consulta Archiviazione di dati numerici di precisione arbitraria per meccanismi alternativi.
BIT BYTES
DATE DATE Sia Spanner che MySQL utilizzano il formato "yyyy-mm-dd" per le date, quindi non è necessaria alcuna trasformazione. Le funzioni SQL sono fornite per convertire le date in una stringa formattata.
DATETIME, TIMESTAMP TIMESTAMP Spanner archivia l'ora indipendentemente dal fuso orario. Se devi archiviare un fuso orario, devi utilizzare una colonna STRING separata. Le funzioni SQL sono fornite per convertire i timestamp in una stringa formattata utilizzando i fusi orari.
CHAR, VARCHAR STRING Nota: Spanner utilizza stringhe Unicode ovunque.
VARCHAR supporta una lunghezza massima di 65.535 byte, mentre Spanner supporta fino a 2.621.440 caratteri.
BINARY, VARBINARY, BLOB, TINYBLOB BYTES Gli oggetti di piccole dimensioni (meno di 10 MiB) possono essere archiviati come BYTES. Valuta l'uso di offerte alternative di Google Cloudd, come Cloud Storage,
TEXT, TINYTEXT, ENUM STRING Valori di TEXT piccoli (meno di 10 MiB) possono essere archiviati come STRING. Valuta la possibilità di utilizzare offerte Google Cloud alternative, come Cloud Storage, per supportare valori TEXT più grandi.
ENUM STRING La convalida dei valori ENUM deve essere eseguita nell'applicazione
SET ARRAY<STRING> La convalida dei valori dell'elemento SET deve essere eseguita nell'applicazione
LONGBLOB, MEDIUMBLOB BYTES o STRING contenente l'URI dell'oggetto. Gli oggetti di piccole dimensioni (meno di 10 MiB) possono essere archiviati come BYTES. Valuta l'uso di offerte alternative di Google Cloud, come Cloud Storage, per archiviare oggetti più grandi.
LONGTEXT, MEDIUMTEXT STRING (contenente dati o URI dell'oggetto esterno) Gli oggetti di piccole dimensioni (meno di 2.621.440 caratteri) possono essere archiviati come STRING. Valuta l'uso di offerte alternative di Google Cloud, come Cloud Storage,
JSON JSON Le stringhe JSON di piccole dimensioni (meno di 2.621.440 caratteri) possono essere archiviate come JSON. Valuta l'uso di offerte alternative di Google Cloud, come Cloud Storage, per archiviare oggetti più grandi.
GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTIPOLYGON e GEOMETRYCOLLECTION Spanner non supporta i tipi di dati geospaziali. Devi archiviare questi dati utilizzando tipi di dati standard e implementare qualsiasi logica di ricerca/filtro nel livello dell'applicazione.

Processo di migrazione

Una tempistica generale del processo di migrazione sarebbe:

  1. Converti lo schema e il modello dei dati.
  2. Traduci le query SQL.
  3. Esegui la migrazione della tua applicazione per utilizzare Spanner oltre a MySQL.
  4. Esporta in blocco i dati da MySQL e importa i tuoi dati in Spanner utilizzando Dataflow.
  5. Mantieni la coerenza tra entrambi i database durante la migrazione.
  6. Esegui la migrazione dell'applicazione da MySQL.

Passaggio 1: converti il database e lo schema

Converti lo schema esistente in uno schema Spanner per archiviare i tuoi dati. Per semplificare le modifiche all'applicazione, assicurati che lo schema convertito corrisponda il più fedelmente possibile allo schema MySQL esistente. Tuttavia, a causa delle differenze nelle funzionalità, alcune modifiche potrebbero essere necessarie.

L'utilizzo delle best practice per la progettazione dello schema può aiutarti ad aumentare la velocità effettiva e a ridurre gli hotspot nel tuo database Spanner.

Chiavi primarie

In Spanner, ogni tabella che deve archiviare più di una riga deve avere una chiave primaria composta da una o più colonne della tabella. La chiave primaria della tabella identifica in modo univoco ogni riga della tabella e Spanner utilizza la chiave primaria per ordinare le righe della tabella. Poiché Spanner è un'elevata distribuzione, è importante scegliere una tecnica di generazione di chiave primaria che si adatti bene alla crescita dei tuoi dati. Per ulteriori informazioni, consulta le strategie di migrazione della chiave principale consigliate.

Tieni presente che dopo aver designato la chiave primaria, non puoi aggiungere o rimuovere una colonna di chiave primaria o modificare il valore di una chiave primaria in un secondo momento senza eliminare e ricreare la tabella. Per ulteriori informazioni su come designare la chiave primaria, consulta Schema e modello dei dati: chiavi primarie.

Interfoliare le tabelle

Spanner offre una funzionalità per cui puoi definire due tabelle con una relazione padre-figlio 1-molti. Questa funzionalità alterna le righe di dati figlio accanto alla riga padre nello spazio di archiviazione, in modo efficace prima del join della tabella e migliorando l'efficienza del recupero dati quando vengono eseguite query insieme su elementi padre e figlio.

La chiave primaria della tabella figlio deve iniziare con le colonne di chiave primaria della tabella padre. Dal punto di vista della riga figlio, la chiave primaria della riga padre viene indicata come chiave esterna. Puoi definire fino a 6 livelli di rapporti genitore-figlio.

Puoi definire le azioni all'eliminazione per le tabelle figlio per determinare cosa succede quando viene eliminata la riga padre: vengono eliminate tutte le righe figlio o l'eliminazione delle righe padre è bloccata mentre esistono righe figlio.

Ecco un esempio di creazione di una tabella Album con interleaving nella tabella Singers principale definita in precedenza:

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  AlbumId      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, AlbumId)
INTERLEAVE IN PARENT (Singers)
ON DELETE CASCADE;

Crea indici secondari

Puoi anche creare indici secondari per indicizzare i dati all'interno della tabella al di fuori della chiave primaria. Spanner implementa gli indici secondari come le tabelle, quindi i valori delle colonne da utilizzare come chiavi di indice avranno gli stessi vincoli delle chiavi primarie delle tabelle. Ciò significa anche che gli indici hanno le stesse garanzie di coerenza delle tabelle Spanner.

Le ricerche di valori mediante indici secondari sono sostanzialmente identiche a quelle di una query con un join di tabella. Puoi migliorare le prestazioni delle query utilizzando gli indici archiviando copie dei valori delle colonne della tabella originale nell'indice secondario tramite la clausola STORING, che diventa un indice di copertura.

Lo strumento di ottimizzazione delle query di Spanner utilizza automaticamente un indice secondario solo quando l'indice stesso archivia tutte le colonne sottoposte a query (una query coperta). Per forzare l'utilizzo di un indice quando si eseguono query sulle colonne della tabella originale, devi utilizzare un'istruzione FORCE INDEX nell'istruzione SQL, ad esempio:

SELECT *
FROM MyTable@{FORCE_INDEX=MyTableIndex}
WHERE IndexedColumn=@value

Gli indici possono essere utilizzati per applicare valori univoci all'interno di una colonna della tabella, definendo un indice UNIQUE in quella colonna. L'indice non impedirà l'aggiunta di valori duplicati.

Ecco un esempio di istruzione DDL che crea un indice secondario per la tabella Album:

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

Se crei indici aggiuntivi dopo aver caricato i dati, il completamento dell'indice potrebbe richiedere del tempo. Ti consigliamo di limitare la frequenza con cui le aggiungi a una media di tre al giorno. Per ulteriori indicazioni sulla creazione di indici secondari, consulta Indici secondari. Per maggiori informazioni sui limiti relativi alla creazione degli indici, consulta Aggiornamenti dello schema.

Passaggio 2: traduci le query SQL

Spanner utilizza il dialetto ANSI 2011 di SQL con estensioni e dispone di molti funzioni e operatori per tradurre e aggregare i dati. Eventuali query SQL che utilizzano dialetto, funzioni e tipi specifici di MySQL dovranno essere convertite per essere compatibili con Spanner.

Anche se Spanner non supporta i dati strutturati come definizioni di colonne, puoi utilizzare i dati strutturati nelle query SQL con i tipi ARRAY<> e STRUCT<>. Ad esempio, potresti scrivere una query che restituisca tutti gli album di un artista utilizzando un valore ARRAY di STRUCT (sfruttando i dati pre-uniti). Per ulteriori informazioni, consulta la sezione Sottoquery della documentazione.

Per eseguire la query, puoi profilare le query SQL utilizzando la pagina Spanner Studio della console Google Cloud. In generale, le query che eseguono scansioni complete delle tabelle su tabelle di grandi dimensioni sono molto costose e dovrebbero essere utilizzate con parsimonia. Per ulteriori informazioni sull'ottimizzazione delle query SQL, consulta la documentazione sulle best practice per SQL.

Passaggio 3: esegui la migrazione dell'applicazione per utilizzare Spanner

Spanner offre un set di librerie client per vari linguaggi e la possibilità di leggere e scrivere dati utilizzando chiamate API specifiche di Spanner e utilizzando query SQL e istruzioni DML (Data Modification Language). L'utilizzo delle chiamate API potrebbe essere più veloce per alcune query, ad esempio le letture di righe dirette per chiave, poiché l'istruzione SQL non deve essere tradotta.

Spanner fornisce un driver JDBC per le applicazioni Java.

Nell'ambito del processo di migrazione, le funzionalità non disponibili in Spanner come indicato in precedenza devono essere implementate nell'applicazione. Ad esempio, un trigger per verificare i valori dei dati e aggiornare una tabella correlata deve essere implementato nell'applicazione utilizzando una transazione di lettura/scrittura per leggere la riga esistente, verificare il vincolo, quindi scrivere le righe aggiornate in entrambe le tabelle.

Spanner offre transazioni di lettura/scrittura e di sola lettura, che garantiscono la coerenza esterna dei dati. Inoltre, alle transazioni di lettura possono essere applicati dei limiti di timestamp, che consentono di leggere una versione coerente dei dati:

  • a un'ora esatta nel passato (fino a un'ora fa).
  • in futuro (in cui la lettura si bloccherà fino all'arrivo di quel momento).
  • con una quantità accettabile di inattività limitata, che restituirà una visualizzazione coerente fino a un certo periodo di tempo nel passato senza dover verificare che i dati successivi siano disponibili su un'altra replica. Ciò può portare vantaggi in termini di prestazioni a scapito di dati potenzialmente inattivi.

Passaggio 4: trasferisci i dati da MySQL a Spanner

Per trasferire i tuoi dati da MySQL a Spanner, devi esportare il tuo database MySQL in un formato file portatile, ad esempio XML, e quindi importare i dati in Spanner utilizzando Dataflow.

Trasferimento di dati da MySQL a Spanner

Esportazione collettiva da MySQL

Lo strumento mysqldump incluso in MySQL è in grado di esportare l'intero database in file XML corretti. In alternativa, puoi utilizzare l'istruzione SQL SELECT ... INTO OUTFILE per creare file CSV per ogni tabella. Tuttavia, questo approccio presenta lo svantaggio che è possibile esportare una sola tabella alla volta, il che significa che è necessario mettere in pausa l'applicazione o disattivare il database in modo che rimanga in uno stato coerente per l'esportazione.

Dopo aver esportato questi file di dati, ti consigliamo di caricarli in un bucket Cloud Storage in modo che siano accessibili per l'importazione.

Importazione collettiva in Spanner

Poiché gli schemi del database probabilmente differiscono tra MySQL e Spanner, potrebbe essere necessario includere alcune conversioni dei dati nel processo di importazione. Il modo più semplice per eseguire queste conversioni e importare i dati in Spanner è utilizzare Dataflow. Dataflow è il servizio ETL di estrazione, trasformazione e caricamento (distribuito) di Google Cloud. Fornisce una piattaforma per eseguire pipeline di dati scritte utilizzando l'SDK Apache Beam al fine di leggere ed elaborare grandi quantità di dati in parallelo su più macchine.

L'SDK Apache Beam richiede la scrittura di un semplice programma Java per impostare le operazioni di lettura, trasformazione e scrittura dei dati. I connettori Beam esistono per Cloud Storage e Spanner, quindi l'unico codice che devi scrivere è la trasformazione dei dati.

Per un esempio di una pipeline semplice che legge dai file CSV e scrive in Spanner, consulta il repository di codice di esempio.

Se utilizzi tabelle con interleaving padre-figlio nello schema Spanner, durante il processo di importazione assicurati che la riga padre venga creata prima della riga secondaria. Il codice della pipeline di importazione di Spanner gestisce questo processo importando prima tutti i dati per le tabelle di livello principale, poi tutte le tabelle figlio di livello 1, poi tutte le tabelle figlio di livello 2 e così via.

Puoi utilizzare la pipeline di importazione di Spanner direttamente per importare i dati in blocco, ma questo approccio richiede che i dati siano presenti nei file Avro utilizzando lo schema corretto.

Passaggio 5: mantieni la coerenza tra i due database

Molte applicazioni hanno requisiti di disponibilità che rendono impossibile mantenere l'applicazione offline per il tempo necessario a esportare e importare i dati. Pertanto, mentre trasferisci i dati in Spanner, la tua applicazione continua a modificare il database esistente. Pertanto, è necessario duplicare gli aggiornamenti al database Spanner mentre l'applicazione è in esecuzione.

Esistono vari metodi per mantenere sincronizzati i due database, tra cui CDC (Change Data Capture) e l'implementazione di aggiornamenti simultanei nell'applicazione.

Change Data Capture (CDC)

MySQL non dispone di un'utilità nativa per CDC (Change Data Capture). Tuttavia, esistono vari progetti open source che possono ricevere binlog MySQL e convertirli in flussi CDC. Ad esempio, il daemon di Maxwell può fornire un flusso CDC per il database.

Puoi scrivere un'applicazione che si abbona a questo flusso e applica le stesse modifiche (ovviamente dopo la conversione dei dati) al database Spanner.

Aggiornamenti simultanei a entrambi i database dall'applicazione

Un metodo alternativo è modificare l'applicazione per eseguire scritture in entrambi i database. Un database (inizialmente MySQL) verrebbe considerato l'origine dati attendibile e, dopo la scrittura di ogni database, l'intera riga viene letta, convertita e scritta nel database Spanner. In questo modo, l'applicazione sovrascrive costantemente le righe di Spanner con i dati più recenti.

Quando hai la certezza che tutti i dati siano stati trasferiti correttamente, puoi trasferire l'origine attendibile al database Spanner. Questo meccanismo fornisce un percorso di rollback se vengono rilevati problemi durante il passaggio a Spanner.

Verifica la coerenza dei dati

Man mano che i flussi di dati raggiungono il tuo database Spanner, puoi eseguire periodicamente un confronto tra i dati di Spanner e quelli di MySQL per assicurarti che i dati siano coerenti. Puoi convalidare la coerenza eseguendo query su entrambe le origini dati e confrontando i risultati.

Puoi utilizzare Dataflow per eseguire un confronto dettagliato tra set di dati di grandi dimensioni utilizzando la trasformazione Join. Questa trasformazione prende 2 set di dati con chiave e associa i valori in base alla chiave. I valori corrispondenti possono quindi essere confrontati per trovare l'uguaglianza. Puoi eseguire regolarmente questa verifica finché il livello di coerenza non soddisfa i requisiti della tua attività.

Passaggio 6: passa a Spanner come fonte attendibile dell'applicazione

Una volta acquisita fiducia nella migrazione dei dati, puoi passare l'applicazione all'utilizzo di Spanner come origine attendibile. Se continui a scrivere le modifiche al database MySQL, questo mantiene il database MySQL aggiornato, fornendo un percorso di rollback in caso di problemi.

Infine, puoi disabilitare e rimuovere il codice di aggiornamento del database MySQL e arrestare il database MySQL obsoleto.

Esporta e importa i database Spanner

Facoltativamente, puoi esportare le tabelle da Spanner in un bucket Cloud Storage utilizzando un modello Dataflow per eseguire l'esportazione. La cartella risultante contiene un insieme di file Avro e file manifest JSON contenenti le tabelle esportate. Questi file possono essere usati per vari scopi, tra cui:

  • Eseguire il backup del database per la conformità alle norme di conservazione dei dati o il ripristino di emergenza.
  • Importare il file Avro in altre offerte Google Cloud come BigQuery.

Per ulteriori informazioni sul processo di esportazione e importazione, consulta Esportazione di database e Importazione di database.

Passaggi successivi