Configuring external replicas

MySQL | PostgreSQL | SQL Server

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 master instance in an external replica configuration to reverse the configuration.

For more information about the external replica configuration, see Requirements and Tips for Configuring Replication.

Setting up the external replica configuration

Before you begin

Before you start this task, you should have a Cloud SQL instance and an external MySQL instance that meet the requirements for external replicas.

Configuring the master instance

Go to the Cloud SQL Instances page in the Google Cloud Console.

Go to the Cloud SQL Instances page
Enable access on the master 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 master 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 master instance:
```
gcloud sql connect [MASTER_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 master 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

2nd Gen

On the machine hosting the replica, seed your new external MySQL instance with the export file you created from the master 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-master 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.

1st Gen

On the machine hosting the replica, seed your new external MySQL instance with the export file you created from the master 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-master 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).
Set the server ID on the read replica by adding a line similar to the following example to the my.cnf option file:
```
[mysqld]
server-id=[SERVER_ID]
```
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.
Open the export file and locate the CHANGE MASTER TO MASTER_LOG_FILE command.
Copy and paste that line, minus the initial dashes ("--"), into the mysql client.

Do not press Enter yet.

Before the final semi-colon, insert values for MASTER_HOST, MASTER_USER, and MASTER_PASSWORD.

Your statement should look similar to the following example:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107,
MASTER_HOST='[MASTER_IP_ADDRESS]', MASTER_USER='[REPLICATION_USER]', MASTER_PASSWORD='[REPLICATION_PASSWORD]';

Press Enter to execute the statement.
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 master 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 master 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 master instance.

When the replica is caught up, the SHOW SLAVE STATUS command will show Seconds Behind Master as 0, and the Executed_Gtid_Set value will be identical between the external replica and the Cloud SQL master.
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 to off and update your applications to point to the source database server.

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.

Was this page helpful? Let us know how we did:

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated December 5, 2019.