Backup and Restore MySQL database

Quick tip in backing up and restore your MySQL database:

Backing up and Restoring MySQL database

Backing up database:
Syntax:
mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
o    [username] -database username
o    [password] -password for your database
o    [databasename] – the name of your database
o    [backupfile.sql] – the file to which the backup should be written.
Example:
mysqldump -u root -p asterisk > asteriskbackupmarch3.sql
(just input the password when prompt for password)

Multiple database:

Syntax:
mysqldump -u [username] -p [password] –databases  [databasename1] [databasename2] > [backupfile.sql]

Example:
mysqldump -u asteriskuser -p –databases asterisk asteriskcdrdb > multibackup.sql
(then input db password)

Backup file in compressed format
Syntax:
mysqldump –all-databases | bzip2 -c >databasebackup.sql.bz2
mysqldump –all-databases | gzip >databasebackup.sql.gz

Restoring Database:

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

Example:
mysql –u asteriskuser –p asterisk < asteriskbackup.sql
<input password when prompt>
mysql –u asteriskuser –p asteriskcdrdb < asteriskcdrdbbackup.sql
<input password when prompt>

Restoring compressed backup files

Example:

gunzip < databasebackup.sql.sql.gz | mysql -u asteriskuser -p asterisk

Addon: Sample Shell Scripts for automating backups

Back up your database without overwriting the older backup

#!/bin/sh
date=`date -I`
mysqldump –all-databases | gzip > /var/backup/backup-$date.sql.gz