This page describes how to configure your external server for replication toCloud SQL, create a source representation instance on Cloud SQL, andreplicate the data to Cloud SQL. You need to go through all the steps onthis page before proceeding to the replication steps.

An alternative to the steps described on this page is theDatabase Migration Service, which offerscontinuous replication or one-time database migration from an externalserver to Cloud SQL.

Before you begin

Terminology

• External server. The MySQL server external to Cloud SQL thatyou want to replicate data from. It's also referred to as the sourcedatabase or the external database server. It can be another Cloud SQLinstance or any other database server, such as on-premises, Amazon RelationalDatabase Service (RDS), and so on.

• Source representation instance. A mock of a Cloud SQL instancethat represents the external server to the Cloud SQL replica.It's visible in the Google Cloud console and appears like a regularCloud SQL instance, but it doesn't contain data, requireconfiguration or maintenance, or affect billing.

• Cloud SQL replica. The Cloud SQL instance that replicatesfrom the external server. Also known as the external primaryread replica.

• Replication user account. The MySQL user account on the externalserver with sufficient permissions to allow replication between theexternal server and the Cloud SQL replica.

• Managed import. The process of importing data directly from the externalserver to the Cloud SQL replica. In this situation, Cloud SQLconnects to the external server using the replication user account andruns the data dump directly on the external server to import data to theCloud SQL replica.

Set up a Google Cloud project

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin API.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the API

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin API.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the API

1. Make sure you have the Cloud SQL Admin, Storage Admin, and Compute Viewer roles on your user account.

   Go to the IAM page

Install the Google Cloud SDK

To configure replication,install Google Cloud SDKfor your external server. You might want to install the SDK on yourexternal server unless it's already installed elsewhere.

Set up the external server for replication

External server checklist

• Ensure that MySQL 5.7, 8.0, or 8.4 is installed on your external server.MySQL Community Edition, Cloud SQL for MySQL, Amazon Aurora, MySQLon Amazon RDS, and Microsoft Azure Database for MySQL Flexible Server aresupported.

  • You must use a major version of MySQL on your Cloud SQL replicathat is the same as or greater than the version of MySQL running on yourexternal server.
  • If MySQL 5.5 is installed on the external server, then GTIDisn't supported.

• Ensure thatbinary logs are enabledand that:

  • Row-based binary loggingis used.
  • Binary logs are retainedfor a long enough period to support the database migration. Generallya week is sufficient.

• Take note of whether your external server hasGTID enabled. This affects thesteps you need to take to set up replication.

• Take note of whether your external server has global read-lockpermissions. This affects the steps you need to take to set upreplication.

• If the Cloud SQL replica is enabled with a private IP address because theoutgoing privateIP address isn't static, configure the external server's firewall to allow theinternal IP range allocated for theprivate services access of the VPC network that the Cloud SQLreplica uses as its private network.

  The source database server's firewall must be configured to allow the entireinternal IP range allocated for theprivate service connectionof the VPC network that the Cloud SQL destination instance uses as theprivateNetwork field of itsipConfiguration settings.

  To find theinternal IP range:

  1. In the Google Cloud console, go to theVPC networks page.

     Go to the VPC networks page

  2. Select the VPC network that you want to use.

  3. Click thePrivate service connection tab.

• If your external server containsDEFINER clauses (views,events, triggers, or stored procedures), depending on the ordering of whenthese statements are executed, replication might fail. Learnmore aboutDEFINERusage and potential workarounds in Cloud SQL.

• InnoDB is the only supported storage engine in Cloud SQL. Migratingwith MyISAM might cause data inconsistency and requires data validation. Formore information, seeConverting tables from MyISAM to InnoDBin MySQL documentation.

callmysql.rds_set_configuration('binlog retention ',HOURS);

Create a replication user account

A MySQL user account dedicated to managing replication to the Cloud SQLreplica must exist on your external server. This user account should onlybe used to manage replication. Depending on the migration approach you use, youmight need to grant permissions to the user later.

To add a replication user account, open aterminal on the external server and enter these MySQL commands:

CREATEUSER'USERNAME'@'%'IDENTIFIEDBY'PASSWORD';GRANTSELECT,SHOWVIEWON*.*TO'USERNAME'@'HOST';FLUSHPRIVILEGES;

CREATEUSER'replicationUser'@'%'IDENTIFIEDBY'8*&fwwd';GRANTSELECT,SHOWVIEWON*.*TO'replicationUser'@'%';FLUSHPRIVILEGES;

Set up a source representation instance

The source representation instance references the external server. Itcontains only the request data from the external server. Create the request dataand use it in acurl command that creates the source representation instancein Cloud SQL.

Create the request data

The request data contains basic information about your external serverin JSON format. The request data can be configured for aCloud SQL replica on a public or private networkand should contain this information.

{"name":"SOURCE_NAME","region":"REGION","databaseVersion":"DATABASE_VERSION","onPremisesConfiguration":{"hostPort":"SOURCE_HOST","username":"USERNAME","password":"PASSWORD","caCertificate":"SOURCE_CERT","clientCertificate":"CLIENT_CERT","clientKey":"CLIENT_KEY","sslOption":"SSL_OPTION"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromaCloudSQLmanagedimport//-doesn'tuseSSL/TLS{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"MYSQL_5_7","onPremisesConfiguration":{"hostPort":"192.0.2.0:3306","username":"replicationUser","password":"486#@%*@","sslOption":"REQUIRE"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromafile//-usesSSL/TLS{"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",    "caCertificate": "contentofyourcertificate",    "clientCertificate": "contentofyourcertificate",    "clientKey": "contentofyourclientkey",    "sslOption": "REQUIRE"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromaCloudSQLcustomimport//-doesn'tuseSSL/TLS{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"MYSQL_5_7","onPremisesConfiguration":{"hostPort":"192.0.2.0:3306","username":"replicationUser","password":"486#@%*@","sslOption":"REQUIRE"}}

Create a source representation instance

Before you start this step,create a JSON file that contains your source request data.

Then, to create the source representation instance in Cloud SQL, open aterminal and run the following commands:

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

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

Update a source representation instance

If you update therequest data from the externalserver, you can update the existing source representation instance touse the modified values.

Modify the request data

Update the request data to include any fields that have changed. This includes thehostPort,username,password,caCertificate,clientCertificate, andclientKey fields. After updating the request data, use it in acurl command to update the instance in Cloud SQL.

The following example shows updating theusername andpasswordfields with a different username and password:

{"name":"SOURCE_NAME","region":"REGION","databaseVersion":"DATABASE_VERSION","onPremisesConfiguration":{"username":"NEW_USERNAME","password":"NEW_PASSWORD"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromaCloudSQLmanagedimport//-doesn'tuseSSL/TLS{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"MYSQL_5_7","onPremisesConfiguration":{"username":"newReplicationUser","password":"525#@%*@"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromafile//-usesSSL/TLS{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"MYSQL_5_7","onPremisesConfiguration":{"username":"newReplicationUser","password":"486#@%*@"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromaCloudSQLcustomimport//-doesn'tuseSSL/TLS{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"MYSQL_5_7","onPremisesConfiguration":{"username":"newReplicationUser","password":"486#@%*@"}}

Modify a source representation instance

Before you start this step,create a JSON file that contains your modified request data.

Then, to modify the source representation instance in Cloud SQL, open aterminal and run the following commands:

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_NAME

gcloudauthloginACCESS_TOKEN="$(gcloud auth print-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

Set up a Cloud SQL replica

The Cloud SQL replica eventually contains the data fromthe external server. In this step, you create the requestdata and use it in acurl command that creates theCloud SQL replica in Cloud SQL.

Create the request data

The request data contains basic information about your externalserver and Cloud SQL replica in JSON format. The request data can beconfigured for a Cloud SQL replica on a public or private networkand should contain this information:

{"settings":{"tier":"TIER","dataDiskSizeGb":"DISK_SIZE","ipConfiguration":{"ipv4Enabled":"PUBLIC_IP_STATUS","privateNetwork":"projects/PROJECT_ID/global/networks/NETWORK_NAME"},"availabilityType":"AVAILABILITY_TYPE"},"masterInstanceName":"SOURCE_REPRESENTATION_INSTANCE_NAME","region":"SOURCE_REGION","databaseVersion":"DATABASE_VERSION","name":"REPLICA_NAME"}

{"settings":{"tier":"db-custom-4-15360","dataDiskSizeGb":"100"},"masterInstanceName":"source-instance","region":"us-central1","databaseVersion":"MYSQL_8_0","name":"replica-instance"}

Create the Cloud SQL replica

Before you start this step,create a JSON file that contains your replica request data.Then, to create a Cloud SQL replica, open a Cloud Shell terminal and run these commands:

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

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

Verify your setup

To ensure your instances were set up correctly, go to theCloud SQL Instances page.

You should see your source representation instance and the Cloud SQLreplica, in a listing similar to the following:

Also make sure that you have thecloudsql.instances.migrate permission onthe Cloud SQL replica. This permission is included in thecloudsql.adminorcloudsql.editorIAM roles.

Set the default user password and add other user accounts to the Cloud SQL replica

After you create the Cloud SQL replica, set the password for thedefault user. You can also add other user accounts to the replica, if required.You can't import database user accounts from the external server, butyou can recreate the user accounts on the Cloud SQL replica.Do these activities before you replicate from the external server.

To set the password for the default user, use thegcloud sql users set-password command as follows.

gcloudsqlusersset-passwordroot\--host=%\--instance=INSTANCE_NAME\--password=PASSWORD

ReplaceINSTANCE_NAME with the name of the instance andPASSWORD with your selected password before runningthe command.

Get the Cloud SQL replica's outgoing IP address

You can use the outgoing IP address of the Cloud SQL replica to create asecure connection between the external server and theCloud SQL replica. You won't be charged for this IP address.

gcloudsqlinstancesdescribeREPLICA_NAME--format="default(ipAddresses)"

Allow incoming connections on the external server

The Cloud SQL replica needs to connect to the external serverfor replication to succeed. You must configure the network firewall for yourexternal server to accept connections from theCloud SQL replica'soutgoing IP address if thefollowing conditions apply:

• The external server is behind a firewall or some other networkrestriction.
• Your Cloud SQL replica is using a public IP.

To connect to the Cloud SQL replica, you use the replica'sprimary IP address. This IP address is displayed in the Google Cloud console.

Update the source representation instance to allow replication to the Cloud SQL replica

After you set up the source representation instance for the Cloud SQLreplica, you might need toupdate the source representation instance. For example, thesescenarios require an update to your configurations:

• The host, port, or IP of the external server changes.
• You want to use a different MySQL replication user.
• The password of the MySQL replication user changes.
• The SSL certificates used to securely connect to theexternal server change.

Seed the Cloud SQL replica

There are three options for performing the initial load of data from theexternal server into the Cloud SQL replica:

• A managed import uses a service that extracts data from the externalserver and imports it into the Cloud SQL instance directly. Cloud SQLrecommends this option. You must use this option if your external serverdoesn't use global transaction identifier (GTID) replication. For moreinformation, seeUsing a managed import to set up replication from external databases.
• A dump file requires you to create a dump file of your external server,move it to a Cloud Storage bucket, and import it into Cloud SQL.You can only use this option if your external server uses GTID replication.For more information, seeUsing a dump file to set up replication from external databases.
• A custom import requires you to make a Cloud SQL primary instance,perform a custom data import using your preferred tools, demote it to aCloud SQL replica, and then set up external server replication. Thisworks best for multi-TB database migrations; however, you must use GTIDreplication. For more information, seeUsing a custom import to set up replication from large external databases.

Which option is right for you?

Monitor replication

When the Cloud SQL replica finishes the initial data load, it connects tothe external server and applies all updates that were made after the exportoperation.Confirm your replication status.

It's important to check the replication status before promoting thereplica to a standalone instance. If the replication process isn't successfullycompleted, a promoted replica doesn't have all the changes from yourexternal server.

If replication delay is not trending toward 0,take steps to address it.You might want to check these metrics:/postgresql/external_sync/initial_sync_complete,postgresql/external_sync/max_replica_byte_lag, anddatabase/replication/state. View the list ofCloud SQL metrics.

After the Cloud SQL replica has caught up with the external server andthere's no replication delay on the Cloud SQL replica, connect to yourdatabase. Run the appropriate database commands to make sure that the contentsare as expected when compared with the external server. Retain your externalserver until the necessary validations are done.

Set up a cascading replica

After migration, you can create cascading read replicas under your Cloud SQL replica beforepromoting the Cloud SQL replica.

To create a cascading replica, run the following commands:

Promote the replica

Promote your replica by following these steps:

1. Promote the replica to a primary instance. Note that GTID and binary logs are turned on and thescheduled backups are enabled on the promoted replica.
2. Add read replicas to your instance.
3. Optional:Configure your instance for high availability (HA).To prevent additional downtime, you can enable HA whilesetting up a replica by settingAVAILABILITY_TYPE toREGIONAL.

What's next

• Learn how to use acustom import to set up replication from large externaldatabases.
• Learn how to use adump file to set up replication from external databases.

• Learn how to use amanaged import to set up replication from external databases.