Here is a cronjob that will dump all your databases for separate directories on your NAS and put the data in the filename. Old dumps
are automatically flushed.
#!/bin/sh
## backup_mysql.sh
## backup all the tables to an NFS mount.
# Set up a cronjob like this:
# 16 1 * * * /home/rudy/bin/backup_mysql.sh
### Copyright (c) 2008, Rudy Rucker All rights reserved.
### Redistribution and use of script, with or without modification, is
### permitted provided that the following condition is met:
### Redistributions of source code must retain the above copyright
### notice, this list of conditions and the following disclaimer.
### THIS SOFTWARE IS PROVIDED BY AUTHOR AND CONTRIBUTORS ``AS IS'' AND
### ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
### IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
### ARE DISCLAIMED.
# put your passwords in another file so
# they are not shown when putting this script on the web ;)
. /root/cron-scripts/cronjob-mysql.cnf
### NAS base:
NAS_PATH=/nas/mysql
NAS_USER=mysql
### executables we will need
MYSQL=/usr/local/bin/mysql
MYSQLDUMP=/usr/local/bin/mysqldump
AWK=/usr/bin/awk
BZIP=/usr/bin/bzip2
FIND=/usr/bin/find
DATE=/bin/date
SU=/usr/bin/su
WC=/usr/bin/wc
RM=/bin/rm
### grab all database names
ALL_DBS=`$MYSQL --user=$MYUSER --pass=$MYPASS -e "show databases\G;" | $AWK '$1 == "Database:" {print $2}'`
if [ -z "$ALL_DBS" ]; then
echo "ERROR, did not find list of DB names."
echo "Script: $0"
echo "Host: `hostname`"
exit;
fi
# for every database found...
for DB in $ALL_DBS; do
# set the backup path
DUMP_PATH="${NAS_PATH}/$DB"
# generate a filename for the backup.
DUMP_FILE=`$DATE +"$DUMP_PATH/$DB-%Y-%m-%d.sql.bz2"`
# make sure path is there... create it as 'mysql' if it ain't
if [ ! -d $DUMP_PATH ]; then
echo "Setting up backup directory for New database, $DB."
echo "Path will be: $DUMP_PATH"
$SU -m ${NAS_USER} -c "mkdir $DUMP_PATH"
/usr/bin/logger "set up new backup directory, $DUMP_PATH"
fi
# nuke old files... prevent disk waste
FIND_ARGS="-mtime +7 \
! -name '*-07.sql.bz2' \
! -name '*-14.sql.bz2' \
! -name '*-21.sql.bz2' \
! -name '*-28.sql.bz2'"
TO_KEEP=`$FIND $DUMP_PATH -name '*bz2' ! \( $FIND_ARGS \) | $WC -l`
# make sure we will have at least 10 around before deleteing anything...
if [ $TO_KEEP -gt 10 ]; then
OLD_FILES=`$FIND $DUMP_PATH -name '*bz2' $FIND_ARGS`;
for OLD_FILE in $OLD_FILES; do
SU -m ${NAS_USER} -c "$RM $OLD_FILE"
done
fi
# dump to file if it doesn't exist
if [ ! -f $DUMP_FILE ]; then
# Must SU to mysql cause NAS mount is owned by mysql...
###echo $DUMP_FILE # uncomment for debugging #
$SU -m ${NAS_USER} -c "$MYSQLDUMP --user=$MYUSER --pass=$MYPASS \
--add-drop-table --add-locks --databases $DB \
| $BZIP > $DUMP_FILE"
else
/usr/bin/logger "Skipping backup. File already there... $DUMP_FILE"
fi
done
|