Friday, May 11, 2012

How to dump a table in MySQL without hurting her feelings.

Breakups are hard.  Dumping and restoring tables in MySQL shouldn't be so painful.  There's a ton of options, but most of the time you just need a few simple commands.

Dump a table from a database:
mysqldump database table > filename

Dump a whole database:
mysqldump database > filename

Once you've dumped your data, you're ready to restore it.   By default, MySQL creates all the CREATE statements you need.  All you have to do is make sure you have the database created.

Restore your dump file:
mysql database < filename

In all of these examples, you may need to pass a username and password.  To do so you would use:

mysqldump -u username -ppassword database > filename
and
mysql -u username -ppassword database < filename

Notice the lack of a space between the -p option and the password itself.  That is not a typo.

All it takes is a little re-direction, and dumping tables can be a painless process for you too!