A bash script to backup MySQL databases automatically
I wrote a bash script that backs up all my MySQL databases:
The script
echo logging in
ssh -tt your_host_username@your_hostname <<'ENDSSH'
stty -echo
USER="your_MySQL_username"
PASSWORD="your_MySQL_password"
BACKUP_DIRECTORY=~/backups/MySQL
BACKUP_FOLDER=$(date +%F_%H-%M-%S)
rm -rf $BACKUP_DIRECTORY/*
mkdir $BACKUP_DIRECTORY/$BACKUP_FOLDER
databases=`MySQL -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
stty echo
for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "MySQL" ]] && [[ "$db" != _* ]] ; then
echo "Dumping database: $db"
MySQLdump -u $USER -p$PASSWORD --databases $db -r $BACKUP_DIRECTORY/$BACKUP_FOLDER/$db.sql
fi
done
echo "logging out"
exit
ENDSSH
stty echo
echo "downloading files, please wait"
sleep 5
rsync --recursive your_host_username@your_hostname:backups/MySQL/ ~/Bluehost_backups/Databases/
echo "done"
Explanation
Here's how the script works. First, the script logs in to the server. Notice that the server doesn't ask for a password. That's because I'm using SSH keys.
Then the script removes the last backup from the server:
rm -rf $BACKUP_DIRECTORY/*
This is so we don't annoy the hosting company by keeping loads and loads of backups on the server.
Next, the script makes a new folder where it will place the latest backup files:
mkdir $BACKUP_DIRECTORY/$BACKUP_FOLDER
Then the script downloads all the MySQL databases.
MySQLdump -u $USER -p$PASSWORD --databases $db -r $BACKUP_DIRECTORY/$BACKUP_FOLDER/$db.sql
Note that for this part to work, you need to create a MySQL user and give that user download permissions for all the databases. With Bluehost, you do this by going to the cPanel and selecting MySQL Databases.
Then the script logs out of the server. Finally, it uses rsync to download the backup from the server to your computer.
Making the script run automatically
You can tell your computer to run the script automatically every day by doing
crontab -e
then adding
0 23 * * * ~/backup_bluehost_databases.sh
to the bottom of the crontab file.
Leave a comment