Tag: linux

Backup all MySQL databases

pbarisLeave a comment 
#! /bin/bash

TIMESTAMP=`date +%Y-%m-%d_%H-%M-%S`
BACKUP_DIR="/backup"
BACKUP_TIMESTAMP_DIR="$BACKUP_DIR/$TIMESTAMP"
MYSQL=/usr/bin/mysql
MYSQL_USER="user"
MYSQL_PASSWORD="pass"
MYSQLDUMP=/usr/bin/mysqldump

mkdir -p "$BACKUP_TIMESTAMP_DIR"

echo "Starting MySQL Backup";
echo `date`;

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|mysql|information_schema|performance_schema)"`

for db in $databases; do
  echo "Dumping database: $db"
  $MYSQLDUMP --force --opt --skip-comments --compact --user=$MYSQL_USER -p$MYSQL_PASSWORD --single-transaction --databases $db | gzip > "$BACKUP_TIMESTAMP_DIR/$db.sql.gz"
done

tar -zcf "$TIMESTAMP.tar.gz" -C "$BACKUP_TIMESTAMP_DIR" .
rm -rf "$BACKUP_TIMESTAMP_DIR"

find "$BACKUP_DIR" -name "*.tar.gz" -mtime 182 -exec rm -f {} \;

echo "Finished MySQL Backup";
echo `date`;