You are backing up your personal files, now what about the database. Databases are just as susceptible to hard disk failures, viruses, and accidental deletion as your personal data. Maybe even more so if your database is getting frequent updates. In this post we will cover the back up and recovery of a MySQL database.

To backup the database:

[user@host:~]$ mysqldump -u [uname] -p[pass] --routines [dbname] > [backup.sql]
Where:
    [name] database username
    [pass] password for username (no space between -p and the password)
    [dbname] The name of the database (case sensitive)
    [backup.sql] The filename of the database backup
    --routines Backup stored procedures, triggers are always on (optional)

To restore the database, use the database backup as an input:

[user@host:~]$ mysqldump -u [uname] -p[pass] [dbname] < [backup.sql]
Where:
    [name] database username
    [pass] password for username (no space between -p and the password)
    [dbname] The name of the database (case sensitive)
    [backup.sql] The filename of the database backup

Bonus:
To reduce the size of the backup, pipe it to gzip:

[user@host:~]$ mysqldump -u [uname] -p[pass] [dbname] |gzip > [backup.sql.gz]
[user@host:~]$ gunzip < [backup.sql.gz] | mysql -u [name] -p[pass] [dbname]

and here is a shell script to backup all your databases to individual files:

#! /bin/sh

pass=password
timeStamp=$(date +%Y%m%d-%H%M%S)
user=username

# Get a list of databases
dbList=$(mysql -u ${user} -p${pass} -Bse 'show databases')

for db in $dbList
do
    echo Backing up $db
    mysqldump -u ${user} -p${pass} --routines ${db} | gzip -9 > ${db}-${timeStamp}.sql.gz
done

References:
Database Backup Methods
Using mysqldump for Backups

http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html
http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
http://www.webcheatsheet.com/SQL/mysql_backup_restore.php

Leave a Reply

You must be logged in to post a comment.