10 Jan, 2019

Script backup DB Mysql

#!/bin/bash
# Shell script to backup MySQL database

# Set these variables
MyUSER=”root” # DB_USERNAME
MyPASS=”” # DB_PASSWORD
MyHOST=”localhost” # DB_HOSTNAME

# Backup Dest directory
DEST=”/var/www/clients/client1/web1/backup_db” # backups_directory

# Email for notifications
EMAIL=”am@mel.kiev.ua”

# How many days old files must be to be removed
DAYS=2

# 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
performance_schema”

# 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 –lock-tables=false –events –ignore-table=mysql.event -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 {} \;

You may also like...

Leave a Reply

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