The MySQL utility command mysqldump can be used to take backup of MySQL database. The output generated from this command can be used to create the same database in another host.

In the following example, we try to login to MySQL server as “root” with password and try to dump the contents of database “mydb“.

[neo@techpulp ~]# mysqldump -u root -p mydb > mydb-backup.sql
Enter password:
[neo@techpulp ~]# ls -l mydb-backup.sql
-rw-rw-r-- 1 neo neo 23876589 2008-10-04 03:29 mydb-backup.sql
[neo@techpulp ~]#

The output present in the file mydb-backup.sql will contain a series of MySQL statements and exactly same database can be rebuilt from scratch using this file.

Let us regenerate the same database on a different host or on the same host by deleting the existing database.
If you want to delete a database, use the following MySQL command.

[neo@techpulp ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
mysql> drop database mydb;
Query OK, 0 rows affected (0.01sec)
mysql>

Now let us just create a blank database using the following MySQL command.

mysql>
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[neo@techpulp ~]#

Use the backup file mydb-backup.sql to populate the blank database.

[neo@techpulp ~]# mysql -u root -p mydb < mydb-backup.sql
Enter password: