Quick dump or backup all databases in MySQL server

Dump all databases to one file

mysqldump -u root -pPASSWORD --all-databases | gzip > database_`date -I`.sql.gz

Dump all MySQL databases to many separate files (one file for database)

You can use this script mydump2many.sh. Here is source code:

#!/bin/bash

# Author: Tomasz Gaweda
# Date:   2011-01-08

# change this
# THIS IS NOT SECURE THINK
# ABOUT CREATING ~/.my.cnf file.
MY_USER="root"
MY_PASS="PASSWORD"
MY_HOST="127.0.0.1"

if [ $# -lt 1 ] ; then
 echo "usage: "$0" directory_to_backup"
 exit 1
fi

MY_BAK_DIR=$1

GZIP="$(which gzip)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

NOW=`date -I` # mm-dd-yyyy format

MY_DBS="$($MYSQL -u $MY_USER -h $MY_HOST -p$MY_PASS -Bse 'show databases')"
if [ ! -d "$MY_BAK_DIR" ] ; then
 mkdir -p "$MY_BAK_DIR"
fi

for DB in $MY_DBS ; do
        FILE="${MY_BAK_DIR}/${NOW}_${DB}.gz"

 # ignore this databases in backup
 case "$DB" in
  "mysql")   continue ;;
  "information_schema") continue ;;
 esac

 echo -e "Please wait while dumping ${DB} ... "
 if [ -f "$FILE" ] ; then
  # file already exists we are probably resuming
  echo "SKIP"
  continue;
 fi
 START=$(date +%s)
 $MYSQLDUMP -u $MY_USER -h $MY_HOST -p$MY_PASS $DB | $GZIP -9 > "${FILE}"
 RC=${PIPESTATUS[0]}
 END=$(date +%s)
 DIFF=$(( $END - $START ))

 echo -ne "Dump of ${DB} ... "
 if [ $RC -eq 0 ] ; then
  # backup OK
  echo "SUCCEDED took ${DIFF} seconds"
 else
  # something went wrong - delete partial backup file
  rm -f "${FILE}"
  echo "FAILED"
  exit 1 # exit on error
 fi
done

Usage:

$ bash mydump2many.sh backup
Please wait while dumping Medchem ...
Dump of Medchem ... SUCCEDED took 0 seconds
Please wait while dumping People ...
Dump of People ... SUCCEDED took 401 seconds
Please wait while dumping TextMining ...
Dump of TextMining ... SUCCEDED took 3027 seconds

Using logrotate utility to make MySQL server backup

If you are searching for comprehensive backup solution on Linux/Unix including rotation of older databases dumps you could use logrotate utility for backup here is example provided by Vivek.

Remember this solution is not the best option (in fact it forces logrotate to do things, that it shouldn’t do)! It is rather quick and dirty method for getting things done at low cost.

/var/backups/db.sql.gz {
    daily
    rotate 14
    nocompress
    create 640 root adm
    postrotate
    mysqldump db -u user -psecret >/var/backups/db.sql
    gzip -9f /var/backups/db.sql
    endscript
}

Comments

comments powered by Disqus