Skip to content

GCP CloudSQL: Exporting a database from CloudSQL

(This page talks about copying and making backups of SQL data. For IMPORTING INTO a database, look elsewhere.

MySQL

Database dumps should be run with at least following parameters:

mysqldump --databases DATABASE_NAME -h 127.0.0.1 -u 'first.lastname' -p \
--single-transaction --set-gtid-purged=OFF > SQL_FILE.sql

Full set of desired parameters:

mysqldump --databases DATABASE_NAME -h 127.0.0.1 -u 'first.lastname' -p \
--hex-blob --single-transaction --set-gtid-purged=OFF \
--default-character-set=utf8mb4 > SQL_FILE.sql

Source: https://cloud.google.com/sql/docs/mysql/import-export/import-export-sql#export-mysqldump

PostgreSQL

Without compression

pg_dump -U "database_user_account" -h 127.0.0.1 -p 55439 -b -v <database-name_deployment-environment> -f "database-name_deployment-environment-$(date +%Y-%m-%d-%H%M).dump"

With compression (if network bandwith is limited, for large databases)

pg_dump -U "first.lastname@aholadigital.com" -h 127.0.0.1 -p 55439 -Fc -b -v <database-name_deployment-environment> -f "database-name_deployment-environment-$(date +%Y-%m-%d-%H%M).dump"

To do: explain what each flag does: 1. -U "first.lastname@aholadigital.com" 2. -h 127.0.0.1 3. -p 55439 4. -Fc 5. -b 6. -f 7. Optional: 1. -Z 6 1. Specifies compression level. Defaults to -Z 6 if not specified. For now, ChatGPT copy:

pg_dump \
  -U "first.lastname@email.com" \     # Connect using this username
  -h 127.0.0.1 \                       # Connect to the database server on localhost
  -p 55439 \                           # Connect to port 55439
  -F c \                               # Output format: 'custom' (compressed, not plain SQL)
  -b \                                 # Include large objects (BLOBs)
  -v \                                 # Verbose mode (more detailed output)
  -f "database-name_deployment-environment-$(date +%Y-%m-%d).dump" \  # Output file with date in name
  -Z 6 \                               # Compression level 6 (highest compression)
  <database-name_deployment-environment>  # The name of the database to dump

Troubleshooting

MySQL: Access denied missing SUPER privileges

If you get an error about missing SUPER privileges, you are likely doing something wrong. In the Cloud SQL environment, SUPER privileges cannot be had. Instead, you should re-check your mysqldump parameters and see if you are missing something (note that cloudsqlsuperadmin is not the same as SUPER privileges!).

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