Database Migration Service (DMS) supplies high-fidelity, minimal downtime migrations for MySQL (Preview) and PostgreSQL (obtainable in Preview by request) workloads to Cloud SQL. Since DMS is serverless, you do not have to fret about provisioning, managing, or monitoring any migration-specific sources.
On this submit, we’ll deal with what’s and isn’t included in database migration for MySQL, and what you are able to do to make sure migration completeness when utilizing DMS. The supply database’s knowledge, schema, and extra database options (triggers, saved procedures, and extra) are replicated to the Cloud SQL vacation spot reliably, and at scale, with no person intervention required. Because of the peculiarities of MySQL, there are some things that gained’t be migrated, although. Let’s take a look at what’s and is not migrated with DMS in additional element.
What’s included in MySQL database migration
DMS for MySQL makes use of the database’s personal native replication expertise to offer a high-fidelity technique to migrate database objects from one database to a different. The migration fidelity part of the documentation goes into element about what’s included within the migration. On the time of this Preview launch, all the following knowledge, schema, and metadata parts are migrated as a part of the database migration:
All tables from all databases and schemas, excluding the next default databases and schemas:
- Major key
- Knowledge kind
- Ordinal place
- Default worth
- Auto-increment attributes
- Secondary indexes
- Saved procedures
- Overseas key constraints
What’s not included in database migration
Whenever you’re migrating a MySQL database, the MySQL system database, which accommodates details about customers and privileges, is just not migrated. That signifies that person account and login data have to be managed within the vacation spot Cloud SQL occasion straight.
The foundation account will must be arrange earlier than the occasion can be utilized. You’ll be able to add customers to the Cloud SQL vacation spot occasion both from the Customers tab within the UI, or from the mysql consumer. The Cloud SQL documentation accommodates extra details about managing MySQL user accounts.
Utilization of Definer clause
Since a MySQL migration job does not migrate customers knowledge, sources which include metadata defined by users with the
DEFINER clause will fail when invoked on the brand new Cloud SQL reproduction, because the customers do not but exist there.
To run a migration from a supply that features the
- Create a migration job with out beginning it (select Create as a substitute of Create & Begin).
- Create the customers on the brand new Cloud SQL vacation spot occasion utilizing the Cloud SQL API or the Customers tab within the UI.
- Begin the migration job from the migration job record or the particular job’s web page.
Alternatively, you possibly can replace the
DEFINER clause to
INVOKER on the supply previous to establishing the migration job. Be aware that if the metadata was created by
’root’@’localhost’, the method will fail. Change the
DEFINER earlier than beginning the migration job.
Subsequent Steps with DMS
Able to be taught extra about migrating your MySQL or PostgreSQL database to Cloud SQL? These sources will allow you to collect the knowledge it’s essential get began: