MonkeyBrains.net/~rudy/example Random examples

backup_mysql.sh

Dump your MySQL onto the NAS!

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


  • Resource for FreeBSD gmirror software RAID
  • Resource for Linux software RAID
  • Resource for 3ware RAID cards