Stay organized with collectionsSave and categorize content based on your preferences.
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:
--databasesSpecify an explicit list of databases to export. This list mustnotcontain the system databases (sys,mysql,performance_schema, andinformation_schema).
--hex-blobIf your database contains any binary fields, then you must use this flag to
ensure that your binary fields are imported correctly.
--single-transactionStarts 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.
--routinesTo include stored procedures and functions.
When usingmysqldumpversion 8 or later to export MySQL databases versions earlier than 8: --column-statistics=0
This flag removes theCOLUMN_STATISTICStable from the database export to avoid theUnknown table 'COLUMN_STATISTICS' in information_schema (1109)error. For more information, seeDiagnose 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:
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:
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 theSHOW DATABASESMySQL 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.gzfile extension (for example,source-database.sql.gz).
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-09-04 UTC."],[[["\u003cp\u003eIf using the Database Migration Service API, ensure \u003ccode\u003emysqldump\u003c/code\u003e is used to update the dump when making REST API calls.\u003c/p\u003e\n"],["\u003cp\u003eWhen exporting to Cloud SQL, use \u003ccode\u003emysqldump\u003c/code\u003e with flags such as \u003ccode\u003e--databases\u003c/code\u003e (excluding system databases), \u003ccode\u003e--hex-blob\u003c/code\u003e for binary fields, \u003ccode\u003e--single-transaction\u003c/code\u003e for consistency, and \u003ccode\u003e--routines\u003c/code\u003e for stored procedures/functions, as well as \u003ccode\u003e--column-statistics=0\u003c/code\u003e if you are on \u003ccode\u003emysqldump\u003c/code\u003e version 8 or later and the database is older than version 8.\u003c/p\u003e\n"],["\u003cp\u003eWhile \u003ccode\u003emysqldump\u003c/code\u003e is running, avoid DDL operations on the database to prevent inconsistencies in the export file.\u003c/p\u003e\n"],["\u003cp\u003eWhen dealing with Relational Database Service (RDS) for MySQL, note that the \u003ccode\u003emaster-data\u003c/code\u003e property is unsupported, and if the source database supports GTID, use \u003ccode\u003e--set-gtid-purged=on\u003c/code\u003e; also be sure to use GTID when manually migrating.\u003c/p\u003e\n"],["\u003cp\u003eWhen calling \u003ccode\u003emysqldump\u003c/code\u003e, make sure to properly replace the \u003ccode\u003e[PROPERTIES_IN_BRACKETS]\u003c/code\u003e values with proper values such as \u003ccode\u003e[SOURCE_ADDR]\u003c/code\u003e, \u003ccode\u003e[SOURCE_PORT]\u003c/code\u003e, \u003ccode\u003e[USERNAME]\u003c/code\u003e, \u003ccode\u003e[DBS]\u003c/code\u003e, \u003ccode\u003e[BUCKET_NAME]\u003c/code\u003e, and \u003ccode\u003e[DUMP_FILENAME]\u003c/code\u003e, and be mindful that the \u003ccode\u003egzip\u003c/code\u003e command may not be ideal if your database contains non-compressible data.\u003c/p\u003e\n"]]],[],null,["# Exporting a MySQL database using mysqldump\n\n\u003cbr /\u003e\n\n| **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](/database-migration/docs/mysql/api-migration-jobs).\n\nYou can run the mysqldump utility directly against your MySQL database,\nusing whatever options you require. However, if you're exporting to\nimport the data into a Cloud SQL database, then use the mysqldump utility\nwith the following flags:\n\n- `--databases` Specify an explicit list of databases to export. This list must *not* contain the system databases (`sys`, `mysql`, `performance_schema`, and `information_schema`).\n- `--hex-blob` If your database contains any binary fields, then you must use this flag to ensure that your binary fields are imported correctly.\n- `--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.\n- `--routines` To include stored procedures and functions.\n- **When using `mysqldump` version 8 or later to export MySQL databases versions earlier than 8** : \n `--column-statistics=0`\n\n This flag removes the [COLUMN_STATISTICS](https://dev.mysql.com/doc/refman/8.0/en/information-schema-column-statistics-table.html) table from the database export to avoid the `Unknown table 'COLUMN_STATISTICS' in information_schema (1109)` error. For more information, see [Diagnose issues](/database-migration/docs/mysql/diagnose-issues#unknown-table-column-statistics).\n\nIt's also recommended to use the following flags:\n\n- `--no-autocommit`\n- `--default-character-set=utf8mb4`\n- `--master-data`\n\n| **Important:** While [mysqldump](https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html) is running, do not perform any [DDL operations](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_ddl) on the database. Doing so could cause inconsistencies in the export file.\n\nFrom a machine with network connectivity to your MySQL server, run the following\ncommand: \n\n mysqldump \\\n -h [SOURCE_ADDR] -P [SOURCE_PORT] -u [USERNAME] -p \\\n --databases [DBS] \\\n --hex-blob \\\n --no-autocommit \\\n --default-character-set=utf8mb4 \\\n --master-data=1 \\\n --single-transaction \\\n --routines \\\n | gzip \\\n | gcloud storage cp - gs://[BUCKET_NAME]/[DUMP_FILENAME].gz\n\n| **Note:** If your source database server doesn't support GTID, then use the `--master-data=1` property; otherwise, don't use this property. Also, if your source database server supports GTID, then use the `--set-gtid-purged=on` property; otherwise, don't use this property.\n\nIf the source of the migration is a Relational Database Service (RDS) for MySQL:\n\n- The `master-data` property isn't supported.\n- If your source database server supports GTID, then use the `--set-gtid-purged=on` property; otherwise, don't use this property.\n- If you're using a manual dump to migrate your data, then perform the migration with GTID enabled.\n\nThis command might look like the following example: \n\n mysqldump \\\n -h [SOURCE_ADDR] -P [SOURCE_PORT] -u [USERNAME] -p \\\n --databases [DBS] \\\n --hex-blob \\\n --no-autocommit \\\n --default-character-set=utf8mb4 \\\n --set-gtid-purged=on \\\n --single-transaction \\\n --routines \\ \n | gzip \\\n | gcloud storage cp - gs://[BUCKET_NAME]/[DUMP_FILENAME].gz\n\nAlso, you should configure RDS instances to retain binlogs longer.\nThis command might look like the following example: \n\n # Sets the retention period to one week.\n call mysql.rds_set_configuration('binlog retention hours', 168);\n\nReplace \u003cvar translate=\"no\"\u003e[PROPERTIES_IN_BRACKETS]\u003c/var\u003e with the following values:\n\n| **Note:** The `gzip` command causes the dump file to be compressed. If your database contains data that does not compress well, such as binary incompressible data or JPG images, then remove `| gzip` from the command."]]
