Taking all mysql db backup is very simple if you use this small shell scripts, just create a file named mysqlbackup.sh and paste the all the text below:
#!/bin/bash
#
NOW=$(date +”%m-%d-%Y”) # mm-dd-yyyy format
FILE=”" # used in a loop
BAK=”/home/backups/mysql” # Path to backup dir
### Server Setup ###
#* MySQL login user name *#
MUSER=”dbbackup”
#* MySQL login PASSWORD name *#
MPASS=”password”
#* MySQL login HOST name *#
MHOST=”localhost”
#* MySQL binaries *#
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
GZIP=”$(which gzip)”
# get all database listing
DBS=”$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse ‘show databases’)”
# start to dump database one by one
for db in $DBS
do
FILE=$BAK/$db-backkup.$NOW-$(date +”%T”).gz
# gzip compression for each backup file
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
Create a cron job that will execute this script regularly at a specific time, in my case i set it to 22:30 PM every night and here is my cron job
30 22 * * * sh /path/to/your/mysqlbackup.sh
Create another cron job that will execute at a specific time to clean your backup files older than 5 days
25 22 * * * find /home/backups/mysql/ -name ‘*.sql’ -and -mtime +4 | xargs rm -f
That’s it.
