Replication from in-house MySQL (master) to Google SQL (slave)

[Mike Dorfman]

I have a setup where data flows from various remote sensing sites to a centralized linux (centos) server with a MySQL database.  I want to then replicate this database out to Google SQL so the data can be publicly available (read-only).  I see that replication is not supported with the master being an in-house MySQL database and slave being Google SQL, but I was wondering what alternatives there may be.  I've set up a federated table on my in-house server (essentially a link to the Google SQL table), and I've created a trigger to write to both this federated table as well as our in-house table.  This is far from an ideal solution, as it's unbearably slow and has some pretty critical limitations (for example, it doesn't support INSERT...ON DUPLICATE KEY UPDATE, which is very important for my application).  This data is critical for the functioning of our organization (whether or not we have internet access), so I don't feel comfortable writing directly to Google SQL and replicating back to our internal MySQL database in case we lose internet access.  Are there any alternatives for a setup like this?  Have people run into this in the past?  

[David Newgas]

Hi Mike,

Super exciting to hear about Google Cloud Platform being used for such valuable purposes. Publicly sharing real-time weather data? So cool.

Unfortunately as you noticed replication to Cloud SQL instances from outside is not supported. Some alternatives are:

• If you run MySQL on a GCE instance you can make that instance the slave, but then you loose the advantages of Cloud SQL.
• If you are willing to suffer some lag you could replace your federation & trigger solution with a periodic export & import.
• If you don't need your public dataset to be queriable you could also share it as static files over GCS, for example as a SQL dump (or even csv).
  

Yours,

David

On Fri, Sep 25, 2015 at 4:37 AM, Mike Dorfman <mdor...@mountwashington.org> wrote:

I have a setup where data flows from various remote sensing sites to a centralized linux (centos) server with a MySQL database.  I want to then replicate this database out to Google SQL so the data can be publicly available (read-only).  I see that replication is not supported with the master being an in-house MySQL database and slave being Google SQL, but I was wondering what alternatives there may be.  I've set up a federated table on my in-house server (essentially a link to the Google SQL table), and I've created a trigger to write to both this federated table as well as our in-house table.  This is far from an ideal solution, as it's unbearably slow and has some pretty critical limitations (for example, it doesn't support INSERT...ON DUPLICATE KEY UPDATE, which is very important for my application).  This data is critical for the functioning of our organization (whether or not we have internet access), so I don't feel comfortable writing directly to Google SQL and replicating back to our internal MySQL database in case we lose internet access.  Are there any alternatives for a setup like this?  Have people run into this in the past?  

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b73d07fd-ceae-4ac2-a146-9694466b5299%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mike Dorfman]

Hi David,

Thanks for the reply!  Unfortunately, I have an appspot project which accesses this data (see xmountwashington.appspot.com/csc.html), so bullet #3 wouldn't work.  The displayed data is assumed to be live and instantaneous (updated every minute), so I'm not sure bullet #2 would work either.  

I'm very interested in bullet #1.  What pros and cons would Cloud SQL have over MySQL on a GCE instance?

Thanks,

Mike

On Friday, September 25, 2015 at 12:20:26 PM UTC-4, David Newgas wrote:

Hi Mike,

Super exciting to hear about Google Cloud Platform being used for such valuable purposes. Publicly sharing real-time weather data? So cool.

Unfortunately as you noticed replication to Cloud SQL instances from outside is not supported. Some alternatives are:

• If you run MySQL on a GCE instance you can make that instance the slave, but then you loose the advantages of Cloud SQL.
• If you are willing to suffer some lag you could replace your federation & trigger solution with a periodic export & import.
• If you don't need your public dataset to be queriable you could also share it as static files over GCS, for example as a SQL dump (or even csv).
  

Yours,

David

On Fri, Sep 25, 2015 at 4:37 AM, Mike Dorfman <mdor...@mountwashington.org> wrote:

I have a setup where data flows from various remote sensing sites to a centralized linux (centos) server with a MySQL database.  I want to then replicate this database out to Google SQL so the data can be publicly available (read-only).  I see that replication is not supported with the master being an in-house MySQL database and slave being Google SQL, but I was wondering what alternatives there may be.  I've set up a federated table on my in-house server (essentially a link to the Google SQL table), and I've created a trigger to write to both this federated table as well as our in-house table.  This is far from an ideal solution, as it's unbearably slow and has some pretty critical limitations (for example, it doesn't support INSERT...ON DUPLICATE KEY UPDATE, which is very important for my application).  This data is critical for the functioning of our organization (whether or not we have internet access), so I don't feel comfortable writing directly to Google SQL and replicating back to our internal MySQL database in case we lose internet access.  Are there any alternatives for a setup like this?  Have people run into this in the past?  

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[David Newgas]

So the main advantages of Cloud SQL, which you would lose if you went to MySQL on GCE are:

• Replication of back end data across multiple zones, allowing easy resilience against zone-level outages.
• No need to manage an extra server
• Automatic daily backups, kept for one week
• Option to stop your instance when it is not in use (probably not relevant for you)
• Automatic management of software upgrades.

Coincidentally I just saw a similar request on stack overflow. A fourth option occurred to me: You can use the `mysqlbinlog` command to dump your binary log as SQL statements, which you can send to your Cloud SQL instance without formally making it a slave.  This is not super resilient, but might do the job for you (esp as if there is a problem you can just nuke and re-create the Cloud SQL instance).

David

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b73d07fd-ceae-4ac2-a146-9694466b5299%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/ef57a530-9fa5-45ca-814a-e8b2e762e888%40googlegroups.com.

[Easwar Swaminathan]

You could create a CloudSQL replica from an on-premises master by using the CloudSQL Admin API. There is no support as of now in the UI or gcloud. But if you are willing to try out with the API, I can help you out in the process. Please let me know.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/CAJZK_bbvkNDEuEHMkW-dKcGoN%3DWO%2BQHL7cZGuWXcLRZNV67ciQ%40mail.gmail.com.

[rober...@parasql.com]

RE: "You could create a CloudSQL replica from an on-premises master by using the CloudSQL Admin API."

Any chance this supports master-master?

[Mike Dorfman]

Hi Easwar,

I am absolutely willing to try it!  Feel free to send me an email (mdorfman at mountwashington dot org) if you want to work with me privately, otherwise I look forward to your response here!  What are the first steps in doing this?  

Another issue which I didn't originally mention-our database is behind a firewall, so I'm guessing I'll have to punch a hole through the firewall before I can do this.

Thanks,

Mike

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/b73d07fd-ceae-4ac2-a146-9694466b5299%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/ef57a530-9fa5-45ca-814a-e8b2e762e888%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[Kris Hogh]

Howdy,

has this situation changed since this post? Can we create a cloud SQL replica instance from an on-prem instance?

On Saturday, September 26, 2015 at 2:20:26 AM UTC+10, David Newgas wrote:

Hi Mike,

Super exciting to hear about Google Cloud Platform being used for such valuable purposes. Publicly sharing real-time weather data? So cool.

Unfortunately as you noticed replication to Cloud SQL instances from outside is not supported. Some alternatives are:

• If you run MySQL on a GCE instance you can make that instance the slave, but then you loose the advantages of Cloud SQL.
• If you are willing to suffer some lag you could replace your federation & trigger solution with a periodic export & import.
• If you don't need your public dataset to be queriable you could also share it as static files over GCS, for example as a SQL dump (or even csv).
  

Yours,

David

On Fri, Sep 25, 2015 at 4:37 AM, Mike Dorfman <mdor...@mountwashington.org> wrote:

I have a setup where data flows from various remote sensing sites to a centralized linux (centos) server with a MySQL database.  I want to then replicate this database out to Google SQL so the data can be publicly available (read-only).  I see that replication is not supported with the master being an in-house MySQL database and slave being Google SQL, but I was wondering what alternatives there may be.  I've set up a federated table on my in-house server (essentially a link to the Google SQL table), and I've created a trigger to write to both this federated table as well as our in-house table.  This is far from an ideal solution, as it's unbearably slow and has some pretty critical limitations (for example, it doesn't support INSERT...ON DUPLICATE KEY UPDATE, which is very important for my application).  This data is critical for the functioning of our organization (whether or not we have internet access), so I don't feel comfortable writing directly to Google SQL and replicating back to our internal MySQL database in case we lose internet access.  Are there any alternatives for a setup like this?  Have people run into this in the past?  

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.