Live data migration from MySQL

This document explains how to perform a live data migration from a source MySQL instance to Spanner with minimal downtime using Terraform to deploy Dataflow and Datastream.

Once you perform the live data migration and are confident that all your data has been transferred, you've migrated your code and dependencies, and completed testing, you can switch your application to using Spanner instead of your source MySQL database.

You can perform a live data migration after creating your target Spanner database. You need to create a compatible schema on your target database before migrating data.

How it works

The live data migration consists of the following two phases:

  • Backfill migration:

    • During backfill migration, Dataflow reads existing data from your source MySQL database and migrates the data to the target Spanner database. You need to use a bulk migration Dataflow template to move the data from your source MySQL instance to Spanner.
    • When the backfill migration fails to write a row to Spanner, it writes that row to a dead-letter queue directory in a Cloud Storage bucket. You can have the backfill migration retry the writing these rows to Spanner.

  • Change data capture (CDC) migration:

    • This phase runs concurrently with the backfill migration, capturing changes occurring in the source MySQL instance in real time. These changes are then applied to Spanner after the backfill migration is complete.
    • You need to use Datastream to capture changes occurring in your source MySQL instance in real time and write them to a Cloud Storage bucket.
    • After the backfill migration is complete, you need to use Dataflow to move the CDC from the Cloud Storage bucket to Spanner. If Dataflow fails to write a row to Spanner for any reason, it writes that row to a dead-letter queue directory in a different Cloud Storage bucket. The CDC migration automatically retries writing the rows from the dead-letter queue directory to Spanner.

Plan for the live data migration

You need to configure the network infrastructure required for data to flow between your source MySQL instance, Datastream, Dataflow , Cloud Storage buckets, and the target Spanner database. We recommend configuring private network connectivity for a secure migration. Depending on your organization's compliance requirements, you might have to configure public or private network connectivity. For more information about Datastream connectivity, see Network connectivity options.

To plan for the live data migration, you might need your organization's network administrator to perform the following tasks:

  • Use the default VPC or create a new VPC in your project with the following requirements:
    • The source MySQL instance must be available on this VPC. You might need to create an egress firewall rule on this VPC, and an ingress firewall rule on the VPC where the source MySQL instance is located.
    • Datastream, Dataflow, Cloud Storage buckets, and the target Spanner database must be available on this VPC.
    • You must create an allowlist on your source MySQL instance to allow connections from the VPC.
  • Determine and allocate an IP address range in the VPC that Datastream can use.
  • Create a subnetwork in the VPC for Dataflow to use to complete the backfill migration.
  • Create a subnetwork in the VPC for Dataflow to use to complete the CDC migration later.

To perform a live data migration, follow these steps:

  1. Set up CDC migration.
  2. Perform the backfill migration.
  3. Finish the CDC migration after the backfill migration is finished.

Performing the live data migration requires deploying and managing a significant number of resources. Spanner provides two sample Terraform templates for each phase of the live data migration.

The live migration template performs the CDC migration in two phases:

  • Set up CDC migration to a Cloud Storage bucket using Datastream. You can use a Terraform variable to prevent the template from creating the Dataflow jobs.
  • Migrate the CDC to Spanner from the Cloud Storage bucket using Dataflow. You must perform this phase only after the backfill migration Terraform template is finished with the backfill migration.

The backfill migration terraform template performs the backfill migration from your source MySQL instance to Spanner.

Before you begin

  • Ensure Terraform is installed on your local shell.
  • Create a service account to run the live data migration. For more information about creating a service account, see Create service accounts.

  • To ensure that the service account has the necessary permissions to perform live migration, ask your administrator to grant the following IAM roles to the service account on your project:

    For more information about granting roles, see Manage access to projects, folders, and organizations.

    These predefined roles contain the permissions required to perform live migration. To see the exact permissions that are required, expand the Required permissions section:

    Required permissions

    The following permissions are required to perform live migration:

    • compute.globalAddresses.create
    • compute.globalAddresses.createInternal
    • compute.globalAddresses.createInternal
    • compute.globalAddresses.delete
    • compute.globalAddresses.deleteInternal
    • compute.globalAddresses.get
    • compute.globalOperations.get
    • compute.networks.addPeering
    • compute.networks.get
    • compute.networks.listPeeringRoutes
    • compute.networks.removePeering
    • compute.networks.use
    • compute.routes.get
    • compute.routes.list
    • compute.subnetworks.get
    • compute.subnetworks.list
    • dataflow.jobs.cancel
    • dataflow.jobs.create
    • dataflow.jobs.updateContents
    • datastream.connectionProfiles.create
    • datastream.connectionProfiles.delete
    • datastream.privateConnections.create
    • datastream.privateConnections.delete
    • datastream.streams.create
    • datastream.streams.delete
    • datastream.streams.update
    • iam.roles.get
    • iam.serviceAccounts.actAs
    • pubsub.subscriptions.create
    • pubsub.subscriptions.delete