Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL backup shell script
#!/bin/bash
# Shell script to backup MySQL database
# Set these variables
MyUSER="" # DB_USERNAME
MyPASS="" # DB_PASSWORD
MyHOST="" # DB_HOSTNAME
# Backup Dest directory
DEST="" # /home/username/backups/DB
# Email for notifications
EMAIL=""
# How many days old files must be to be removed
DAYS=3
# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y_%s")"
# Create Backup sub-directories
MBD="$DEST/$NOW/mysql"
install -d $MBD
# DB skip list
SKIP="information_schema
another_one_db"
# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER -p$MyPASS -Bse 'show databases')"
# Archive database dumps
for db in $DBS
do
skipdb=-1
if [ "$SKIP" != "" ];
then
for i in $SKIP
do
[ "$db" == "$i" ] && skipdb=1 || :
done
fi
if [ "$skipdb" == "-1" ] ; then
FILE="$MBD/$db.sql"
$MYSQLDUMP -h $MyHOST -u $MyUSER -p$MyPASS $db > $FILE
fi
done
# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar
echo "MySQL backup is completed! Backup name is $NOW.tar.gz" | mail -s "MySQL backup" $EMAIL
rm -rf $NOW
# Remove old files
find $DEST -mtime +$DAYS -exec rm -f {} \;
@mhawila

This comment has been minimized.

Copy link

mhawila commented May 17, 2018

Thanks for the script. I think I will opt out of the mailing part though. Good work!

@SNOWGUM

This comment has been minimized.

Copy link

SNOWGUM commented Aug 17, 2018

Hey, I gave this a shot without the email much like @mhawila, the file was created properly but the MySQL Dump didn't work although all connection information is correct?

@x011

This comment has been minimized.

Copy link

x011 commented Dec 3, 2018

Thank you for the script.I It would be nice to have a restore script from the same archive.

@dkduyanh

This comment has been minimized.

Copy link

dkduyanh commented Dec 27, 2018

Thanks for the script.

@metalcristh

This comment has been minimized.

Copy link

metalcristh commented Jan 2, 2019

Hi,
How i do to include --routines in this script!?

This way, i backuped just data, not includding functions/S.Procedures

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.