Dumping databases with style

 

I am sure that most of the people have been aquaited with the mysqldump tool with allows to backup your database in the form of sql calls that create the table schema and populate then with the data that was in the database by using inserts.

Here is the basic syntax of the mysqldump call:

mysqldump -u<user> -p<pass> <dbname>

note that -p option (the password) must not contain spaces between the option itself and the actual password. so if your username johndoe and pasword is mypwd to access database with name mydb you would type:

mysqldump -u johndoe -pmypwd mydb

or

mysqldump -u johndoe -p mydb

the second would incurr a new input line to type in your password

or you can do

mysqldump -u johndoe mydb

if your db is not password protected

 

BUT all of this is pretty standard stuff,

what if you would like to backup only your data?

your solution would be to use --no-create-info option like so:

mysqldump -u<user> -p<pass> --no-create-info <dbname>

 

what if you would like to dump of only specific tables?

use mysqldump like so:

mysqldump -u<user> -p<pass> --no-create-info <dbname> [tables]

e.g. mysqldump -u johndoe mydb dbtable1 dbtable2 dbtable3

 

what if you would like to dump of several specific databases?

use mysqldump like so:

mysqldump -u<user> -p<pass> --databases <dbname1> [otherdbs]

e.g. mysqldump -u johndoe --databases mydb mydb1 mydb2 mydb3

 

and finally what if you want to backup all your databases?

use mysqldump like so:

mysqldump -u<user> -p<pass> --all-databases

e.g. mysqldump -u johndoe --all-databases

 

Hope these tips help! Enjoy :-)

This page was last updated on: 23/06/2009 08:58