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:

Property Value
[SOURCE_ADDR] The IPv4 address or hostname for the source database server.
[SOURCE_PORT] The port for the source database server.
[USERNAME] The MySQL user account.
[DBS] A space-separated list of the databases on the source database server to include in the dump. Use the SHOW DATABASES MySQL command to list your databases.
[BUCKET_NAME] The bucket in Cloud Storage that's created by the user and that's used for storing the dump file (for example, replica-bucket).
[DUMP_FILENAME] The dump's filename, ending with a .gz file extension (for example, source-database.sql.gz).