Ah it's that time again, backing up your SQL database. While there are automated ways to back up databases, nothing beats the old fashion quick and easily command line sqldump. Backing up and Your MySQL database is easier than ever using the command line.The command Sqldump creates a snapshot of a database simply by taking all of tables and data in a database and turning them into SQL statements that can be stored into a file(s) that can be used later.

$> mysqldump -u[username] -p[password] [database_name] > [file_name].sql

For example if you want to back up a single database it would look something like this:

 $> mysqldump -ujohndoe -p123 primary_DB > ./primary_DB.sql

executing the mysqldump using the credential username "johndoe", password "123". Database name "primary_DB" then redirecting the output into a file called primary_DB.sql located in our current directory.

likewise if you like to backup multiple database, it would look some thing like this:

$> mysqldump -ujohndoe -p123 primary_DB secondary_DB > ./DB_complete.sql

simply by adding more database names will cause sql dump them to dump them into into that same file.Otherwise if you want to create a complete backup of the database simply add the -all-database option like so:

$> mysqldump -ujohndoe -p123  --all-database > ./DB_complete.sql

To restore a database simply execute mysql command telling it to execute the commands from the sql files into the correct database.

$> mysql -ujohndoe -p123  primary_DB < ./primary_DB.sql

If you are trying to restore a single database from dump of all the databases, simply add the option --one-database telling it to ignore other database except for the one defined in commmand like so:

$> mysql  -ujohndoe -p123 --one-database primary_DB < DB_complete.sql	

Tags:MYSQL, SQL tutorial