This page describes how to set up and use a managed import for data whenreplicating from an external server to Cloud SQL.

You must complete all the steps on this page. When finished, you canadminister and monitor the source representation instance the same way as youwould any other Cloud SQL instance.

Before you begin

Before you begin, complete these steps:

1. Configure the external server.

2. Create the source representation instance.

3. Set up the Cloud SQL replica.

Update privileges for the replication user

The replication user on the external server is configured toaccept connections from any host (%). Update this user accountso that it can be used only with the Cloud SQL replica.

Required privileges

There are four types of combinations of migrations and dumps.

• Type 1: Continuous migration and managed dump
• Type 2: Continuous migration and manual dump
• Type 3: One-time migration and managed dump
• Type 4: One-time migration and manual dump

The privileges for each type of migration and dumb combination are listed below.

Update privileges

To update privileges, open a terminal on the external server and enter the following commands.

UPDATEmysql.userSETHost='NEW_HOST'WHEREHost='OLD_HOST'ANDUser='USERNAME';GRANTREPLICATIONSLAVE,EXECUTE,SELECT,SHOWVIEW,REPLICATIONCLIENT,RELOADON.TO'USERNAME'@'HOST';FLUSHPRIVILEGES;

UPDATEmysql.userSETHost='NEW_HOST'WHEREHost='OLD_HOST'ANDUser='USERNAME';GRANTREPLICATIONSLAVE,EXECUTE,SELECT,SHOWVIEW,REPLICATIONCLIENT,RELOADON.TO'GCP_USERNAME'@'HOST';FLUSHPRIVILEGES;

UPDATEmysql.userSETHost='192.0.2.0'WHEREHost='%'ANDUser='replicationUser';GRANTREPLICATIONSLAVE,EXECUTE,SELECT,SHOWVIEW,REPLICATIONCLIENT,RELOADON*.*TO'username'@'host.com';FLUSHPRIVILEGES;

Verify your replication settings

After your setup is complete, ensure that the Cloud SQL replicacan replicate from the external server.

The following external sync settings must be correct.

• Connectivity between the Cloud SQL replica and external server
• Replication user privileges
• Version compatibility
• The Cloud SQL replica is not already replicating
• Binlogs are enabled on the external server
• GTID is enabled if you are trying to do an external sync from anRDS external server and are using a Google Cloud bucket

To verify these settings, open a Cloud Shell terminal and enter the followingcommands:

gcloud auth loginACCESS_TOKEN="$(gcloud auth print-access-token)"curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \     --header 'Content-Type: application/json' \     --data '{         "syncMode": "SYNC_MODE",         "syncParallelLevel": "SYNC_PARALLEL_LEVEL",         "mysqlSyncConfig": {           "initialSyncFlags": "SYNC_FLAGS"         }       }' \     -X POST \     https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_INSTANCE_ID/verifyExternalSyncSettings

gcloudauthloginACCESS_TOKEN="$(gcloud auth print-access-token)"curl--header "Authorization: Bearer ${ACCESS_TOKEN}" \--header 'Content-Type: application/json' \--data '{"syncMode":"online","syncParallelLevel":"optimal"}'\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/myproject/instances/myreplica/verifyExternalSyncSettings

gcloudauthloginACCESS_TOKEN="$(gcloud auth print-access-token)"curl--header "Authorization: Bearer ${ACCESS_TOKEN}" \--header 'Content-Type: application/json' \--data '{"syncMode":"online","syncParallelLevel":"optimal""mysqlSyncConfig":{"initialSyncFlags":[{"name":"max-allowed-packet","value":"1073741824"},{"name":"hex-blob"},{"name":"compress"}]}}'\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/replica-instance/verifyExternalSyncSettings

These calls return a list of typesql#externalSyncSettingErrorList.

If the list is empty, then there are no errors. A response without errors appears like this:

{"kind":"sql#externalSyncSettingErrorList"}

Global read lock permission

If you don't have permission to access the global read lock on the externalserver, as might be the case with Amazon RDS and Amazon Aurora, pause writesto your server as described in the following steps:

1. Go to theLogs Explorer, and select your Cloud SQL replica from the resource list. You should see a list of the most recent logs for your Cloud SQL replica. Ignore them for now.
2. Open a terminal and enter the commands inStart replication on the external server to replicate from the external server.

3. Return to the Logs Explorer. When you see the log as follows, stop writing to the database on your external server. In most cases, this is required only for a few seconds.

   DUMP_IMPORT(START):Startimportingdata,pleasepauseanywritetotheexternalprimarydatabase.

4. When you see the following log entry in Logs Explorer, re-enable writing to the database on your external server.

   DUMP_IMPORT(SYNC):Consistentstateonprimaryandreplica.Writestotheexternalprimarymayresume.

Start replication on the external server

After verifying that you can replicate from the external server, start the replication. The speed for performing the replication for the initial import process is up to 500 GB per hour. However, this speed can vary based on the machine tier, data disk size, network throughput, and nature of your database.

During the initial import process, do not perform anyDDL operations on the external server.Doing so could cause inconsistencies during the import. After the importprocess completes, the replica uses the binary logs on theexternal server to catch up to the current state of theexternal server.

gcloud auth loginACCESS_TOKEN="$(gcloud auth print-access-token)"curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \     --header 'Content-Type: application/json' \     --data '{         "syncMode": "SYNC_MODE",         "skipVerification": "SKIP_VERIFICATION",         "syncParallelLevel": "SYNC_PARALLEL_LEVEL",         "mysqlSyncConfig": {           "initialSyncFlags": "SYNC_FLAGS"         }       }' \     -X POST \     https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_INSTANCE_ID/startExternalSync

gcloudauthloginACCESS_TOKEN="$(gcloud auth print-access-token)"curl--header "Authorization: Bearer ${ACCESS_TOKEN}" \--header 'Content-Type: application/json' \--data '{"syncMode":"online","syncParallelLevel":"optimal"}'\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/replica-instance/startExternalSync

gcloudauthloginACCESS_TOKEN="$(gcloud auth print-access-token)"curl--header "Authorization: Bearer ${ACCESS_TOKEN}" \--header 'Content-Type: application/json' \--data '{"syncMode":"online","syncParallelLevel":"optimal""skipVerification":false,"mysqlSyncConfig":{"initialSyncFlags":[{"name":"max-allowed-packet","value":"1073741824"},{"name":"hex-blob"},{"name":"compress"}]}}'\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/replica-instance/startExternalSync

Initial sync flags

To migrate with custom database flags, you can use the following allowed flags:

• --add-drop-database
• --add-drop-table
• --add-drop-trigger
• --add-locks
• --allow-keywords
• --all-tablespaces
• --apply-slave-statements
• --column-statistics
• --comments
• --compact
• --compatible
• --complete-insert
• --compress
• --compression-algorithms
• --create-options
• --default-character-set
• --delayed-insert
• --disable-keys
• --dump-date
• --events
• --extended-insert
• --fields-enclosed-by
• --fields-escaped-by
• --fields-optionally-enclosed-by
• --fields-terminated-by
• --flush-logs
• --flush-privileges
• --force
• --get-server-public-key
• --hex-blob
• --ignore-error
• --ignore-read-lock-error
• --ignore-table
• --insert-ignore
• --lines-terminated-by
• --lock-all-tables
• --lock-tables
• --max-allowed-packet
• --net-buffer-length
• --network-timeout
• --no-autocommit
• --no-create-db
• --no-create-info
• --no-data
• --no-defaults
• --no-set-names
• --no-tablespaces
• --opt
• --order-by-primary
• --pipe
• --quote-names
• --quick
• --replace
• --routines
• --secure-auth
• --set-charset
• --shared-memory-base-name
• --show-create-skip-secondary-engine
• --skip-opt
• --ssl-cipher
• --ssl-fips-mode
• --ssl-verify-server-cert
• --tls-ciphersuites
• --tls-version
• --triggers
• --tz-utc
• --verbose
• --xml
• --zstd-compression-level

For allowed values, see theMySQL public docs.

Monitor the migration

Once you start replication from the external server, you need to monitorreplication. To learn more, seeMonitoring replication.You can then complete your migration.

Troubleshoot

Consider the following troubleshooting options:

Additionally, for MySQL, also consider the following options:

Review your replication logs

When youverify your replication settings, logs areproduced.

You can view these logs by following these steps:

1. Go to the Logs Viewer in the Google Cloud console.

   Go to the Logs Viewer

2. Select the Cloud SQL replica from theInstance dropdown.
3. Select thereplication-setup.log log file.

If the Cloud SQL replica is unable to connect to the external server,confirm the following:

• Any firewall on the external server is configured to allow connections from the Cloud SQL replica'soutgoing IP address.
• Your SSL/TLS configuration is correct.
• Your replication user, host, and password are correct.

What's next

• Learn aboutupdating an instance.
• Learn aboutmanaging replicas.
• Learn aboutmonitoring instances.
• Learn aboutpromoting your Cloud SQL replica.