Commands for Exporting Data Through mysqldump

Updated on 2023-10-19 GMT+08:00

View PDF

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

**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 table structure data.
set-gtid-purged=OFF	Does not export GTID statements.
hex-blob	Exports binary string fields in hexadecimal format.

Examples are as follows:

Export all data of databases db1 and db2.
mysqldump -uroot -p -P8635 -h192.168.0.199 --hex-blob --set-gtid-purged=OFF --single-transaction --order-by-primary --flush-logs -q --databases db1 db2 >db12.sql
Export the t1 and t2 tables of database db1.
mysqldump -uroot -p -P8635 -h192.168.0.199 --hex-blob --set-gtid-purged=OFF --single-transaction --order-by-primary --flush-logs -q --databases db1 --tables t1 t2 >t1_t2.sql
Export data whose id equals 1 from table t1 in database db1.
mysqldump -uroot -p -P8635 -h192.168.0.199 --hex-blob --set-gtid-purged=OFF --single-transaction --order-by-primary --flush-logs -q --databases db1 --tables t1 --where='id=1'>t1_id.sql
Export all table structures in database db1 without exporting data.
mysqldump -uroot -p -P8635 -h192.168.0.199 --no-data --set-gtid-purged=OFF --single-transaction --order-by-primary -n --flush-logs -q --databases db1 >db1_table.sql
Export all data excluding the tables and data in database db1.
mysqldump -uroot -p -h192.168.0.199 -P8635 --set-gtid-purged=OFF -F -n -t -d -E -R db1> others.sql