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