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

PHP Eccentricity

I never have used the PHP CLI executable (php.exe); and perhaps this is why I never came across this earlier. It was while trying to extract PEAR through the provided go-pear.bat file (located in root folder of your PHP installation), that I became irritatingly familiar to it.

Here is what was spewed out

This application has failed to start because php_mbstring.dll was not found. Re-installing the application may fix this problem.

PHP Warning: PHP Startup: Unable to load dynamic library 'C:\php5\ext\php_exif.
dll' - The specified module could not be found.

And here are some solutions that did not work

• Moving php_mbstring.dll to root folder of PHP: PHP executable started crashing, each and every time.
• Commenting out php_mbstring.dll from php.ini: Even worse, as its provided functions are required by other libraries eg. exif library
• Changing the php root folder (and the associated settings in php.ini, and apache) : To no avail
• Re-installing the files: No change.

I could not trace out where the problem was. I could've been the CLI, or the PEAR package, or the php_exif.ddl or php_mbstring.dll.
In the end, I found something related, and the Online PHP Reference seemed to confirm it...

"Windows users must enable both the php_mbstring.dll and php_exif.dll DLL's in php.ini. The php_mbstring.dll DLL must be loaded before the php_exif.dll DLL so adjust your php.ini accordingly."

The easy solution: Open your php.ini, and paste the line that loads the php_mbstring.dll over the line for php_exif.dll.
Alternatively, you can disable both the mbstring and exif extensions, but that makes little sense. You wouldn't enable them if you didn't need them in the first place.