Create folder

mkdir /etc/scripts

edit backup files

vim backup_mysql.sh
#!/bin/bash
 
USER="dbuser"
PASSWORD="dbpassword"
OUTPUT="/opt/backup_mysql/"
 
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
 
for db in $databases; do
 if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
 echo "Dumping database: $db"
 mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/`date +%Y%m%d`.$db.sql
 gzip $OUTPUT/`date +%Y%m%d`.$db.sql
 fi
done

Delete old backups:

#!/bin/bash
find /opt/backup_mysql/* -mtime +6 -exec rm {} \;

Change permission:

chmod 700 backup_mysql.sh
Add task
crontab -e
#delete old backups
0 2 * * * SHELL=/bin/sh PATH=/bin:/sbin:/usr/bin:/usr/sbin /etc/scripts/delete_old_backups.sh > /dev/null 2>&1
#new backup
0 3 * * * SHELL=/bin/sh PATH=/bin:/sbin:/usr/bin:/usr/sbin /etc/scripts/backup_mysql.sh > /dev/null 2>&1
Advertisements