[google-cloud-sql-discuss] best way to Export Cloud SQL database

Hi

i'm trying to find the best way to export Cloud sql database from one project and import it to another.

i have 2 cloud sql instances on 2 different projects (prod/staging).

in order to sync prod to staging database i'm using mysqldump command to export each needed table from prod DB and then import the output to the staging db.

i'm doing so using the following command:

mysqldump db table--skip-lock-tables --single-transaction | gzip > /tmp/mytable.gz

once finished exporting all tables i'm using mysqldump to import the gz files into the staging db.

the problem is , that sometimes (too often) i'm getting one of two issues:

- i'm getting error on "mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table mytable at row: xxxxxx"

- export just hang and takes way too much time.

i see it happens on big tables, so at first i thought its probably related to net_read_timeout and net_write_timeout and which had default value (30/60) so i've tried to increased them to 900, and even 3600.

the second issue still happens.

also tried to add the --quick option which supposed to be better for large tables as it doesnt load the entire content to memory, and do it row by row. still hangs.

after investigating this issue on the net i've started to think that i'm not using mysqldump the right way.

- seems like skip-lock-tables is useless here (innodb) as the single-transaction will make sure the dump will happen in one transaction. right?

- although using the single-transaction, still it means that during that time of dump, which as i saw sometimes takes alot of time, i'm blocking my production database from operations such as create/alter table etc??

- what is the different of using mysqldump then gcloud sql export sql ? is it better suited for this kind of operation?

thanks a lot

Chen

--
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+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/aa007a21-7787-43c8-9d87-111dfc700a0a%40googlegroups.com.