Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL backup script
#!/bin/sh
BACKUP=/var/www/html/mysql_backup/backups
cd $BACKUP
sudo mkdir `date '+%d-%m-%Y'`
NOW=$(date +"%d-%m-%Y")
MUSER="user"
MPASS="pass"
MHOST="host"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
MAIL="info@example.com"
STATUSFILE="/tmp/statusfile.$NOW"
# It succeeds but stderr will get:
# Warning: Using a password on the command line interface can be insecure.
# You can fix this with the below hack:
credentialsFile=/mysql-credentials.cnf
echo "[client]" > $credentialsFile
echo "user=$MUSER" >> $credentialsFile
echo "password=$MPASS" >> $credentialsFile
echo "host=$MHOST" >> $credentialsFile
echo "Backup report from $NOW" > $STATUSFILE
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=$BACKUP/$NOW/mysql-$db.$NOW-$(date +"%T").sql.gz
$MYSQLDUMP --defaults-extra-file=$credentialsFile --lock-all-tables $db | $GZIP -9 > $FILE
if [ "$?" -eq "0" ]; then
echo "$db backup is OK" >> $STATUSFILE
else
echo "##### WARNING: ##### $db backup failed" >> $STATUSFILE
fi
done
# delete backups older than 1 week
find /path/to/base/dir/* -type d -ctime +10 -exec rm -rf {} \;
mail -s "Backup report for $NOW" -- $MAIL < $STATUSFILE
rm $STATUSFILE
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.