2006
07.27
07.27
Here is a good script for backing up a MySQL database nightly in a cron job. We use it here and run it from the root user’s cron job. It just keeps a rotating set of backups and shoots you an e-mail with the results each night. Before you install the script create however many days worth of backup files you want to keep. For instance if you want to always have 5 days worth of backups on hand, execute these commands as root:
# touch /root/mysql-nightly1.sql # touch /root/mysql-nightly2.sql # touch /root/mysql-nightly3.sql # touch /root/mysql-nightly4.sql # touch /root/mysql-nightly5.sql
Here is the script:
#!/bin/sh
##: Go home
cd /root
##: Keep five days of backups on hand
dumpfile=(`ls -1tr mysql-nightly?.sql`)
dbserver=’localhost’
dbadmin=’you@domain.com’
mysqldump --host=$dbserver --user=user --password=password \
--all-databases > ${dumpfile[0]}
if [ $? -eq 0 ]; then
echo "Successfully dumped MySql database from $dbserver."\
| mail -s "INFO: MYSQL DATABASE DUMPED" $dbadmin
else
echo "Error dumping MySql database from $dbserver." \
| mail -s "ALERT: ERROR DUMPING MYSQL" $dbadmin
fi
##: Restart the database server
/sbin/service mysqld restart
cd -
Here is the cron job entry:
18 21 * * * /root/backup-database.sh > /dev/null
