Exportar uma base de dados do MySQL com o mysqldump
Mantenha tudo organizado com as coleções
Salve e categorize o conteúdo com base nas suas preferências.
Pode executar o utilitário mysqldump diretamente na sua base de dados do MySQL,
usando as opções que precisar. No entanto, se estiver a exportar para importar os dados para uma base de dados do Cloud SQL, use o utilitário mysqldump com as seguintes flags:
--databases
Especifique uma lista explícita de bases de dados a exportar. Esta lista não
pode conter as bases de dados do sistema (sys, mysql, performance_schema e
information_schema).
--hex-blob
Se a sua base de dados contiver campos binários, tem de usar esta flag para
garantir que os campos binários são importados corretamente.
--single-transaction
Inicia uma transação antes da execução. Em vez de bloquear toda a base de dados,
isto permite que o mysqldump leia a base de dados no estado atual,
o que resulta numa descarga de dados consistente.
--routines
Para incluir procedimentos e funções armazenados.
Quando usar a versão mysqldump 8 ou posterior para exportar versões de bases de dados MySQL anteriores à versão 8: --column-statistics=0
Esta flag remove a tabela COLUMN_STATISTICS da exportação da base de dados para evitar o erro Unknown table 'COLUMN_STATISTICS' in information_schema (1109). Para mais informações, consulte o artigo Diagnostique problemas.
Também recomendamos que use as seguintes flags:
--no-autocommit
--default-character-set=utf8mb4
--master-data
Numa máquina com conetividade de rede ao seu servidor MySQL, execute o seguinte comando:
Além disso, deve configurar as instâncias do RDS para reter os binlogs durante mais tempo.
Este comando pode ter o seguinte aspeto:
# Sets the retention period to one week.callmysql.rds_set_configuration('binlog retention hours',168);
Substitua [PROPERTIES_IN_BRACKETS] pelos seguintes valores:
Propriedade
Valor
[SOURCE_ADDR]
O endereço IPv4 ou o nome de anfitrião do servidor de base de dados de origem.
[SOURCE_PORT]
A porta do servidor de base de dados de origem.
[USERNAME]
A conta de utilizador do MySQL.
[DBS]
Uma lista separada por espaços das bases de dados no servidor de bases de dados de origem a incluir na captura. Use o comando SHOW DATABASES MySQL para
listar as suas bases de dados.
[BUCKET_NAME]
O contentor no Cloud Storage criado pelo utilizador e usado para armazenar o ficheiro de despejo (por exemplo, replica-bucket).
[DUMP_FILENAME]
O nome do ficheiro do despejo, que termina com uma extensão de ficheiro .gz (por exemplo, source-database.sql.gz).
[[["Fácil de entender","easyToUnderstand","thumb-up"],["Meu problema foi resolvido","solvedMyProblem","thumb-up"],["Outro","otherUp","thumb-up"]],[["Difícil de entender","hardToUnderstand","thumb-down"],["Informações incorretas ou exemplo de código","incorrectInformationOrSampleCode","thumb-down"],["Não contém as informações/amostras de que eu preciso","missingTheInformationSamplesINeed","thumb-down"],["Problema na tradução","translationIssue","thumb-down"],["Outro","otherDown","thumb-down"]],["Última atualização 2025-08-21 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."]]
