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
# @author Greg Rundlett <info@eQuality-Tech.com>
# This is a quick shell script to create a sql dump of your database.
# You may need to adjust the path of mysqldump, 
# or sudo apt-get install mysqldump  if it doesn't exist

# We'll make it so you can pass the database name as the first parameter 
# to the script for playbook / cron / non-interactive use
# If no parameter is passed, we'll prompt you for the name
DB=$1
if [ $# -ne 1 ]; then 
  echo "Here are the current databases on the server"
  mysql -u root --batch --skip-column-names -e 'show databases;'
  echo "Enter the name of the database you want to backup"
  read DB
fi
# If on a Virtual Machine, use a location that is exported to the host, 
# so that our backups are accessible even if the virtual machine is no longer accessible.
# backupdir="/vagrant/mediawiki/backups";
backupdir="/backups";
if [ ! -d "$backupdir" ]; then
  mkdir -p "$backupdir";
fi

# shell parameter expansion 
# see http://www.gnu.org/software/bash/manual/html_node/Shell-Parameter-Expansion.html
# we'll start with a default backup file named '01' in the sequence
backup="${backupdir}/dump-$(date +%F).$(hostname)-${DB}.01.sql";
# and we'll increment the counter in the filename if it already exists
i=1
filename=$(basename "$backup") # foo.txt (basename is everything after the last slash)
extension=${filename##*.}             # .txt (filename with the longest matching pattern of *. being deleted)
file=${filename%.*}                         # foo (filename with the shortest matching pattern of .* deleted)
file=${file%.*}                                  # repeat the strip to get rid of the counter
# file=${filename%.{00..99}.$extension} # foo (filename with the shortest matching pattern of .[01-99].* deleted)
while [ -f $backup ]; do
  backup="$backupdir/${file}.$(printf '%.2d' $(( i+1 ))).${extension}"
  i=$(( i+1 ))  # increments $i 
  # note that i is naked because $(( expression )) is arithmetic expansion in bash
done
if /usr/bin/mysqldump --single-transaction "$DB" > "$backup"; then
  echo "backup created successfully"
  ls -al "$backup";
  echo "A command such as"
  echo "mysql -u root $DB < $backup" 
  echo "will restore the database from the chosen sql dump file"
else
  echo "ERROR: Something went wrong with the backup"
  exit 1
fi

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 --single-transaction \1 > \1.'$(date +"%Y%m%d")'.sql/'
# Then just redo the command piped to sh

One of many ways to dump a database from one machine to another

sudo mysqldump --single-transaction mydb | gzip -c | ssh ubuntu@10.0.50.53 'cat > /home/ubuntu/mydb.dump.sql.gz'

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)