Exporting a MySQL database using mysqldump

Important: If you're using the Database Migration Service API to create migration jobs, then make sure you use the mysqldump utility to update the dump when you run the REST API call. For more information, see Manage migration jobs using the API.

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`).