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

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

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

  • The master-data property is not 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

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.