Creating a mysqldump file

Question

Goal: migrate google-cloud-sql First Generation to second generation

Exporting Data from Cloud SQL is working fine.

https://cloud.google.com/sql/docs/backup-recovery/backing-up
But:
Note: If you are exporting your data for use in a Cloud SQL instance, you must use the instructions provided in Exporting data for Import into Cloud SQL. You cannot use these instructions.

So i get to this page: Exporting Data for Import into Cloud SQL https://cloud.google.com/sql/docs/import-export/creating-mysqldump-csv#mysqldump

This pages describes how to create a mysqldump or CSV file from a MySQL database that does not reside in Cloud SQL.

Instructions are not working: mysqldump --databases [DATABASE_NAME] -h [INSTANCE_IP] -u [USERNAME] -p \ --hex-blob --skip-triggers --set-gtid-purged=OFF --default-character-set=utf8 > [DATABASE_FILE].sql

mysqldump: unknown variable 'set-gtid-purged=OFF

How do I create mysqldump for import in cloud sql second generation?

thanks in advance, Sander

edit:
Using google cloud sql first generation via google cloud console

removed set-gtid-purged=OFF
result:
Enter password:
mysqldump: Got error: 2013: Lost connection to MySQL server at 'reading initial communication packet', system error: 0 when trying to connect
s@folkloric-alpha-618:~$

What version of mysqldump are you trying this with? This probably means that your version of mysqldump doesn't support set-gtid-purge yet. On ubuntu 14.04 that's the case. Try to remove it from the command line and run it again. — Mustafa
Done:, Enter password: mysqldump: Got error: 2013: Lost connection to MySQL server at 'reading initial communication packet', system error: 0 when trying to connect s@folkloric-alpha-618:~$ — no1san
It sounds like you have not authorized your IP address to connect to the instance you are trying to export from. You can confirm by running mysql -h <IP> -u <USER> -p and see if you get the same error. — Vadim
You need to authorize the IP you are connecting from: cloud.google.com/sql/docs/external#appaccessIP — Vadim

gabidavila gabidavila · Accepted Answer · 2019-09-27T14:28:23

For set-gtid-purged. Please verify which mysql-client version you have installed. Many OS come with the MariaDB version which does not support this flag (since their implementation of GTID is different).

I know the Oracle official mysql-client supports this flag since 5.6.9.

To verify your package run:

mysqldump --version

If you get this, you don't have the official client:

mysqldump  Ver 10.16 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64)

The official client would be something like this:

mysqldump  Ver 10.13 Distrib 5.7.27, for Linux (x86_64)

If you want to change the version, you can use their official repository.

Creating a mysqldump file

1 Answers