Use a managed import to set up replication from external databases

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

You must complete all the steps on this page. When finished, you can administer and monitor the source representation instance the same way as you would 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 to accept connections from any host ( % ). Update this user account so 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.

  
UPDATE  
mysql.user  
SET  
 Host 
 = 
 ' NEW_HOST 
' 
  
WHERE  
 Host 
 = 
 ' OLD_HOST 
' 
  
AND  
 User 
 = 
 ' USERNAME 
' 
 ; 
  
GRANT  
REPLICATION  
SLAVE,  
EXECUTE,  
SELECT,  
SHOW  
VIEW,  
REPLICATION_CLIENT,  
RELOAD  
ON  
 . 
  
TO  
 ' USERNAME 
' 
@ ' HOST 
' 
 ; 
  
FLUSH  
PRIVILEGES ; 

  
UPDATE  
mysql.user  
SET  
 Host 
 = 
 ' NEW_HOST 
' 
  
WHERE  
 Host 
 = 
 ' OLD_HOST 
' 
  
AND  
 User 
 = 
 ' USERNAME 
' 
 ; 
  
GRANT  
REPLICATION  
SLAVE,  
EXECUTE,  
SELECT,  
SHOW  
VIEW,  
REPLICATION  
CLIENT,  
RELOAD  
ON  
 . 
  
TO  
 ' GCP_USERNAME 
' 
@ ' HOST 
' 
 ; 
  
FLUSH  
PRIVILEGES ; 

  UPDATE 
  
 mysql 
 . 
 user 
  
 SET 
  
 Host 
 = 
 '192.0.2.0' 
  
 WHERE 
  
 Host 
 = 
 '%' 
  
 AND 
  
 User 
 = 
 'replicationUser' 
 ; 
 GRANT 
  
 REPLICATION 
  
 SLAVE 
 , 
  
 EXECUTE 
 , 
  
 SELECT 
 , 
  
 SHOW 
  
 VIEW 
 , 
  
 REPLICATION 
  
 CLIENT 
 , 
 RELOAD 
  
 ON 
  
 * 
 . 
 * 
  
 TO 
  
 'username' 
 @ 
 'host.com' 
 ; 
 FLUSH 
  
 PRIVILEGES 
 ; 
 

Verify your replication settings

After your setup is complete, ensure that the Cloud SQL replica can 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 an RDS external server and are using a Google Cloud bucket

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

  gcloud auth login 
 ACCESS_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 
 

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

  gcloud 
  
 auth 
  
 login 
 ACCESS_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" 
 } 
 ] 
  
 } 
  
 }' 
  
 \ 
  
 - 
 X 
  
 POST 
  
 \ 
  
 https 
 : 
 // 
 sqladmin 
 . 
 googleapis 
 . 
 com 
 / 
 sql 
 / 
 v1beta4 
 / 
 projects 
 / 
 MyProject 
 / 
 instances 
 / 
 replica 
 - 
 instance 
 / 
 verifyExternalSyncSettings 
 

These calls return a list of type sql#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 external server, as might be the case with Amazon RDS and Amazon Aurora, pause writes to your server as described in the following steps:

1. Go to the Logs 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 in Start 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 
    ): 
     
    Start 
     
    importing 
     
    data 
    , 
     
    please 
     
    pause 
     
    any 
     
    write 
     
    to 
     
    the 
     
    external 
     
    primary 
     
    database 
    . 
    
   

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

      
    DUMP_IMPORT 
    ( 
    SYNC 
    ): 
     
    Consistent 
     
    state 
     
    on 
     
    primary 
     
    and 
     
    replica 
    . 
     
    Writes 
     
    to 
     
    the 
     
    external 
     
    primary 
     
    may 
     
    resume 
    . 
    
   

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 any DDL operations on the external server. Doing so could cause inconsistencies during the import. After the import process completes, the replica uses the binary logs on the external server to catch up to the current state of the external server.

  gcloud auth login 
 ACCESS_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 
 

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

  gcloud 
  
 auth 
  
 login 
 ACCESS_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" 
 } 
 ] 
  
 } 
  
 }' 
  
 \ 
  
 - 
 X 
  
 POST 
  
 \ 
  
 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 the MySQL public docs .

Monitor the migration

Once you start replication from the external server, you need to monitor replication. To learn more, see Monitoring 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 you verify your replication settings , logs are produced.

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 the Instance dropdown.
3. Select the replication-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's outgoing IP address .
• Your SSL/TLS configuration is correct.
• Your replication user, host, and password are correct.

What's next

• Learn about updating an instance .
• Learn about managing replicas .
• Learn about monitoring instances .
• Learn about promoting your Cloud SQL replica .