Exporting a MySQL database using mysqldump

You can run the mysqldump utility directly against your MySQL database, using whatever options you require. However, if you're exporting to import the data into a Cloud SQL database, then use the mysqldump utility with the following flags:

• --databases Specify an explicit list of databases to export. This list must not contain the system databases (sys, mysql, performance_schema, and information_schema).
• --hex-blob If your database contains any binary fields, then you must use this flag to ensure that your binary fields are imported correctly.
• --single-transaction Starts a transaction before running. Rather than lock the entire database, this lets mysqldump read the database in the current state, making for a consistent data dump.
• --routines To include stored procedures and functions.

• When using mysqldump version 8 or later to export MySQL databases versions earlier than 8:
  --column-statistics=0

  This flag removes the COLUMN_STATISTICS table from the database export to avoid the Unknown table 'COLUMN_STATISTICS' in information_schema (1109) error. For more information, see Diagnose issues.

It's also recommended to use the following flags:

• --no-autocommit
• --default-character-set=utf8mb4
• --master-data

From a machine with network connectivity to your MySQL server, run the following command:

 mysqldump \  -h [SOURCE_ADDR] -P [SOURCE_PORT] -u [USERNAME] -p \  --databases [DBS] \  --hex-blob \  --no-autocommit \  --default-character-set=utf8mb4 \  --master-data=1 \  --single-transaction \  --routines \  | gzip \  | gcloud storage cp - gs://[BUCKET_NAME]/[DUMP_FILENAME].gz 

If the source of the migration is a Relational Database Service (RDS) for MySQL:

• The master-data property isn't supported.
• If your source database server supports GTID, then use the --set-gtid-purged=on property; otherwise, don't use this property.
• If you're using a manual dump to migrate your data, then perform the migration with GTID enabled.

This command might look like the following example:

 mysqldump \  -h [SOURCE_ADDR] -P [SOURCE_PORT] -u [USERNAME] -p \  --databases [DBS] \  --hex-blob \  --no-autocommit \  --default-character-set=utf8mb4 \  --set-gtid-purged=on \  --single-transaction \  --routines \   | gzip \  | gcloud storage cp - gs://[BUCKET_NAME]/[DUMP_FILENAME].gz 

Also, you should configure RDS instances to retain binlogs longer. This command might look like the following example:

 # Sets the retention period to one week.  call mysql.rds_set_configuration('binlog retention hours', 168); 

Replace [PROPERTIES_IN_BRACKETS] with the following values: