FreeBSD Backup All MySQL Databases To a NAS Server

#!/bin/sh
# A Shell script to backup all MySQL databases to a NAS server mounted via mount_smbfs
# You need to setup username, password and other stuff
# Tested on FreeBSD 6.x and 7.x - 32 bit and 64 bit systems.
# May work on OpenBSD / NetBSD.
# -------------------------------------------------------------------------
# Copyright (c) 2007 nixCraft project <http://www.cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# ----------------------------------------------------------------------
### SETUP BIN PATHS ###
MYSQLADMIN=/usr/local/bin/mysqladmin
MYSQL=/usr/local/bin/mysql
LOGGER=/usr/bin/logger
MYSQLDUMP=/usr/local/bin/mysqldump
MKDIR=/bin/mkdir
CP=/bin/cp
GZIP=/usr/bin/gzip
CUT=/usr/bin/cut
AWK=/usr/bin/awk
MOUNT=/sbin/mount
GREP=/usr/bin/grep
UMOUNT=/sbin/umount
MSMBFS=/usr/sbin/mount_smbfs
HOST=/usr/bin/host
TAIL=/usr/bin/tail
SSH=/usr/bin/ssh
SCP=/usr/bin/scp
HOSTNAME=/bin/hostname
 
### SETUP MYSQL LOGIN ###
MUSER=root
MPASS='PASSWORD'
MHOST="127.0.0.1"
 
### SETUP NAS LOGIN ###
NASUSER=vivek
NASPASSWORD=myPassword
NASSERVER=nas05.vip.nixcraft.com
NASMNT=/nas05
NASSHARE=$NASUSER
NASPASSWDFILE=$HOME/.nsmbrc
#GET NAS IP
NASIP=$($HOST $NASSERVER  | $TAIL -1 | $AWK '{ print $4}')
# NAS BACKUP PATH
MBAKPATH=${NASMNT}/$(hostname -s)/mysql
NOW=$(date +"%d-%m-%Y")
TIME_FORMAT='%H_%M_%S%P'
 
mount_nas(){
	[ ! -d $NASMNT ] && $MKDIR -p $NASMNT
	$MOUNT | $GREP $NASMNT >/dev/null
	if [ $? -ne 0 ]
	then
		echo "[$NASIP:$NASUSER]"  >$NASPASSWDFILE
		echo "password=$NASPASSWORD" >>$NASPASSWDFILE
		$MSMBFS -N -I $NASSERVER //$NASUSER@$NASIP/$NASSHARE $NASMNT
	fi
}
 
umount_nas(){
	$MOUNT | $GREP $NASMNT >/dev/null
	[ $? -eq 0 ] && $UMOUNT $NASMNT 
}
 
backup_mysql(){
	$LOGGER "$(basename $0) mysql: Started at $(date)"
	local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
	local db="";
	[ ! -d $MBAKPATH/$NOW ] && $MKDIR -p $MBAKPATH/$NOW
	for db in $DBS
	do
		local tTime=$(date +"${TIME_FORMAT}")
		local FILE="${MBAKPATH}/$NOW/${db}.${tTime}.gz"
		$MYSQLDUMP -u $MUSER -h $MHOST -p"$MPASS" $db | $GZIP -9 > $FILE
		#mysql_file_hook $FILE
	done
	$LOGGER "$(basename $0) mysql: Ended at $(date)"
}
 
# process each sql database file and backup to another server via ssh
# must have ssh keys
mysql_file_hook(){
	local f="$1"
	local d=/nas/mysqlbackup/$(hostname -s)/$NOW
	$SSH someuser@remote.nixcraft.com mkdir -p $d
	$SCP $f someuser@remote.nixcraft.com:$d
}
 
case "$1" in
        mysql)
		mount_nas
                backup_mysql
		umount_nas
                ;;
        mount)
		mount_nas;;
        umount)
		umount_nas;;
        *)
                echo "Usage: $0 {mysql|mount|umount}"
esac
Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics:
Category List of Unix and Linux commands
File Management cat
Firewall Alpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilities dig host ip nmap
OpenVPN CentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Manager apk apt
Processes Management bg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searching grep whereis which
User Information groups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPN Alpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
2 comments… add one
  • oussama larhmich Sep 6, 2010 @ 10:49

    thinks is very nice article :)

  • simplex Jun 9, 2009 @ 20:29

    I’ve used it as-is and the mysql part for another backup script, works great.
    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.