When migrating to a unique model than your supply database, your supply and vacation spot databases might have totally different values for the sql_mode flag. The SQL mode defines what SQL syntax MySQL helps and what forms of information validation checks it performs. For example, the default SQL mode values are totally different between MySQL 5.6 and 5.7. 

Consequently, with the default SQL modes in place, a date like 0000-00-00 could be legitimate in model 5.6 however wouldn’t be legitimate in model 5.7. Moreover, with the default SQL modes, there are modifications to the behavior of GROUP_BY between model 5.6 and model 5.7. Examine to make sure that the values for the sql_mode flag are set appropriately in your vacation spot database.

You may be taught extra about  the sql_mode flag and what the totally different values imply within the MySQL documentation. 

Conditions

Earlier than you’ll be able to proceed with the migration, there are a number of conditions it’s worthwhile to full. We now have a quickstart that exhibits all the steps for migrating your database, however what we need to deal with on this put up is what it’s worthwhile to do to configure your supply database, and we’ll additionally briefly describe organising a connection profile and configuring connectivity.

Configure your supply database

There are a number of steps it’s worthwhile to take to configure your source database. Please observe that relying in your present configuration, a restart in your supply database could also be obligatory to use the required configurations.

Cease DDL write operations

Earlier than you start emigrate information from the supply database to the vacation spot database, you need to cease all Information Definition Language (DDL) write operations, if any are working on the supply. This script can be utilized to confirm whether or not any DDL operations have been executed up to now 24 hours, or if there are any energetic operations in progress.

server_id system variable

One of the essential objects to arrange in your supply database occasion is the server_id system variable. In case you are undecided what your present worth is, you’ll be able to verify by working this in your mysql consumer:

SELECT @@GLOBAL.server_id;

The worth displayed have to be any worth equal or larger than 1. In case you are undecided about methods to configure the server_id, you’ll be able to take a look at this page. Though this worth may be dynamically modified, replication will not be routinely began if you change the variable until you restart your server.

International transaction ID (GTID) logging

The gtid_mode flag controls whether or not or not world transaction ID logging is enabled and what forms of transactions the logs can include. Be sure that gtid_mode is ready to ON or OFF, as ON_PERMISSIVE and OFF_PERMISSIVE usually are not supported with DMS. 

To know which gtid_mode you might have in your supply database run the next command:

SELECT @@GLOBAL.gtid_mode;

If the worth for gtid_mode is ready to ON_PERMISSIVE or OFF_PERMISSIVE, when you find yourself altering it, observe that modifications to the worth can solely be one step at a time. For instance, if gtid_mode is ready to ON_PERMISSIVE, you’ll be able to change it to ON or OFF_PERMISSIVE, however to not OFF in a single step. 

Though the gtid_mode worth may be dynamically modified with out requiring a server reboot, it is suggested that you just change it globally. In any other case, it would solely be legitimate for the session the place the change occurred and it will not have impact if you begin the migration through DMS. You may be taught extra about gtid_mode within the MySQL documentation.

Database person account

The person account that you’re utilizing to hook up with the supply database must have these world privileges:

• EXECUTE
• RELOAD
• REPLICATION CLIENT
• REPLICATION SLAVE
• SELECT
• SHOW VIEW

We advocate that you just create a particular person for the aim of migration, and you’ll quickly depart the entry to this database host as %. Extra info on making a person may be found here.

The password of the person account used to hook up with the supply database should not exceed 32 characters in size. This is a matter particular to MySQLreplication. For extra details about the MySQL person password size limitation, see MySQL Bug #43439.

DEFINER clause

As a result of a MySQL migration job doesn’t migrate user data, sources that include metadata outlined by customers with the DEFINER clause will fail when invoked on the brand new Cloud SQL reproduction, because the customers do not but exist there.

You may determine which DEFINER values exist in your metadata through the use of these queries. Examine if there are entries for both rootpercentlocalhost or customers that do not exist within the goal occasion.

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS;

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES;

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS;

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS;

In case your supply database does include this metadata you are able to do one of many following:

• Replace the DEFINER clause to INVOKER in your supply MySQL occasion previous to organising your migration job.
• Create the customers in your goal Cloud SQL occasion earlier than beginning your migration job.

1. Create a migration job with out beginning it. That’s, select Create as an alternative of Create & Begin.

2. Create the customers out of your supply MySQL occasion in your goal Cloud SQL occasion utilizing the Cloud SQL API or UI.

3. Begin the migration job from the migration job listing or the precise job’s web page.

Binary logging

Allow binary logging in your supply database, and set retention to a minimal of two days. We advocate setting it to 7 days to reduce the chance of misplaced log place. You may be taught extra about binary logging within the MySQL documentation.

InnoDB

All tables, besides tables in system databases, will use the InnoDB storage engine. In the event you want extra details about changing to InnoDB, you’ll be able to reference this documentation on converting tables from MyISAM to InnoDB.

Arrange a connection profile

A connection profile represents all the data it’s worthwhile to join to a knowledge supply. You may create a connection profile by itself or within the context of making a particular migration job. Making a supply connection profile by itself is helpful if the one who has the supply entry info will not be the identical one that creates the migration job. You can even reuse a supply connection profile definition in a number of migration jobs.

Study extra about connection profiles and methods to set them up in the documentation.

Configure connectivity

DMS presents a number of alternative ways you can arrange connectivity between the vacation spot Cloud SQL database and your supply database. 

There are 4 connectivity strategies you’ll be able to select from:

The connectivity method you choose will depend on the type of source database, and whether it resides on-premises, in Google Cloud, or in another cloud provider. For a more in-depth look at connectivity, you can read this blog post.

Additional Assets

Now that you have realized methods to put together your MySQL database for migration, you’ll be able to go to the DMS documentation to get began, or proceed studying by studying these weblog posts:

Try out DMS in the Google Cloud console. It is obtainable at no extra cost for native lift-and-shift migrations to Cloud SQL.