Is it possible to select databases? - Database migration service

[MiloRo1ey]

Is it possible to move a subset of databases using Google's Database Migration Service (DMS)?

We are not interested in migrating the entire database at once. We won’t be moving databases to the cloud in large batches, but rather individually.

The source database is MySQL and destination is Cloud SQL.

As I read it in the documentation, it is not possible. Are there any workarounds for this?

Thanks in advance!

[ms4446 · Accepted solution]

Yes, while DMS doesn't directly support selective database migration within a MySQL instance, there are effective workarounds:

Custom Migration Script with mysqldump

• Backup Specific Databases: Use mysqldump with recommended flags for consistency and include routines:

  mysqldump --single-transaction --set-gtid-purged=OFF --routines --databases db1 db2 > specific_dbs.sql
  

• Import into Cloud SQL: Import the dump to your Cloud SQL instance:

  mysql -h [CLOUD_SQL_IP] -u [USER] -p [CLOUD_SQL_INSTANCE_NAME] < specific_dbs.sql
  

  This approach works well for smaller databases and when some downtime is acceptable.

Staged Migration

• Temporary MySQL Instance: Set up a temporary MySQL instance containing only the databases you want to migrate. This might involve configuring replication filters or using tools like pt-table-checksum to ensure only the desired data is synchronized.

• Replicate Select Databases: Replicate only the desired databases to this temporary instance.

• Migrate with DMS: Use DMS to migrate from the temporary instance to Cloud SQL.

  Important Considerations:

  • Additional costs for the temporary instance.
  • Added complexity in replication management.
  • Careful planning needed for cutover timing.

Direct Replication

• Prerequisites:

  • Configure binary logging on the source MySQL server by enabling log_bin and setting a unique server_id. This is typically done in the source MySQL server's configuration file, which might be my.cnf or a similar file depending on the environment.
  • Establish secure network connectivity between the source and Cloud SQL (VPN/Cloud SQL proxy/private IP).

• MySQL Replication Configuration in Cloud SQL:

  • Use the gcloud command-line tool: You can use the gcloud sql instances patch command to set the replicate-do-db flags for each database you want to replicate. For example, to replicate database1 and database2, you would use these commands:

    gcloud sql instances patch [INSTANCE_NAME] --database-flags replicate-do-db=database1
    gcloud sql instances patch [INSTANCE_NAME] --database-flags replicate-do-db=database2
    

  • Use the Cloud SQL console: Alternatively, you can set these flags through the Cloud SQL console in the Instance settings under Configuration flags.

• Establish Replication: Execute a CHANGE MASTER TO statement on the Cloud SQL instance to initiate replication from the source. This statement will include connection details to the source and binary log coordinates.

  This method is suitable for larger databases needing minimal downtime.

Recommended Approaches:

• Factors to Consider: When choosing an approach, consider database size, network bandwidth, downtime tolerance, data change frequency, and complexity.
  • mysqldump is generally suitable for smaller databases, less frequent data changes, and when some downtime is acceptable.
  • Direct replication is often preferred for larger databases, high-frequency data changes, and minimal downtime requirements.

Critical Considerations:

• Storage: Ensure sufficient storage capacity in Cloud SQL.
• Monitoring: Monitor replication lag during migration.
• Dependencies: Evaluate database dependencies across different databases.
• Testing: Test thoroughly with non-critical data first.
• Network: Plan for secure network connectivity and security requirements.
• Performance: Consider the impact on source database performance during migration.

I've made the following key changes:

• Removed all references to editing my.cnf in Cloud SQL.
• Clarified that my.cnf configuration applies to the source MySQL server.
• Provided the correct methods for setting replicate-do-db flags in Cloud SQL using the gcloud tool and the Cloud SQL console.

[ms4446 · Accepted solution]

Yes, while DMS doesn't directly support selective database migration within a MySQL instance, there are effective workarounds:

Custom Migration Script with mysqldump

• Backup Specific Databases: Use mysqldump with recommended flags for consistency and include routines:

  mysqldump --single-transaction --set-gtid-purged=OFF --routines --databases db1 db2 > specific_dbs.sql
  

• Import into Cloud SQL: Import the dump to your Cloud SQL instance:

  mysql -h [CLOUD_SQL_IP] -u [USER] -p [CLOUD_SQL_INSTANCE_NAME] < specific_dbs.sql
  

  This approach works well for smaller databases and when some downtime is acceptable.

Staged Migration

• Temporary MySQL Instance: Set up a temporary MySQL instance containing only the databases you want to migrate. This might involve configuring replication filters or using tools like pt-table-checksum to ensure only the desired data is synchronized.

• Replicate Select Databases: Replicate only the desired databases to this temporary instance.

• Migrate with DMS: Use DMS to migrate from the temporary instance to Cloud SQL.

  Important Considerations:

  • Additional costs for the temporary instance.
  • Added complexity in replication management.
  • Careful planning needed for cutover timing.

Direct Replication

• Prerequisites:

  • Configure binary logging on the source MySQL server by enabling log_bin and setting a unique server_id. This is typically done in the source MySQL server's configuration file, which might be my.cnf or a similar file depending on the environment.
  • Establish secure network connectivity between the source and Cloud SQL (VPN/Cloud SQL proxy/private IP).

• MySQL Replication Configuration in Cloud SQL:

  • Use the gcloud command-line tool: You can use the gcloud sql instances patch command to set the replicate-do-db flags for each database you want to replicate. For example, to replicate database1 and database2, you would use these commands:

    gcloud sql instances patch [INSTANCE_NAME] --database-flags replicate-do-db=database1
    gcloud sql instances patch [INSTANCE_NAME] --database-flags replicate-do-db=database2
    

  • Use the Cloud SQL console: Alternatively, you can set these flags through the Cloud SQL console in the Instance settings under Configuration flags.

• Establish Replication: Execute a CHANGE MASTER TO statement on the Cloud SQL instance to initiate replication from the source. This statement will include connection details to the source and binary log coordinates.

  This method is suitable for larger databases needing minimal downtime.

Recommended Approaches:

• Factors to Consider: When choosing an approach, consider database size, network bandwidth, downtime tolerance, data change frequency, and complexity.
  • mysqldump is generally suitable for smaller databases, less frequent data changes, and when some downtime is acceptable.
  • Direct replication is often preferred for larger databases, high-frequency data changes, and minimal downtime requirements.

Critical Considerations:

• Storage: Ensure sufficient storage capacity in Cloud SQL.
• Monitoring: Monitor replication lag during migration.
• Dependencies: Evaluate database dependencies across different databases.
• Testing: Test thoroughly with non-critical data first.
• Network: Plan for secure network connectivity and security requirements.
• Performance: Consider the impact on source database performance during migration.

I've made the following key changes:

• Removed all references to editing my.cnf in Cloud SQL.
• Clarified that my.cnf configuration applies to the source MySQL server.
• Provided the correct methods for setting replicate-do-db flags in Cloud SQL using the gcloud tool and the Cloud SQL console.

[-Rhett · Accepted solution]

Hi @MiloRo1ey,

Just wanted to add to what @ms4446 mentioned about using a custom Migration Script with mysqldump. There’s a Google Blog entry you could check out with more details and steps on how to implement the workaround.

We’ve also been getting feature requests for this functionality to be added to Cloud SQL. For now, I’d recommend creating a Feature Request ticket on our issue tracker with the details of your use case. Do note that there isn’t a specific timeframe for issue resolution. Instead, we wait for a feature to have a handful of stars and, hopefully, comments from several users about how the feature would be useful.

If you need more assistance, here’s how to get support for Cloud SQL or Database Migration Service.

I hope this helps!