Tetap teratur dengan koleksi
Simpan dan kategorikan konten berdasarkan preferensi Anda.
Anda dapat menjalankan utilitas mysqldump langsung ke database MySQL,
menggunakan opsi apa pun yang Anda perlukan. Namun, jika Anda mengekspor untuk
mengimpor data ke database Cloud SQL, gunakan utilitas mysqldump
dengan flag berikut:
--databases
Menentukan daftar eksplisit database yang akan diekspor. Daftar ini tidak
boleh berisi database sistem (sys, mysql, performance_schema, dan
information_schema).
--hex-blob
Jika database Anda berisi kolom biner, Anda harus menggunakan flag ini untuk
memastikan kolom biner Anda diimpor dengan benar.
--single-transaction
Memulai transaksi sebelum berjalan. Daripada mengunci seluruh database,
tindakan ini memungkinkan mysqldump membaca database dalam kondisi saat ini,
sehingga menghasilkan dump data yang konsisten.
--routines
Untuk menyertakan prosedur dan fungsi tersimpan.
Saat menggunakan mysqldump versi 8 atau yang lebih baru untuk mengekspor database MySQL versi yang lebih lama dari 8: --column-statistics=0
Flag ini menghapus tabel COLUMN_STATISTICS dari ekspor database untuk menghindari error Unknown table 'COLUMN_STATISTICS' in information_schema (1109). Untuk mengetahui informasi selengkapnya, lihat Mendiagnosis masalah.
Sebaiknya gunakan juga flag berikut:
--no-autocommit
--default-character-set=utf8mb4
--master-data
Dari mesin yang terhubung ke server MySQL Anda, jalankan perintah
berikut:
Selain itu, Anda harus mengonfigurasi instance RDS untuk mempertahankan binlog lebih lama.
Perintah ini mungkin terlihat seperti contoh berikut:
# Sets the retention period to one week.callmysql.rds_set_configuration('binlog retention hours',168);
Ganti [PROPERTIES_IN_BRACKETS] dengan nilai berikut:
Properti
Nilai
[SOURCE_ADDR]
Alamat IPv4 atau nama host untuk server database sumber.
[SOURCE_PORT]
Port untuk server database sumber.
[USERNAME]
Akun pengguna MySQL.
[DBS]
Daftar database yang dipisahkan spasi di server database sumber
yang akan disertakan dalam dump. Gunakan perintah MySQL SHOW DATABASES untuk
mencantumkan database Anda.
[BUCKET_NAME]
Bucket di Cloud Storage yang dibuat oleh pengguna dan digunakan untuk menyimpan file dump (misalnya, replica-bucket).
[DUMP_FILENAME]
Nama file dump, yang diakhiri dengan ekstensi file .gz (misalnya, source-database.sql.gz).
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Sulit dipahami","hardToUnderstand","thumb-down"],["Informasi atau kode contoh salah","incorrectInformationOrSampleCode","thumb-down"],["Informasi/contoh yang saya butuhkan tidak ada","missingTheInformationSamplesINeed","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-09-05 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."]]
