0

We want to create a Cloud SQL replica of an external MySQL database. I have tried the steps in Replicating from an external server, but I ended up with a read-only replica.

I tried creating a new MySQL instance, then when I tried loading the dump produced by mysqldump --master-data=1, I got the following error:

Import failed: Import error: exit status 1 ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Line 18 is:

SET @@SESSION.SQL_LOG_BIN= 0;

According to the manual you need SUPER privilege to set sql_log_bin.

I need to create a second schema on the replica that needs to be writable. Is there a way to set up replication into a non-read-only Cloud SQL MySQL instance where I can create another schema?

Maxim
  • 4,075
  • 1
  • 14
  • 23
Carlos A. Ibarra
  • 6,002
  • 1
  • 28
  • 38
  • 1
    A master to master configuration is not possible in Cloud SQL. See [here](https://stackoverflow.com/questions/47204223/google-cloud-mysql-and-master-master-replication) or [here](https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/google-cloud-sql-discuss/BrivbTEi_NI/Vb1oJyMVAAAJ). The only other type of replicas Cloud SQL has are [failover replicas](https://cloud.google.com/sql/docs/mysql/high-availability), but I don't think that's what you're looking for. – Maxim Jan 04 '20 at 19:39
  • 3
    When replicating an external MySQL database to Cloud SQL, you have a read only replica. You can break the replica and promote to a master. If you want read-write then you will need to set up your own MySQL server on a VM. Cloud SQL does not support what you want to do. Note: it is possible to manually enable write for a replica. Make sure you understand the implications and know how to exclude tables from replication (on your own MySQL instance and not Cloud SQL). – John Hanley Jan 04 '20 at 23:27
  • 1
    @JohnHanley, thanks. I am going the route of setting up MySQL on a VM. You should make this comment an answer and I will accept it. – Carlos A. Ibarra Jan 05 '20 at 11:51

0 Answers0