MYSQLDUMP Cheatsheet

MYSQLDUMP CLI UTILITY CHEATSHEET
- you access mysqldump.exe through the dos prompt
- the file is located in bin folder of your mysql installation dir

General Format
mysqldump [options] db_name [tables]
mysqldump [options] --databases db_name1 [db_name2 db_name3...]
mysqldump [options] --all-databases
-----------------------------

normal: mysqldump -uUSERNAME -pPASSWORD db_name > file

nodata: mysqldump -uUSERNAME -pPASSWORD -d db_name > file
nocreate: mysqldump -USERNAME -pPASSWORD -t db_name > file

-----------------------------


TO IMPORT A FILE
mysql db_name < backup-file.sql
mysql -e "source /path-to-backup/backup-file.sql" db_name
-----------------------------


MYSQLDUMP OPTIONS

--quick: dumps tables row by row, instead of caching them into memory. --opt enablesit by default
--add-drop-database
--add-drop-table
--all-databases, -A: dump all tables in all databases. same as specifying --databases followed by names of all of em
--comments,-i: insert comments and addl info into the dump file
--extended-insert, -e : Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
--host=host_name, -h host_name : Dump data from the MySQL server on the given host. The default host is localhost.
--ignore-table=db_name.tbl_name : Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times.
--no-create-info, -t : Do not write CREATE TABLE statements that re-create each dumped table.
--no-data, -d : Do not write any table row information (that is, do not dump table contents). This is very useful if you want to dump only the CREATE TABLE statement for the table.
--order-by-primary : Sorts each table's rows by its primary key, or by its first unique index, if such an index exists.
--xml, -X : Write dump output as well-formed XML.

-----------------------------


SHORTHANDS
--opt: --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
--compact: suppresses comments and enables --skip-add-drop-table, --skip-set-charset, --skip-disable-keys, and --skip-add-locks

-----------------------------

NOTES

• --opt is on by default in v5.0 and up
• To disable certain features, use --skip-[feature name] e.g. --skip-extended-insert
• When you selectively enable or disable the effect of a group option, order is important because options are processed first to last.
• You can leave the password blank, it would ask for it upon the execution of the command