Migrazione da MySQL a Cloud Spanner

Questo articolo spiega come eseguire la migrazione del database OLTP (Online Transactional Processing) da MySQL a Cloud Spanner.

Vincoli di migrazione

Cloud Spanner utilizza alcuni concetti in modo diverso dagli altri strumenti di gestione dei database aziendali, per cui potrebbe essere necessario modificare l'architettura dell'applicazione per sfruttarne al massimo le capacità. Potresti anche dover integrare Cloud Spanner con altri servizi di Google Cloud per soddisfare le tue esigenze.

Stored procedure e trigger

Cloud Spanner non supporta l'esecuzione del codice utente a livello di database; pertanto, 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-valore primarie. La funzione GENERATE_UUID() (GoogleSQL, PostgreSQL) restituisce i valori UUID versione 4 rappresentati come tipo STRING.

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

Per saperne di più, consulta le strategie per i valori predefiniti della chiave principale.

Controlli di accesso

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

Vincoli di convalida dei dati

Spanner supporta un insieme limitato di vincoli di convalida dei dati nel livello database. Se hai bisogno di vincoli dei dati più complessi, devi implementarli nel livello dell'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 le tabelle normali) Vedi Creare e gestire relazioni di chiave esterna.
Azioni ON DELETE/ON UPDATE chiave esterna Possibile solo per le tabelle con interleaving, altrimenti implementate nel livello di applicazione
Controlli dei valori e convalida tramite vincoli CHECK Consulta la sezione Creazione e gestione dei vincoli di controllo.
Controlli e convalida dei valori tramite trigger Implementata nel livello di applicazione

Colonne generate

Spanner supporta le colonne generate in base alle quali 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 delle colonne durante un'istruzione DDL (Data Definition Language) di CREATE TABLE o ALTER TABLE. La parola chiave AS è seguita da una funzione SQL valida e dalla parola chiave suffisso obbligatoria STORED. La parola chiave STORED fa parte della specifica SQL ANSI 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 consultando Creazione e gestione delle colonne generate.

Tipi di dati supportati

MySQL e Spanner supportano set di tipi di dati diversi. La seguente tabella elenca i tipi di dati MySQL e i relativi equivalenti 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 database Spanner. Ad esempio, puoi archiviare un elemento BLOB di grandi dimensioni come oggetto in un bucket Cloud Storage anziché nel database, quindi 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 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 vengono fornite per convertire le date in una stringa formattata.
DATETIME, TIMESTAMP TIMESTAMP Spanner archivia l'ora indipendentemente dal fuso orario. Se devi memorizzare un fuso orario, devi utilizzare una colonna STRING separata. Le funzioni SQL vengono fornite per convertire i timestamp in una stringa formattata utilizzando i fusi orari.
CHAR, VARCHAR STRING Nota: Spanner utilizza stringhe Unicode.
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. Potresti usare offerte alternative di Google Cloud, come Cloud Storage, per archiviare
TEXT, TINYTEXT, ENUM STRING I valori 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 di TEXT più grandi.
ENUM STRING Nell'applicazione deve essere eseguita la convalida dei valori ENUM
SET ARRAY<STRING> La convalida dei valori dell'elemento SET deve essere eseguita nell'applicazione
LONGBLOB, MEDIUMBLOB BYTES o STRING contenente l'URI all'oggetto. Gli oggetti di piccole dimensioni (meno di 10 MiB) possono essere archiviati come BYTES. Prendi in considerazione l'uso di offerte Google Cloud alternative 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. usa offerte alternative di Google Cloud, come Cloud Storage, per archiviare
JSON JSON Le stringhe JSON di piccole dimensioni (meno di 2.621.440 caratteri) possono essere archiviate come JSON. Potresti usare offerte Google Cloud alternative 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 di applicazione.

Processo di migrazione

Le tempistiche generali del processo di migrazione saranno le seguenti:

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

Passaggio 1: converti il database e lo schema

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

L'utilizzo delle best practice per la progettazione degli schemi può aiutarti ad aumentare la velocità effettiva e ridurre gli hotspot nel database di Spanner.

Chiavi primarie

In Spanner, ogni tabella che deve archiviare più di una riga deve avere una chiave primaria costituita da una o più colonne della tabella. La chiave primaria della tabella identifica in modo univoco ogni riga al suo interno, mentre Spanner utilizza la chiave primaria per ordinare le righe della tabella. Poiché Spanner è altamente distribuito, è importante scegliere una tecnica di generazione di chiave primaria che offra una buona scalabilità in base alla crescita dei dati. Per ulteriori informazioni, consulta le strategie di migrazione della chiave principale che consigliamo.

Tieni presente che, dopo aver designato la chiave primaria, non puoi aggiungere o rimuovere una colonna di chiave primaria né modificare un valore di 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.

Crea interfoliazione delle tabelle

Spanner include una funzionalità in cui puoi definire due tabelle con una relazione padre-figlio 1-many. Questa funzionalità interlaccia le righe di dati figlio accanto alla riga padre nello spazio di archiviazione, in modo efficace prima dell'unione della tabella e migliorando l'efficienza di recupero dei dati quando le query padre e figlio vengono eseguite insieme.

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 è detta chiave esterna. Puoi definire fino a sei livelli di relazioni padre-figlio.

Puoi definire azioni al momento dell'eliminazione per le tabelle figlio per determinare cosa succede quando viene eliminata la riga padre: vengono eliminate tutte le righe figlio oppure viene bloccata l'eliminazione della riga padre mentre esistono le righe secondarie.

Ecco un esempio di creazione di una tabella Album con interleaving nella tabella principale Cantanti 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 nello stesso modo delle tabelle, pertanto i valori delle colonne da utilizzare come chiavi di indice avranno gli stessi vincoli delle chiavi primarie delle tabelle. Questo significa anche che gli indici hanno le stesse garanzie di coerenza delle tabelle Spanner.

Le ricerche di valori che utilizzano indici secondari sono di fatto le stesse di una query con un join di tabella. Puoi migliorare le prestazioni delle query utilizzando gli indici mediante l'archiviazione delle copie dei valori della colonna della tabella originale nell'indice secondario tramite la clausola STORING, che diventa così 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 oggetto di query (una query coperta). Per forzare l'utilizzo di un indice durante l'esecuzione di query sulle colonne nella 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 su quella colonna. L'indice impedisce l'aggiunta di valori duplicati.

Ecco un'istruzione DDL di esempio 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 li aggiungi a una media di tre al giorno. Per ulteriori indicazioni sulla creazione di indici secondari, consulta Indici secondari. Per ulteriori informazioni sulle limitazioni relative alla creazione degli indici, consulta Aggiornamenti dello schema.

Passaggio 2: traduci le query SQL

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

Sebbene Spanner non supporti 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 precedentemente uniti). Per ulteriori informazioni, consulta la sezione Sottoquery della documentazione.

Le query SQL possono essere profilate utilizzando l'interfaccia Query di Spanner nella console Google Cloud per eseguire la query. In generale, le query che eseguono scansioni complete delle tabelle su tabelle di grandi dimensioni sono molto costose e devono essere utilizzate con parsimonia. Per ulteriori informazioni sull'ottimizzazione delle query SQL, consulta la documentazione sulle best practice SQL.

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

Spanner offre una serie di librerie client per vari linguaggi e la possibilità di leggere e scrivere dati utilizzando chiamate API specifiche di Spanner, nonché l'utilizzo di query SQL e istruzioni DML (Data Modification Language). L'utilizzo delle chiamate API potrebbe essere più rapido per alcune query, ad esempio le letture delle righe dirette per chiave, in quanto non è necessario tradurre l'istruzione SQL.

Puoi anche utilizzare il driver Java Database Connectivity (JDBC) per connetterti a Spanner, sfruttando gli strumenti e l'infrastruttura esistenti che non dispongono di integrazione nativa.

Nell'ambito del processo di migrazione, le funzionalità non disponibili in Spanner come indicato sopra 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 e 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 limiti di timestamp, nel caso in cui tu stia leggendo una versione coerente dei dati:

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

Passaggio 4: trasferisci i dati da MySQL a Spanner

Per trasferire i dati da MySQL a Spanner, devi esportare il database MySQL in un formato file portatile, ad esempio XML, e poi 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 ben strutturati. 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 devi mettere in pausa l'applicazione o chiudere 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é è probabile che gli schemi di database di MySQL e Spanner siano diversi, potresti dover includere alcune conversioni dei dati nel processo di importazione. Il modo più semplice per eseguire queste conversioni dei dati e importarli in Spanner è utilizzare Dataflow. Dataflow è il servizio ETL distribuito di estrazione, trasformazione e caricamento (ETL) distribuito di Google Cloud. Fornisce una piattaforma per l'esecuzione di 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 la lettura, la trasformazione e la scrittura dei dati. Esistono connettori Beam per Cloud Storage e Spanner, quindi l'unico codice che devi scrivere è la trasformazione dei dati stessa.

Per un esempio di pipeline semplice che legge i file CSV e scrive in Spanner, consulta il repository del 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 figlio. Il codice della pipeline di importazione di Spanner gestisce questo problema 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 direttamente la pipeline di importazione di Spanner per importare i dati in blocco, ma questo approccio richiede che i dati esistano nei file Avro utilizzando lo schema corretto.

Passaggio 5: mantieni la coerenza tra i due database

Molte applicazioni hanno requisiti di disponibilità che ne impediscono l'utilizzo per il tempo necessario all'esportazione e all'importazione dei dati. Di conseguenza, durante il trasferimento dei dati su Spanner, l'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 l'acquisizione dei dati delle modifiche e l'implementazione di aggiornamenti simultanei nell'applicazione.

Change Data Capture (CDC)

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

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

Aggiornamenti simultanei a entrambi i database dall'applicazione

Un metodo alternativo consiste nel modificare la tua applicazione per eseguire le scritture su entrambi i database. Un database (inizialmente MySQL) è considerato la fonte attendibile e, dopo ogni scrittura del 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 sono stati trasferiti correttamente, puoi passare come origine attendibile al database Spanner. Questo meccanismo fornisce un percorso di rollback se vengono rilevati problemi durante il passaggio a Spanner.

Verificare la coerenza dei dati

Man mano che i dati vengono inviati al database Spanner, puoi eseguire periodicamente un confronto tra i dati Spanner e quelli MySQL per assicurarti che 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 su set di dati di grandi dimensioni utilizzando la trasformazione di join. Questa trasformazione richiede 2 set di dati con chiave e corrisponde ai valori in base alla chiave. I valori corrispondenti possono essere confrontati per ottenere 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

Se hai la certezza di aver eseguito la migrazione dei dati, puoi passare la tua applicazione all'utilizzo di Spanner come fonte attendibile. Se continui a riscrivere le modifiche al database MySQL, il database MySQL rimane aggiornato e viene fornito 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 ora obsoleto.

Esporta e importa 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 servire per vari scopi, tra cui:

  • Backup del database per la conformità ai criteri di conservazione dei dati o il ripristino di emergenza.
  • Importazione del file Avro in altre offerte Google Cloud come BigQuery.

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

Passaggi successivi