MySQL/Linux: Creating and restoring database backups using command line

Most of us have worked on different database technologies. MySQL is one amongst them.This tutorial explains the backup/restore process of db's in mysql.

Mysql provides a tool mysqldump which allows the user to take the backup of current state of the databases and restore them at a later point in time,if required.This tool comes in with the default installation of mysql server and can be used on the command line.

In this tutorial i am using mysql-server-5+ and Linux OS command line to explain the usage of this tool.

For a single database

Suppose you need to backup a single database.This command will do your work.

mysqldump -u root -p[root_password] [database_name] >filename.sql

So to backup a database named 'testing',the command would be

mysqldump -u root -p[root_password] testing > testing.sql

Note:The dump file testing.sqlwill be generated and stored in the directory location where you issue this command.However,you can specify your own directory location.Something like this 

mysqldump -u root -p[root_password] [database_name] > /path/to/file

Now comes the restore part.To restore a dump into a database,the syntax is
mysql -u root -p[root_password] [database_name] < filename.sql

where filename.sql is the dump file generated using mysqldump command.

Suppose you need to restore the dump file generated in the previous step to a database named 'testing1'.The command is

mysql -u root -p[root_password] testing1 < testing.sql

Note: The database into which the dump needs to be restored(in this case testing1) should be created in advance (using the Create database) before dumping the content of the dump into file.Not doing so will throw up an error when the restoration is done.

For multiple databases

Sometimes it is necessary to back up more that one database at once. In this case you can use the --databases option followed by the list of databases you would like to backup. Each database name has to be separated by space.

mysqldump -u root -p[root_password] --databases db1 db2 > content_backup.sql

The above command will dump two databases db1 and db2 into the content_backup.sql file.

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

mysqldump -u root -p[root_password] --all-databases > alldb_backup.sql

Selective backup with mysqldump

The mysqldump utility also allows you to take selective backups in a database.You may want to backup one or more tables in database or,backup only selective data or records(using 'where' conditional clause).

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only blog_template1 and blog_template2 tables from the ‘Templates’ database,use the command below. Each table name has to be separated by space.

mysqldump -u root -p[root_password] \blog_template1 blog_template2  > dbtemplate_backup.sql

For backing up or dumping only selective records in tables we can use the --where or -w option.For example,to backup the records from table t1 in the database db1 where vendor is google, the command will be

mysqldump -u root -p[root_password] db1 \t1 --where "vendor='google'" > t1.sql

This completes the tutorial on backing up/restoring data in mysql.Comments and feedbacks will be appreciated.Cheers!!!