Esegui la migrazione da MySQL a Spanner

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

Vincoli di migrazione

Spanner utilizza determinati concetti in modo diverso rispetto ad altri strumenti di gestione dei database aziendali, pertanto potrebbe essere necessario modificare l'architettura dell'applicazione per sfruttare appieno le sue funzionalità. Potresti anche dover integrare Spanner con altri servizi diGoogle Cloud per soddisfare le tue esigenze.

Stored procedure e trigger

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

Sequenze

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

Se devi generare valori interi, Spanner supporta sequenze positive con inversione dei bit (GoogleSQL, PostgreSQL), che producono valori distribuiti in modo uniforme nello spazio numerico positivo a 64 bit. Puoi utilizzare questi numeri per evitare problemi di hotspotting.

Per ulteriori informazioni, consulta le strategie per i valori predefiniti della chiave primaria.

Controlli di accesso

Spanner supporta il controllo dell'accesso granulare a livello di tabella e colonna. Il controllo granulare degli accessi per le visualizzazioni non è supportato. 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 del database. Se hai bisogno di vincoli di dati più complessi, devi implementarli nel 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 della colonna NOT NULL
Univoco Indice secondario con vincolo UNIQUE
Foreign key (per le tabelle normali) Consulta l'articolo Creare e gestire relazioni con le chiavi esterne.
Azioni ON DELETE/ON UPDATE della chiave esterna Possibile solo per le tabelle interlacciate; in caso contrario, implementato nel livello di applicazione
Controlli e convalida dei valori tramite vincoli CHECK Consulta Creare e gestire i vincoli check.
Controlli e convalida dei valori tramite gli attivatori Implementato nel livello di applicazione

Colonne generate

Spanner supporta le colonne generate in cui il valore della colonna viene sempre generato da una funzione fornita nell'ambito della definizione della tabella. Come in MySQL, le colonne generate non possono essere impostate esplicitamente su un valore fornito in un'istruzione DML.

Le colonne generate vengono definite nell'ambito della definizione della colonna durante un'istruzione CREATE TABLE o ALTER TABLE Data Definition Language (DDL). 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 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 deterministico e può essere utilizzata negli indici secondari o come chiave esterna.

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

Tipi di dati supportati

MySQL e Spanner supportano insiemi diversi di tipi di dati. La tabella seguente elenca i tipi di dati MySQL e i relativi equivalenti in Spanner. Per definizioni dettagliate di ogni tipo di dato Spanner, consulta Tipi di dati.

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

Tipo di dati MySQL Equivalente Spanner Note
INTEGER, INT, BIGINT MEDIUMINT, SMALLINT INT64
TINYINT, BOOL, BOOLEAN BOOL, INT64 I valori TINYINT(1) vengono utilizzati per rappresentare valori booleani di 'true' (diverso da 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 scala, 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 la sezione Memorizzazione di dati numerici di precisione arbitraria per conoscere meccanismi alternativi.
BIT BYTES
DATE DATE Sia Spanner che MySQL utilizzano il formato "yyyy-mm-dd" per le date, pertanto non è necessaria alcuna trasformazione. Sono disponibili funzioni SQL per convertire le date in una stringa formattata.
DATETIME, TIMESTAMP TIMESTAMP Spanner memorizza 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 sempre 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. Valuta la possibilità di utilizzare offerte Google Cloud alternative come Cloud Storage per archiviare oggetti di dimensioni maggiori
TEXT, TINYTEXT, ENUM STRING I valori TEXT di piccole dimensioni (inferiori a 10 MiB) possono essere memorizzati come STRING. Valuta la possibilità di utilizzare offerte Google Cloud alternative come Cloud Storage per supportare valori TEXT più elevati.
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 la possibilità di utilizzare offerte Google Cloud alternative come Cloud Storage per archiviare oggetti di dimensioni maggiori.
LONGTEXT, MEDIUMTEXT STRING (contenente dati o URI a un oggetto esterno) Gli oggetti di piccole dimensioni (meno di 2.621.440 caratteri) possono essere archiviati come STRING. Valuta la possibilità di utilizzare offerte Google Cloud alternative come Cloud Storage per archiviare oggetti di dimensioni maggiori
JSON JSON Le stringhe JSON piccole (meno di 2.621.440 caratteri) possono essere archiviate come JSON. Valuta la possibilità di utilizzare offerte Google Cloud alternative come Cloud Storage per archiviare oggetti di dimensioni maggiori.
GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTIPOLYGON, GEOMETRYCOLLECTION Spanner non supporta i tipi di dati geospaziali. Devi memorizzare questi dati utilizzando tipi di dati standard e implementare qualsiasi logica di ricerca/filtro nel livello dell'applicazione.

Processo di migrazione

Una sequenza temporale complessiva del processo di migrazione è la seguente:

  1. Converti lo schema e il modello di dati.
  2. Tradurre eventuali query SQL.
  3. Esegui la migrazione dell'applicazione in modo che utilizzi Spanner oltre a MySQL.
  4. Esporta collettivamente i dati da MySQL e importali in Spanner utilizzando Dataflow.
  5. Mantieni la coerenza tra i due 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 dati. Per semplificare le modifiche dell'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 di best practice nella progettazione dello schema può aiutarti ad aumentare la velocità effettiva e ridurre gli hot spot nel database Spanner.

Chiavi primarie

In Spanner, ogni tabella che deve memorizzare 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 di una tabella e Spanner la utilizza per ordinare le righe della tabella. Poiché Spanner è molto distribuito, è importante scegliere una tecnica di generazione della chiave primaria che si adatti bene alla crescita dei dati. Per ulteriori informazioni, consulta le strategie di migrazione delle chiavi principali consigliate.

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

Intercala le tabelle

Spanner dispone di una funzionalità che consente di definire due tabelle come esistenti in una relazione 1-many padre-figlio. Questa funzionalità intercala le righe di dati figlio accanto alla riga padre nello spazio di archiviazione, pre-unire efficacemente la tabella e migliorare l'efficienza del recupero dei dati quando vengono eseguite query su righe padre e figlie contemporaneamente.

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 è indicata come chiave esterna. Puoi definire fino a 6 livelli di relazioni tra elementi principali e secondari.

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

Ecco un esempio di creazione di una tabella Album interlacciata 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;

Creare 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. Ciò significa anche che gli indici hanno le stesse garanzie di coerenza delle tabelle Spanner.

Le ricerche dei valori che utilizzano gli indici secondari sono in pratica le stesse di una query con una join di tabelle. Puoi migliorare le prestazioni delle query che utilizzano gli indici memorizzando copie dei valori delle colonne della tabella originale nell'indice secondario utilizzando la clausola STORING, creando così un indice che copre.

L'ottimizzatore delle query di Spanner utilizza automaticamente un indice secondario solo quando l'indice stesso memorizza tutte le colonne su cui viene eseguita la query (una query coperta). Per forzare l'utilizzo di un indice quando esegui 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 di una tabella definendo un indice UNIQUE in quella colonna. L'indice impedirà l'aggiunta di valori duplicati.

Di seguito è riportato un esempio di istruzione DDL che crea un indice secondario per la tabella Albums:

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

Se crei altri indici dopo aver caricato i dati, l'inserimento dell'indice potrebbe richiedere del tempo. Ti consigliamo di limitarne la frequenza ad 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 di indici, consulta Aggiornamenti dello schema.

Passaggio 2: traduci eventuali query SQL

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

Sebbene Spanner non supporti i dati strutturati come definizioni di colonna, puoi utilizzare i dati strutturati nelle query SQL utilizzando i tipi ARRAY<> e STRUCT<>. Ad esempio, potresti scrivere una query che restituisca tutti gli album di un artista utilizzando un ARRAY di STRUCT (sfruttando i dati precombinati). Per ulteriori informazioni, consulta la sezione Subquery della documentazione.

È possibile eseguire il profiling delle query SQL utilizzando la pagina Spanner Studio nella console Google Cloud per eseguire la query. In generale, le query che eseguono scansioni complete della tabella 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 relativa alle best practice per SQL.

Passaggio 3: esegui la migrazione dell'applicazione per l'utilizzo di Spanner

Spanner fornisce un insieme di librerie client per vari linguaggi e la possibilità di leggere e scrivere dati utilizzando chiamate API specifiche di Spanner, nonché utilizzando query SQL e istruzioni Data Modification Language (DML). L'utilizzo delle chiamate API potrebbe essere più veloce per alcune query, ad esempio le letture di righe dirette per chiave, perché 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 sopra devono essere implementate nell'applicazione. Ad esempio, nell'applicazione deve essere implementato un attivatore per verificare i valori dei dati e aggiornare una tabella correlata 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, se stai leggendo una versione coerente dei dati:

  • in un momento esatto nel passato (fino a 1 ora fa).
  • in futuro (la lettura verrà bloccata fino a quel momento).
  • con un livello accettabile di inattualità limitata, che restituirà una visualizzazione coerente fino a un determinato momento nel passato senza dover verificare che i dati più recenti siano disponibili su un'altra replica. Ciò può offrire vantaggi in termini di prestazioni a scapito di dati potenzialmente obsoleti.

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 dei 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 formattati. In alternativa, puoi utilizzare l'istruzione SQL SELECT ... INTO OUTFILE per creare file CSV per ogni tabella. Tuttavia, questo approccio ha lo svantaggio di poter esportare una sola tabella alla volta, il che significa che devi mettere in pausa l'applicazione o mettere in stato di attesa 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 di database probabilmente differiscono tra MySQL e Spanner, potrebbe essere necessario includere alcune conversioni di dati nella procedura di importazione. Il modo più semplice per eseguire queste conversioni dei dati e importarli in Spanner è utilizzare Dataflow. Dataflow è il Google Cloud servizio ETL (estrazione, trasformazione e caricamento) distribuito. Fornisce una piattaforma per l'esecuzione di pipeline di dati scritte utilizzando l'SDK Apache Beam per leggere ed elaborare grandi quantità di dati in parallelo su più macchine.

L'SDK Apache Beam richiede di scrivere 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 degli stessi dati.

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

Se utilizzi tabelle interlacciate principali-secondarie nello schema Spanner, assicurati che la riga principale venga creata prima della riga secondaria durante il processo di importazione. Il codice della pipeline di importazione di Spanner gestisce questo problema importando prima tutti i dati per le tabelle di primo livello, poi tutte le tabella secondarie di primo livello, poi tutte le tabelle secondarie di secondo livello e così via.

Puoi utilizzare direttamente la pipeline di importazione Spanner per importare collettivamente i dati, ma questo approccio richiede che i dati esistano in file Avro che utilizzano 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 per esportare e importare i dati. Pertanto, durante il trasferimento dei dati a Spanner, la tua applicazione continua a modificare il database esistente. Pertanto, è necessario duplicare gli aggiornamenti al database Spanner durante l'esecuzione dell'applicazione.

Esistono vari metodi per mantenere sincronizzati i due database, tra cui la cattura dei dati di modifica 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 i log bin di MySQL e convertirli in uno stream CDC. Ad esempio, il demone di Maxwell può fornire uno stream CDC per il tuo database.

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

Aggiornamenti simultanei di entrambi i database dall'applicazione

Un metodo alternativo consiste nel modificare l'applicazione in modo da eseguire scritture in entrambi i database. Un database (inizialmente MySQL) viene considerato la fonte di verità e, dopo ogni scrittura nel 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 cambiare l'origine attendibile nel database Spanner. Questo meccanismo fornisce un percorso di rollback se vengono rilevati problemi durante il passaggio a Spanner.

Verifica la coerenza dei dati

Quando i dati vengono inseriti nel database Spanner, puoi eseguire periodicamente un confronto tra i dati di Spanner e quelli di 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 Join. Questa trasformazione prende due set di dati con chiave e abbina i valori in base alla chiave. I valori corrispondenti possono quindi essere confrontati per verificare se sono uguali. Puoi eseguire regolarmente questa verifica finché il livello di coerenza non corrisponde ai requisiti della tua attività.

Passaggio 6: passa a Spanner come origine attendibile della tua applicazione

Quando hai la certezza che la migrazione dei dati sia andata a buon fine, puoi impostare la tua applicazione in modo che utilizzi Spanner come origine di riferimento. Se continui a scrivere le modifiche nel database MySQL, il database MySQL viene mantenuto aggiornato, fornendo un percorso di rollback in caso di problemi.

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

Esportare e importare 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 avere vari scopi, tra cui:

  • Esegui il backup del database per la conformità alle norme sulla conservazione dei dati o per il recupero di emergenza.
  • Importazione del file Avro in altre Google Cloud offerte come BigQuery.

Per ulteriori informazioni sulla procedura di esportazione e importazione, consulta Esportare i database e Importare i database.

Passaggi successivi