/var/net/sys/admin/blog
| More

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

Share

1 Response to “Backup and Restore MySQL database”

  1. Saurabh Pant

    on March 24 2012

    This is best answer so far I’ve read online. You’ve elaborate it very nicely. It helped me lot, thanks for sharing with us. Over the internet, I’ve found some other good articles which also explained very well about Restoring MySQL database. Here I’m sharing the posts link of those article….
    http://mindstick.com/Blog/241/Restoring%20MySQL%20Database
    http://www.roseindia.net/mysql/mysql_backup_restore.shtml

    At last!! I would like to thanks everyone for your valuable post which helped me lot in completing my task.

Comment RSS · TrackBack URI

Leave a comment

Name: (Required)

E-mail: (Required)

Website:

Comment:

 

About FLT

This site is dedicated to everyone who likes to learn and explore the beautiful world of Linux. If you have comments and suggestions, please feel free to email at freelinuxtutorials@gmail.com. I am happy to serve and share things esp. that is free and enjoyable as Linux.