Mysqldump

When using MySQL, I always use a .my.cnf file to store my password so that I can switch to 'root' on the host, and execute whatever commands I need.

file=~/.my.cnf
touch $file
chmod 600 $file
cat <<EOF >> $file
[client]
user=root
password=SuperSecretSauce
EOF

Backup Script

Here's a quick recipe using mysqldump

cat ./backup.db.sh

#!/bin/sh

DB=wiki
backupdir="$HOME/backups";
if [ ! -d "$backupdir" ]; then
  mkdir -p $backupdir;
fi
backup="$backupdir/dump-$(date +%F).$(hostname)-$DB.sql";
# increment the filename if it already exists
# http://www.gnu.org/software/bash/manual/html_node/Shell-Parameter-Expansion.html#Shell-Parameter-Expansion
i=1
filename=$(basename "$backup") # foo.txt
extension=${filename##*.}      # .txt
file=${filename%.*}            # foo
while [ -f $backup ]; do
  backup="$backupdir/${file}.${i}.${extension}"
  i=$(( i+1 ))   # increments $i
done
/usr/bin/mysqldump $DB > $backup;
ls -al $backup;

Backup One-liner

For times when you need to enter a password

db=MYDATABASE;
mysqldump -u db_user $db -p > ./tmp/dump-$(date +%F).$(hostname)-$db.sql

For all databases on a host

mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/'
# Then just redo the command piped to sh

Restore

mysql $DB < $backup

Using process substitution and zcat, you don't even need to uncompress your gzipped backups first.

mysql -p -u db_user db < <(zcat ./scheduled/eQualityTechnology-2015-03-15T23-11-50.mysql.gz)