Auto Backup MySQL Ubuntu

#!/bin/bash

#Options##############
BKFOLDER=’/.backup/db’
DBHOST=’localhost’
DBUSER=’db_user’
DBPW=’db_password’
DBNAME=(  ‘dbname’ )
#for all databases the user has access to, keep this uncommented:
#DBNAME=( `echo “show databases” |  mysql –user=$DBUSER –password=$DBPW –host=$DBHOST  | tail -n+3 `)

#End Options #########

echo “check if local .backups directory exists. if not create it”;
if [ ! -d “$BKFOLDER” ]; then
mkdir -p $BKFOLDER ;
fi

echo “remove db files older than 6 day”;
find $BKFOLDER/*.tar.gz -type f -daystart -mtime +5 -exec rm {} \;

echo “get recent version of databases”;
for i in “${DBNAME[@]}”
do
FILENAME=$BKFOLDER/$i-`date +\%Y\%m\%d-\%H\%M\%S`.sql
mysqldump –opt –user=$DBUSER –password=$DBPW –host=$DBHOST $i –lock-tables=false   > $FILENAME
tar -zcvf $FILENAME.tar.gz $FILENAME
rm $FILENAME
done

echo “all done!”

#cron
#0 0 * * * /.backup/backupmysql.sh

Leave a Reply

Your email address will not be published. Required fields are marked *