Using mysqldump

Using ‘mysqldump’ command you can take the mysql database backup. This command all the datas to a text file.

The syntax is

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] –databases db_name1 [db_name2 db_name3…]
shell> mysqldump [options] –all-databases

Backuping Examples:

shell> mysqldump -u root -p dbaseName > file.sql;

shell> Enter the password

Restoring Example:

Suppose, we have the database as file.sql, and we want to load it to the database, we can use ‘mysql’ command to restore the database.

shell> mysql -u root -p passwod dbaseName < file.sql

shell> Enter the password

Before executing this, make sure you are in the bin folder of mysql

Using mysqlhotcopy

Syntax:
shell> mysqlhotcopy -u user -p password dbname

Backuping Examples:
shell> mysqlhotcopy -u user -p password mydb1 mydb2 /backup/location/

This will create a directory with the database name called ‘mydb1’ and ‘mydb2′ in /backup/location/’

Restoring Example:
To restore the backup you have to stop mysql using
shell> /etc/init.d/mysql stop

Then you have to copy the database backup file which is in the backup directory to the original mysql director which is ‘/var/lib/mysql/mysql1’