This page offers an overview of how to migrate your Online TransactionalProcessing (OLTP) database from MySQL toSpanner.The process to migrate to Spanner might vary depending on factorslike data size, downtime requirements, application code complexity, shardingschema, custom functions, and failover and replication strategies.

The Spanner migration is broken down into the following steps:

1. Assess your migration.
2. Migrate your schema, and translate any SQL queries.
3. Migrate your application to use Spanner in addition to MySQL.
4. Load sample data and optimize your performance.
5. Migrate your data.
6. Validate the migration.
7. Configure cutover and fallback mechanisms.

Assess your migration

Assessing a migration from your source MySQL database to Spannerrequires evaluating your business, technical, operational, and financial needs.For more information, seeAssess your migration.

Migrate your schema

You convert your existing schema to a Spannerschema using Spannermigration tool.

For more information, seeMigrate schema from MySQL overview.

Migrate your application to use Spanner

Spanner provides a set ofClient librariesfor various languages, and the ability to read and write data usingSpanner-specific API calls, as well as by usingSQL queriesandData Modification Language (DML)statements. Using API calls might be faster for some queries, such as direct rowreads by key, because the SQL statement doesn't have to be translated.

Spanner provides aJDBC driverfor Java applications.

As part of the migration process, features not available inSpanner as mentioned previously must be implemented in theapplication. For example, a trigger to verify data values and update a relatedtable would need to be implemented in the application using a read or writetransaction to read the existing row, verify the constraint, then write theupdated rows to both tables.

Spanner offersread or write and read-only transactionswhich ensure external consistency of your data. Additionally, read transactionscan haveTimestamp boundsapplied, where you are reading a consistent version of the data either:

• at an exact time in the past (up to 1 hour ago).
• in the future (where the read will block until that time arrives).
• with an acceptable amount of bounded staleness, which will return aconsistent view up to some time in the past without needing to check thatlater data is available on another replica. This can give performancebenefits at the expense of possibly stale data.

Load sample data to Spanner

You can load sample data in to Spannerbefore performing a complete data migration to test schemas, queries, and yourapplication.

You can use theBigQuery reverse ETLworkflow and theGoogle Cloud CLI to load a small amount of data in theCSV file format in to Spanner.

For more information, seeLoad sample data.

To transfer your data from MySQL to Spanner, you can alsoexport your MySQL database to a portable file format—for example,XML—and then import that data into Spanner usingDataflow.

Migrate data to Spanner

After optimizing your Spanner schema and loading sample data, youcan move your data into an empty production-sized Spanner database.

For more information, seeLive data migration from MySQL.

Validate your data migration

As data streams into your Spanner database, you can periodicallyrun a comparison between your Spanner data and your MySQL datato make sure that the data is consistent. You can validate consistency byquerying both data sources and comparing the results.

You can use Dataflow to perform a detailed comparison over largedata sets by usingjoin transform.This transform takes 2 keyed data sets, and matches the values by key. Thematched values can then be compared for equality. You can regularly run thisverification until the level of consistency matches your business requirements.

For more information, seeValidate your data migration.

Configure cutover and fallback mechanisms

You can set up cutover and fallback for MySQL using reverse replication. Cutoverand fallback means you have a contingency plan of reverting to your sourceMySQL database if you encounter issues with Spanner.

Reverse replication is useful when you encounter unforeseen issues withSpanner and need to fall back to the original MySQL database withminimum disruption to the service. Reverse replication enables fall back byreplicating data written on Spanner back on your source MySQLdatabase.

The reverse replication flow involves the following steps, performed by theSpanner to SourceDB Dataflow template:

1. Read changes from Spanner usingSpanner change streams.

2. Filter the forward-migrated changes.

3. Transform Spanner data to be compatible with your source databaseschema.

4. Verify whether the source database already contains more recent data for thespecified primary key.

5. Write the data to your source database.

What's next

• Best practices in schema design.
• Optimize your Spanner schema.
• Learn how to useDataflowfor more complex situations.