求助：配置Cloud SQL第一代实例与本地MySQL外部主节点

Hey Emmanuel, nice work getting the data dump uploaded to Cloud Storage—you’re already past one of the trickier parts! Let’s break down the remaining steps to get your local MySQL server configured as the external master for your Cloud SQL Gen 1 instance.

1. Configure Replication Permissions on Your Local MySQL #

First, you need a dedicated replication user that Cloud SQL can use to connect and pull binlog data. Log into your local MySQL server and run these commands:

CREATE USER 'cloudsql_repl'@'%' IDENTIFIED BY 'your_strong_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cloudsql_repl'@'%';
FLUSH PRIVILEGES;

• Replace cloudsql_repl with a username of your choice, and your_strong_password with a secure password.
• The % allows connections from any IP (Cloud SQL will connect from its public IPs). If you want tighter security, you can restrict it to Cloud SQL’s specific IP range (find this in your Cloud SQL instance’s "Connections" tab in the GCP Console).

Also, ensure your local MySQL allows external connections:

• Edit your MySQL config file (usually my.cnf or my.ini) and set bind-address = 0.0.0.0 (or your local server’s public IP).
• Restart your MySQL service to apply the change.
• Double-check your local firewall/router settings to open port 3306 for incoming connections from Cloud SQL’s IPs.

2. Capture Local MySQL Master Status #

Before configuring Cloud SQL, grab the exact binlog position it should start replicating from. Run this in your local MySQL:

SHOW MASTER STATUS;

You’ll get output like this:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      123 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Save the File (e.g., mysql-bin.000001) and Position (e.g., 123) values—you’ll need these next.

Note: If you didn’t use --master-data=2 when creating your mysqldump, re-run the dump with that flag while your database is read-locked (run FLUSH TABLES WITH READ LOCK; before dumping, then UNLOCK TABLES; after). This ensures the dump matches the binlog position you just captured.

3. Configure External Master in Cloud SQL #

You can use either the GCP Console or gcloud CLI for this:

Option 1: GCP Console #

1. Go to the Cloud SQL Instances page in the GCP Console.
2. Select your Gen 1 instance.
3. Navigate to the Replication tab.
4. Click Set up external master.
5. Fill in the details:
   • External master IP address: Your local MySQL server’s public IP.
   • Port: 3306 (unless you changed the default MySQL port).
   • Replication username: The user you created earlier (e.g., cloudsql_repl).
   • Replication password: The password for that user.
   • Binary log file: The File value from SHOW MASTER STATUS.
   • Binary log position: The Position value from SHOW MASTER STATUS.
6. Click Save to start the replication setup.

Option 2: gcloud CLI #

Run this command (replace all placeholders with your actual values):

gcloud sql instances patch YOUR_CLOUDSQL_INSTANCE_NAME \
  --master-instance-ip=LOCAL_MYSQL_PUBLIC_IP \
  --master-user=cloudsql_repl \
  --master-password=your_strong_password \
  --master-log-file=mysql-bin.000001 \
  --master-log-position=123

4. Verify Replication is Working #

Once setup is complete, connect to your Cloud SQL instance and run:

SHOW SLAVE STATUS\G

Look for these two fields—both should say Yes:

• Slave_IO_Running (means Cloud SQL is successfully connecting to your local master and pulling binlogs)
• Slave_SQL_Running (means Cloud SQL is applying replicated data correctly)

If either is No, check the Last_IO_Error or Last_SQL_Error fields for details. Common issues include firewall blocks, incorrect credentials, mismatched binlog positions, or local MySQL not having binary logging enabled (confirm log_bin = ON in your my.cnf).

Let me know if you hit specific error messages—I can help troubleshoot further!

内容的提问来源于stack exchange，提问作者Emmanuel Nkansah