Commands for Exporting Data Through mysqldump
Background
mysqldump is the most commonly used tool for importing and exporting MySQL data.
mysqldump Options
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
- 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
- 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
- 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
- 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
- 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
Database Migration FAQs
- Why Do I Need to Use the mysqldump or pg_dump Tools for Migration?
- Precautions for Exporting Large Tables Through mysqldump
- Commands for Exporting Data Through mysqldump
- What Should I Do When a Large Number of Binlog Files Cause Storage Space Insufficiency During an RDS MySQL Instance Migration?
- What Types of DB Engines Does RDS Support for Importing Data?
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbotmore