Help Center/ Relational Database Service/ FAQs/ Database Migration/ Commands for Exporting Data Through mysqldump
Updated on 2024-05-13 GMT+08:00
View PDF
Share

Commands for Exporting Data Through mysqldump

Background

mysqldump is the most commonly used tool for importing and exporting MySQL data.

mysqldump Options

Table 1 Option description

Option Name

Description

add-drop-table

Adds the DROP TABLE statement before each data table is created.

events, E

Exports events.

routines, R

Exports stored procedures and customized functions.

flush-logs

Updates logs before the logs are exported.

no-create-db, n

Exports only data without adding of the CREATE DATABASE statement.

add-drop-database

Adds the DROP DATABASE statement before each database is created.

no-create-info, t

Exports only data without adding of the CREATE TABLE statement.

no-data, d

Exports only the database table structure.

set-gtid-purged=OFF

Does not export GTID statements.

hex-blob

Exports binary string fields in hexadecimal format.

Examples for Using mysqldump

  1. Export all data of databases db1 and db2.

    >db12.sqldb1 db2--hex-blob --set-gtid-purged=OFF --single-transaction --order-by-primary --flush-logs -q --databases 192.168.0.199 -h8635mysqldump -uroot -p -P

  2. Export the t1 and t2 tables of database db1.

    .sqlt1_t2 >t1 t2 --tables db1 --hex-blob --set-gtid-purged=OFF --single-transaction --order-by-primary --flush-logs -q --databases 192.168.0.199 -h8635mysqldump -uroot -p -P

  3. Export data whose id equals 1 from table t1 in database db1.

    .sqlt1_id'>id=1 --where='t1 --tables db1 --hex-blob --set-gtid-purged=OFF --single-transaction --order-by-primary --flush-logs -q --databases 192.168.0.199 -h8635mysqldump -uroot -p -P

  4. Export all table structures in database db1 without exporting data.

    _table.sqldb1 >db1set-gtid-purged=OFF --single-transaction --order-by-primary -n --flush-logs -q --databases --no-data --192.168.0.199 -h8635mysqldump -uroot -p -P

  5. Export all data excluding the tables and data in database db1.

    > others.sql db1 --set-gtid-purged=OFF -F -n -t -d -E -R8635 -P192.168.0.199mysqldump -uroot -p -h

Parent topic: Database Migration