This page describes the process for setting up replication when you have a dumpfile that you created from your external server.

You must complete all the steps on this page. When finished, you can administerand monitor the source representation instance the same way as you would anyother Cloud SQL instance.

Before you begin

Before you start, you should haveconfigured the external server,created thesource representation instance,and set up theCloud SQL replica.

Update permissions for the replication user

The replication user on the external server is configured toaccept connections from any host (%). You should update this user accountso that it can only be used with the Cloud SQL replica.Open a terminal on the external server and enter these commands:

UPDATEmysql.userSETHost='NEW_HOST'WHEREHost='OLD_HOST'ANDUser='USERNAME';GRANTREPLICATIONSLAVE,EXECUTEON*.*TO'GCP_USERNAME'@'HOST';FLUSHPRIVILEGES;

UPDATEmysql.userSETHost='192.0.2.0'WHEREHost='%'ANDUser='replicationUser';GRANTREPLICATIONSLAVE,EXECUTEON*.*TO'gcp_user'@'gmail.com';FLUSHPRIVILEGES;

Verify your replication settings

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

First, ensure that your external sync settings are correct. To do this,use the commands below to verify:

• 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
• A global transaction identifier (GTID) is enabled

Open a terminal and enter these commands to verify external syncsettings are correct:

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"       }' \     -X POST \     https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_INSTANCE/verifyExternalSyncSettings

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

Export your database to a Cloud Storage bucket

You can populate a Cloud SQL replica with amysqldump file located in aCloud Storage bucket. These conditions apply:

• You must use themysqldump utility bundled with MySQL.
• Whilemysqldump is running, do not perform anyDDL operations on the external server.Doing so could cause inconsistencies in the export file.

To export your database to a Cloud Storage bucket, follow these steps:

1. In Google Cloud,create a Cloud Storage bucket.
2. Open a terminal using a client that connects to the external database serverand run the following command.

mysqldump\--host=EXTERNAL_HOST\--port=EXTERNAL_PORT\--user=USERNAME\--password=PASSWORD\--databases=DATABASE_LIST\--hex-blob\SOURCE_DATA\--no-autocommit\--default-character-set=utf8mb4\--single-transaction\--set-gtid-purged=on\ADD_DROP_TABLE\ROUTINES\COMPRESS\GZIP\|gcloudstoragecp-gs://BUCKET/DUMP_FILENAME

mysqldump\--host=192.0.2.1\--port=3306\--user=replicationUser\--password\--databasesguestbookjournal\--hex-blob\--master-data=1\--no-autocommit\--default-character-set=utf8mb4\--single-transaction\--compress\|gzip\|gcloudstoragecp-gs://replica-bucket/external-database.sql.gz

Update the source representation instance with the file path of the Cloud Storage bucket

The source representation instance is a Cloud SQL instance that represents thesource database server to the Cloud SQL replica. It's visible in the Google Cloud console and appears the same as a regular Cloud SQL instance, but it contains no data, requires no configuration or maintenance, and doesn't affect billing.

Thesource.json file contains information about the sourcerepresentation instance.

{"name":"PRIMARY_INSTANCE_NAME","region":"REGION_NAME","databaseVersion":"DB_NAME_AND_VERSION","onPremisesConfiguration":{"hostPort":"IP_ADDRESS_AND_PORT","username":"USERNAME","password":"PASSWORD"},"dumpFilePath":"DUMP_FILE_PATH"}

{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"MYSQL_5_7","onPremisesConfiguration":{"hostPort":"192.0.2.0:3306","username":"replicationUser","password":"486#@%*@"},"dumpFilePath":"gs://replica-bucket/source-database.sql.gz"}

For a list of the full set of parameters that you can use with thesource.json file, seeREST Resource: instances.

TheonPremisesConfiguration parameter contains information that Cloud SQL needs to connect to the on-premises source. For more information about the values of this parameter, seeOnPremisesConfiguration.

After you set up the Cloud SQL replica, you need to update your sourcerepresentation instance with the file path of the Cloud Storage bucket.

gcloudauthloginACCESS_TOKEN="$(gcloudauthprint-access-token)"curl--header"Authorization: Bearer${ACCESS_TOKEN}"\--header'Content-Type: application/json'\--data@JSON_PATH\-XPATCH\https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances/SOURCE_REPRESENTATION_INSTANCE

gcloudauthloginACCESS_TOKEN="$(gcloudauthprint-access-token)"curl--header"Authorization: Bearer${ACCESS_TOKEN}"\--header'Content-Type: application/json'\--data@./source.json\-XPATCH\https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/cloudsql-source-instance

Start replication on the external server

After you have verified that you can replicate fromthe external server, you are ready to perform the replication.

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.

Open a terminal, log in usinggcloud, then enter thecurl command toreplicate from the external server.

gcloudauthloginACCESS_TOKEN="$(gcloudauthprint-access-token)"curl--header"Authorization: Bearer${ACCESS_TOKEN}"\--header'Content-Type: application/json'\--data'{           "syncMode": "SYNC_MODE",           "skipVerification": "SKIP_VERIFICATION"         }'\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_INSTANCE/startExternalSync

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

Clean up your storage

If you replicated from a file in a bucket, you can remove this fileand bucket. See the Cloud Storage documentation forDeleting Objects andDeleting Buckets.

Proceed with replication

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

Troubleshoot

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 topromote the replica to a standalone instance and stop replicating from theexternal server.