This page describes the process for setting up replication when you have a dump file that you created from your external server.
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 start, you should have configured the external server, created the source representation instance, and set up the Cloud SQL replica.
Update permissions for the replication user
The replication user on the external server is configured to
accept connections from any host (%
). You should update this user account
so that it can only be used with the Cloud SQL replica.
Open a terminal on the external server and enter these commands:
mysql Client
UPDATE mysql.user SET Host='NEW_HOST' WHERE Host='OLD_HOST' AND User='USERNAME'; GRANT REPLICATION SLAVE, EXECUTE ON *.* TO 'GCP_USERNAME'@'HOST'; FLUSH PRIVILEGES;
example
UPDATE mysql.user SET Host='192.0.2.0' WHERE Host='%' AND User='replicationUser'; GRANT REPLICATION SLAVE, EXECUTE ON *.* TO 'gcp_user'@'gmail.com'; FLUSH PRIVILEGES;
Property | Description |
---|---|
NEW_HOST | Specify the outgoing IP of the Cloud SQL replica. |
OLD_HOST | The current value assigned to Host that you want to
change. |
USERNAME | The replication user account on the external server. |
GCP_USERNAME | The username for the Google Cloud Platform (GCP) user account. |
HOST | The hostname for the Google Cloud Platform (GCP) user account. |
Verify your replication settings
After your setup is complete, ensure that the Cloud SQL replica can 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 sync settings are correct:
curl
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"
}' \
-X POST \
https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_INSTANCE/verifyExternalSyncSettings
example
gcloud auth login
ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
--header 'Content-Type: application/json' \
--data '{
"syncMode": "online",
}' \
-X POST \
https://sqladmin.googleapis.com/sql/v1beta4/projects/myproject/instances/myreplica/verifyExternalSyncSettings
Property | Description |
---|---|
SYNC_MODE | verifyExternalSyncSettings verifies that you
can keep the Cloud SQL replica and
external server
in sync after replication is set up. Sync modes
include EXTERNAL_SYNC_MODE_UNSPECIFIED ,
ONLINE , and OFFLINE . |
SKIP_VERIFICATION | Whether or not to skip the built-in verification step before syncing your data. Only recommended if you have already verified your replication settings. |
PROJECT_ID | The ID of your project in Google Cloud. |
REPLICA_INSTANCE | The ID of your Cloud SQL replica. |
Export your database to a Cloud Storage bucket
You can populate a Cloud SQL replica with a mysqldump
file located in a
Cloud Storage bucket. These conditions apply:
- You must use the
mysqldump
utility bundled with MySQL. - While
mysqldump
is running, do not perform any DDL 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:
- In Google Cloud, create a Cloud Storage bucket.
- Open a terminal using a client that connects to the external database server and run the following command.
mysqldump
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 \ | gcloud storage cp - gs://BUCKET/DUMP_FILENAME
example
mysqldump \ --host=192.0.2.1 \ --port=3306 \ --user=replicationUser \ --password \ --databases guestbook journal \ --hex-blob \ --master-data=1 \ --no-autocommit \ --default-character-set=utf8mb4 \ --single-transaction \ --compress \ | gzip \ | gcloud storage cp - gs://replica-bucket/external-database.sql.gz
Property | Description |
---|---|
EXTERNAL_HOST | The IPv4 or DNS address for the external server. |
EXTERNAL_PORT | The port for the external server.
If the external server is hosted on Cloud SQL,
this is 3306 . |
USERNAME | The name of the replication user account or user account on the external server that has database read permissions. |
PASSWORD | Replication user password. |
DATABASE_LIST | Space-separated list of all databases on the external
server, except for the system databases (sys ,
mysql , performance_schema , and
information_schema ). Use the SHOW DATABASES MySQL
command to list your databases. |
SOURCE_DATA | If you're using an earlier version of MySQL than 8.0.26,
then use --master-data as the value for this parameter.
For versions of MySQL that are 8.0.26 or higher,
set the value of this parameter to --source-data . |
ADD_DROP_TABLE | If you want to add a DROP TABLE statement before
each CREATE TABLE statement, include --add-drop-table . |
ROUTINES | If you want to show stored routines, such as
procedures and functions, in the output for dumped
databases, include --routines . |
COMPRESS | If you want to compress all information sent between
the Cloud SQL replica and the external server,
use --compress . |
GZIP | If you want to compress the dump file even more, use
| gzip . If your database contains data that does
not compress well, such as binary incompressible data
or JPG images, don't use this. |
BUCKET | The name of the bucket you created in Step 1 to contain the dump file. |
DUMP_FILENAME | A file with this name is created in your bucket. This file contains the contents of the database on your external server. |
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 the source 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.
The source.json
file contains information about the source
representation instance.
REST
{ "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" }
example
{ "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" }
Property | Description |
---|---|
PRIMARY_INSTANCE_NAME | The name of the Cloud SQL instance that's associated with the source representation instance. |
REGION_NAME | The name of the region that's assigned to the source representation instance. |
DB_NAME_AND_VERSION | The name and version number of the database that's associated with the source representation instance. |
IP_ADDRESS_AND_PORT | The IP address and port number reserved for the source representation instance. |
USERNAME | The username of the source representation instance. |
PASSWORD | The password of the source representation instance. |
DUMP_FILE_PATH | The path of the dump file that contains the contents of the database on your external server. |
After you set up the Cloud SQL replica, you need to update your source representation instance with the file path of the Cloud Storage bucket.
REST
gcloud auth login ACCESS_TOKEN="$(gcloud auth print-access-token)" curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \ --header 'Content-Type: application/json' \ --data @JSON_PATH \ -X PATCH \ https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances/SOURCE_REPRESENTATION_INSTANCE
example
gcloud auth login ACCESS_TOKEN="$(gcloud auth print-access-token)" curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \ --header 'Content-Type: application/json' \ --data @./source.json \ -X PATCH \ https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/cloudsql-source-instance
Property | Description |
---|---|
JSON_PATH | The path of the JSON file that's stored in the Cloud Storage bucket.
This file contains data about the source representation
instance. |
PROJECT_ID | The ID of your project in Google Cloud. |
SOURCE_REPRESENTATION_INSTANCE | The name of the source representation instance. |
Start replication on the external server
After you have verified that you can replicate from the external server, you are ready to perform the replication.
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.
Open a terminal, log in using gcloud
, then enter the curl
command to
replicate from the external server.
REST
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" }' \ -X POST \ https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_INSTANCE/startExternalSync
example
gcloud auth login ACCESS_TOKEN="$(gcloud auth print-access-token)" curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \ --header 'Content-Type: application/json' \ --data '{ "syncMode": "online", "skipVerification": false }' \ -X POST \ https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/replica-instance/startExternalSync
Property | Description |
---|---|
SYNC_MODE | Verifies that you can keep the Cloud SQL replica and external server in sync after replication is set up. |
SKIP_VERIFICATION | Whether or not to skip the built-in verification step before syncing your data. Only recommended if you have already verified your replication settings. |
PROJECT_ID | The ID of your project in Google Cloud. |
REPLICA_INSTANCE | The ID of your Cloud SQL replica. |
Clean up your storage
If you replicated from a file in a bucket, you can remove this file and bucket. See the Cloud Storage documentation for Deleting Objects and Deleting Buckets.
Proceed with replication
Once you start replication from the external server, you need to monitor replication and then complete your migration. To learn more, see Monitoring replication.
Troubleshoot
Issue | Troubleshooting |
---|---|
Lost connection to MySQL server during query when dumping table . |
The source may have become unavailable, or the dump contained packets
too large.
Make sure the external primary is available to connect. You can also modify the values of the net_read_timeout and net_write_timeout flags on the source instance to stop the error. For more information on the allowable values for these flags, see Configure database flags. To learn more about using |
The initial data migration was successful, but no data is being replicated. | One possible root cause could be your source database has defined
replication flags which result in some or all database changes not being
replicated over.
Make sure the replication flags such as Run the command |
The initial data migration was successful but data replication stops working after a while. | Things to try:
|
mysqld check failed: data disk is full . |
The data disk of the replica instance is full.
Increase the disk size of the replica instance. You can either manually increase the disk size or enable auto storage increase. |
Review your replication logs
When you verify your replication settings, logs are produced.
You can view these logs by following these steps:
Go to the Logs Viewer in the Google Cloud console.
- Select the Cloud SQL replica from the Instance dropdown.
- 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 to promote the replica to a standalone instance and stop replicating from the external server.