This page describes how to configure a Cloud SQL instance that replicates to one or more replicas external to Cloud SQL, and how to demote the primary instance in an external replica configuration to reverse the configuration.
For more information about the external replica configuration, see About external replication.
Setting up the external replica configuration
Before you begin
Before you start this task, you must have a Cloud SQL instance and an external MySQL instance that meets the requirements for external replicas.
Configuring the primary instance
Go to the Cloud SQL Instances page in the Google Cloud Console.
Enable access on the primary instance for the IP address of the external replica.
For information about enabling IP access, see Configuring access for IP connections.
Record the IP address of the primary instance for later use.
You can find the IP address on the instance's Overview page.
Click the Cloud Shell icon (
) in the upper right corner.
At the Cloud Shell prompt, use the built-in
mysql
client to connect to your primary instance:gcloud sql connect [PRIMARY_INSTANCE_NAME] --user=root
Enter your root password.
You should see the
mysql
prompt.Create a special user for replication:
CREATE USER '[REPLICATION_USER]'@'%' IDENTIFIED BY '[REPLICATION_PASSWORD]';
Grant replication privileges to the replication user:
GRANT REPLICATION SLAVE ON *.* TO '[REPLICATION_USER]'@'%';
Export the database from the primary instance to a Cloud Storage bucket.
Follow the external replication instructions in Creating a SQL dump file.
Download the export file from the bucket to the replica by clicking the file in the Cloud Storage console from the machine hosting the replica.
Configuring the external replica
-
On the machine hosting the replica, seed your new external MySQL instance
with the export file you created from the primary instance.
For example, the following command loads the export file called
mysqldump.sql
:mysql --user=root --password < mysqldump.sql
- Determine the server ID for this replica-primary pair.
The server ID is a numeric value (for example, "3") that must be unique across the external replica configuration (each replica must have a unique server ID).
- Add the following options to the replica's
my.cnf
option file:[mysqld] server-id=[SERVER_ID] gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON replicate-ignore-db=mysql binlog-format=ROW log_bin=mysql-bin expire_logs_days=1 read_only=ON
For more information about MySQL replication options, see Replication and Binary Logging Options.
- Restart the
mysqld
process to cause the configuration file to be read. - In a
mysql
client on the replica, enter the following command:CHANGE MASTER TO MASTER_HOST='[MASTER_IP_ADDRESS]', MASTER_USER='[REPLICATION_USER]', MASTER_PASSWORD='[REPLICATION_PASSWORD]', MASTER_AUTO_POSITION=1;
- Start replication on the replica:
START SLAVE;
Confirm replication status:
SHOW SLAVE STATUS\G;
If you see "Waiting for master to send event", replication is working.
Demoting the primary instance of an external replica
When you have a Cloud SQL instance with an external replica, you can reverse the configuration, which causes these changes to happen:
- The external replica becomes the new primary instance.
- The Cloud SQL instance becomes a read replica, replicating from the server that was previously the external replica (now called the source database server).
To reverse the external replica configuration:
-
Create a source representation instance.
This instance will represent the source database server to the Cloud SQL replica after the demotion operation completes.
gcloud auth login ACCESS_TOKEN="$(gcloud auth print-access-token)" curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \ --header 'Content-Type: application/json' \ --data '{ "name": "[SOURCE_REPRESENTATION_NAME]", "region": "[REGION]", "databaseVersion": "[EXTERNAL_SERVER_MYSQL_VERSION]", "onPremisesConfiguration": { "hostPort": "[EXTERNAL_SERVER_IP]:[EXTERNAL_SERVER_PORT]" } }' \ -X POST \ https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances
Use the region where you want your Cloud SQL replica to reside.
Start the demotion process.
Because this API call requires you to provide sensitive information, you should use a JSON file to provide your data to cURL, rather than providing it on the command line.
Create the data file:
{ "demoteMasterContext": { "replicaConfiguration": { "mysqlReplicaConfiguration": { "username": "[MYSQL_REPLICATION_USERNAME]", "password": "[PASSWORD]", "caCertificate": "[EXTERNAL_SERVER_CA]", "clientCertificate": "[CLIENT_CERT]", "clientKey": "[PRIVATE_KEY]" } }, "masterInstanceName": "[SOURCE_REPRESENTATION_NAME]", }, }
Then, call the API.
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \ --header 'Content-Type: application/json' \ --data @[PATH_TO_DATA_FILE] \ https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/demoteMaster
For more information about your options for SSL/TLS, see SSL/TLS options. For more information about the properties used by the
replicaConfiguration
object, see Replicating from an External Server.-
Wait for the external replica to complete all pending transactions from the primary instance.
When the replica is caught up, the
SHOW SLAVE STATUS
command will showSeconds Behind Master
as 0, and theExecuted_Gtid_Set
value will be identical between the external replica and the Cloud SQL primary. Use the
mysql
client to stop replication on the external replica:STOP SLAVE RESET SLAVE ALL
Wait for the Cloud SQL instance to start replicating from the external server, which is now the source database server.
Running the
SHOW SLAVE STATUS
command on the Cloud SQL instance provides replication status.-
When the Cloud SQL instance is successfully replicating from the
source database server, set the
read_only
flag on the source database server tooff
and update your applications to point to the source database server.
Troubleshooting
Click the links in the table for details:
For this problem... | The issue might be... | Try this... |
---|---|---|
Error message: The slave is connecting ... master has purged
binary logs containing GTIDs that the slave requires . |
The replica doesn't know where to start reading the logs from. | Create a new dump file with the correct flag settings and configure the external replica using that file. |
Purged binary logs containing GTIDs
You see the error message: The slave is connecting ... master has purged
binary logs containing GTIDs that the slave requires
in MySQL.
The issue might be
The primary Cloud SQL instance has automatic backups and binary logs and point-in-time recovery is enabled, so it should have enough logs for the replica to be able to catch up. However, in this case although the binary logs exist, the replica doesn't know which row to start reading from.
Things to try
Create a new dump file using the correct flag settings, and configure the external replica using that file.
- Connect to your mysql client through a Compute Engine instance.
- Run
mysqldump and use the
--master-data=1
and--flush-privileges
flags.Important: Do not include the
--set-gtid-purged=OFF
flag. - Ensure that the dump file just created contains the
SET @@GLOBAL.GTID_PURGED='...'
line. - Upload the dump file to a Cloud Storage bucket and configure the replica using the dump file.
What's next
- Learn how to manage replicas.
- Learn about requirements and best practices for the external replica configuration.
- Learn more about MySQL replication.
- Learn more about replication options.
- Learn more about checking replication status.
- Learn more about replicating from an external server.