Tuesday, March 3, 2009

Useful 'mysqldump' one-liners.

note: This article is intended for a technical audience -- you should use extreme caution when modifying a production system, as your data will be nearly impossible to recover if you use this command incorrectly -- caveat emptor.

I always seem to use these when doing remote administration from the command line -- but when I forget if I should be using -add-drop-table or -add-drop-databases, the usual suspects from Google render terribly on my mobile phone -- so I thought i'd put them here, for later.

To backup a single database:

mysqldump -u [username] -p [database_name] --add-drop-database --lock-all-tables > backupfile.sql

To backup multiple databases:

mysqldump -u [username] -p --all-databases --add-drop-database --lock-all-tables > backupfile.sql

To restore your backup:

mysql -u [username] -p < backupfile.sql

No comments: