mysqldump utility – some useful no nonsense stuff


mysqldump is a great mysql utility that can be used for backing up and restoring existing mysql databases. Find below, some of the most useful stuff which can be accomplished using this utility.

I. Back Up and Restore a DB

Format : mysqldump -h domainname -u username -ppassword databasename > dumpfile.sql
Example : mysqldump -h localhost -u root -proot test > testdb_dump.sql

You can restore the database using the following commnad

Format : mysql -hdomainname -u username -ppassword databasename < dumpfile.sql
Example : mysql -hlocalhost -u root -proot test_new < testdb_dump.sql

This will dump the contents of testdb_dump.sql into the database test_new. Only thing that you need to make sure is that test_new already exists in your mysql.

II. Back Up only table structure

Format : mysqldump -d -h domainname -u username -ppassword databasename > dumpfile.sql
Example : mysqldump -d -h localhost -u root -proot test > testdb_dump.sql

The only difference here is the -d switch which isntructs mysql not to dump the data.

III. Back Up multiple databases

Format : mysqldump -h domainname -u username -ppassword --databases database1 database2 ... database n > dumpfile.sql
Example : mysqldump -h localhost -u root -proot --databases test test_new > testAndTest_NewDump.sql

IV. Back Up all databases

Format : mysql -h domainname -u username -ppassword --all-databases > dumpfile.sql
Example : mysql -h localhost -u root -proot --all-databases > allDatabasesDump.sql

V. Back Up a spcific table

Format : mysql -h domainname -u username -ppassword databasse table > dumpfile.sql
Example : mysql -h localhost -u root -proot test test_tabale > testtabledump.sql

For more on mysqldump, refer the official documentation @ : http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Cheers,
The Nonsense Blogger !! 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s