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\
|gcloudstoragecp-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\
|gcloudstoragecp-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.
callmysql.rds_set_configuration('binlog retention hours',168);

Replace [PROPERTIES_IN_BRACKETS] with the following values: