Backup local MySQL Databases

Nice script by Xavez. Put it in a daily cronjob if you like, or extend it to copy the backup to another machine:

#!/bin/bash

# Path to where you want to backup mysql databases.
opath=/Users/username/Sites/Backups/mysql/

# Local mysql details. Make a username with only read access. Allow SELECT, LOCK TABLES.
mysqlhost=127.0.0.1
username=read_only_user
password=read_only_password

# Get current date and temporary directory.
date=$(date "+%Y-%m-%d-%H%M%S")
cpath=$opath/incomplete_back-${date}

	if [ -d $cpath ]
	then
		filler="just some action to prevent syntax error"
	else
		echo Creating $cpath
		mkdir -p $cpath
	fi
	
	#
	# Make backups. Adjust paths to binaries if necessary.
	#
	/usr/local/mysql/bin/mysql -s -r -u${username} -p${password} -e 'show databases' | while read db; do /usr/local/mysql/bin/mysqldump -u${username} -p${password} $db -r ${cpath}/${db}.sql; [[ $? -eq 0 ]] && /usr/bin/gzip ${cpath}/${db}.sql; done
	
	#
	# Symlink to latest backup.
	#
	mv $opath/incomplete_back-$date $opath/back-$date \
	&& rm -f $opath/current \
	&& ln -s back-$date $opath/current

Elsewhere , ,

Leave a Reply

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