Configuring External Replicas

MySQL | PostgreSQL

This page describes how to configure a Cloud SQL instance that replicates to one or more replicas external to Cloud SQL.

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

Before you begin

Before you start this task, you should have a Cloud SQL master 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 Platform 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.

When the Cloud Shell finishes initializing, you should see:
```
Welcome to Cloud Shell! For help, visit https://cloud.google.com/cloud-shell/help.
username@example-id:~$
```
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:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.25-google-log (Google)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

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.

Do not export the mysql database. For instructions, see Exporting Data from Cloud SQL.
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.

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.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.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 June 1, 2017.