Friday, 11 July 2014

Database Backup script

#!/bin/bash
export savepath='/var/mysqlbackups'
export usr='mysql user'
export pwd=''
if [ ! -d $savepath ]; then
    mkdir -p $savepath
fi
chmod 700 $savepath
rm -rf $savepath/*
echo 'mySQL Backup Script'
echo 'Dumping individual tables..'
for a in `echo 'show databases' | mysql -u$usr -p$pwd | grep -v Database | grep -v information_schema`;

do
echo $a
  mkdir -p $savepath/$a
  chmod 700 $savepath/$a
  echo "Dumping database: $a"
echo
for i in `mysqldump --no-data -u $usr -p$pwd $a | grep 'CREATE TABLE' | sed -e 's/CREATE TABLE //' | sed -e 's/(.*//' | sed -e 's/\ /|/g' |sed -e's/|$//'`
  do
   echo "i = $i";
   c=`echo $i|sed -e's/|/\ /g'|sed -e 's/\`//g'`;
   echo " * Dumping table: $c"
   mysqldump --compact --allow-keywords --add-drop-table --allow-keywords --skip-dump-date -q -a -c -u$usr -p$pwd $a "$c" > "$savepath/$a/$c.sql"
   gzip -f "$savepath/$a/$c.sql"
   chmod 600 "$savepath/$a/$c.sql.gz"
  done
done

No comments:

Post a Comment