In essence in order to fully backup and restore a MySql database server, you just need to use two commands mysqldump and mysql. For a “small databases” scenario you can find at the end a simple script to send the backup by e-mail. In conjunction with crontab you can almost name it a backup policy.

  • MySQL backup>
mysqldump -u <user> -p <passwd> --all-databases > <backup file>
  • MySQL restore>
mysql -u <user> -p <passwd> < <backup file>
mysql --execute "update mysql.user set host='old' where host='host';"

Backup script example

#!/bin/bash
# This script runs nightly to back a MySQL database to a e-mail account
DATE1=`date +%D`
DATE2=`date +%Y%m%d%H%M`
# The email address that receives backups
ADDRESS="alias@domain"
# Local temp directory
BACKUPDIR="/tmp"
# MySQL username and password
MYSQLUSER="user"
MYSQLPASS="pass"
# MySQL database to backup (or --all-databases to backup everything)
DATABASE="--all-databases"
# Name of backup file
FILENAME="mysql\_backup"
# Change to the backup directory
cd $BACKUPDIR
# Dump the MySQL databases to a plaintext file
mysqldump -u $MYSQLUSER -p $MYSQLPASS $DATABASE > $FILENAME\_$DATE2
# Package (tar) and compress (gzip) the plaintext MySQL dump
tar czf $FILENAME\_$DATE2.tar.gz $FILENAME\_$DATE2
# Mail the resulting archive to our Gmail account
date | mutt -s "$FILENAME ($DATE1)" -a $FILENAME\_$DATE2.tar.gz $ADDRESS

thanks to Bruno Rodrigues