1) To make a backup of a single MySQL database

You have to make backup before you enter inside your MySQL system i.e. by typing mysql –u username –p password . (Here, username and password indicates your MySQL server username and password.)

Hence, go to your MySQL root path.

Type “mysqldump –u username –p password database_name > backup.sql” (without quote)

Here, database_name is the name of the database of which you want to create backup and backup.sql is the backup file name. You can name it as yourname.sql or whatever you like.

The file backup.sql created from the above procedure will be saved inside the ‘bin’ directory (root directory) of the MySQL server.

You can specify other location as well for the file backup.sql .

Type “mysqldump –u username –p password database_name > E:/backup.sql” (without quote)

This will save the file to the drive E [for windows OS]

Hope, unix, linux users know it well to specify location path.

Incase, your MySQL server password is blank, you need not have to type –p password.

You can type

mysqldump –u username database_name > backup.sql

Or, if your MySQL username and password both are blank than you can type

mysqldump database_name > backup.sql

2) To make a backup of multiple MySQL database

Type

mysqldump –u username –p password –databases db1 db2 db3 > threedb.sql

Here, db1, db2, db3 are three different database name. You can use more databases, similarly.

3) To make a backup of all MySQL databases

Type

mysqldump –u username –p password –all-databases > alldb.sql

4) To read back the backup we have made i.e. to create tables and databases from the backup file

If you had made a backup file of a single database, then you should create a database in your MySQL server. Then type “mysql –u username –p password db_name < backup.sql”

Here, db_name is the database name in your MySQL server. All tables are created inside this database from the backup.sql file.

If you had made a backup file of multiple databases, then you don’t have to create database beforehand. The backup file consists of the query to create all the databases and tables. Note it here, that you don’t have to specify the database name. You just have to type “mysql –u username –p password < backup.sql” and that’s all.

Note: Be careful to specify the proper location of your backup.sql file.

Enjoy!!!