Backup and Restore MySQL - so easy that caveman can do it...

There are various ways to perform backup and restore the MySQL database. However, I always found command line to be very easy and powerful.

Command: mysqldump

Usage:

1. Backup only one database

mysqldump -h [hostname] -u [username] -p [password] [databasename] > [backupMySQLfile.sql]

[hostname] - the hostname or IP address or loopback address i.e 127.0.0.1

[username] - the database username

[password] - the password for your database

[databasename] - the name of your database

[backupMySQLfile.sql]- the file to which the backup should be written.

The dump file will contain the SQL statements required to create the table and populate the table. Let's see one example of backing up database 'AsteriskCDR' with the username 'root' and password 'abcd1234' to a file 'DailyBackupCDR.sql'

#mysqldump -h 127.0.0.1 -u root -pabcd1234 AsteriskCDR > DailyBackupCDR.sql

[Caution: there shouldn't be the space between -p option and password]
[Note: Your system may not require to use '-h 127.0.0.1' ]


If you want to create the backup that will automatically overwrite the existing database while restoring the database without having to delete the older database manually first, you need to use the option --add-drop-table.

#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR > DailyBackupCDR.sql

2. Backup with compression

Dump file are usually huge, so it's better idea to apply compression while dumping the file.

Using GZIP compression
#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR gzip > DailyBackupCDR.sql.gz

or

Using ZIP compression
#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR gzip > DailyBackupCDR.sql.zip

or

Using BZIP2 compression
#mysql --add-drop-table -u root -pabcd1234 AsteriskCDR bzip2 > DailyBackupCDR.sql.bz2

3. Restore the database

The backup dump file can be easily restored using the mysql command. In fact, it rebuilds the database from the scratch.

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

#msyql -u root -pabcd1234 AsteriskCDR < DailyBackupCDR. sql


Compressed dump files need to be uncompressed before restore.

#unzip DailyBackupCDR.sql.zip [for zipped file]
#gunzip DailyBackupCDR.sql.gz [for gzipped file]
#bunzip2 DailyBackupCDR.sql.bzip2 [for compression using bzip2]

unzip is the counterpart of zip
gunzip is the counterpart of gzip
bunzip2 is the counterpart of bzip2

Once the file is uncompressed, it will result in the regular dump file which can be restored using msyql command
#msyql -u root -pabcd1234 AsteriskCDR < DailyBackupCDR. sql



4. Backup entire database

#mysqldump --all-databases > alldatabases.sql

5. Backup only the database structure

#mysqldump --no-data --all-databases > alldatabases.sql


For more : http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/